SQLite

Check-in [ea8b091004]
Login

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

Overview
Comment:Make sure that an index that covers a proper superset of the WHERE clause terms of some other index has a lower cost than the other index.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | query-plan-experiments
Files: files | file ages | folders
SHA1: ea8b0910040198751551b0b960e6b783913607df
User & Date: drh 2014-03-31 18:24:18.867
Context
2014-03-31
19:49
Also make sure an index that is a proper subset of some other index has a higher cost than that other index. Add test cases. (check-in: b7830d232b user: drh tags: query-plan-experiments)
18:24
Make sure that an index that covers a proper superset of the WHERE clause terms of some other index has a lower cost than the other index. (check-in: ea8b091004 user: drh tags: query-plan-experiments)
2014-03-29
21:16
Experiments in picking better query plans, especially when the usage of one index is a subset of another. (check-in: 8f869ca7a6 user: drh tags: query-plan-experiments)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
3706
3707
3708
3709
3710
3711
3712













































3713
3714
3715
3716
3717
3718
3719
      WhereLoop *p = pWInfo->pLoops;
      pWInfo->pLoops = p->pNextLoop;
      whereLoopDelete(db, p);
    }
    sqlite3DbFree(db, pWInfo);
  }
}














































/*
** Search the list of WhereLoops in *ppPrev looking for one that can be
** supplanted by pTemplate.
**
** Return NULL if the WhereLoop list contains an entry that can supplant
** pTemplate, in other words if pTemplate does not belong on the list.







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







3706
3707
3708
3709
3710
3711
3712
3713
3714
3715
3716
3717
3718
3719
3720
3721
3722
3723
3724
3725
3726
3727
3728
3729
3730
3731
3732
3733
3734
3735
3736
3737
3738
3739
3740
3741
3742
3743
3744
3745
3746
3747
3748
3749
3750
3751
3752
3753
3754
3755
3756
3757
3758
3759
3760
3761
3762
3763
3764
      WhereLoop *p = pWInfo->pLoops;
      pWInfo->pLoops = p->pNextLoop;
      whereLoopDelete(db, p);
    }
    sqlite3DbFree(db, pWInfo);
  }
}

/*
** Compare every WhereLoop X on the list p against pTemplate.  If:
**
**    (1) both X and pTemplate refer to the same table, and
**    (2) both X and pTemplate use a single index, and
**    (3) pTemplate uses all the same WHERE clause terms as X plus
**        at least one more term,
**
** then make sure the cost pTemplate is less than the cost of X, adjusting
** the cost of pTemplate downward if necessary.
**
** Example: When computing the query plan for the SELECT below:
**
**     CREATE TABLE t1(a,b,c,d);
**     CREATE INDEX t1abc ON t1(a,b,c);
**     CREATE INDEX t1bc ON t1(b,c);
**     SELECT d FROM t1 WHERE a=? AND b=? AND c>=? ORDER BY c;
** 
** Make sure the cost of using three three columns of index t1abc is less
** than the cost of using both columns of t1bc.
*/
static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){
  if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return;
  if( pTemplate->nLTerm==0 ) return;
  for(; p; p=p->pNextLoop){
    if( p->iTab==pTemplate->iTab
     && (p->wsFlags & WHERE_INDEXED)!=0
     && p->nLTerm<pTemplate->nLTerm
     && (p->rRun<pTemplate->rRun || (p->rRun==pTemplate->rRun &&
                                     p->nOut<=pTemplate->nOut))
    ){
      int i, j;
      for(j=0, i=p->nLTerm-1; i>=0 && j>=0; i--){
        for(j=pTemplate->nLTerm-1; j>=0; j--){
          if( pTemplate->aLTerm[j]==p->aLTerm[i] ) break;
        }
      }
      if( j>=0 ){
        pTemplate->rRun = p->rRun;
        pTemplate->nOut = p->nOut - 1;
      }
    }
  }
}

