/ Check-in [00fe0950]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Adding the "noskipscan" token to an sqlite_stat1.stat field prevents an index for being used with the skip-scan algorithm.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | noskipscan-token
Files: files | file ages | folders
SHA1: 00fe09505792cd0d104b2da9d040f023e30fa871
User & Date: drh 2014-11-15 19:08:13
Context
2014-11-18
21:54
Adding the "noskipscan" token to an sqlite_stat1.stat field prevents an index for being used with the skip-scan algorithm. check-in: 4461bf04 user: drh tags: trunk
2014-11-15
19:08
Adding the "noskipscan" token to an sqlite_stat1.stat field prevents an index for being used with the skip-scan algorithm. Closed-Leaf check-in: 00fe0950 user: drh tags: noskipscan-token
2014-11-14
15:28
Do not automatically remove the DISTINCT keyword from "a IN (SELECT DISTINCT ...)" expressions. Fix for [db87229497]. check-in: 55e453aa user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

1462
1463
1464
1465
1466
1467
1468


1469
1470
1471
1472
1473
1474
1475
  if( pIndex )
#endif
  while( z[0] ){
    if( sqlite3_strglob("unordered*", z)==0 ){
      pIndex->bUnordered = 1;
    }else if( sqlite3_strglob("sz=[0-9]*", z)==0 ){
      pIndex->szIdxRow = sqlite3LogEst(sqlite3Atoi(z+3));


    }
#ifdef SQLITE_ENABLE_COSTMULT
    else if( sqlite3_strglob("costmult=[0-9]*",z)==0 ){
      pIndex->pTable->costMult = sqlite3LogEst(sqlite3Atoi(z+9));
    }
#endif
    while( z[0]!=0 && z[0]!=' ' ) z++;







>
>







1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
  if( pIndex )
#endif
  while( z[0] ){
    if( sqlite3_strglob("unordered*", z)==0 ){
      pIndex->bUnordered = 1;
    }else if( sqlite3_strglob("sz=[0-9]*", z)==0 ){
      pIndex->szIdxRow = sqlite3LogEst(sqlite3Atoi(z+3));
    }else if( sqlite3_strglob("noskipscan*", z)==0 ){
      pIndex->noSkipScan = 1;
    }
#ifdef SQLITE_ENABLE_COSTMULT
    else if( sqlite3_strglob("costmult=[0-9]*",z)==0 ){
      pIndex->pTable->costMult = sqlite3LogEst(sqlite3Atoi(z+9));
    }
#endif
    while( z[0]!=0 && z[0]!=' ' ) z++;

Changes to src/sqliteInt.h.

1791
1792
1793
1794
1795
1796
1797

1798
1799
1800
1801
1802
1803
1804
  u16 nColumn;             /* Number of columns stored in the index */
  u8 onError;              /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  unsigned idxType:2;      /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */
  unsigned bUnordered:1;   /* Use this index for == or IN queries only */
  unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
  unsigned isResized:1;    /* True if resizeIndexObject() has been called */
  unsigned isCovering:1;   /* True if this is a covering index */

#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  int nSample;             /* Number of elements in aSample[] */
  int nSampleCol;          /* Size of IndexSample.anEq[] and so on */
  tRowcnt *aAvgEq;         /* Average nEq values for keys not in aSample */
  IndexSample *aSample;    /* Samples of the left-most key */
  tRowcnt *aiRowEst;       /* Non-logarithmic stat1 data for this index */
  tRowcnt nRowEst0;        /* Non-logarithmic number of rows in the index */







>







1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
  u16 nColumn;             /* Number of columns stored in the index */
  u8 onError;              /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */
  unsigned idxType:2;      /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */
  unsigned bUnordered:1;   /* Use this index for == or IN queries only */
  unsigned uniqNotNull:1;  /* True if UNIQUE and NOT NULL for all columns */
  unsigned isResized:1;    /* True if resizeIndexObject() has been called */
  unsigned isCovering:1;   /* True if this is a covering index */
  unsigned noSkipScan:1;   /* Do not try to use skip-scan if true */
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
  int nSample;             /* Number of elements in aSample[] */
  int nSampleCol;          /* Size of IndexSample.anEq[] and so on */
  tRowcnt *aAvgEq;         /* Average nEq values for keys not in aSample */
  IndexSample *aSample;    /* Samples of the left-most key */
  tRowcnt *aiRowEst;       /* Non-logarithmic stat1 data for this index */
  tRowcnt nRowEst0;        /* Non-logarithmic number of rows in the index */

Changes to src/where.c.

4583
4584
4585
4586
4587
4588
4589

4590
4591
4592
4593
4594
4595
4596
  ** contains fewer than 2^17 rows we assume otherwise in other parts of
  ** the code). And, even if it is not, it should not be too much slower. 
  ** On the other hand, the extra seeks could end up being significantly
  ** more expensive.  */
  assert( 42==sqlite3LogEst(18) );
  if( saved_nEq==saved_nSkip
   && saved_nEq+1<pProbe->nKeyCol

   && pProbe->aiRowLogEst[saved_nEq+1]>=42  /* TUNING: Minimum for skip-scan */
   && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
  ){
    LogEst nIter;
    pNew->u.btree.nEq++;
    pNew->nSkip++;
    pNew->aLTerm[pNew->nLTerm++] = 0;







>







4583
4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
  ** contains fewer than 2^17 rows we assume otherwise in other parts of
  ** the code). And, even if it is not, it should not be too much slower. 
  ** On the other hand, the extra seeks could end up being significantly
  ** more expensive.  */
  assert( 42==sqlite3LogEst(18) );
  if( saved_nEq==saved_nSkip
   && saved_nEq+1<pProbe->nKeyCol
   && pProbe->noSkipScan==0
   && pProbe->aiRowLogEst[saved_nEq+1]>=42  /* TUNING: Minimum for skip-scan */
   && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
  ){
    LogEst nIter;
    pNew->u.btree.nEq++;
    pNew->nSkip++;
    pNew->aLTerm[pNew->nLTerm++] = 0;

Changes to test/skipscan1.test.

268
269
270
271
272
273
274
275



















276
} {/ANY.a. AND b=/}
do_execsql_test skipscan1-6.3 {
  -- Two distinct values for the skip-scan column again.  Skip-scan is not used.
  UPDATE sqlite_stat1 SET stat='500000 125000 62500';
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
} {~/ANY/}




















finish_test








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

268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
} {/ANY.a. AND b=/}
do_execsql_test skipscan1-6.3 {
  -- Two distinct values for the skip-scan column again.  Skip-scan is not used.
  UPDATE sqlite_stat1 SET stat='500000 125000 62500';
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
} {~/ANY/}

# If the sqlite_stat1 entry includes the "noskipscan" token, then never use
# skipscan with that index.
#
do_execsql_test skipscan1-7.1 {
  UPDATE sqlite_stat1 SET stat='500000 125000 1 sz=100';
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
} {/ANY/}
do_execsql_test skipscan1-7.2 {
  UPDATE sqlite_stat1 SET stat='500000 125000 1 noskipscan sz=100';
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
} {~/ANY/}
do_execsql_test skipscan1-7.3 {
  UPDATE sqlite_stat1 SET stat='500000 125000 1 sz=100 noskipscan';
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
} {~/ANY/}

finish_test