Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not use an automatic index on an outer loop that only runs once. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | planner-improvements |
| Files: | files | file ages | folders |
| SHA1: |
5957e793414ff80ed01a7a67e70c3fd0 |
| User & Date: | drh 2016-02-25 18:03:38 |
Context
|
2016-02-25
| ||
| 18:22 | Improvement on the previous check-in: disallow automatic indexes for any loop that is expected to run less than twice. Closed-Leaf check-in: aab53a21 user: drh tags: planner-improvements | |
| 18:03 | Do not use an automatic index on an outer loop that only runs once. check-in: 5957e793 user: drh tags: planner-improvements | |
| 16:04 | Take the LIMIT clause into account when estimating the cost of sorting. check-in: d491745c user: drh tags: planner-improvements | |
Changes
Changes to src/where.c.
3581 3582 3583 3584 3585 3586 3587 3588 3589 3590 3591 3592 3593 3594 |
LogEst rUnsorted; /* Unsorted cost of (pFrom+pWLoop) */
i8 isOrdered = pFrom->isOrdered; /* isOrdered for (pFrom+pWLoop) */
Bitmask maskNew; /* Mask of src visited by (..) */
Bitmask revMask = 0; /* Mask of rev-order loops for (..) */
if( (pWLoop->prereq & ~pFrom->maskLoop)!=0 ) continue;
if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue;
/* At this point, pWLoop is a candidate to be the next loop.
** Compute its cost */
rUnsorted = sqlite3LogEstAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow);
rUnsorted = sqlite3LogEstAdd(rUnsorted, pFrom->rUnsorted);
nOut = pFrom->nRow + pWLoop->nOut;
maskNew = pFrom->maskLoop | pWLoop->maskSelf;
if( isOrdered<0 ){
|
> > > > > > > > |
3581 3582 3583 3584 3585 3586 3587 3588 3589 3590 3591 3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 |
LogEst rUnsorted; /* Unsorted cost of (pFrom+pWLoop) */
i8 isOrdered = pFrom->isOrdered; /* isOrdered for (pFrom+pWLoop) */
Bitmask maskNew; /* Mask of src visited by (..) */
Bitmask revMask = 0; /* Mask of rev-order loops for (..) */
if( (pWLoop->prereq & ~pFrom->maskLoop)!=0 ) continue;
if( (pWLoop->maskSelf & pFrom->maskLoop)!=0 ) continue;
if( iLoop==0
&& (pWLoop->wsFlags & WHERE_AUTO_INDEX)!=0
&& pParse->nQueryLoop==0
){
/* Never put an automatic index in the outer loop if the query
** is only being run once. */
continue;
}
/* At this point, pWLoop is a candidate to be the next loop.
** Compute its cost */
rUnsorted = sqlite3LogEstAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow);
rUnsorted = sqlite3LogEstAdd(rUnsorted, pFrom->rUnsorted);
nOut = pFrom->nRow + pWLoop->nOut;
maskNew = pFrom->maskLoop | pWLoop->maskSelf;
if( isOrdered<0 ){
|
Changes to test/autoindex2.test.
214 215 216 217 218 219 220 221 222 223 224 225 226 227 |
WHERE
t1.ptime > 1393520400
AND param3<>9001
AND t3.flg7 = 1
AND t1.did = t2.did
AND t2.uid = t3.uid
ORDER BY t1.ptime desc LIMIT 500;
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USING INDEX t2x0 (did=?)} 0 2 2 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}}
#
# ^^^--- Before being fixed, the above was using an automatic covering
# on t3 and reordering the tables so that t3 was in the outer loop and
# implementing the ORDER BY clause using a B-Tree.
finish_test
|
| | > > > > > |
214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 |
WHERE
t1.ptime > 1393520400
AND param3<>9001
AND t3.flg7 = 1
AND t1.did = t2.did
AND t2.uid = t3.uid
ORDER BY t1.ptime desc LIMIT 500;
} {~/AUTO/}
#
# ^^^--- Before being fixed, the above was using an automatic covering
# on t3 and reordering the tables so that t3 was in the outer loop and
# implementing the ORDER BY clause using a B-Tree.
#
# This test is sanitized data received from a user. The original unsanitized
# data and STAT4 data is found in the th3private test repository. See one of
# the th3private check-ins on 2016-02-25. The test is much more accurate when
# STAT4 data is used.
finish_test
|