/*
** Search the list of WhereLoops in *ppPrev looking for one that can be
** supplanted by pTemplate.
**
** Return NULL if the WhereLoop list contains an entry that can supplant
** pTemplate, in other words if pTemplate does not belong on the list.
3743
3744
3745
3746
3747
3748
3749





3750
3751
3752
3753
3754
3755
3756
3757
3758
3759
3760
3761
3762
3763
3764
3765
3766
3767
3768
3769
3770
3771
3772





3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783
3784
3785
3786
3787
3788
3789
                 || p->rSetup==pTemplate->rSetup );

    /* whereLoopAddBtree() always generates and inserts the automatic index
    ** case first.  Hence compatible candidate WhereLoops never have a larger
    ** rSetup. Call this SETUP-INVARIANT */
    assert( p->rSetup>=pTemplate->rSetup );






    if( (p->prereq & pTemplate->prereq)==p->prereq
     && p->rSetup<=pTemplate->rSetup
     && p->rRun<=pTemplate->rRun
     && p->nOut<=pTemplate->nOut
    ){
      /* This branch taken when p is equal or better than pTemplate in 
      ** all of (1) dependencies (2) setup-cost, (3) run-cost, and
      ** (4) number of output rows. */
      assert( p->rSetup==pTemplate->rSetup );
      if( p->prereq==pTemplate->prereq
       && p->nLTerm<pTemplate->nLTerm
       && (p->wsFlags & pTemplate->wsFlags & WHERE_INDEXED)!=0
       && (p->u.btree.pIndex==pTemplate->u.btree.pIndex
          || pTemplate->rRun+p->nLTerm<=p->rRun+pTemplate->nLTerm)
      ){
        /* Overwrite an existing WhereLoop with an similar one that uses
        ** more terms of the index */
        break;
      }else{
        /* There is an existing WhereLoop that is better than pTemplate */
        return 0;
      }
    }





    if( (p->prereq & pTemplate->prereq)==pTemplate->prereq
     && p->rRun>=pTemplate->rRun
     && p->nOut>=pTemplate->nOut
    ){
      /* Overwrite an existing WhereLoop with a better one: one that is
      ** better at one of (1) dependencies, (2) setup-cost, (3) run-cost
      ** or (4) number of output rows, and is no worse in any of those
      ** categories. */
      assert( p->rSetup>=pTemplate->rSetup ); /* SETUP-INVARIANT above */
      break;
    }
  }
  return ppPrev;
}

/*
** Insert or replace a WhereLoop entry using the template supplied.







>
>
>
>
>
|
|
|
|

<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
|
|
|
>
>
>
>
>
|
|
|

<
<
<
<

|







3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
3804















3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816




3817
3818
3819
3820
3821
3822
3823
3824
3825
                 || p->rSetup==pTemplate->rSetup );

    /* whereLoopAddBtree() always generates and inserts the automatic index
    ** case first.  Hence compatible candidate WhereLoops never have a larger
    ** rSetup. Call this SETUP-INVARIANT */
    assert( p->rSetup>=pTemplate->rSetup );

    /* If existing WhereLoop p is better than pTemplate, pTemplate can be
    ** discarded.  WhereLoop p is better if:
    **   (1)  p has no more dependencies than pTemplate, and
    **   (2)  p has an equal or lower cost than pTemplate
    */
    if( (p->prereq & pTemplate->prereq)==p->prereq    /* (1)  */
     && p->rSetup<=pTemplate->rSetup                  /* (2a) */
     && p->rRun<=pTemplate->rRun                      /* (2b) */
     && p->nOut<=pTemplate->nOut                      /* (2c) */
    ){















      return 0;  /* Discard pTemplate */
    }

    /* If pTemplate is always better than p, then cause p to be overwritten
    ** with pTemplate.  pTemplate is better than p if:
    **   (1)  pTemplate has no more dependences than p, and
    **   (2)  pTemplate has an equal or lower cost than p.
    */
    if( (p->prereq & pTemplate->prereq)==pTemplate->prereq   /* (1)  */
     && p->rRun>=pTemplate->rRun                             /* (2a) */
     && p->nOut>=pTemplate->nOut                             /* (2b) */
    ){




      assert( p->rSetup>=pTemplate->rSetup ); /* SETUP-INVARIANT above */
      break;   /* Cause p to be overwritten by pTemplate */
    }
  }
  return ppPrev;
}

