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 |
| 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.505 |
Context
|
2018-06-07
| ||
| 15:28 | Merge the ".stat/.eqp" CLI fix from trunk. (check-in: a91cad3381 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: 522f1eacc2 user: drh tags: multikey-opt-idea) | |
| 14:32 | Merge recent trunk enhancements. (check-in: e9d7bf4f7b 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 |
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++;
| > | 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 |
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++;
|
| ︙ | ︙ | |||
1660 1661 1662 1663 1664 1665 1666 |
}
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{
| | | 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 |
}
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 );
|
| ︙ | ︙ | |||
1726 1727 1728 1729 1730 1731 1732 |
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 );
}
| | | 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 |
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) ){
|
| ︙ | ︙ |