Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -3258,36 +3258,31 @@ ** Apart from that, we have little to go on besides intuition as to ** how aiRowEst[] should be initialized. The numbers generated here ** are based on typical values found in actual indices. */ void sqlite3DefaultRowEst(Index *pIdx){ -#if 0 - tRowcnt *a = pIdx->aiRowEst; - int i; - tRowcnt n; - assert( a!=0 ); - a[0] = pIdx->pTable->nRowEst; - if( a[0]<10 ) a[0] = 10; - n = 10; - for(i=1; i<=pIdx->nKeyCol; i++){ - a[i] = n; - if( n>5 ) n--; - } - if( pIdx->onError!=OE_None ){ - a[pIdx->nKeyCol] = 1; - } -#endif - /* 1000000, 10, 9, 8, 7, 6, 5, 4, 3, 2 */ - LogEst aVal[] = { 33, 32, 30, 28, 26, 23, 20, 16, 10 }; + /* 10, 9, 8, 7, 6 */ + LogEst aVal[] = { 33, 32, 30, 28, 26 }; LogEst *a = pIdx->aiRowLogEst; int nCopy = MIN(ArraySize(aVal), pIdx->nKeyCol); + int i; + /* Set the first entry (number of rows in the index) to the estimated + ** number of rows in the table. Or 10, if the estimated number of rows + ** in the table is less than that. */ a[0] = pIdx->pTable->nRowLogEst; + if( a[0]<33 ) a[0] = 33; assert( 33==sqlite3LogEst(10) ); + + /* Estimate that a[1] is 10, a[2] is 9, a[3] is 8, a[4] is 7, a[5] is + ** 6 and each subsequent value (if any) is 5. */ memcpy(&a[1], aVal, nCopy*sizeof(LogEst)); - if( pIdx->onError!=OE_None ){ - a[pIdx->nKeyCol] = 0; + for(i=nCopy+1; i<=pIdx->nKeyCol; i++){ + a[i] = 23; assert( 23==sqlite3LogEst(5) ); } + + assert( 0==sqlite3LogEst(1) ); + if( pIdx->onError!=OE_None ) a[pIdx->nKeyCol] = 0; } /* ** This routine will drop an existing named index. This routine ** implements the DROP INDEX statement. Index: test/cost.test ================================================================== --- test/cost.test +++ test/cost.test @@ -189,10 +189,55 @@ 0 0 2 {SCAN TABLE track} 0 1 0 {SEARCH TABLE album USING INTEGER PRIMARY KEY (rowid=?)} 0 2 1 {SEARCH TABLE composer USING INTEGER PRIMARY KEY (rowid=?)} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } + +#------------------------------------------------------------------------- +# +do_execsql_test 9.1 { + CREATE TABLE t1( + a,b,c,d,e, f,g,h,i,j, + k,l,m,n,o, p,q,r,s,t + ); + CREATE INDEX i1 ON t1(k,l,m,n,o,p,q,r,s,t); +} +do_test 9.2 { + for {set i 0} {$i < 100} {incr i} { + execsql { INSERT INTO t1 DEFAULT VALUES } + } + execsql { + ANALYZE; + CREATE INDEX i2 ON t1(a,b,c,d,e,f,g,h,i,j); + } +} {} + +set L [list a=? b=? c=? d=? e=? f=? g=? h=? i=? j=?] +foreach {tn nTerm nRow} { + 1 1 10 + 2 2 9 + 3 3 8 + 4 4 7 + 5 5 6 + 6 6 5 + 7 7 5 + 8 8 5 + 9 9 5 + 10 10 5 +} { + set w [join [lrange $L 0 [expr $nTerm-1]] " AND "] + set p1 [expr ($nRow-1) / 100.0] + set p2 [expr ($nRow+1) / 100.0] + + set sql1 "SELECT * FROM t1 WHERE likelihood(k=?, $p1) AND $w" + set sql2 "SELECT * FROM t1 WHERE likelihood(k=?, $p2) AND $w" + + do_eqp_test 9.3.$tn.1 $sql1 {/INDEX i1/} + do_eqp_test 9.3.$tn.2 $sql2 {/INDEX i2/} +} + + finish_test