SQLite

Check-in [5957e79341]
Login

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: 5957e793414ff80ed01a7a67e70c3fd096a3f6e0
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
Unified Diff Ignore Whitespace Patch
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