SQLite

Check-in [991a76bf42]
Login

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

Overview
Comment:The restriction on using automatic indexes for low cardinality loops only applies if the cardinality is restricted by a LIMIT clause.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | exp-indexed-clause
Files: files | file ages | folders
SHA3-256: 991a76bf427ade0059a3308454ebf1e7908bf959619752a13fbb3d41b4b0a162
User & Date: drh 2018-05-14 18:09:20.470
Context
2018-05-14
18:09
The restriction on using automatic indexes for low cardinality loops only applies if the cardinality is restricted by a LIMIT clause. (Closed-Leaf check-in: 991a76bf42 user: drh tags: exp-indexed-clause)
17:12
Experimental syntax enhancement for an "INDEXED" keyword following a FROM-clause subquery that indicates that an automatic index on that subquery is suggested. (check-in: 32b3d11050 user: drh tags: exp-indexed-clause)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
4001
4002
4003
4004
4005
4006
4007
4008



4009
4010
4011
4012
4013
4014
4015
4016
4017

        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);








|
>
>
>

|








4001
4002
4003
4004
4005
4006
4007
4008
4009
4010
4011
4012
4013
4014
4015
4016
4017
4018
4019
4020

        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
         && (pWInfo->wctrlFlags & WHERE_USE_LIMIT)!=0
         && pWInfo->iLimit<10
        ){
          /* Do not use an automatic index if the this loop is expected
          ** to run less than twice due to a LIMIT clause. */
          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);
︙︙