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: |
0cf16decd534bf2d66620c293f8c8987 |
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
Changes to src/where.c.
︙ | ︙ | |||
4849 4850 4851 4852 4853 4854 4855 4856 4857 4858 4859 4860 4861 4862 4863 4864 | 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; | > > < > > > > | | 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); |
︙ | ︙ |