/ Check-in [00fe0950]
Login

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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

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

Changes to src/sqliteInt.h.

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

Changes to src/where.c.

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

Changes to test/skipscan1.test.

   268    268   } {/ANY.a. AND b=/}
   269    269   do_execsql_test skipscan1-6.3 {
   270    270     -- Two distinct values for the skip-scan column again.  Skip-scan is not used.
   271    271     UPDATE sqlite_stat1 SET stat='500000 125000 62500';
   272    272     ANALYZE sqlite_master;
   273    273     EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
   274    274   } {~/ANY/}
          275  +
          276  +# If the sqlite_stat1 entry includes the "noskipscan" token, then never use
          277  +# skipscan with that index.
          278  +#
          279  +do_execsql_test skipscan1-7.1 {
          280  +  UPDATE sqlite_stat1 SET stat='500000 125000 1 sz=100';
          281  +  ANALYZE sqlite_master;
          282  +  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
          283  +} {/ANY/}
          284  +do_execsql_test skipscan1-7.2 {
          285  +  UPDATE sqlite_stat1 SET stat='500000 125000 1 noskipscan sz=100';
          286  +  ANALYZE sqlite_master;
          287  +  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
          288  +} {~/ANY/}
          289  +do_execsql_test skipscan1-7.3 {
          290  +  UPDATE sqlite_stat1 SET stat='500000 125000 1 sz=100 noskipscan';
          291  +  ANALYZE sqlite_master;
          292  +  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=1;
          293  +} {~/ANY/}
   275    294   
   276    295   finish_test