SQLite

Check-in [bc985caa78]
Login

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

Overview
Comment:Enhancements to skip-scan such that it is operable when a middle column of an index is skipped while the left-most column is constrained in the WHERE clause.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bc985caa7816f1f873ad8e4467c5278399f315ce
User & Date: drh 2014-08-20 23:38:07.310
Context
2014-08-20
23:42
Increase the version number to 3.8.7 (check-in: 91594aae07 user: drh tags: trunk)
23:38
Enhancements to skip-scan such that it is operable when a middle column of an index is skipped while the left-most column is constrained in the WHERE clause. (check-in: bc985caa78 user: drh tags: trunk)
18:43
A small performance improvement in freeSpace() by special-casing the relatively common case of an empty freelist. (check-in: 49f44d355f user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805



3806

3807
3808
3809
3810
3811
3812
3813
  struct SrcList_item *pItem = pWInfo->pTabList->a + p->iTab;
  Table *pTab = pItem->pTab;
  sqlite3DebugPrintf("%c%2d.%0*llx.%0*llx", p->cId,
                     p->iTab, nb, p->maskSelf, nb, p->prereq);
  sqlite3DebugPrintf(" %12s",
                     pItem->zAlias ? pItem->zAlias : pTab->zName);
  if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){
     const char *zName;
     if( p->u.btree.pIndex && (zName = p->u.btree.pIndex->zName)!=0 ){
      if( strncmp(zName, "sqlite_autoindex_", 17)==0 ){
        int i = sqlite3Strlen30(zName) - 1;
        while( zName[i]!='_' ) i--;
        zName += i;
      }
      sqlite3DebugPrintf(".%-16s %2d", zName, p->u.btree.nEq);
    }else{
      sqlite3DebugPrintf("%20s","");
    }
  }else{
    char *z;
    if( p->u.vtab.idxStr ){
      z = sqlite3_mprintf("(%d,\"%s\",%x)",
                p->u.vtab.idxNum, p->u.vtab.idxStr, p->u.vtab.omitMask);
    }else{
      z = sqlite3_mprintf("(%d,%x)", p->u.vtab.idxNum, p->u.vtab.omitMask);
    }
    sqlite3DebugPrintf(" %-19s", z);
    sqlite3_free(z);
  }



  sqlite3DebugPrintf(" f %05x N %d", p->wsFlags, p->nLTerm);

  sqlite3DebugPrintf(" cost %d,%d,%d\n", p->rSetup, p->rRun, p->nOut);
#ifdef SQLITE_ENABLE_TREE_EXPLAIN
  /* If the 0x100 bit of wheretracing is set, then show all of the constraint
  ** expressions in the WhereLoop.aLTerm[] array.
  */
  if( p->nLTerm && (sqlite3WhereTrace & 0x100)!=0 ){  /* WHERETRACE 0x100 */
    int i;







|
|




















>
>
>
|
>







3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
3817
  struct SrcList_item *pItem = pWInfo->pTabList->a + p->iTab;
  Table *pTab = pItem->pTab;
  sqlite3DebugPrintf("%c%2d.%0*llx.%0*llx", p->cId,
                     p->iTab, nb, p->maskSelf, nb, p->prereq);
  sqlite3DebugPrintf(" %12s",
                     pItem->zAlias ? pItem->zAlias : pTab->zName);
  if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){
    const char *zName;
    if( p->u.btree.pIndex && (zName = p->u.btree.pIndex->zName)!=0 ){
      if( strncmp(zName, "sqlite_autoindex_", 17)==0 ){
        int i = sqlite3Strlen30(zName) - 1;
        while( zName[i]!='_' ) i--;
        zName += i;
      }
      sqlite3DebugPrintf(".%-16s %2d", zName, p->u.btree.nEq);
    }else{
      sqlite3DebugPrintf("%20s","");
    }
  }else{
    char *z;
    if( p->u.vtab.idxStr ){
      z = sqlite3_mprintf("(%d,\"%s\",%x)",
                p->u.vtab.idxNum, p->u.vtab.idxStr, p->u.vtab.omitMask);
    }else{
      z = sqlite3_mprintf("(%d,%x)", p->u.vtab.idxNum, p->u.vtab.omitMask);
    }
    sqlite3DebugPrintf(" %-19s", z);
    sqlite3_free(z);
  }
  if( p->wsFlags & WHERE_SKIPSCAN ){
    sqlite3DebugPrintf(" f %05x %d-%d", p->wsFlags, p->nLTerm,p->u.btree.nSkip);
  }else{
    sqlite3DebugPrintf(" f %05x N %d", p->wsFlags, p->nLTerm);
  }
  sqlite3DebugPrintf(" cost %d,%d,%d\n", p->rSetup, p->rRun, p->nOut);
