SQLite: Check-in [0cf16dec]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Comment:	Only use the skip-ahead-distinct optimization if the index has been analyzed and we know that a skip-head is likely to skip over at least 11 rows. The magic number 11 was determined by experimentation.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| skip-ahead-distinct
Files:	files \| file ages \| folders
SHA3-256:	0cf16decd534bf2d66620c293f8c8987f356305f2d97f8fd12d260bda1571385
User & Date:	drh 2017-04-13 13:01:59

Context

2017-04-13
18:33		New test cases and minor fixes for the optimization on this branch. (check-in: 70ac9ea1 user: dan tags: skip-ahead-distinct)
13:01		Only use the skip-ahead-distinct optimization if the index has been analyzed and we know that a skip-head is likely to skip over at least 11 rows. The magic number 11 was determined by experimentation. (check-in: 0cf16dec user: drh tags: skip-ahead-distinct)
01:19		Forward port the skip-ahead-distinct branch which was abandoned for some reason that I do not recall. This port should have been achived by a merge of trunk into the previous head of skip-ahead-distinct, but that did not work. So I had to manually "rebase" the changes. (check-in: 132339a1 user: drh tags: skip-ahead-distinct)

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

    pLoop = pLevel->pWLoop;
    sqlite3VdbeResolveLabel(v, pLevel->addrCont);
    if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
      int n = -1;
      int j, k, op;
      int r1 = pParse->nMem+1;

      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
       && (pLoop->wsFlags & WHERE_INDEXED)!=0
       && OptimizationEnabled(db, SQLITE_SkipAhead)

      ){
        /* This is the Skip-ahead optimization.  When doing a DISTINCT query
        ** that has WHERE_DISTINCT_ORDERED, use OP_SkipGT/OP_SkipLT to skip
        ** over all duplicate entries, rather than visiting all duplicates
        ** using OP_Next/OP_Prev. */
        ExprList *pX = pWInfo->pResultSet;
        Index *pIdx = pLoop->u.btree.pIndex;
        for(j=0; j<pX->nExpr; j++){
          Expr *pE = sqlite3ExprSkipCollate(pX->a[j].pExpr);
          if( pE->op==TK_COLUMN ){
            if( pE->iTable!=pLevel->iTabCur ) continue;
            k = 1+sqlite3ColumnOfIndex(pIdx, pE->iColumn);
            if( k>n ) n = k;
          }else if( pIdx->aColExpr ){
................................................................................
                n = k+1;
                break;
              }
            }
          }
        }
      }
      if( n>0 ){




        for(j=0; j<n; j++){
          sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
        }
        pParse->nMem += n;
        op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
        k = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
        VdbeCoverageIf(v, op==OP_SeekLT);

    pLoop = pLevel->pWLoop;
    sqlite3VdbeResolveLabel(v, pLevel->addrCont);
    if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
      int n = -1;
      int j, k, op;
      int r1 = pParse->nMem+1;
      Index *pIdx;
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
       && (pLoop->wsFlags & WHERE_INDEXED)!=0
       && OptimizationEnabled(db, SQLITE_SkipAhead)
       && (pIdx = pLoop->u.btree.pIndex)->hasStat1
      ){
        /* This is the Skip-ahead optimization.  When doing a DISTINCT query
        ** that has WHERE_DISTINCT_ORDERED, use OP_SkipGT/OP_SkipLT to skip
        ** over all duplicate entries, rather than visiting all duplicates
        ** using OP_Next/OP_Prev. */
        ExprList *pX = pWInfo->pResultSet;

        for(j=0; j<pX->nExpr; j++){
          Expr *pE = sqlite3ExprSkipCollate(pX->a[j].pExpr);
          if( pE->op==TK_COLUMN ){
            if( pE->iTable!=pLevel->iTabCur ) continue;
            k = 1+sqlite3ColumnOfIndex(pIdx, pE->iColumn);
            if( k>n ) n = k;
          }else if( pIdx->aColExpr ){
................................................................................
                n = k+1;
                break;
              }
            }
          }
        }
      }
      /* TUNING: Only try to skip ahead using OP_Seek if we expect to
      ** skip over 11 or more rows.  Otherwise, OP_Next is just as fast.
      */
      assert( 36==sqlite3LogEst(12) );
      if( n>0 && pIdx->aiRowLogEst[n]>=36 ){
        for(j=0; j<n; j++){
          sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
        }
        pParse->nMem += n;
        op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
        k = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
        VdbeCoverageIf(v, op==OP_SeekLT);