/ Check-in [1e0db997]
Login

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

Overview
Comment:Add new WHERETRACE macros for better diagnostics of the query planner. Added a new test case for the performance regression fixed by the previous check-in.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:1e0db99797be2821716de7138931ebd5cf8fa63b
User & Date: drh 2010-10-21 03:13:59
Context
2010-10-21
12:34
Fix a typo-bug that prevented --disable-amalgamation from working in Makefile.in. Also fix an overly long line in Makfile.in. check-in: 2c3c4ba0 user: drh tags: trunk
03:13
Add new WHERETRACE macros for better diagnostics of the query planner. Added a new test case for the performance regression fixed by the previous check-in. check-in: 1e0db997 user: drh tags: trunk
02:05
Fix the query planner so that it uses the multi-index OR-clause solution if that is the lowest cost estimate. A prior bug cause the multi-index solution to be ignored in some circumstances. check-in: 28ba6255 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  4063   4063       Bitmask m;                  /* Bitmask value for j or bestJ */
  4064   4064       int isOptimal;              /* Iterator for optimal/non-optimal search */
  4065   4065       int nUnconstrained;         /* Number tables without INDEXED BY */
  4066   4066       Bitmask notIndexed;         /* Mask of tables that cannot use an index */
  4067   4067   
  4068   4068       memset(&bestPlan, 0, sizeof(bestPlan));
  4069   4069       bestPlan.rCost = SQLITE_BIG_DBL;
         4070  +    WHERETRACE(("*** Begin search for loop %d ***\n", i));
  4070   4071   
  4071   4072       /* Loop through the remaining entries in the FROM clause to find the
  4072   4073       ** next nested loop. The loop tests all FROM clause entries
  4073   4074       ** either once or twice. 
  4074   4075       **
  4075   4076       ** The first test is always performed if there are two or more entries
  4076   4077       ** remaining and never performed if there is only one FROM clause entry
................................................................................
  4127   4128             if( j==iFrom ) iFrom++;
  4128   4129             continue;
  4129   4130           }
  4130   4131           mask = (isOptimal ? m : notReady);
  4131   4132           pOrderBy = ((i==0 && ppOrderBy )?*ppOrderBy:0);
  4132   4133           if( pTabItem->pIndex==0 ) nUnconstrained++;
  4133   4134     
         4135  +        WHERETRACE(("=== trying table %d with isOptimal=%d ===\n",
         4136  +                    j, isOptimal));
  4134   4137           assert( pTabItem->pTab );
  4135   4138   #ifndef SQLITE_OMIT_VIRTUALTABLE
  4136   4139           if( IsVirtual(pTabItem->pTab) ){
  4137   4140             sqlite3_index_info **pp = &pWInfo->a[j].pIdxInfo;
  4138   4141             bestVirtualIndex(pParse, pWC, pTabItem, mask, notReady, pOrderBy,
  4139   4142                              &sCost, pp);
  4140   4143           }else 
................................................................................
  4179   4182               && (bestJ<0 || (notIndexed&m)!=0               /* (2) */
  4180   4183                   || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)
  4181   4184               && (nUnconstrained==0 || pTabItem->pIndex==0   /* (3) */
  4182   4185                   || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0))
  4183   4186               && (bestJ<0 || sCost.rCost<bestPlan.rCost      /* (4) */
  4184   4187                   || (sCost.rCost<=bestPlan.rCost && sCost.nRow<bestPlan.nRow))
  4185   4188           ){
  4186         -          WHERETRACE(("... best so far with cost=%g and nRow=%g\n",
  4187         -                      sCost.rCost, sCost.nRow));
         4189  +          WHERETRACE(("=== table %d is best so far"
         4190  +                      " with cost=%g and nRow=%g\n",
         4191  +                      j, sCost.rCost, sCost.nRow));
  4188   4192             bestPlan = sCost;
  4189   4193             bestJ = j;
  4190   4194           }
  4191   4195           if( doNotReorder ) break;
  4192   4196         }
  4193   4197       }
  4194   4198       assert( bestJ>=0 );
  4195   4199       assert( notReady & getMask(pMaskSet, pTabList->a[bestJ].iCursor) );
  4196         -    WHERETRACE(("*** Optimizer selects table %d for loop %d\n", bestJ,
  4197         -           pLevel-pWInfo->a));
         4200  +    WHERETRACE(("*** Optimizer selects table %d for loop %d"
         4201  +                " with cost=%g and nRow=%g\n",
         4202  +                bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.nRow));
  4198   4203       if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){
  4199   4204         *ppOrderBy = 0;
  4200   4205       }
  4201   4206       andFlags &= bestPlan.plan.wsFlags;
  4202   4207       pLevel->plan = bestPlan.plan;
  4203   4208       testcase( bestPlan.plan.wsFlags & WHERE_INDEXED );
  4204   4209       testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX );

