Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add new WHERETRACE macros for better diagnostics of the query planner. Added a new test case for the performance regression fixed by the previous check-in. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
1e0db99797be2821716de7138931ebd5 |
User & Date: | drh 2010-10-21 03:13:59 |
Context
2010-10-21
| ||
12:34 | Fix a typo-bug that prevented --disable-amalgamation from working in Makefile.in. Also fix an overly long line in Makfile.in. (check-in: 2c3c4ba0 user: drh tags: trunk) | |
03:13 | Add new WHERETRACE macros for better diagnostics of the query planner. Added a new test case for the performance regression fixed by the previous check-in. (check-in: 1e0db997 user: drh tags: trunk) | |
02:05 | Fix the query planner so that it uses the multi-index OR-clause solution if that is the lowest cost estimate. A prior bug cause the multi-index solution to be ignored in some circumstances. (check-in: 28ba6255 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
4063 4064 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 | Bitmask m; /* Bitmask value for j or bestJ */ int isOptimal; /* Iterator for optimal/non-optimal search */ int nUnconstrained; /* Number tables without INDEXED BY */ Bitmask notIndexed; /* Mask of tables that cannot use an index */ memset(&bestPlan, 0, sizeof(bestPlan)); bestPlan.rCost = SQLITE_BIG_DBL; /* Loop through the remaining entries in the FROM clause to find the ** next nested loop. The loop tests all FROM clause entries ** either once or twice. ** ** The first test is always performed if there are two or more entries ** remaining and never performed if there is only one FROM clause entry | > | 4063 4064 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 4077 | Bitmask m; /* Bitmask value for j or bestJ */ int isOptimal; /* Iterator for optimal/non-optimal search */ int nUnconstrained; /* Number tables without INDEXED BY */ Bitmask notIndexed; /* Mask of tables that cannot use an index */ memset(&bestPlan, 0, sizeof(bestPlan)); bestPlan.rCost = SQLITE_BIG_DBL; WHERETRACE(("*** Begin search for loop %d ***\n", i)); /* Loop through the remaining entries in the FROM clause to find the ** next nested loop. The loop tests all FROM clause entries ** either once or twice. ** ** The first test is always performed if there are two or more entries ** remaining and never performed if there is only one FROM clause entry |
︙ | ︙ | |||
4127 4128 4129 4130 4131 4132 4133 4134 4135 4136 4137 4138 4139 4140 | if( j==iFrom ) iFrom++; continue; } mask = (isOptimal ? m : notReady); pOrderBy = ((i==0 && ppOrderBy )?*ppOrderBy:0); if( pTabItem->pIndex==0 ) nUnconstrained++; assert( pTabItem->pTab ); #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTabItem->pTab) ){ sqlite3_index_info **pp = &pWInfo->a[j].pIdxInfo; bestVirtualIndex(pParse, pWC, pTabItem, mask, notReady, pOrderBy, &sCost, pp); }else | > > | 4128 4129 4130 4131 4132 4133 4134 4135 4136 4137 4138 4139 4140 4141 4142 4143 | if( j==iFrom ) iFrom++; continue; } mask = (isOptimal ? m : notReady); pOrderBy = ((i==0 && ppOrderBy )?*ppOrderBy:0); if( pTabItem->pIndex==0 ) nUnconstrained++; WHERETRACE(("=== trying table %d with isOptimal=%d ===\n", j, isOptimal)); assert( pTabItem->pTab ); #ifndef SQLITE_OMIT_VIRTUALTABLE if( IsVirtual(pTabItem->pTab) ){ sqlite3_index_info **pp = &pWInfo->a[j].pIdxInfo; bestVirtualIndex(pParse, pWC, pTabItem, mask, notReady, pOrderBy, &sCost, pp); }else |
︙ | ︙ | |||
4179 4180 4181 4182 4183 4184 4185 | && (bestJ<0 || (notIndexed&m)!=0 /* (2) */ || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0) && (nUnconstrained==0 || pTabItem->pIndex==0 /* (3) */ || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)) && (bestJ<0 || sCost.rCost<bestPlan.rCost /* (4) */ || (sCost.rCost<=bestPlan.rCost && sCost.nRow<bestPlan.nRow)) ){ | > | | | > | | 4182 4183 4184 4185 4186 4187 4188 4189 4190 4191 4192 4193 4194 4195 4196 4197 4198 4199 4200 4201 4202 4203 4204 4205 4206 4207 4208 4209 | && (bestJ<0 || (notIndexed&m)!=0 /* (2) */ || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0) && (nUnconstrained==0 || pTabItem->pIndex==0 /* (3) */ || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)) && (bestJ<0 || sCost.rCost<bestPlan.rCost /* (4) */ || (sCost.rCost<=bestPlan.rCost && sCost.nRow<bestPlan.nRow)) ){ WHERETRACE(("=== table %d is best so far" " with cost=%g and nRow=%g\n", j, sCost.rCost, sCost.nRow)); bestPlan = sCost; bestJ = j; } if( doNotReorder ) break; } } assert( bestJ>=0 ); assert( notReady & getMask(pMaskSet, pTabList->a[bestJ].iCursor) ); WHERETRACE(("*** Optimizer selects table %d for loop %d" " with cost=%g and nRow=%g\n", bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.nRow)); if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){ *ppOrderBy = 0; } andFlags &= bestPlan.plan.wsFlags; pLevel->plan = bestPlan.plan; testcase( bestPlan.plan.wsFlags & WHERE_INDEXED ); testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX ); |
︙ | ︙ |
Changes to test/where7.test.
︙ | ︙ | |||
23295 23296 23297 23298 23299 23300 23301 | OR a=23 OR (f GLOB '?defg*' AND f GLOB 'cdef*') OR d<0.0 OR (d>=22.0 AND d<23.0 AND d NOT NULL) OR a=91 } } {2 22 23 28 54 80 91 scan 0 sort 0} | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 23295 23296 23297 23298 23299 23300 23301 23302 23303 23304 23305 23306 23307 23308 23309 23310 23311 23312 23313 23314 23315 23316 23317 23318 23319 23320 23321 23322 23323 23324 23325 23326 23327 23328 23329 23330 23331 23332 23333 23334 23335 23336 23337 23338 23339 23340 23341 23342 23343 23344 23345 23346 23347 | OR a=23 OR (f GLOB '?defg*' AND f GLOB 'cdef*') OR d<0.0 OR (d>=22.0 AND d<23.0 AND d NOT NULL) OR a=91 } } {2 22 23 28 54 80 91 scan 0 sort 0} # test case for the performance regression fixed by # check-in 28ba6255282b on 2010-10-21 02:05:06 # # The test case that follows is code from an actual # application with identifiers change and unused columns # remove. # do_test where7-3.1 { db eval { CREATE TABLE t301 ( c8 INTEGER PRIMARY KEY, c6 INTEGER, c4 INTEGER, c7 INTEGER, FOREIGN KEY (c4) REFERENCES series(c4) ); CREATE INDEX t301_c6 on t301(c6); CREATE INDEX t301_c4 on t301(c4); CREATE INDEX t301_c7 on t301(c7); CREATE TABLE t302 ( c1 INTEGER PRIMARY KEY, c8 INTEGER, c5 INTEGER, c3 INTEGER, c2 INTEGER, c4 INTEGER, FOREIGN KEY (c8) REFERENCES t301(c8) ); CREATE INDEX t302_c3 on t302(c3); CREATE INDEX t302_c8_c3 on t302(c8, c3); CREATE INDEX t302_c5 on t302(c5); EXPLAIN QUERY PLAN SELECT t302.c1 FROM t302 JOIN t301 ON t302.c8 = t301.c8 WHERE t302.c2 = 19571 AND t302.c3 > 1287603136 AND (t301.c4 = 1407449685622784 OR t301.c8 = 1407424651264000) ORDER BY t302.c5 LIMIT 200; } } {0 1 {TABLE t301 VIA MULTI-INDEX UNION} 1 0 {TABLE t302 WITH INDEX t302_c8_c3} 0 0 {TABLE t301 WITH INDEX t301_c4} 0 0 {TABLE t301 USING PRIMARY KEY}} finish_test |