/ Check-in [292724ff]
Login

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

Overview
Comment:Avoid invoking the whereLoopAddOr() routine in the query planner if there are no OR operators in the WHERE clause, thus speeding up query planning slightly.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:292724ffc4bfca435fff309383d488ffdbe1e314e5eb26da21cf2f621b64bce5
User & Date: drh 2018-06-09 00:09:58
References
2018-06-11
01:30
Always initialize the WhereClause.hasOr field that was added by check-in [292724ffc4]. Error detected by OSSFuzz. check-in: 9faf4171 user: drh tags: trunk
Context
2018-06-09
01:12
Compute the bitmask of indexed columns for each index once when the Index objecct is constructed, instead of recomputing it every time it is needed. check-in: d735872e user: drh tags: trunk
00:09
Avoid invoking the whereLoopAddOr() routine in the query planner if there are no OR operators in the WHERE clause, thus speeding up query planning slightly. check-in: 292724ff user: drh tags: trunk
2018-06-08
23:23
When the query planner has the opportunity to use an IN operater constraint on a term of an index other than the left-most term, use the estimated number of elements on the right-hand side of the IN operator to determine if makes sense to use the IN operator with index lookups, or to just do a scan over the range of the table identified by the index terms to the left. Only do this if sqlite_stat1 measurements are available as otherwise the performance estimates will not be accurate enough to discern the best plan. Bias the decision slightly in favor of using index lookups on each element of the IN operator. check-in: 2cbbabdf user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3530   3530         }
  3531   3531         rc = whereLoopAddVirtual(pBuilder, mPrereq, mUnusable);
  3532   3532       }else
  3533   3533   #endif /* SQLITE_OMIT_VIRTUALTABLE */
  3534   3534       {
  3535   3535         rc = whereLoopAddBtree(pBuilder, mPrereq);
  3536   3536       }
  3537         -    if( rc==SQLITE_OK ){
         3537  +    if( rc==SQLITE_OK && pBuilder->pWC->hasOr ){
  3538   3538         rc = whereLoopAddOr(pBuilder, mPrereq, mUnusable);
  3539   3539       }
  3540   3540       mPrior |= pNew->maskSelf;
  3541   3541       if( rc || db->mallocFailed ) break;
  3542   3542     }
  3543   3543   
  3544   3544     whereLoopClear(db, pNew);

Changes to src/whereInt.h.

   318    318   ** the subclauses "(b AND c)" and "(d AND e)".  The pOuter field of the
   319    319   ** subclauses points to the WhereClause object for the whole clause.
   320    320   */
   321    321   struct WhereClause {
   322    322     WhereInfo *pWInfo;       /* WHERE clause processing context */
   323    323     WhereClause *pOuter;     /* Outer conjunction */
   324    324     u8 op;                   /* Split operator.  TK_AND or TK_OR */
          325  +  u8 hasOr;                /* True if any a[].eOperator is WO_OR */
   325    326     int nTerm;               /* Number of terms */
   326    327     int nSlot;               /* Number of entries in a[] */
   327    328     WhereTerm *a;            /* Each a[] describes a term of the WHERE cluase */
   328    329   #if defined(SQLITE_SMALL_STACK)
   329    330     WhereTerm aStatic[1];    /* Initial static space for a[] */
   330    331   #else
   331    332     WhereTerm aStatic[8];    /* Initial static space for a[] */

Changes to src/whereexpr.c.

   668    668     }
   669    669   
   670    670     /*
   671    671     ** Record the set of tables that satisfy case 3.  The set might be
   672    672     ** empty.
   673    673     */
   674    674     pOrInfo->indexable = indexable;
   675         -  pTerm->eOperator = indexable==0 ? 0 : WO_OR;
          675  +  if( indexable ){
          676  +    pTerm->eOperator = WO_OR;
          677  +    pWC->hasOr = 1;
          678  +  }else{
          679  +    pTerm->eOperator = WO_OR;
          680  +  }
   676    681   
   677    682     /* For a two-way OR, attempt to implementation case 2.
   678    683     */
   679    684     if( indexable && pOrWc->nTerm==2 ){
   680    685       int iOne = 0;
   681    686       WhereTerm *pOne;
   682    687       while( (pOne = whereNthSubterm(&pOrWc->a[0],iOne++))!=0 ){