/ Check-in [e2d42f90]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Test that the default values used when sqlite_stat1 data is not available are calculated correctly. Fixes for the same.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental-costs
Files: files | file ages | folders
SHA1: e2d42f909de85a0586389f2dc0e654f7af2e351a
User & Date: dan 2014-04-29 19:01:57
Context
2014-04-30
13:19
Fix long-standing typos in comments. check-in: b9f91317 user: drh tags: experimental-costs
2014-04-29
19:01
Test that the default values used when sqlite_stat1 data is not available are calculated correctly. Fixes for the same. check-in: e2d42f90 user: dan tags: experimental-costs
16:46
Merge trunk changes into this branch. check-in: d74299f0 user: dan tags: experimental-costs
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/build.c.

  3256   3256   **           aiRowEst[N]>=1
  3257   3257   **
  3258   3258   ** Apart from that, we have little to go on besides intuition as to
  3259   3259   ** how aiRowEst[] should be initialized.  The numbers generated here
  3260   3260   ** are based on typical values found in actual indices.
  3261   3261   */
  3262   3262   void sqlite3DefaultRowEst(Index *pIdx){
  3263         -#if 0
  3264         -  tRowcnt *a = pIdx->aiRowEst;
  3265         -  int i;
  3266         -  tRowcnt n;
  3267         -  assert( a!=0 );
  3268         -  a[0] = pIdx->pTable->nRowEst;
  3269         -  if( a[0]<10 ) a[0] = 10;
  3270         -  n = 10;
  3271         -  for(i=1; i<=pIdx->nKeyCol; i++){
  3272         -    a[i] = n;
  3273         -    if( n>5 ) n--;
  3274         -  }
  3275         -  if( pIdx->onError!=OE_None ){
  3276         -    a[pIdx->nKeyCol] = 1;
  3277         -  }
  3278         -#endif
  3279         -  /*            1000000, 10,  9,  8,  7,  6,  5,  4,  3,  2 */
  3280         -  LogEst aVal[] = { 33, 32, 30, 28, 26, 23, 20, 16, 10 };
         3263  +  /*                10,  9,  8,  7,  6 */
         3264  +  LogEst aVal[] = { 33, 32, 30, 28, 26 };
  3281   3265     LogEst *a = pIdx->aiRowLogEst;
  3282   3266     int nCopy = MIN(ArraySize(aVal), pIdx->nKeyCol);
         3267  +  int i;
  3283   3268   
         3269  +  /* Set the first entry (number of rows in the index) to the estimated 
         3270  +  ** number of rows in the table. Or 10, if the estimated number of rows 
         3271  +  ** in the table is less than that.  */
  3284   3272     a[0] = pIdx->pTable->nRowLogEst;
         3273  +  if( a[0]<33 ) a[0] = 33;        assert( 33==sqlite3LogEst(10) );
         3274  +
         3275  +  /* Estimate that a[1] is 10, a[2] is 9, a[3] is 8, a[4] is 7, a[5] is
         3276  +  ** 6 and each subsequent value (if any) is 5.  */
  3285   3277     memcpy(&a[1], aVal, nCopy*sizeof(LogEst));
  3286         -  if( pIdx->onError!=OE_None ){
  3287         -    a[pIdx->nKeyCol] = 0;
         3278  +  for(i=nCopy+1; i<=pIdx->nKeyCol; i++){
         3279  +    a[i] = 23;                    assert( 23==sqlite3LogEst(5) );
  3288   3280     }
         3281  +
         3282  +  assert( 0==sqlite3LogEst(1) );
         3283  +  if( pIdx->onError!=OE_None ) a[pIdx->nKeyCol] = 0;
  3289   3284   }
  3290   3285   
  3291   3286   /*
  3292   3287   ** This routine will drop an existing named index.  This routine
  3293   3288   ** implements the DROP INDEX statement.
  3294   3289   */
  3295   3290   void sqlite3DropIndex(Parse *pParse, SrcList *pName, int ifExists){

Changes to test/cost.test.

   187    187        AND unlikely(album.aid=track.aid);
   188    188   } {
   189    189     0 0 2 {SCAN TABLE track} 
   190    190     0 1 0 {SEARCH TABLE album USING INTEGER PRIMARY KEY (rowid=?)}
   191    191     0 2 1 {SEARCH TABLE composer USING INTEGER PRIMARY KEY (rowid=?)}
   192    192     0 0 0 {USE TEMP B-TREE FOR DISTINCT}
   193    193   }
          194  +
          195  +#-------------------------------------------------------------------------
          196  +#
          197  +do_execsql_test 9.1 {
          198  +  CREATE TABLE t1(
          199  +    a,b,c,d,e, f,g,h,i,j,
          200  +    k,l,m,n,o, p,q,r,s,t
          201  +  );
          202  +  CREATE INDEX i1 ON t1(k,l,m,n,o,p,q,r,s,t);
          203  +}
          204  +do_test 9.2 {
          205  +  for {set i 0} {$i < 100} {incr i} {
          206  +    execsql { INSERT INTO t1 DEFAULT VALUES }
          207  +  }
          208  +  execsql {
          209  +    ANALYZE;
          210  +    CREATE INDEX i2 ON t1(a,b,c,d,e,f,g,h,i,j);
          211  +  }
          212  +} {}
          213  +
          214  +set L [list a=? b=? c=? d=? e=? f=? g=? h=? i=? j=?]
          215  +foreach {tn nTerm nRow} {
          216  +  1   1 10
          217  +  2   2  9
          218  +  3   3  8
          219  +  4   4  7
          220  +  5   5  6
          221  +  6   6  5
          222  +  7   7  5
          223  +  8   8  5
          224  +  9   9  5
          225  +  10 10  5
          226  +} {
          227  +  set w [join [lrange $L 0 [expr $nTerm-1]] " AND "]
          228  +  set p1 [expr ($nRow-1) / 100.0]
          229  +  set p2 [expr ($nRow+1) / 100.0]
          230  +
          231  +  set sql1 "SELECT * FROM t1 WHERE likelihood(k=?, $p1) AND $w"
          232  +  set sql2 "SELECT * FROM t1 WHERE likelihood(k=?, $p2) AND $w"
          233  +
          234  +  do_eqp_test 9.3.$tn.1 $sql1 {/INDEX i1/}
          235  +  do_eqp_test 9.3.$tn.2 $sql2 {/INDEX i2/}
          236  +}
          237  +
          238  +
   194    239   
   195    240   finish_test
   196    241   
   197    242   
   198    243