#ifdef SQLITE_ENABLE_TREE_EXPLAIN
  /* If the 0x100 bit of wheretracing is set, then show all of the constraint
  ** expressions in the WhereLoop.aLTerm[] array.
  */
  if( p->nLTerm && (sqlite3WhereTrace & 0x100)!=0 ){  /* WHERETRACE 0x100 */
    int i;
4312
4313
4314
4315
4316
4317
4318
4319
4320
4321
4322
4323
4324
4325
4326
4327
4328
4329
4330






4331
4332
4333


4334
4335
4336
4337
4338
4339
4340
  ** The magic number 18 is selected on the basis that scanning 17 rows
  ** is almost always quicker than an index seek (even though if the index
  ** 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( pTerm==0
   && 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->u.btree.nSkip++;
    pNew->aLTerm[pNew->nLTerm++] = 0;
    pNew->wsFlags |= WHERE_SKIPSCAN;
    nIter = pProbe->aiRowLogEst[saved_nEq] - pProbe->aiRowLogEst[saved_nEq+1];






    pNew->nOut -= nIter;
    whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nIter + nInMul);
    pNew->nOut = saved_nOut;


  }
  for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
    u16 eOp = pTerm->eOperator;   /* Shorthand for pTerm->eOperator */
    LogEst rCostIdx;
    LogEst nOutUnadjusted;        /* nOut before IN() and WHERE adjustments */
    int nIn = 0;
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4







<
|










>
>
>
>
>
>



>
>







4316
4317
4318
4319
4320
4321
4322

4323
4324
4325
4326
4327
4328
4329
4330
4331
4332
4333
4334
4335
4336
4337
4338
4339
4340
4341
4342
4343
4344
4345
4346
4347
4348
4349
4350
4351
  ** The magic number 18 is selected on the basis that scanning 17 rows
  ** is almost always quicker than an index seek (even though if the index
  ** 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->u.btree.nSkip++;
    pNew->aLTerm[pNew->nLTerm++] = 0;
    pNew->wsFlags |= WHERE_SKIPSCAN;
    nIter = pProbe->aiRowLogEst[saved_nEq] - pProbe->aiRowLogEst[saved_nEq+1];
    if( pTerm ){
      /* TUNING:  When estimating skip-scan for a term that is also indexable,
      ** increase the cost of the skip-scan by 2x, to make it a little less
      ** desirable than the regular index lookup. */
      nIter += 10;  assert( 10==sqlite3LogEst(2) );
    }
    pNew->nOut -= nIter;
    whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nIter + nInMul);
    pNew->nOut = saved_nOut;
    pNew->u.btree.nEq = saved_nEq;
    pNew->u.btree.nSkip = saved_nSkip;
  }
  for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
    u16 eOp = pTerm->eOperator;   /* Shorthand for pTerm->eOperator */
    LogEst rCostIdx;
    LogEst nOutUnadjusted;        /* nOut before IN() and WHERE adjustments */
    int nIn = 0;
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
Added test/skipscan3.test.


















































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
# 2014-08-20
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file implements tests of the "skip-scan" query strategy.
# In particular, this file looks at skipping intermediate terms
# in an index.  For example, if (a,b,c) are indexed, and we have
# "WHERE a=?1 AND c=?2" - verify that skip-scan can still be used.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl

do_execsql_test skipscan3-1.1 {
  CREATE TABLE t1(a,b,c,d,PRIMARY KEY(a,b,c));
  WITH RECURSIVE
    c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
  INSERT INTO t1(a,b,c,d)
    SELECT 1, 1, x, printf('x%04d',x) FROM c;
  ANALYZE;
} {}

# This version has long used skip-scan because of the "+a"
#
do_execsql_test skipscan3-1.2eqp {
  EXPLAIN QUERY PLAN SELECT d FROM t1 WHERE +a=1 AND c=32;
} {/*ANY(a) AND ANY(b)*/}
do_execsql_test skipscan3-1.2 {
  SELECT d FROM t1 WHERE +a=1 AND c=32;
} {x0032}

# This version (with "a" instead of "+a") should use skip-scan but
# did not prior to changes implemented on 2014-08-20
#
do_execsql_test skipscan3-1.3eqp {
  EXPLAIN QUERY PLAN SELECT d FROM t1 WHERE a=1 AND c=32;
} {/*ANY(a) AND ANY(b)*/}
do_execsql_test skipscan3-1.3 {
  SELECT d FROM t1 WHERE a=1 AND c=32;
} {x0032}

# Repeat the test on a WITHOUT ROWID table
#
do_execsql_test skipscan3-2.1 {
  CREATE TABLE t2(a,b,c,d,PRIMARY KEY(a,b,c)) WITHOUT ROWID;
  WITH RECURSIVE
    c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<1000)
  INSERT INTO t2(a,b,c,d)
    SELECT 1, 1, x, printf('x%04d',x) FROM c;
  ANALYZE;
} {}
do_execsql_test skipscan3-2.2eqp {
  EXPLAIN QUERY PLAN SELECT d FROM t2 WHERE +a=1 AND c=32;
} {/*ANY(a) AND ANY(b)*/}
do_execsql_test skipscan3-2.2 {
  SELECT d FROM t2 WHERE +a=1 AND c=32;
} {x0032}
do_execsql_test skipscan3-2.3eqp {
  EXPLAIN QUERY PLAN SELECT d FROM t2 WHERE a=1 AND c=32;
} {/*ANY(a) AND ANY(b)*/}
do_execsql_test skipscan3-2.3 {
  SELECT d FROM t2 WHERE a=1 AND c=32;
} {x0032}

  
finish_test