SQLite

Check-in [0cf16decd5]
Login

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
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.793
Context
2017-04-13
18:33
New test cases and minor fixes for the optimization on this branch. (check-in: 70ac9ea1a6 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: 0cf16decd5 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: 132339a1fb user: drh tags: skip-ahead-distinct)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
4849
4850
4851
4852
4853
4854
4855

4856
4857
4858

4859
4860
4861
4862
4863
4864
4865
4866
4867
4868
4869
4870
4871
4872
4873
4874
4875
4876
4877
4878
4879
4880
4881
4882




4883
4884
4885
4886
4887
4888
4889
4890
    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 ){
            for(k=n+1; k<pIdx->nKeyCol; k++){
              Expr *pI = pIdx->aColExpr->a[k].pExpr;
              if( pI && sqlite3ExprCompare(pE,pI,0)<2 ){
                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);







>



>






<

















>
>
>
>
|







4849
4850
4851
4852
4853
4854
4855
4856
4857
4858
4859
4860
4861
4862
4863
4864
4865
4866

4867
4868
4869
4870
4871
4872
4873
4874
4875
4876
4877
4878
4879
4880
4881
4882
4883
4884
4885
4886
4887
4888
4889
4890
4891
4892
4893
4894
4895
    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 ){
            for(k=n+1; k<pIdx->nKeyCol; k++){
              Expr *pI = pIdx->aColExpr->a[k].pExpr;
              if( pI && sqlite3ExprCompare(pE,pI,0)<2 ){
                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);