SQLite

Check-in [c106b75536]
Login

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

Overview
Comment:Enhance whereLoopCheaperProperSubset(X,Y) so that it does not report true if X uses skip-scan less than Y, since in that case X might deserve to be cheaper even if it is a proper subset.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c106b755369c1f8546e897ecd2ac56fd09d6e885
User & Date: drh 2014-11-05 13:13:13.983
Context
2014-11-05
14:19
Add a test case to check that the previous commit is effective. (check-in: 948d6e5d07 user: dan tags: trunk)
13:13
Enhance whereLoopCheaperProperSubset(X,Y) so that it does not report true if X uses skip-scan less than Y, since in that case X might deserve to be cheaper even if it is a proper subset. (check-in: c106b75536 user: drh tags: trunk)
09:07
Add the ".scanstats on" command to the shell tool. Executing this command causes the shell tool to print values from sqlite3_stmt_scanstatus() after each query is run. (check-in: 7974c0ed10 user: dan tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/where.c.
4008
4009
4010
4011
4012
4013
4014
4015

4016
4017
4018

4019
4020
4021
4022
4023
4024
4025
4026



4027
4028
4029
4030
4031
4032
4033
4034
4035

4036
4037
4038
4039
4040
4041
4042
4008
4009
4010
4011
4012
4013
4014

4015
4016
4017
4018
4019
4020
4021
4022
4023
4024
4025
4026

4027
4028
4029
4030
4031
4032
4033
4034
4035
4036
4037
4038
4039
4040
4041
4042
4043
4044
4045
4046







-
+



+







-
+
+
+









+







      whereLoopDelete(db, p);
    }
    sqlite3DbFree(db, pWInfo);
  }
}

/*
** Return TRUE if both of the following are true:
** Return TRUE if all of the following are true:
**
**   (1)  X has the same or lower cost that Y
**   (2)  X is a proper subset of Y
**   (3)  X skips at least as many columns as Y
**
** By "proper subset" we mean that X uses fewer WHERE clause terms
** than Y and that every WHERE clause term used by X is also used
** by Y.
**
** If X is a proper subset of Y then Y is a better choice and ought
** to have a lower cost.  This routine returns TRUE when that cost 
** relationship is inverted and needs to be adjusted.
** relationship is inverted and needs to be adjusted.  The third rule
** was added because if X uses skip-scan less than Y it still might
** deserve a lower cost even if it is a proper subset of Y.
*/
static int whereLoopCheaperProperSubset(
  const WhereLoop *pX,       /* First WhereLoop to compare */
  const WhereLoop *pY        /* Compare against this WhereLoop */
){
  int i, j;
  if( pX->nLTerm-pX->nSkip >= pY->nLTerm-pY->nSkip ){
    return 0; /* X is not a subset of Y */
  }
  if( pY->nSkip > pX->nSkip ) return 0;
  if( pX->rRun >= pY->rRun ){
    if( pX->rRun > pY->rRun ) return 0;    /* X costs more than Y */
    if( pX->nOut > pY->nOut ) return 0;    /* X costs more than Y */
  }
  for(i=pX->nLTerm-1; i>=0; i--){
    if( pX->aLTerm[i]==0 ) continue;
    for(j=pY->nLTerm-1; j>=0; j--){
4064
4065
4066
4067
4068
4069
4070
4071

4072
4073
4074
4075
4076
4077
4078
4079
4080
4068
4069
4070
4071
4072
4073
4074

4075


4076
4077
4078
4079
4080
4081
4082







-
+
-
-







static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){
  if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return;
  for(; p; p=p->pNextLoop){
    if( p->iTab!=pTemplate->iTab ) continue;
    if( (p->wsFlags & WHERE_INDEXED)==0 ) continue;
    if( whereLoopCheaperProperSubset(p, pTemplate) ){
      /* Adjust pTemplate cost downward so that it is cheaper than its 
      ** subset p.  Except, do not adjust the cost estimate downward for
      ** subset p. */
      ** a loop that skips more columns. */
      if( pTemplate->nSkip>p->nSkip ) continue;
      WHERETRACE(0x80,("subset cost adjustment %d,%d to %d,%d\n",
                       pTemplate->rRun, pTemplate->nOut, p->rRun, p->nOut-1));
      pTemplate->rRun = p->rRun;
      pTemplate->nOut = p->nOut - 1;
    }else if( whereLoopCheaperProperSubset(pTemplate, p) ){
      /* Adjust pTemplate cost upward so that it is costlier than p since
      ** pTemplate is a proper subset of p */