SQLite

Check-in [aab53a2189]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improvement on the previous check-in: disallow automatic indexes for any loop that is expected to run less than twice.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | planner-improvements
Files: files | file ages | folders
SHA1: aab53a21894ba51d325fd8f8f4bb4163ece74391
User & Date: drh 2016-02-25 18:22:09.962
Context
2016-02-25
18:40
Query planner improvements: Take the LIMIT into account when estimated the cost of an ORDER BY. Avoid automatic indexes on query loops that are expected to run less than twice. See [9e2b2681] for a later enhancement. (check-in: bf46179d44 user: drh tags: trunk)
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)
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
3595
3596
3597
3598
3599
3600

        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;








<
|
<
<
|
|
>








3581
3582
3583
3584
3585
3586
3587

3588


3589
3590
3591
3592
3593
3594
3595
3596
3597
3598

        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( (pWLoop->wsFlags & WHERE_AUTO_INDEX)!=0 && pFrom->nRow<10 ){


          /* Do not use an automatic index if the this loop is expected
          ** to run less than 2 times. */
          assert( 10==sqlite3LogEst(2) );
          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;
︙︙