/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/build.c.

3256
3257
3258
3259
3260
3261
3262
3263
3264
3265
3266
3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277
3278
3279
3280
3281
3282

3283



3284




3285
3286
3287

3288



3289
3290
3291
3292
3293
3294
3295
**           aiRowEst[N]>=1
**
** 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 };
  LogEst *a = pIdx->aiRowLogEst;
  int nCopy = MIN(ArraySize(aVal), pIdx->nKeyCol);





  a[0] = pIdx->pTable->nRowLogEst;




  memcpy(&a[1], aVal, nCopy*sizeof(LogEst));
  if( pIdx->onError!=OE_None ){
    a[pIdx->nKeyCol] = 0;

  }



}

/*
** This routine will drop an existing named index.  This routine
** implements the DROP INDEX statement.
*/
void sqlite3DropIndex(Parse *pParse, SrcList *pName, int ifExists){







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
|


>

>
>
>

>
>
>
>

<
|
>

>
>
>







3256
3257
3258
3259
3260
3261
3262
















3263
3264
3265
3266
3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277

3278
3279
3280
3281
3282
3283
3284
3285
3286
3287
3288
3289
3290
**           aiRowEst[N]>=1
**
** 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){
















  /*                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));

  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.
*/
void sqlite3DropIndex(Parse *pParse, SrcList *pName, int ifExists){

Changes to test/cost.test.

187
188
189
190
191
192
193
194













































195
196
197
198
     AND unlikely(album.aid=track.aid);
} {
  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}
}














































finish_test











>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
     AND unlikely(album.aid=track.aid);
} {
  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