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: |
bc985caa7816f1f873ad8e4467c52783 |
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
Changes to src/where.c.
︙ | ︙ | |||
3777 3778 3779 3780 3781 3782 3783 | 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 ){ | | | > > > | > | 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 | ** 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) ); | < | > > > > > > > > | 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 |