Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Avoid generating hints using constraints that are also used to initialize the cursor, since presumably the cursor already knows about those constraints. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | cursor-hints |
| Files: | files | file ages | folders |
| SHA1: |
142b048ac778620dd4e448c2e969982e |
| User & Date: | drh 2015-08-17 17:19:28 |
Context
|
2015-08-18
| ||
| 15:58 | Provide hints for all terms in a range constraint if there are any equality terms anywhere in the constraint. Range constraint terms are only omitted for a pure range constraint with no equality prefix. check-in: b5897bc0 user: drh tags: cursor-hints | |
|
2015-08-17
| ||
| 17:19 | Avoid generating hints using constraints that are also used to initialize the cursor, since presumably the cursor already knows about those constraints. check-in: 142b048a user: drh tags: cursor-hints | |
|
2015-08-15
| ||
| 00:51 | Change the display of the P4 operand of CursorHint in EXPLAIN output to function notation. check-in: bee73d42 user: drh tags: cursor-hints | |
Changes
Changes to src/wherecode.c.
665 665 return rc; 666 666 } 667 667 668 668 /* 669 669 ** Insert an OP_CursorHint instruction if it is appropriate to do so. 670 670 */ 671 671 static void codeCursorHint( 672 - WhereInfo *pWInfo, 673 - WhereLevel *pLevel 672 + WhereInfo *pWInfo, /* The where clause */ 673 + WhereLevel *pLevel, /* Which loop to provide hints for */ 674 + WhereTerm *pEndRange /* Hint this end-of-scan boundary term if not NULL */ 674 675 ){ 675 676 Parse *pParse = pWInfo->pParse; 676 677 sqlite3 *db = pParse->db; 677 678 Vdbe *v = pParse->pVdbe; 678 679 Expr *pExpr = 0; 679 680 WhereLoop *pLoop = pLevel->pWLoop; 680 681 int iCur; 681 682 WhereClause *pWC; 682 683 WhereTerm *pTerm; 683 - int i; 684 + WhereLoop *pWLoop; 685 + int i, j; 684 686 struct CCurHint sHint; 685 687 Walker sWalker; 686 688 687 689 if( OptimizationDisabled(db, SQLITE_CursorHints) ) return; 688 690 iCur = pLevel->iTabCur; 689 691 assert( iCur==pWInfo->pTabList->a[pLevel->iFrom].iCursor ); 690 692 sHint.iTabCur = iCur; 691 693 sHint.iIdxCur = pLevel->iIdxCur; 692 694 sHint.pIdx = pLoop->u.btree.pIndex; 693 695 memset(&sWalker, 0, sizeof(sWalker)); 694 696 sWalker.pParse = pParse; 695 697 sWalker.u.pCCurHint = &sHint; 696 698 pWC = &pWInfo->sWC; 699 + pWLoop = pLevel->pWLoop; 697 700 for(i=0; i<pWC->nTerm; i++){ 698 701 pTerm = &pWC->a[i]; 699 702 if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue; 700 703 if( pTerm->prereqAll & pLevel->notReady ) continue; 701 704 if( ExprHasProperty(pTerm->pExpr, EP_FromJoin) ) continue; 705 + 706 + /* All terms in pWLoop->aLTerm[] except pEndRange are used to initialize 707 + ** the cursor. No need to hint initialization terms. */ 708 + if( pTerm!=pEndRange ){ 709 + for(j=0; j<pWLoop->nLTerm && pWLoop->aLTerm[j]!=pTerm; j++){} 710 + if( j<pWLoop->nLTerm ) continue; 711 + } 712 + 713 + /* No subqueries or non-deterministic functions allowed */ 702 714 if( sqlite3ExprContainsSubquery(pTerm->pExpr) ) continue; 715 + 716 + /* For an index scan, make sure referenced columns are actually in 717 + ** the index. */ 703 718 if( sHint.pIdx!=0 ){ 704 719 sWalker.eCode = 0; 705 720 sWalker.xExprCallback = codeCursorHintCheckExpr; 706 721 sqlite3WalkExpr(&sWalker, pTerm->pExpr); 707 722 if( sWalker.eCode ) continue; 708 723 } 724 + 725 + /* If we survive all prior tests, that means this term is worth hinting */ 709 726 pExpr = sqlite3ExprAnd(db, pExpr, sqlite3ExprDup(db, pTerm->pExpr, 0)); 710 727 } 711 728 if( pExpr!=0 ){ 712 729 sWalker.xExprCallback = codeCursorHintFixExpr; 713 730 sqlite3WalkExpr(&sWalker, pExpr); 714 731 sqlite3VdbeAddOp4(v, OP_CursorHint, 715 732 (sHint.pIdx ? sHint.iIdxCur : sHint.iTabCur), 0, 0, 716 733 (const char*)pExpr, P4_EXPR); 717 734 } 718 735 } 719 736 #else 720 -# define codeCursorHint(A,B) /* No-op */ 737 +# define codeCursorHint(A,B,C) /* No-op */ 721 738 #endif /* SQLITE_ENABLE_CURSOR_HINTS */ 722 739 723 740 /* 724 741 ** Generate code for the start of the iLevel-th loop in the WHERE clause 725 742 ** implementation described by pWInfo. 726 743 */ 727 744 Bitmask sqlite3WhereCodeOneLoopStart( ................................................................................ 879 896 if( pLoop->wsFlags & WHERE_TOP_LIMIT ) pEnd = pLoop->aLTerm[j++]; 880 897 assert( pStart!=0 || pEnd!=0 ); 881 898 if( bRev ){ 882 899 pTerm = pStart; 883 900 pStart = pEnd; 884 901 pEnd = pTerm; 885 902 } 886 - codeCursorHint(pWInfo, pLevel); 903 + codeCursorHint(pWInfo, pLevel, pEnd); 887 904 if( pStart ){ 888 905 Expr *pX; /* The expression that defines the start bound */ 889 906 int r1, rTemp; /* Registers for holding the start boundary */ 890 907 891 908 /* The following constant maps TK_xx codes into corresponding 892 909 ** seek opcodes. It depends on a particular ordering of TK_xx 893 910 */ ................................................................................ 1102 1119 testcase( pRangeEnd && (pRangeEnd->eOperator & WO_LE)!=0 ); 1103 1120 testcase( pRangeEnd && (pRangeEnd->eOperator & WO_GE)!=0 ); 1104 1121 startEq = !pRangeStart || pRangeStart->eOperator & (WO_LE|WO_GE); 1105 1122 endEq = !pRangeEnd || pRangeEnd->eOperator & (WO_LE|WO_GE); 1106 1123 start_constraints = pRangeStart || nEq>0; 1107 1124 1108 1125 /* Seek the index cursor to the start of the range. */ 1109 - codeCursorHint(pWInfo, pLevel); 1126 + codeCursorHint(pWInfo, pLevel, pRangeEnd); 1110 1127 nConstraint = nEq; 1111 1128 if( pRangeStart ){ 1112 1129 Expr *pRight = pRangeStart->pExpr->pRight; 1113 1130 sqlite3ExprCode(pParse, pRight, regBase+nEq); 1114 1131 whereLikeOptimizationStringFixup(v, pLevel, pRangeStart); 1115 1132 if( (pRangeStart->wtFlags & TERM_VNULL)==0 1116 1133 && sqlite3ExprCanBeNull(pRight) ................................................................................ 1530 1547 static const u8 aStart[] = { OP_Rewind, OP_Last }; 1531 1548 assert( bRev==0 || bRev==1 ); 1532 1549 if( pTabItem->isRecursive ){ 1533 1550 /* Tables marked isRecursive have only a single row that is stored in 1534 1551 ** a pseudo-cursor. No need to Rewind or Next such cursors. */ 1535 1552 pLevel->op = OP_Noop; 1536 1553 }else{ 1537 - codeCursorHint(pWInfo, pLevel); 1554 + codeCursorHint(pWInfo, pLevel, 0); 1538 1555 pLevel->op = aStep[bRev]; 1539 1556 pLevel->p1 = iCur; 1540 1557 pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrBrk); 1541 1558 VdbeCoverageIf(v, bRev==0); 1542 1559 VdbeCoverageIf(v, bRev!=0); 1543 1560 pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP; 1544 1561 }
Changes to test/cursorhint.test.
90 90 p4_of_opcode db CursorHint { 91 91 SELECT * FROM t3 WHERE a<15 AND b>22 AND id!=98 92 92 } 93 93 } {AND(AND(LT(c1,15),GT(c2,22)),NE(c0,98))} 94 94 95 95 # Indexed queries 96 96 # 97 -do_test 4.1 { 97 +do_test 4.1asc { 98 98 db eval { 99 99 CREATE INDEX t1bc ON t1(b,c); 100 100 CREATE INDEX t2yz ON t2(y,z); 101 101 } 102 102 p4_of_opcode db CursorHint { 103 - SELECT * FROM t1 WHERE b>11; 103 + SELECT * FROM t1 WHERE b>11 ORDER BY b ASC; 104 + } 105 +} {} 106 +do_test 4.1desc { 107 + p4_of_opcode db CursorHint { 108 + SELECT * FROM t1 WHERE b>11 ORDER BY b DESC; 104 109 } 105 110 } {GT(c0,11)} 106 111 do_test 4.2 { 107 112 p5_of_opcode db OpenRead . { 108 113 SELECT * FROM t1 WHERE b>11; 109 114 } 110 115 } {02 00} 111 -do_test 4.3 { 116 +do_test 4.3asc { 117 + p4_of_opcode db CursorHint { 118 + SELECT c FROM t1 WHERE b<11 ORDER BY b ASC; 119 + } 120 +} {LT(c0,11)} 121 +do_test 4.3desc { 112 122 p4_of_opcode db CursorHint { 113 - SELECT c FROM t1 WHERE b>11; 123 + SELECT c FROM t1 WHERE b<11 ORDER BY b DESC; 114 124 } 115 -} {GT(c0,11)} 125 +} {} 116 126 do_test 4.4 { 117 127 p5_of_opcode db OpenRead . { 118 - SELECT c FROM t1 WHERE b>11; 128 + SELECT c FROM t1 WHERE b<11; 119 129 } 120 130 } {00} 121 131 132 +do_test 4.5asc { 133 + p4_of_opcode db CursorHint { 134 + SELECT c FROM t1 WHERE b>=10 AND b<=20 ORDER BY b ASC; 135 + } 136 +} {LE(c0,20)} 137 +do_test 4.5desc { 138 + p4_of_opcode db CursorHint { 139 + SELECT c FROM t1 WHERE b>=10 AND b<=20 ORDER BY b DESC; 140 + } 141 +} {GE(c0,10)} 142 +do_test 4.6asc { 143 + p4_of_opcode db CursorHint { 144 + SELECT rowid FROM t1 WHERE b=22 AND c>=10 AND c<=20 ORDER BY b,c ASC; 145 + } 146 +} {LE(c1,20)} 147 +do_test 4.6desc { 148 + p4_of_opcode db CursorHint { 149 + SELECT rowid FROM t1 WHERE b=22 AND c>=10 AND c<=20 ORDER BY b,c DESC; 150 + } 151 +} {GE(c1,10)} 122 152 123 153 finish_test