Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not use virtual (and hence redundant) WHERE-clause terms to restrict the content of a automatic partial index. Show when an automatic partial index is used in the EXPLAIN QUERY PLAN output. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | b9ad601eab1d7298d369267eb697c7fa1bc16985 |
| User & Date: | drh 2014-10-25 12:28:25 |
Context
|
2014-10-25
| ||
| 13:42 | Increase the resolution of the second parameter to the likelihood() SQL function (the probability value) so that it can handle probabilities as small as 0.00000001. Formerly, it ran out of precision at 0.001. check-in: 0f08924f user: drh tags: trunk | |
| 12:28 | Do not use virtual (and hence redundant) WHERE-clause terms to restrict the content of a automatic partial index. Show when an automatic partial index is used in the EXPLAIN QUERY PLAN output. check-in: b9ad601e user: drh tags: trunk | |
|
2014-10-24
| ||
| 19:28 | Enhance the automatic index logic so that it creates a partial index when doing so gives the same answer for less work. UPDATE: This change introduced a bug described by ticket [2326c258d02ead33]. check-in: d95d0313 user: drh tags: trunk | |
Changes
Changes to ext/rtree/rtree6.test.
98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1}
0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
}
do_eqp_test rtree6.2.4.2 {
SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
} {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1}
0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
}
do_eqp_test rtree6.2.5 {
SELECT * FROM t1,t2 WHERE k=ii AND x1<v
} {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:}
0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
|
| |
98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1}
0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
}
do_eqp_test rtree6.2.4.2 {
SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
} {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1}
0 1 1 {SEARCH TABLE t2 USING AUTOMATIC PARTIAL COVERING INDEX (v=?)}
}
do_eqp_test rtree6.2.5 {
SELECT * FROM t1,t2 WHERE k=ii AND x1<v
} {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:}
0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
|
Changes to src/where.c.
1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 .... 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 .... 2861 2862 2863 2864 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 |
nKeyCol = 0;
pTable = pSrc->pTab;
pWCEnd = &pWC->a[pWC->nTerm];
pLoop = pLevel->pWLoop;
idxCols = 0;
for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
if( pLoop->prereq==0
&& sqlite3ExprIsTableConstant(pTerm->pExpr, pSrc->iCursor) ){
pPartial = sqlite3ExprAnd(pParse->db, pPartial,
sqlite3ExprDup(pParse->db, pTerm->pExpr, 0));
}
if( termCanDriveIndex(pTerm, pSrc, notReady) ){
int iCol = pTerm->u.leftColumn;
Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
................................................................................
/* Fill the automatic index with content */
sqlite3ExprCachePush(pParse);
addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur); VdbeCoverage(v);
if( pPartial ){
iContinue = sqlite3VdbeMakeLabel(v);
sqlite3ExprIfFalse(pParse, pPartial, iContinue, SQLITE_JUMPIFNULL);
}
regRecord = sqlite3GetTempReg(pParse);
sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 0, 0, 0, 0);
sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
if( pPartial ) sqlite3VdbeResolveLabel(v, iContinue);
sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1); VdbeCoverage(v);
................................................................................
assert( pLoop->u.btree.pIndex!=0 );
pIdx = pLoop->u.btree.pIndex;
assert( !(flags&WHERE_AUTO_INDEX) || (flags&WHERE_IDX_ONLY) );
if( !HasRowid(pItem->pTab) && IsPrimaryKeyIndex(pIdx) ){
if( isSearch ){
zFmt = "PRIMARY KEY";
}
}else if( flags & WHERE_AUTO_INDEX ){
zFmt = "AUTOMATIC COVERING INDEX";
}else if( flags & WHERE_IDX_ONLY ){
zFmt = "COVERING INDEX %s";
}else{
zFmt = "INDEX %s";
}
|
> > > > |
1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 .... 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 .... 2863 2864 2865 2866 2867 2868 2869 2870 2871 2872 2873 2874 2875 2876 2877 2878 |
nKeyCol = 0;
pTable = pSrc->pTab;
pWCEnd = &pWC->a[pWC->nTerm];
pLoop = pLevel->pWLoop;
idxCols = 0;
for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
if( pLoop->prereq==0
&& (pTerm->wtFlags & TERM_VIRTUAL)==0
&& sqlite3ExprIsTableConstant(pTerm->pExpr, pSrc->iCursor) ){
pPartial = sqlite3ExprAnd(pParse->db, pPartial,
sqlite3ExprDup(pParse->db, pTerm->pExpr, 0));
}
if( termCanDriveIndex(pTerm, pSrc, notReady) ){
int iCol = pTerm->u.leftColumn;
Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
................................................................................
/* Fill the automatic index with content */
sqlite3ExprCachePush(pParse);
addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur); VdbeCoverage(v);
if( pPartial ){
iContinue = sqlite3VdbeMakeLabel(v);
sqlite3ExprIfFalse(pParse, pPartial, iContinue, SQLITE_JUMPIFNULL);
pLoop->wsFlags |= WHERE_PARTIALIDX;
}
regRecord = sqlite3GetTempReg(pParse);
sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 0, 0, 0, 0);
sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
if( pPartial ) sqlite3VdbeResolveLabel(v, iContinue);
sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1); VdbeCoverage(v);
................................................................................
assert( pLoop->u.btree.pIndex!=0 );
pIdx = pLoop->u.btree.pIndex;
assert( !(flags&WHERE_AUTO_INDEX) || (flags&WHERE_IDX_ONLY) );
if( !HasRowid(pItem->pTab) && IsPrimaryKeyIndex(pIdx) ){
if( isSearch ){
zFmt = "PRIMARY KEY";
}
}else if( flags & WHERE_PARTIALIDX ){
zFmt = "AUTOMATIC PARTIAL COVERING INDEX";
}else if( flags & WHERE_AUTO_INDEX ){
zFmt = "AUTOMATIC COVERING INDEX";
}else if( flags & WHERE_IDX_ONLY ){
zFmt = "COVERING INDEX %s";
}else{
zFmt = "INDEX %s";
}
|
Changes to src/whereInt.h.
455 456 457 458 459 460 461 |
#define WHERE_VIRTUALTABLE 0x00000400 /* WhereLoop.u.vtab is valid */ #define WHERE_IN_ABLE 0x00000800 /* Able to support an IN operator */ #define WHERE_ONEROW 0x00001000 /* Selects no more than one row */ #define WHERE_MULTI_OR 0x00002000 /* OR using multiple indices */ #define WHERE_AUTO_INDEX 0x00004000 /* Uses an ephemeral index */ #define WHERE_SKIPSCAN 0x00008000 /* Uses the skip-scan algorithm */ #define WHERE_UNQ_WANTED 0x00010000 /* WHERE_ONEROW would have been helpful*/ |
> |
455 456 457 458 459 460 461 462 |
#define WHERE_VIRTUALTABLE 0x00000400 /* WhereLoop.u.vtab is valid */
#define WHERE_IN_ABLE 0x00000800 /* Able to support an IN operator */
#define WHERE_ONEROW 0x00001000 /* Selects no more than one row */
#define WHERE_MULTI_OR 0x00002000 /* OR using multiple indices */
#define WHERE_AUTO_INDEX 0x00004000 /* Uses an ephemeral index */
#define WHERE_SKIPSCAN 0x00008000 /* Uses the skip-scan algorithm */
#define WHERE_UNQ_WANTED 0x00010000 /* WHERE_ONEROW would have been helpful*/
#define WHERE_PARTIALIDX 0x00020000 /* The automatic index is partial */
|