/ Check-in [142b048a]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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: 142b048ac778620dd4e448c2e969982eb8188501
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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