diff options
| author | drh <> | 2022-09-01 10:29:02 +0000 |
|---|---|---|
| committer | drh <> | 2022-09-01 10:29:02 +0000 |
| commit | a3fc683c80c41292b03aa89a689ee25ab987fa12 (patch) | |
| tree | b60ee42cec3821c359570a14f9d64263da374b0c /src | |
| parent | 9c3a114ca057e319bc0923d25366d1cbf29e225d (diff) | |
| download | sqlite-a3fc683c80c41292b03aa89a689ee25ab987fa12.tar.gz sqlite-a3fc683c80c41292b03aa89a689ee25ab987fa12.zip | |
In the query planner, add a heuristic that will reduce the cost of a full
table scan for a materialized view or subquery if the full scan is the
outer-most loop. This is shown to speed up some queries.
FossilOrigin-Name: 609fbb94b8f01d6792e5941ab23ce041313d359f6788c4dde6b1ca749ab49137
Diffstat (limited to 'src')
| -rw-r--r-- | src/where.c | 10 | ||||
| -rw-r--r-- | src/whereInt.h | 1 |
2 files changed, 11 insertions, 0 deletions
diff --git a/src/where.c b/src/where.c index 0a6f5e627..3e90fa9a7 100644 --- a/src/where.c +++ b/src/where.c @@ -3463,6 +3463,9 @@ static int whereLoopAddBtree( #else pNew->rRun = rSize + 16; #endif + if( IsView(pTab) || (pTab->tabFlags & TF_Ephemeral)!=0 ){ + pNew->wsFlags |= WHERE_VIEWSCAN; + } ApplyCostMultiplier(pNew->rRun, pTab->costMult); whereLoopOutputAdjust(pWC, pNew, rSize); rc = whereLoopInsert(pBuilder, pNew); @@ -4843,6 +4846,13 @@ static int wherePathSolver(WhereInfo *pWInfo, LogEst nRowEst){ rUnsorted -= 2; /* TUNING: Slight bias in favor of no-sort plans */ } + /* TUNING: A full-scan of a VIEW or subquery in the outer loop + ** is not so bad. */ + if( iLoop==0 && (pWLoop->wsFlags & WHERE_VIEWSCAN)!=0 ){ + rCost += -10; + nOut += -30; + } + /* Check to see if pWLoop should be added to the set of ** mxChoice best-so-far paths. ** diff --git a/src/whereInt.h b/src/whereInt.h index bc96ff7e6..fda207890 100644 --- a/src/whereInt.h +++ b/src/whereInt.h @@ -648,5 +648,6 @@ void sqlite3WhereTabFuncArgs(Parse*, SrcItem*, WhereClause*); #define WHERE_BLOOMFILTER 0x00400000 /* Consider using a Bloom-filter */ #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 */ #endif /* !defined(SQLITE_WHEREINT_H) */ |