Changes to test/where7.test.

 23295  23295            OR a=23
 23296  23296            OR (f GLOB '?defg*' AND f GLOB 'cdef*')
 23297  23297            OR d<0.0
 23298  23298            OR (d>=22.0 AND d<23.0 AND d NOT NULL)
 23299  23299            OR a=91
 23300  23300     }
 23301  23301   } {2 22 23 28 54 80 91 scan 0 sort 0}
 23302         -finish_test
        23302  +
        23303  +# test case for the performance regression fixed by
        23304  +# check-in 28ba6255282b on 2010-10-21 02:05:06
        23305  +#
        23306  +# The test case that follows is code from an actual
        23307  +# application with identifiers change and unused columns
        23308  +# remove.
        23309  +#
        23310  +do_test where7-3.1 {
        23311  +  db eval {
        23312  +    CREATE TABLE t301 (
        23313  +        c8 INTEGER PRIMARY KEY,
        23314  +        c6 INTEGER,
        23315  +        c4 INTEGER,
        23316  +        c7 INTEGER,
        23317  +        FOREIGN KEY (c4) REFERENCES series(c4)
        23318  +    );
        23319  +    CREATE INDEX t301_c6 on t301(c6);
        23320  +    CREATE INDEX t301_c4 on t301(c4);
        23321  +    CREATE INDEX t301_c7 on t301(c7);
        23322  +    
        23323  +    CREATE TABLE t302 (
        23324  +        c1 INTEGER PRIMARY KEY,
        23325  +        c8 INTEGER,
        23326  +        c5 INTEGER,
        23327  +        c3 INTEGER,
        23328  +        c2 INTEGER,
        23329  +        c4 INTEGER,
        23330  +        FOREIGN KEY (c8) REFERENCES t301(c8)
        23331  +    );
        23332  +    CREATE INDEX t302_c3 on t302(c3);
        23333  +    CREATE INDEX t302_c8_c3 on t302(c8, c3);
        23334  +    CREATE INDEX t302_c5 on t302(c5);
        23335  +    
        23336  +    EXPLAIN QUERY PLAN
        23337  +    SELECT t302.c1 
        23338  +      FROM t302 JOIN t301 ON t302.c8 = t301.c8
        23339  +      WHERE t302.c2 = 19571
        23340  +        AND t302.c3 > 1287603136
        23341  +        AND (t301.c4 = 1407449685622784
        23342  +             OR t301.c8 = 1407424651264000)
        23343  +     ORDER BY t302.c5 LIMIT 200;
        23344  +  }
        23345  +} {0 1 {TABLE t301 VIA MULTI-INDEX UNION} 1 0 {TABLE t302 WITH INDEX t302_c8_c3} 0 0 {TABLE t301 WITH INDEX t301_c4} 0 0 {TABLE t301 USING PRIMARY KEY}}
 23303  23346   
 23304  23347   finish_test