/ Check-in [c106b755]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c106b755369c1f8546e897ecd2ac56fd09d6e885
User & Date: drh 2014-11-05 13:13:13
Context
2014-11-05
14:19
Add a test case to check that the previous commit is effective. check-in: 948d6e5d 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: c106b755 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: 7974c0ed user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  4008   4008         whereLoopDelete(db, p);
  4009   4009       }
  4010   4010       sqlite3DbFree(db, pWInfo);
  4011   4011     }
  4012   4012   }
  4013   4013   
  4014   4014   /*
  4015         -** Return TRUE if both of the following are true:
         4015  +** Return TRUE if all of the following are true:
  4016   4016   **
  4017   4017   **   (1)  X has the same or lower cost that Y
  4018   4018   **   (2)  X is a proper subset of Y
         4019  +**   (3)  X skips at least as many columns as Y
  4019   4020   **
  4020   4021   ** By "proper subset" we mean that X uses fewer WHERE clause terms
  4021   4022   ** than Y and that every WHERE clause term used by X is also used
  4022   4023   ** by Y.
  4023   4024   **
  4024   4025   ** If X is a proper subset of Y then Y is a better choice and ought
  4025   4026   ** to have a lower cost.  This routine returns TRUE when that cost 
  4026         -** relationship is inverted and needs to be adjusted.
         4027  +** relationship is inverted and needs to be adjusted.  The third rule
         4028  +** was added because if X uses skip-scan less than Y it still might
         4029  +** deserve a lower cost even if it is a proper subset of Y.
  4027   4030   */
  4028   4031   static int whereLoopCheaperProperSubset(
  4029   4032     const WhereLoop *pX,       /* First WhereLoop to compare */
  4030   4033     const WhereLoop *pY        /* Compare against this WhereLoop */
  4031   4034   ){
  4032   4035     int i, j;
  4033   4036     if( pX->nLTerm-pX->nSkip >= pY->nLTerm-pY->nSkip ){
  4034   4037       return 0; /* X is not a subset of Y */
  4035   4038     }
         4039  +  if( pY->nSkip > pX->nSkip ) return 0;
  4036   4040     if( pX->rRun >= pY->rRun ){
  4037   4041       if( pX->rRun > pY->rRun ) return 0;    /* X costs more than Y */
  4038   4042       if( pX->nOut > pY->nOut ) return 0;    /* X costs more than Y */
  4039   4043     }
  4040   4044     for(i=pX->nLTerm-1; i>=0; i--){
  4041   4045       if( pX->aLTerm[i]==0 ) continue;
  4042   4046       for(j=pY->nLTerm-1; j>=0; j--){
................................................................................
  4064   4068   static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){
  4065   4069     if( (pTemplate->wsFlags & WHERE_INDEXED)==0 ) return;
  4066   4070     for(; p; p=p->pNextLoop){
  4067   4071       if( p->iTab!=pTemplate->iTab ) continue;
  4068   4072       if( (p->wsFlags & WHERE_INDEXED)==0 ) continue;
  4069   4073       if( whereLoopCheaperProperSubset(p, pTemplate) ){
  4070   4074         /* Adjust pTemplate cost downward so that it is cheaper than its 
  4071         -      ** subset p.  Except, do not adjust the cost estimate downward for
  4072         -      ** a loop that skips more columns. */
  4073         -      if( pTemplate->nSkip>p->nSkip ) continue;
         4075  +      ** subset p. */
  4074   4076         WHERETRACE(0x80,("subset cost adjustment %d,%d to %d,%d\n",
  4075   4077                          pTemplate->rRun, pTemplate->nOut, p->rRun, p->nOut-1));
  4076   4078         pTemplate->rRun = p->rRun;
  4077   4079         pTemplate->nOut = p->nOut - 1;
  4078   4080       }else if( whereLoopCheaperProperSubset(pTemplate, p) ){
  4079   4081         /* Adjust pTemplate cost upward so that it is costlier than p since
  4080   4082         ** pTemplate is a proper subset of p */