/ Check-in [683dd379]
Login

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

Overview
Comment:Merge changes to the query planner that strive to ensure that any index usage that is a proper subset of some other index usage always has a slightly higher cost.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 683dd379a293b2f330e1e4cd746f190527fe48ee
User & Date: drh 2014-04-04 18:20:35
Context
2014-04-04
18:49
Performance improvements when reading large blobs, especially if SQLITE_DIRECT_OVERFLOW_READ is defined. check-in: 2312eb6a user: drh tags: trunk
18:37
Merge the latest trunk changes into the threads branch. check-in: 39ac79cf user: drh tags: threads
18:20
Merge changes to the query planner that strive to ensure that any index usage that is a proper subset of some other index usage always has a slightly higher cost. check-in: 683dd379 user: drh tags: trunk
14:12
Ensure the "PRAGMA journal_mode=WAL" works coming from any other journal_mode with ATTACH-ed databases. check-in: e54330b4 user: drh tags: trunk
2014-03-31
20:05
Remove an unnecessary conditional. Closed-Leaf check-in: 7473c4df user: drh tags: query-plan-experiments
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3706   3706         WhereLoop *p = pWInfo->pLoops;
  3707   3707         pWInfo->pLoops = p->pNextLoop;
  3708   3708         whereLoopDelete(db, p);
  3709   3709       }
  3710   3710       sqlite3DbFree(db, pWInfo);
  3711   3711     }
  3712   3712   }
         3713  +
         3714  +/*
         3715  +** Return TRUE if the set of WHERE clause terms used by pA is a proper
         3716  +** subset of the WHERE clause terms used by pB.
         3717  +*/
         3718  +static int whereLoopProperSubset(const WhereLoop *pA, const WhereLoop *pB){
         3719  +  int i, j;
         3720  +  assert( pA->nLTerm<pB->nLTerm );  /* Checked by calling function */
         3721  +  for(j=0, i=pA->nLTerm-1; i>=0 && j>=0; i--){
         3722  +    for(j=pB->nLTerm-1; j>=0; j--){
         3723  +      if( pB->aLTerm[j]==pA->aLTerm[i] ) break;
         3724  +    }
         3725  +  }
         3726  +  return j>=0;
         3727  +}
         3728  +
         3729  +/*
         3730  +** Try to adjust the cost of WhereLoop pTemplate upwards or downwards so
         3731  +** that:
         3732  +**
         3733  +**   (1) pTemplate costs less than any other WhereLoops that are a proper
         3734  +**       subset of pTemplate
         3735  +**
         3736  +**   (2) pTemplate costs more than any other WhereLoops for which pTemplate
         3737  +**       is a proper subset.
         3738  +**
         3739  +** To say "WhereLoop X is a proper subset of Y" means that X uses fewer
         3740  +** WHERE clause terms than Y and that every WHERE clause term used by X is
         3741  +** also used by Y.
         3742  +*/
         3743  +static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){
         3744  +  if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return;
         3745  +  for(; p; p=p->pNextLoop){
         3746  +    if( p->iTab!=pTemplate->iTab ) continue;
         3747  +    if( (p->wsFlags & WHERE_INDEXED)==0 ) continue;
         3748  +    if( p->nLTerm<pTemplate->nLTerm
         3749  +     && (p->rRun<pTemplate->rRun || (p->rRun==pTemplate->rRun &&
         3750  +                                     p->nOut<=pTemplate->nOut))
         3751  +     && whereLoopProperSubset(p, pTemplate)
         3752  +    ){
         3753  +      pTemplate->rRun = p->rRun;
         3754  +      pTemplate->nOut = p->nOut - 1;
         3755  +    }else
         3756  +    if( p->nLTerm>pTemplate->nLTerm
         3757  +     && (p->rRun>pTemplate->rRun || (p->rRun==pTemplate->rRun &&
         3758  +                                     p->nOut>=pTemplate->nOut))
         3759  +     && whereLoopProperSubset(pTemplate, p)
         3760  +    ){
         3761  +      pTemplate->rRun = p->rRun;
         3762  +      pTemplate->nOut = p->nOut + 1;
         3763  +    }
         3764  +  }
         3765  +}
         3766  +
         3767  +/*
         3768  +** Search the list of WhereLoops in *ppPrev looking for one that can be
         3769  +** supplanted by pTemplate.
         3770  +**
         3771  +** Return NULL if the WhereLoop list contains an entry that can supplant
         3772  +** pTemplate, in other words if pTemplate does not belong on the list.
         3773  +**
         3774  +** If pX is a WhereLoop that pTemplate can supplant, then return the
         3775  +** link that points to pX.
         3776  +**
         3777  +** If pTemplate cannot supplant any existing element of the list but needs
         3778  +** to be added to the list, then return a pointer to the tail of the list.
         3779  +*/
         3780  +static WhereLoop **whereLoopFindLesser(
         3781  +  WhereLoop **ppPrev,
         3782  +  const WhereLoop *pTemplate
         3783  +){
         3784  +  WhereLoop *p;
         3785  +  for(p=(*ppPrev); p; ppPrev=&p->pNextLoop, p=*ppPrev){
         3786  +    if( p->iTab!=pTemplate->iTab || p->iSortIdx!=pTemplate->iSortIdx ){
         3787  +      /* If either the iTab or iSortIdx values for two WhereLoop are different
         3788  +      ** then those WhereLoops need to be considered separately.  Neither is
         3789  +      ** a candidate to replace the other. */
         3790  +      continue;
         3791  +    }
         3792  +    /* In the current implementation, the rSetup value is either zero
         3793  +    ** or the cost of building an automatic index (NlogN) and the NlogN
         3794  +    ** is the same for compatible WhereLoops. */
         3795  +    assert( p->rSetup==0 || pTemplate->rSetup==0 
         3796  +                 || p->rSetup==pTemplate->rSetup );
         3797  +
         3798  +    /* whereLoopAddBtree() always generates and inserts the automatic index
         3799  +    ** case first.  Hence compatible candidate WhereLoops never have a larger
         3800  +    ** rSetup. Call this SETUP-INVARIANT */
         3801  +    assert( p->rSetup>=pTemplate->rSetup );
         3802  +
         3803  +    /* If existing WhereLoop p is better than pTemplate, pTemplate can be
         3804  +    ** discarded.  WhereLoop p is better if:
         3805  +    **   (1)  p has no more dependencies than pTemplate, and
         3806  +    **   (2)  p has an equal or lower cost than pTemplate
         3807  +    */
         3808  +    if( (p->prereq & pTemplate->prereq)==p->prereq    /* (1)  */
         3809  +     && p->rSetup<=pTemplate->rSetup                  /* (2a) */
         3810  +     && p->rRun<=pTemplate->rRun                      /* (2b) */
         3811  +     && p->nOut<=pTemplate->nOut                      /* (2c) */
         3812  +    ){
         3813  +      return 0;  /* Discard pTemplate */
         3814  +    }
         3815  +
         3816  +    /* If pTemplate is always better than p, then cause p to be overwritten
         3817  +    ** with pTemplate.  pTemplate is better than p if:
         3818  +    **   (1)  pTemplate has no more dependences than p, and
         3819  +    **   (2)  pTemplate has an equal or lower cost than p.
         3820  +    */
         3821  +    if( (p->prereq & pTemplate->prereq)==pTemplate->prereq   /* (1)  */
         3822  +     && p->rRun>=pTemplate->rRun                             /* (2a) */
         3823  +     && p->nOut>=pTemplate->nOut                             /* (2b) */
         3824  +    ){
         3825  +      assert( p->rSetup>=pTemplate->rSetup ); /* SETUP-INVARIANT above */
         3826  +      break;   /* Cause p to be overwritten by pTemplate */
         3827  +    }
         3828  +  }
         3829  +  return ppPrev;
         3830  +}
  3713   3831   
  3714   3832   /*
  3715   3833   ** Insert or replace a WhereLoop entry using the template supplied.
  3716   3834   **
  3717   3835   ** An existing WhereLoop entry might be overwritten if the new template
  3718   3836   ** is better and has fewer dependencies.  Or the template will be ignored
  3719   3837   ** and no insert will occur if an existing WhereLoop is faster and has
  3720   3838   ** fewer dependencies than the template.  Otherwise a new WhereLoop is
  3721   3839   ** added based on the template.
  3722   3840   **
  3723         -** If pBuilder->pOrSet is not NULL then we only care about only the
         3841  +** If pBuilder->pOrSet is not NULL then we care about only the
  3724   3842   ** prerequisites and rRun and nOut costs of the N best loops.  That
  3725   3843   ** information is gathered in the pBuilder->pOrSet object.  This special
  3726   3844   ** processing mode is used only for OR clause processing.
  3727   3845   **
  3728   3846   ** When accumulating multiple loops (when pBuilder->pOrSet is NULL) we
  3729   3847   ** still might overwrite similar loops with the new template if the
  3730         -** template is better.  Loops may be overwritten if the following 
         3848  +** new template is better.  Loops may be overwritten if the following 
  3731   3849   ** conditions are met:
  3732   3850   **
  3733   3851   **    (1)  They have the same iTab.
  3734   3852   **    (2)  They have the same iSortIdx.
  3735   3853   **    (3)  The template has same or fewer dependencies than the current loop
  3736   3854   **    (4)  The template has the same or lower cost than the current loop
  3737         -**    (5)  The template uses more terms of the same index but has no additional
  3738         -**         dependencies          
  3739   3855   */
  3740   3856   static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){
  3741         -  WhereLoop **ppPrev, *p, *pNext = 0;
         3857  +  WhereLoop **ppPrev, *p;
  3742   3858     WhereInfo *pWInfo = pBuilder->pWInfo;
  3743   3859     sqlite3 *db = pWInfo->pParse->db;
  3744   3860   
  3745   3861     /* If pBuilder->pOrSet is defined, then only keep track of the costs
  3746   3862     ** and prereqs.
  3747   3863     */
  3748   3864     if( pBuilder->pOrSet!=0 ){
................................................................................
  3757   3873         sqlite3DebugPrintf(x?"   or-%d:  ":"   or-X:  ", n);
  3758   3874         whereLoopPrint(pTemplate, pBuilder->pWC);
  3759   3875       }
  3760   3876   #endif
  3761   3877       return SQLITE_OK;
  3762   3878     }
  3763   3879   
  3764         -  /* Search for an existing WhereLoop to overwrite, or which takes
  3765         -  ** priority over pTemplate.
  3766         -  */
  3767         -  for(ppPrev=&pWInfo->pLoops, p=*ppPrev; p; ppPrev=&p->pNextLoop, p=*ppPrev){
  3768         -    if( p->iTab!=pTemplate->iTab || p->iSortIdx!=pTemplate->iSortIdx ){
  3769         -      /* If either the iTab or iSortIdx values for two WhereLoop are different
  3770         -      ** then those WhereLoops need to be considered separately.  Neither is
  3771         -      ** a candidate to replace the other. */
  3772         -      continue;
  3773         -    }
  3774         -    /* In the current implementation, the rSetup value is either zero
  3775         -    ** or the cost of building an automatic index (NlogN) and the NlogN
  3776         -    ** is the same for compatible WhereLoops. */
  3777         -    assert( p->rSetup==0 || pTemplate->rSetup==0 
  3778         -                 || p->rSetup==pTemplate->rSetup );
  3779         -
  3780         -    /* whereLoopAddBtree() always generates and inserts the automatic index
  3781         -    ** case first.  Hence compatible candidate WhereLoops never have a larger
  3782         -    ** rSetup. Call this SETUP-INVARIANT */
  3783         -    assert( p->rSetup>=pTemplate->rSetup );
  3784         -
  3785         -    if( (p->prereq & pTemplate->prereq)==p->prereq
  3786         -     && p->rSetup<=pTemplate->rSetup
  3787         -     && p->rRun<=pTemplate->rRun
  3788         -     && p->nOut<=pTemplate->nOut
  3789         -    ){
  3790         -      /* This branch taken when p is equal or better than pTemplate in 
  3791         -      ** all of (1) dependencies (2) setup-cost, (3) run-cost, and
  3792         -      ** (4) number of output rows. */
  3793         -      assert( p->rSetup==pTemplate->rSetup );
  3794         -      if( p->prereq==pTemplate->prereq
  3795         -       && p->nLTerm<pTemplate->nLTerm
  3796         -       && (p->wsFlags & pTemplate->wsFlags & WHERE_INDEXED)!=0
  3797         -       && (p->u.btree.pIndex==pTemplate->u.btree.pIndex
  3798         -          || pTemplate->rRun+p->nLTerm<=p->rRun+pTemplate->nLTerm)
  3799         -      ){
  3800         -        /* Overwrite an existing WhereLoop with an similar one that uses
  3801         -        ** more terms of the index */
  3802         -        pNext = p->pNextLoop;
  3803         -        break;
  3804         -      }else{
  3805         -        /* pTemplate is not helpful.
  3806         -        ** Return without changing or adding anything */
  3807         -        goto whereLoopInsert_noop;
  3808         -      }
  3809         -    }
  3810         -    if( (p->prereq & pTemplate->prereq)==pTemplate->prereq
  3811         -     && p->rRun>=pTemplate->rRun
  3812         -     && p->nOut>=pTemplate->nOut
  3813         -    ){
  3814         -      /* Overwrite an existing WhereLoop with a better one: one that is
  3815         -      ** better at one of (1) dependencies, (2) setup-cost, (3) run-cost
  3816         -      ** or (4) number of output rows, and is no worse in any of those
  3817         -      ** categories. */
  3818         -      assert( p->rSetup>=pTemplate->rSetup ); /* SETUP-INVARIANT above */
  3819         -      pNext = p->pNextLoop;
  3820         -      break;
  3821         -    }
         3880  +  /* Look for an existing WhereLoop to replace with pTemplate
         3881  +  */
         3882  +  whereLoopAdjustCost(pWInfo->pLoops, pTemplate);
         3883  +  ppPrev = whereLoopFindLesser(&pWInfo->pLoops, pTemplate);
         3884  +
         3885  +  if( ppPrev==0 ){
         3886  +    /* There already exists a WhereLoop on the list that is better
         3887  +    ** than pTemplate, so just ignore pTemplate */
         3888  +#if WHERETRACE_ENABLED /* 0x8 */
         3889  +    if( sqlite3WhereTrace & 0x8 ){
         3890  +      sqlite3DebugPrintf("ins-noop: ");
         3891  +      whereLoopPrint(pTemplate, pBuilder->pWC);
         3892  +    }
         3893  +#endif
         3894  +    return SQLITE_OK;  
         3895  +  }else{
         3896  +    p = *ppPrev;
  3822   3897     }
  3823   3898   
  3824   3899     /* If we reach this point it means that either p[] should be overwritten
  3825   3900     ** with pTemplate[] if p[] exists, or if p==NULL then allocate a new
  3826   3901     ** WhereLoop and insert it.
  3827   3902     */
  3828   3903   #if WHERETRACE_ENABLED /* 0x8 */
................................................................................
  3832   3907         whereLoopPrint(p, pBuilder->pWC);
  3833   3908       }
  3834   3909       sqlite3DebugPrintf("ins-new:  ");
  3835   3910       whereLoopPrint(pTemplate, pBuilder->pWC);
  3836   3911     }
  3837   3912   #endif
  3838   3913     if( p==0 ){
  3839         -    p = sqlite3DbMallocRaw(db, sizeof(WhereLoop));
         3914  +    /* Allocate a new WhereLoop to add to the end of the list */
         3915  +    *ppPrev = p = sqlite3DbMallocRaw(db, sizeof(WhereLoop));
  3840   3916       if( p==0 ) return SQLITE_NOMEM;
  3841   3917       whereLoopInit(p);
         3918  +    p->pNextLoop = 0;
         3919  +  }else{
         3920  +    /* We will be overwriting WhereLoop p[].  But before we do, first
         3921  +    ** go through the rest of the list and delete any other entries besides
         3922  +    ** p[] that are also supplated by pTemplate */
         3923  +    WhereLoop **ppTail = &p->pNextLoop;
         3924  +    WhereLoop *pToDel;
         3925  +    while( *ppTail ){
         3926  +      ppTail = whereLoopFindLesser(ppTail, pTemplate);
         3927  +      if( NEVER(ppTail==0) ) break;
         3928  +      pToDel = *ppTail;
         3929  +      if( pToDel==0 ) break;
         3930  +      *ppTail = pToDel->pNextLoop;
         3931  +#if WHERETRACE_ENABLED /* 0x8 */
         3932  +      if( sqlite3WhereTrace & 0x8 ){
         3933  +        sqlite3DebugPrintf("ins-del: ");
         3934  +        whereLoopPrint(pToDel, pBuilder->pWC);
         3935  +      }
         3936  +#endif
         3937  +      whereLoopDelete(db, pToDel);
         3938  +    }
  3842   3939     }
  3843   3940     whereLoopXfer(db, p, pTemplate);
  3844         -  p->pNextLoop = pNext;
  3845         -  *ppPrev = p;
  3846   3941     if( (p->wsFlags & WHERE_VIRTUALTABLE)==0 ){
  3847   3942       Index *pIndex = p->u.btree.pIndex;
  3848   3943       if( pIndex && pIndex->tnum==0 ){
  3849   3944         p->u.btree.pIndex = 0;
  3850   3945       }
  3851   3946     }
  3852   3947     return SQLITE_OK;
  3853         -
  3854         -  /* Jump here if the insert is a no-op */
  3855         -whereLoopInsert_noop:
  3856         -#if WHERETRACE_ENABLED /* 0x8 */
  3857         -  if( sqlite3WhereTrace & 0x8 ){
  3858         -    sqlite3DebugPrintf("ins-noop: ");
  3859         -    whereLoopPrint(pTemplate, pBuilder->pWC);
  3860         -  }
  3861         -#endif
  3862         -  return SQLITE_OK;  
  3863   3948   }
  3864   3949   
  3865   3950   /*
  3866   3951   ** Adjust the WhereLoop.nOut value downward to account for terms of the
  3867   3952   ** WHERE clause that reference the loop but which are not used by an
  3868   3953   ** index.
  3869   3954   **

Added test/whereH.test.

            1  +# 2014-03-31
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# 
           12  +# Test cases for query planning decisions where one candidate index
           13  +# covers a proper superset of the WHERE clause terms of another
           14  +# candidate index.
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +do_execsql_test whereH-1.1 {
           21  +  CREATE TABLE t1(a,b,c,d);
           22  +  CREATE INDEX t1abc ON t1(a,b,c);
           23  +  CREATE INDEX t1bc ON t1(b,c);
           24  +
           25  +  EXPLAIN QUERY PLAN
           26  +  SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
           27  +} {/INDEX t1abc /}
           28  +do_execsql_test whereH-1.2 {
           29  +  EXPLAIN QUERY PLAN
           30  +  SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
           31  +} {~/TEMP B-TREE FOR ORDER BY/}
           32  +
           33  +do_execsql_test whereH-2.1 {
           34  +  DROP TABLE t1;
           35  +  CREATE TABLE t1(a,b,c,d);
           36  +  CREATE INDEX t1bc ON t1(b,c);
           37  +  CREATE INDEX t1abc ON t1(a,b,c);
           38  +
           39  +  EXPLAIN QUERY PLAN
           40  +  SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
           41  +} {/INDEX t1abc /}
           42  +do_execsql_test whereH-2.2 {
           43  +  EXPLAIN QUERY PLAN
           44  +  SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
           45  +} {~/TEMP B-TREE FOR ORDER BY/}
           46  +
           47  +do_execsql_test whereH-3.1 {
           48  +  DROP TABLE t1;
           49  +  CREATE TABLE t1(a,b,c,d,e);
           50  +  CREATE INDEX t1cd ON t1(c,d);
           51  +  CREATE INDEX t1bcd ON t1(b,c,d);
           52  +  CREATE INDEX t1abcd ON t1(a,b,c,d);
           53  +
           54  +  EXPLAIN QUERY PLAN
           55  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
           56  +} {/INDEX t1abcd /}
           57  +do_execsql_test whereH-3.2 {
           58  +  EXPLAIN QUERY PLAN
           59  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
           60  +} {~/TEMP B-TREE FOR ORDER BY/}
           61  +
           62  +do_execsql_test whereH-4.1 {
           63  +  DROP TABLE t1;
           64  +  CREATE TABLE t1(a,b,c,d,e);
           65  +  CREATE INDEX t1cd ON t1(c,d);
           66  +  CREATE INDEX t1abcd ON t1(a,b,c,d);
           67  +  CREATE INDEX t1bcd ON t1(b,c,d);
           68  +
           69  +  EXPLAIN QUERY PLAN
           70  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
           71  +} {/INDEX t1abcd /}
           72  +do_execsql_test whereH-4.2 {
           73  +  EXPLAIN QUERY PLAN
           74  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
           75  +} {~/TEMP B-TREE FOR ORDER BY/}
           76  +
           77  +do_execsql_test whereH-5.1 {
           78  +  DROP TABLE t1;
           79  +  CREATE TABLE t1(a,b,c,d,e);
           80  +  CREATE INDEX t1bcd ON t1(b,c,d);
           81  +  CREATE INDEX t1cd ON t1(c,d);
           82  +  CREATE INDEX t1abcd ON t1(a,b,c,d);
           83  +
           84  +  EXPLAIN QUERY PLAN
           85  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
           86  +} {/INDEX t1abcd /}
           87  +do_execsql_test whereH-5.2 {
           88  +  EXPLAIN QUERY PLAN
           89  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
           90  +} {~/TEMP B-TREE FOR ORDER BY/}
           91  +
           92  +do_execsql_test whereH-6.1 {
           93  +  DROP TABLE t1;
           94  +  CREATE TABLE t1(a,b,c,d,e);
           95  +  CREATE INDEX t1bcd ON t1(b,c,d);
           96  +  CREATE INDEX t1abcd ON t1(a,b,c,d);
           97  +  CREATE INDEX t1cd ON t1(c,d);
           98  +
           99  +  EXPLAIN QUERY PLAN
          100  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
          101  +} {/INDEX t1abcd /}
          102  +do_execsql_test whereH-6.2 {
          103  +  EXPLAIN QUERY PLAN
          104  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
          105  +} {~/TEMP B-TREE FOR ORDER BY/}
          106  +
          107  +do_execsql_test whereH-7.1 {
          108  +  DROP TABLE t1;
          109  +  CREATE TABLE t1(a,b,c,d,e);
          110  +  CREATE INDEX t1abcd ON t1(a,b,c,d);
          111  +  CREATE INDEX t1bcd ON t1(b,c,d);
          112  +  CREATE INDEX t1cd ON t1(c,d);
          113  +
          114  +  EXPLAIN QUERY PLAN
          115  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
          116  +} {/INDEX t1abcd /}
          117  +do_execsql_test whereH-7.2 {
          118  +  EXPLAIN QUERY PLAN
          119  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
          120  +} {~/TEMP B-TREE FOR ORDER BY/}
          121  +
          122  +do_execsql_test whereH-8.1 {
          123  +  DROP TABLE t1;
          124  +  CREATE TABLE t1(a,b,c,d,e);
          125  +  CREATE INDEX t1abcd ON t1(a,b,c,d);
          126  +  CREATE INDEX t1cd ON t1(c,d);
          127  +  CREATE INDEX t1bcd ON t1(b,c,d);
          128  +
          129  +  EXPLAIN QUERY PLAN
          130  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
          131  +} {/INDEX t1abcd /}
          132  +do_execsql_test whereH-8.2 {
          133  +  EXPLAIN QUERY PLAN
          134  +  SELECT d FROM t1 WHERE a=? AND b=? AND c=? AND d>=? ORDER BY d;
          135  +} {~/TEMP B-TREE FOR ORDER BY/}
          136  +
          137  +
          138  +
          139  +finish_test