/*
** Insert or replace a WhereLoop entry using the template supplied.
3797
3798
3799
3800
3801
3802
3803
3804
3805
3806
3807
3808
3809
3810
3811
3812
3813
3814
3815
3816
3817
3818
3819
** If pBuilder->pOrSet is not NULL then we care about only the
** prerequisites and rRun and nOut costs of the N best loops.  That
** information is gathered in the pBuilder->pOrSet object.  This special
** processing mode is used only for OR clause processing.
**
** When accumulating multiple loops (when pBuilder->pOrSet is NULL) we
** still might overwrite similar loops with the new template if the
** template is better.  Loops may be overwritten if the following 
** conditions are met:
**
**    (1)  They have the same iTab.
**    (2)  They have the same iSortIdx.
**    (3)  The template has same or fewer dependencies than the current loop
**    (4)  The template has the same or lower cost than the current loop
**    (5)  The template uses more terms of the same index but has no additional
**         dependencies          
*/
static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){
  WhereLoop **ppPrev, *p;
  WhereInfo *pWInfo = pBuilder->pWInfo;
  sqlite3 *db = pWInfo->pParse->db;

  /* If pBuilder->pOrSet is defined, then only keep track of the costs







|






<
<







3833
3834
3835
3836
3837
3838
3839
3840
3841
3842
3843
3844
3845
3846


3847
3848
3849
3850
3851
3852
3853
** If pBuilder->pOrSet is not NULL then we care about only the
** prerequisites and rRun and nOut costs of the N best loops.  That
** information is gathered in the pBuilder->pOrSet object.  This special
** processing mode is used only for OR clause processing.
**
** When accumulating multiple loops (when pBuilder->pOrSet is NULL) we
** still might overwrite similar loops with the new template if the
** new template is better.  Loops may be overwritten if the following 
** conditions are met:
**
**    (1)  They have the same iTab.
**    (2)  They have the same iSortIdx.
**    (3)  The template has same or fewer dependencies than the current loop
**    (4)  The template has the same or lower cost than the current loop


*/
static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){
  WhereLoop **ppPrev, *p;
  WhereInfo *pWInfo = pBuilder->pWInfo;
  sqlite3 *db = pWInfo->pParse->db;

  /* If pBuilder->pOrSet is defined, then only keep track of the costs
3833
3834
3835
3836
3837
3838
3839

3840
3841
3842
3843
3844
3845
3846
    }
#endif
    return SQLITE_OK;
  }

  /* Look for an existing WhereLoop to replace with pTemplate
  */

  ppPrev = whereLoopFindLesser(&pWInfo->pLoops, pTemplate);

  if( ppPrev==0 ){
    /* There already exists a WhereLoop on the list that is better
    ** than pTemplate, so just ignore pTemplate */
#if WHERETRACE_ENABLED /* 0x8 */
    if( sqlite3WhereTrace & 0x8 ){







>







3867
3868
3869
3870
3871
3872
3873
3874
3875
3876
3877
3878
3879
3880
3881
    }
#endif
    return SQLITE_OK;
  }

  /* Look for an existing WhereLoop to replace with pTemplate
  */
  whereLoopAdjustCost(pWInfo->pLoops, pTemplate);
  ppPrev = whereLoopFindLesser(&pWInfo->pLoops, pTemplate);

  if( ppPrev==0 ){
    /* There already exists a WhereLoop on the list that is better
    ** than pTemplate, so just ignore pTemplate */
#if WHERETRACE_ENABLED /* 0x8 */
    if( sqlite3WhereTrace & 0x8 ){