Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add the WHERE_IN_EARLYOUT flag and use it to clarify the logic of this optimization. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | multikey-opt-idea |
| Files: | files | file ages | folders |
| SHA3-256: |
522f1eacc20f11002cad58232a7c2610 |
| User & Date: | drh 2018-06-07 14:59:22 |
Context
|
2018-06-07
| ||
| 15:28 | Merge the ".stat/.eqp" CLI fix from trunk. check-in: a91cad33 user: drh tags: multikey-opt-idea | |
| 14:59 | Add the WHERE_IN_EARLYOUT flag and use it to clarify the logic of this optimization. check-in: 522f1eac user: drh tags: multikey-opt-idea | |
| 14:32 | Merge recent trunk enhancements. check-in: e9d7bf4f user: drh tags: multikey-opt-idea | |
Changes
Changes to src/where.c.
5080 5081 5082 5083 5084 5085 5086 5087 5088 5089 5090 5091 5092 5093 |
struct InLoop *pIn;
int j;
sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){
sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
if( pIn->eEndLoopOp!=OP_Noop ){
if( pIn->nPrefix ){
sqlite3VdbeAddOp4Int(v, OP_IfNoHope, pLevel->iIdxCur,
sqlite3VdbeCurrentAddr(v)+2,
pIn->iBase, pIn->nPrefix);
VdbeCoverage(v);
}
sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop);
VdbeCoverage(v);
|
> |
5080 5081 5082 5083 5084 5085 5086 5087 5088 5089 5090 5091 5092 5093 5094 |
struct InLoop *pIn;
int j;
sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){
sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
if( pIn->eEndLoopOp!=OP_Noop ){
if( pIn->nPrefix ){
assert( pLoop->wsFlags & WHERE_IN_EARLYOUT );
sqlite3VdbeAddOp4Int(v, OP_IfNoHope, pLevel->iIdxCur,
sqlite3VdbeCurrentAddr(v)+2,
pIn->iBase, pIn->nPrefix);
VdbeCoverage(v);
}
sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop);
VdbeCoverage(v);
|
Changes to src/whereInt.h.
553 554 555 556 557 558 559 |
#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 */ |
> |
553 554 555 556 557 558 559 560 |
#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 */
#define WHERE_IN_EARLYOUT 0x00040000 /* Perhaps quit IN loops early */
|
Changes to src/wherecode.c.
591 592 593 594 595 596 597 598 599 600 601 602 603 604 .... 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 .... 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 |
sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v);
if( i==iEq ){
pIn->iCur = iTab;
pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen;
if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 ){
pIn->iBase = iReg - i;
pIn->nPrefix = i;
}else{
pIn->nPrefix = 0;
}
}else{
pIn->eEndLoopOp = OP_Noop;
}
pIn++;
................................................................................
}
codeApplyAffinity(pParse, regBase, nConstraint - bSeekPastNull, zStartAff);
if( pLoop->nSkip>0 && nConstraint==pLoop->nSkip ){
/* The skip-scan logic inside the call to codeAllEqualityConstraints()
** above has already left the cursor sitting on the correct row,
** so no further seeking is needed */
}else{
if( pLoop->wsFlags & WHERE_IN_ABLE ){
sqlite3VdbeAddOp1(v, OP_SeekHit, iIdxCur);
}
op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev];
assert( op!=0 );
sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
VdbeCoverage(v);
VdbeCoverageIf(v, op==OP_Rewind); testcase( op==OP_Rewind );
................................................................................
sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
testcase( op==OP_IdxGT ); VdbeCoverageIf(v, op==OP_IdxGT );
testcase( op==OP_IdxGE ); VdbeCoverageIf(v, op==OP_IdxGE );
testcase( op==OP_IdxLT ); VdbeCoverageIf(v, op==OP_IdxLT );
testcase( op==OP_IdxLE ); VdbeCoverageIf(v, op==OP_IdxLE );
}
if( pLoop->wsFlags & WHERE_IN_ABLE ){
sqlite3VdbeAddOp2(v, OP_SeekHit, iIdxCur, 1);
}
/* Seek the table cursor, if required */
if( omitTable ){
/* pIdx is a covering index. No need to access the main table. */
}else if( HasRowid(pIdx->pTable) ){
|
> | | |
591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 .... 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 .... 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 |
sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v);
if( i==iEq ){
pIn->iCur = iTab;
pIn->eEndLoopOp = bRev ? OP_PrevIfOpen : OP_NextIfOpen;
if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 ){
pIn->iBase = iReg - i;
pIn->nPrefix = i;
pLoop->wsFlags |= WHERE_IN_EARLYOUT;
}else{
pIn->nPrefix = 0;
}
}else{
pIn->eEndLoopOp = OP_Noop;
}
pIn++;
................................................................................
}
codeApplyAffinity(pParse, regBase, nConstraint - bSeekPastNull, zStartAff);
if( pLoop->nSkip>0 && nConstraint==pLoop->nSkip ){
/* The skip-scan logic inside the call to codeAllEqualityConstraints()
** above has already left the cursor sitting on the correct row,
** so no further seeking is needed */
}else{
if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){
sqlite3VdbeAddOp1(v, OP_SeekHit, iIdxCur);
}
op = aStartOp[(start_constraints<<2) + (startEq<<1) + bRev];
assert( op!=0 );
sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
VdbeCoverage(v);
VdbeCoverageIf(v, op==OP_Rewind); testcase( op==OP_Rewind );
................................................................................
sqlite3VdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase, nConstraint);
testcase( op==OP_IdxGT ); VdbeCoverageIf(v, op==OP_IdxGT );
testcase( op==OP_IdxGE ); VdbeCoverageIf(v, op==OP_IdxGE );
testcase( op==OP_IdxLT ); VdbeCoverageIf(v, op==OP_IdxLT );
testcase( op==OP_IdxLE ); VdbeCoverageIf(v, op==OP_IdxLE );
}
if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){
sqlite3VdbeAddOp2(v, OP_SeekHit, iIdxCur, 1);
}
/* Seek the table cursor, if required */
if( omitTable ){
/* pIdx is a covering index. No need to access the main table. */
}else if( HasRowid(pIdx->pTable) ){
|