From 71aff85503b826122aa22db1e341b87c29a5b305 Mon Sep 17 00:00:00 2001 From: drh <> Date: Thu, 21 Jan 2021 20:42:36 +0000 Subject: [PATCH] Performance optimizations in exprAnalyze() FossilOrigin-Name: 6d60cf540b8cc231448175f1e16e1f4f7a0aee26898570a5b8a09c89fae53c02 --- manifest | 15 +++---- manifest.uuid | 2 +- src/whereexpr.c | 112 ++++++++++++++++++++++++------------------------ 3 files changed, 64 insertions(+), 65 deletions(-) diff --git a/manifest b/manifest index 008fa8c252..a19e4809b7 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Always\senable\sthe\sIS\sNOT\sNULL\soptimization,\seven\sif\sSTAT4\sis\snot\senabled. -D 2021-01-21T17:54:41.795 +C Performance\soptimizations\sin\sexprAnalyze() +D 2021-01-21T20:42:36.445 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724 @@ -631,7 +631,7 @@ F src/walker.c d9c4e454ebb9499e908aa62d55b8994c375cf5355ac78f60d45af17f7890701c F src/where.c 0e6abb22a2323fec80b450825593c26a2ad8f4815d1ee3af9969d8f6144bf681 F src/whereInt.h ae03b5e3a4cca9bd9cb1b7d3c63faf8f1f177200fc8cecc87d3d0cab6ca338e6 F src/wherecode.c a3a1aff30fe99a818d8e7c607980f033f40c68d890e03ed25838b9dbb7908bee -F src/whereexpr.c 1f11bdd204ad021f5935df6e7726b4f1dbdd3157a4f31d0dafc9c457e5e545a8 +F src/whereexpr.c a182038cf7d10aa9a95a96b8563a34f34990323cf307dee6559e995961966d43 F src/window.c edd6f5e25a1e8f2b6f5305b7f5f7da7bb35f07f0d432b255b1d4c2fcab4205aa F test/8_3_names.test ebbb5cd36741350040fd28b432ceadf495be25b2 F test/affinity2.test ce1aafc86e110685b324e9a763eab4f2a73f737842ec3b687bd965867de90627 @@ -1898,10 +1898,7 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 02264ab6a02d6cc95cf865920bcbaf4307d034640e6e4f3371b009ae9818540e -R 8df6cd517dfa239c965fc40092a79782 -T *branch * isnotnull-opt -T *sym-isnotnull-opt * -T -sym-trunk * +P fc98218cf69e63bdb9e5f154521a341508502cd8cfe04cb870cabee2d99e0cb3 +R f46bf0fe436bc977a841647e6a3e3ebc U drh -Z d06e074a8f142c72ee8a673bfd500f4d +Z 4d2704e467917a55449d9cd09c1bd5e8 diff --git a/manifest.uuid b/manifest.uuid index 1e251fa4a5..3639833e1d 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -fc98218cf69e63bdb9e5f154521a341508502cd8cfe04cb870cabee2d99e0cb3 \ No newline at end of file +6d60cf540b8cc231448175f1e16e1f4f7a0aee26898570a5b8a09c89fae53c02 \ No newline at end of file diff --git a/src/whereexpr.c b/src/whereexpr.c index 0dc56ea8a3..f69210975c 100644 --- a/src/whereexpr.c +++ b/src/whereexpr.c @@ -1502,6 +1502,7 @@ static void exprAnalyze( } } + #ifndef SQLITE_OMIT_LIKE_OPTIMIZATION /* Add constraints to reduce the search space on a LIKE or GLOB ** operator. @@ -1516,7 +1517,8 @@ static void exprAnalyze( ** bound is made all lowercase so that the bounds also work when comparing ** BLOBs. */ - if( pWC->op==TK_AND + else if( pExpr->op==TK_FUNCTION + && pWC->op==TK_AND && isLikeOrGlob(pParse, pExpr, &pStr1, &isComplete, &noCase) ){ Expr *pLeft; /* LHS of LIKE/GLOB operator */ @@ -1586,52 +1588,6 @@ static void exprAnalyze( } #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */ -#ifndef SQLITE_OMIT_VIRTUALTABLE - /* Add a WO_AUX auxiliary term to the constraint set if the - ** current expression is of the form "column OP expr" where OP - ** is an operator that gets passed into virtual tables but which is - ** not normally optimized for ordinary tables. In other words, OP - ** is one of MATCH, LIKE, GLOB, REGEXP, !=, IS, IS NOT, or NOT NULL. - ** This information is used by the xBestIndex methods of - ** virtual tables. The native query optimizer does not attempt - ** to do anything with MATCH functions. - */ - if( pWC->op==TK_AND ){ - Expr *pRight = 0, *pLeft = 0; - int res = isAuxiliaryVtabOperator(db, pExpr, &eOp2, &pLeft, &pRight); - while( res-- > 0 ){ - int idxNew; - WhereTerm *pNewTerm; - Bitmask prereqColumn, prereqExpr; - - prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight); - prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft); - if( (prereqExpr & prereqColumn)==0 ){ - Expr *pNewExpr; - pNewExpr = sqlite3PExpr(pParse, TK_MATCH, - 0, sqlite3ExprDup(db, pRight, 0)); - if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){ - ExprSetProperty(pNewExpr, EP_FromJoin); - pNewExpr->iRightJoinTable = pExpr->iRightJoinTable; - } - idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC); - testcase( idxNew==0 ); - pNewTerm = &pWC->a[idxNew]; - pNewTerm->prereqRight = prereqExpr; - pNewTerm->leftCursor = pLeft->iTable; - pNewTerm->u.x.leftColumn = pLeft->iColumn; - pNewTerm->eOperator = WO_AUX; - pNewTerm->eMatchOp = eOp2; - markTermAsChild(pWC, idxNew, idxTerm); - pTerm = &pWC->a[idxTerm]; - pTerm->wtFlags |= TERM_COPIED; - pNewTerm->prereqAll = pTerm->prereqAll; - } - SWAP(Expr*, pLeft, pRight); - } - } -#endif /* SQLITE_OMIT_VIRTUALTABLE */ - /* If there is a vector == or IS term - e.g. "(a, b) == (?, ?)" - create ** new terms for each component comparison - "a = ?" and "b = ?". The ** new terms completely replace the original vector comparison, which is @@ -1639,12 +1595,12 @@ static void exprAnalyze( ** ** This is only required if at least one side of the comparison operation ** is not a sub-select. */ - if( pWC->op==TK_AND - && (pExpr->op==TK_EQ || pExpr->op==TK_IS) - && (nLeft = sqlite3ExprVectorSize(pExpr->pLeft))>1 - && sqlite3ExprVectorSize(pExpr->pRight)==nLeft - && ( (pExpr->pLeft->flags & EP_xIsSelect)==0 - || (pExpr->pRight->flags & EP_xIsSelect)==0) + if( (pExpr->op==TK_EQ || pExpr->op==TK_IS) + && (nLeft = sqlite3ExprVectorSize(pExpr->pLeft))>1 + && sqlite3ExprVectorSize(pExpr->pRight)==nLeft + && ( (pExpr->pLeft->flags & EP_xIsSelect)==0 + || (pExpr->pRight->flags & EP_xIsSelect)==0) + && pWC->op==TK_AND ){ int i; for(i=0; iop==TK_AND - && pExpr->op==TK_IN + else if( pExpr->op==TK_IN && pTerm->u.x.iField==0 && pExpr->pLeft->op==TK_VECTOR && pExpr->x.pSelect->pPrior==0 #ifndef SQLITE_OMIT_WINDOWFUNC && pExpr->x.pSelect->pWin==0 #endif + && pWC->op==TK_AND ){ int i; for(i=0; ipLeft); i++){ @@ -1691,6 +1647,52 @@ static void exprAnalyze( } } +#ifndef SQLITE_OMIT_VIRTUALTABLE + /* Add a WO_AUX auxiliary term to the constraint set if the + ** current expression is of the form "column OP expr" where OP + ** is an operator that gets passed into virtual tables but which is + ** not normally optimized for ordinary tables. In other words, OP + ** is one of MATCH, LIKE, GLOB, REGEXP, !=, IS, IS NOT, or NOT NULL. + ** This information is used by the xBestIndex methods of + ** virtual tables. The native query optimizer does not attempt + ** to do anything with MATCH functions. + */ + else if( pWC->op==TK_AND ){ + Expr *pRight = 0, *pLeft = 0; + int res = isAuxiliaryVtabOperator(db, pExpr, &eOp2, &pLeft, &pRight); + while( res-- > 0 ){ + int idxNew; + WhereTerm *pNewTerm; + Bitmask prereqColumn, prereqExpr; + + prereqExpr = sqlite3WhereExprUsage(pMaskSet, pRight); + prereqColumn = sqlite3WhereExprUsage(pMaskSet, pLeft); + if( (prereqExpr & prereqColumn)==0 ){ + Expr *pNewExpr; + pNewExpr = sqlite3PExpr(pParse, TK_MATCH, + 0, sqlite3ExprDup(db, pRight, 0)); + if( ExprHasProperty(pExpr, EP_FromJoin) && pNewExpr ){ + ExprSetProperty(pNewExpr, EP_FromJoin); + pNewExpr->iRightJoinTable = pExpr->iRightJoinTable; + } + idxNew = whereClauseInsert(pWC, pNewExpr, TERM_VIRTUAL|TERM_DYNAMIC); + testcase( idxNew==0 ); + pNewTerm = &pWC->a[idxNew]; + pNewTerm->prereqRight = prereqExpr; + pNewTerm->leftCursor = pLeft->iTable; + pNewTerm->u.x.leftColumn = pLeft->iColumn; + pNewTerm->eOperator = WO_AUX; + pNewTerm->eMatchOp = eOp2; + markTermAsChild(pWC, idxNew, idxTerm); + pTerm = &pWC->a[idxTerm]; + pTerm->wtFlags |= TERM_COPIED; + pNewTerm->prereqAll = pTerm->prereqAll; + } + SWAP(Expr*, pLeft, pRight); + } + } +#endif /* SQLITE_OMIT_VIRTUALTABLE */ + /* Prevent ON clause terms of a LEFT JOIN from being used to drive ** an index for tables to the left of the join. */ -- 2.47.2