diff options
| -rw-r--r-- | manifest | 14 | ||||
| -rw-r--r-- | manifest.uuid | 2 | ||||
| -rw-r--r-- | src/where.c | 103 | ||||
| -rw-r--r-- | src/whereInt.h | 1 |
4 files changed, 81 insertions, 39 deletions
@@ -1,5 +1,5 @@ -C Better\sreporting\sof\swhen\sthe\sWHERE\sclause\sanalysis\sthinks\sthat\san\sindex\nis\scovering. -D 2022-11-26T20:52:38.117 +C Rework\sthe\scovering\sindex\schecking\sroutine,\swhereIsCoveringIndex(),\sso\sthat\nit\scan\sreturn\sa\s"maybe"\sresult\sfor\saggregate\squeries\swhere\swe\sare\snot\sexactly\nsure.\s\sThe\sindex\sis\sscored\sas\sif\sit\sis\scovering,\sbut\sthe\smain\stable\sis\nstill\sopened. +D 2022-11-28T15:23:53.775 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -728,8 +728,8 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9 F src/wal.c b9df133a705093da8977da5eb202eaadb844839f1c7297c08d33471f5491843d F src/wal.h c3aa7825bfa2fe0d85bef2db94655f99870a285778baa36307c0a16da32b226a F src/walker.c f890a3298418d7cba3b69b8803594fdc484ea241206a8dfa99db6dd36f8cbb3b -F src/where.c 1f64631ab2c94b3c11de00ad1d08be9d51ee73acd613c54c4367526f5e2ac241 -F src/whereInt.h df0c79388c0b71b4a91f480d02791679fe0345d40410435c541c8893e95a4d3f +F src/where.c 81422870d17fdf3880fdb9d3b3965e1aa5efb7a72c6933cec3f1bc8081c463db +F src/whereInt.h e25203e5bfee149f5f1225ae0166cfb4f1e65490c998a024249e98bb0647377c F src/wherecode.c 133a94f82858787217d073143617df19e4a6a7d0b771a1519f957608109ad5a5 F src/whereexpr.c 05295b44b54eea76d1ba766f0908928d0e20e990c249344c9521454d3d09c7ae F src/window.c 14836767adb26573b50f528eb37f8b1336f2c430ab38de7cead1e5c546bb4d8c @@ -2060,8 +2060,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 9ac67ff968e874cf955e46e3993e3215c766feec3d5bdd38d77884eedd86b59e -R 1dffa8ce2c066e6a4b41c4abd6aac427 +P 17ebcf316b91042c823eea7bb6f1309325023cb5c70538cdb2ce932caee2ef05 +R 254ab4db9b1e239ec16eb2e1a31dc657 U drh -Z 2a27a64568a31340c97915e2beb3adb7 +Z 489a805b1f3c740affbc27a0febf881c # Remove this line to create a well-formed Fossil manifest. diff --git a/manifest.uuid b/manifest.uuid index 7f494549c..d0a052a41 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -17ebcf316b91042c823eea7bb6f1309325023cb5c70538cdb2ce932caee2ef05
\ No newline at end of file +b8eec4214363192e6f3e12b3faa5810d8269a5fdaecab3ec09b02e5002cf798a
\ No newline at end of file diff --git a/src/where.c b/src/where.c index 6ab9230d8..589300c6b 100644 --- a/src/where.c +++ b/src/where.c @@ -3094,7 +3094,7 @@ static int whereLoopAddBtreeIndex( assert( pSrc->pTab->szTabRow>0 ); rCostIdx = pNew->nOut + 1 + (15*pProbe->szIdxRow)/pSrc->pTab->szTabRow; pNew->rRun = sqlite3LogEstAdd(rLogSize, rCostIdx); - if( (pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK))==0 ){ + if( (pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK|WHERE_EXPRIDX))==0 ){ pNew->rRun = sqlite3LogEstAdd(pNew->rRun, pNew->nOut + 16); } ApplyCostMultiplier(pNew->rRun, pProbe->pTable->costMult); @@ -3270,9 +3270,12 @@ static int exprIsCoveredByIndex( /* ** Structure passed to the whereIsCoveringIndex Walker callback. */ +typedef struct CoveringIndexCheck CoveringIndexCheck; struct CoveringIndexCheck { Index *pIdx; /* The index */ int iTabCur; /* Cursor number for the corresponding table */ + u8 bExpr; /* Uses an indexed expression */ + u8 bUnidx; /* Uses an unindexed column not within an indexed expr */ }; /* @@ -3293,24 +3296,28 @@ struct CoveringIndexCheck { ** matches pExpr, then prune the search. */ static int whereIsCoveringIndexWalkCallback(Walker *pWalk, Expr *pExpr){ - int i; /* Loop counter */ - const Index *pIdx; /* The index of interest */ - const i16 *aiColumn; /* Columns contained in the index */ - u16 nColumn; /* Number of columns in the index */ - pIdx = pWalk->u.pCovIdxCk->pIdx; + int i; /* Loop counter */ + const Index *pIdx; /* The index of interest */ + const i16 *aiColumn; /* Columns contained in the index */ + u16 nColumn; /* Number of columns in the index */ + CoveringIndexCheck *pCk; /* Info about this search */ + + pCk = pWalk->u.pCovIdxCk; + pIdx = pCk->pIdx; if( (pExpr->op==TK_COLUMN || pExpr->op==TK_AGG_COLUMN) ){ /* if( pExpr->iColumn<(BMS-1) && pIdx->bHasExpr==0 ) return WRC_Continue;*/ - if( pExpr->iTable!=pWalk->u.pCovIdxCk->iTabCur ) return WRC_Continue; + if( pExpr->iTable!=pCk->iTabCur ) return WRC_Continue; pIdx = pWalk->u.pCovIdxCk->pIdx; aiColumn = pIdx->aiColumn; nColumn = pIdx->nColumn; for(i=0; i<nColumn; i++){ if( aiColumn[i]==pExpr->iColumn ) return WRC_Continue; } - pWalk->eCode = 1; + pCk->bUnidx = 1; return WRC_Abort; }else if( pIdx->bHasExpr && exprIsCoveredByIndex(pExpr, pIdx, pWalk->u.pCovIdxCk->iTabCur) ){ + pCk->bExpr = 1; return WRC_Prune; } return WRC_Continue; @@ -3319,30 +3326,39 @@ static int whereIsCoveringIndexWalkCallback(Walker *pWalk, Expr *pExpr){ /* ** pIdx is an index that covers all of the low-number columns used by -** pWInfo->pSelect (columns from 0 through 62). But there are columns -** in pWInfo->pSelect beyond 62. This routine tries to answer the question -** of whether pIdx covers *all* columns in the query. +** pWInfo->pSelect (columns from 0 through 62) or an index that has +** expressions terms. Hence, we cannot determine whether or not it is +** a covering index by using the colUsed bitmasks. We have to do a search +** to see if the index is covering. This routine does that search. +** +** The return value is one of these: +** +** 0 The index is definitely not a covering index ** -** Return 0 if pIdx is a covering index. Return non-zero if pIdx is -** not a covering index or if we are unable to determine if pIdx is a -** covering index. +** WHERE_IDX_ONLY The index is definitely a covering index ** -** This routine is an optimization. It is always safe to return non-zero. -** But returning zero when non-zero should have been returned can lead to -** incorrect bytecode and assertion faults. +** WHERE_EXPRIDX The index is likely a covering index, but it is +** difficult to determine precisely because of the +** expressions that are indexed. Score it as a +** covering index, but still keep the main table open +** just in case we need it. +** +** This routine is an optimization. It is always safe to return zero. +** But returning one of the other two values when zero should have been +** returned can lead to incorrect bytecode and assertion faults. */ static SQLITE_NOINLINE u32 whereIsCoveringIndex( WhereInfo *pWInfo, /* The WHERE clause context */ Index *pIdx, /* Index that is being tested */ int iTabCur /* Cursor for the table being indexed */ ){ - int i; + int i, rc; struct CoveringIndexCheck ck; Walker w; if( pWInfo->pSelect==0 ){ /* We don't have access to the full query, so we cannot check to see ** if pIdx is covering. Assume it is not. */ - return 1; + return 0; } if( pIdx->bHasExpr==0 ){ for(i=0; i<pIdx->nColumn; i++){ @@ -3352,18 +3368,26 @@ static SQLITE_NOINLINE u32 whereIsCoveringIndex( /* pIdx does not index any columns greater than 62, but we know from ** colMask that columns greater than 62 are used, so this is not a ** covering index */ - return 1; + return 0; } } ck.pIdx = pIdx; ck.iTabCur = iTabCur; + ck.bExpr = 0; + ck.bUnidx = 0; memset(&w, 0, sizeof(w)); w.xExprCallback = whereIsCoveringIndexWalkCallback; w.xSelectCallback = sqlite3SelectWalkNoop; w.u.pCovIdxCk = &ck; - w.eCode = 0; sqlite3WalkSelect(&w, pWInfo->pSelect); - return w.eCode; + if( ck.bUnidx ){ + rc = 0; + }else if( ck.bExpr ){ + rc = WHERE_EXPRIDX; + }else{ + rc = WHERE_IDX_ONLY; + } + return rc; } /* @@ -3579,21 +3603,38 @@ static int whereLoopAddBtree( }else{ Bitmask m; if( pProbe->isCovering ){ - pNew->wsFlags = WHERE_IDX_ONLY | WHERE_INDEXED; m = 0; + pNew->wsFlags = WHERE_IDX_ONLY | WHERE_INDEXED; }else{ m = pSrc->colUsed & pProbe->colNotIdxed; - if( m==TOPBIT || (pProbe->bHasExpr && !pProbe->bHasVCol) ){ - m = whereIsCoveringIndex(pWInfo, pProbe, pSrc->iCursor); - WHERETRACE(0xff, - ("-> %s %s a covering index according to whereIsCoveringIndex()\n", - pProbe->zName, m==0 ? "is" : "is not")); - }else{ + pNew->wsFlags = WHERE_INDEXED; + if( m==TOPBIT || (pProbe->bHasExpr && !pProbe->bHasVCol && m!=0) ){ + u32 isCov = whereIsCoveringIndex(pWInfo, pProbe, pSrc->iCursor); + if( isCov==0 ){ + WHERETRACE(0xff, + ("-> %s is not a covering index" + " according to whereIsCoveringIndex()\n", pProbe->zName)); + assert( m!=0 ); + }else{ + m = 0; + pNew->wsFlags |= isCov; + if( isCov & WHERE_IDX_ONLY ){ + WHERETRACE(0xff, + ("-> %s is a covering expression index" + " according to whereIsCoveringIndex()\n", pProbe->zName)); + }else{ + assert( isCov==WHERE_EXPRIDX ); + WHERETRACE(0xff, + ("-> %s might be a covering expression index" + " according to whereIsCoveringIndex()\n", pProbe->zName)); + } + } + }else if( m==0 ){ WHERETRACE(0xff, - ("-> %s %s a covering index according to bitmasks\n", + ("-> %s a covering index according to bitmasks\n", pProbe->zName, m==0 ? "is" : "is not")); + pNew->wsFlags = WHERE_IDX_ONLY | WHERE_INDEXED; } - pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED; } /* Full scan via index */ diff --git a/src/whereInt.h b/src/whereInt.h index 28ede5be6..b89a4513e 100644 --- a/src/whereInt.h +++ b/src/whereInt.h @@ -634,5 +634,6 @@ void sqlite3WhereTabFuncArgs(Parse*, SrcItem*, WhereClause*); #define WHERE_SELFCULL 0x00800000 /* nOut reduced by extra WHERE terms */ #define WHERE_OMIT_OFFSET 0x01000000 /* Set offset counter to zero */ #define WHERE_VIEWSCAN 0x02000000 /* A full-scan of a VIEW or subquery */ +#define WHERE_EXPRIDX 0x04000000 /* Uses an index-on-expressions */ #endif /* !defined(SQLITE_WHEREINT_H) */ |