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 |
Timelines: | family | ancestors | descendants | both | planner-improvements |
Files: | files | file ages | folders |
SHA1: |
5957e793414ff80ed01a7a67e70c3fd0 |
User & Date: | drh 2016-02-25 18:03:38.680 |
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: aab53a2189 user: drh tags: planner-improvements) | |
18:03 | Do not use an automatic index on an outer loop that only runs once. (check-in: 5957e79341 user: drh tags: planner-improvements) | |
16:04 | Take the LIMIT clause into account when estimating the cost of sorting. (check-in: d491745cab 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 | 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; | | > > > > > | 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 |