/ Check-in [f4747eb8]
Login

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

Overview
Comment:Experimental changes to EXPLAIN QUERY PLAN.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1:f4747eb83dacce6430ad6e5eb20155ffad975514
User & Date: dan 2010-11-08 19:01:16
Context
2010-11-09
14:49
Further enhancements and fixes for explain query plan. check-in: 73c93f5a user: dan tags: experimental
2010-11-08
19:01
Experimental changes to EXPLAIN QUERY PLAN. check-in: f4747eb8 user: dan tags: experimental
2010-11-05
20:50
Fix to xTruncate and more journal mode tests for the multiplex VFS. check-in: 65fa1164 user: shaneh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/prepare.c.

   624    624     }
   625    625     rc = pParse->rc;
   626    626   
   627    627   #ifndef SQLITE_OMIT_EXPLAIN
   628    628     if( rc==SQLITE_OK && pParse->pVdbe && pParse->explain ){
   629    629       static const char * const azColName[] = {
   630    630          "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment",
   631         -       "order", "from", "detail"
          631  +       "selectid", "order", "from", "detail"
   632    632       };
   633    633       int iFirst, mx;
   634    634       if( pParse->explain==2 ){
   635         -      sqlite3VdbeSetNumCols(pParse->pVdbe, 3);
          635  +      sqlite3VdbeSetNumCols(pParse->pVdbe, 4);
   636    636         iFirst = 8;
   637         -      mx = 11;
          637  +      mx = 12;
   638    638       }else{
   639    639         sqlite3VdbeSetNumCols(pParse->pVdbe, 8);
   640    640         iFirst = 0;
   641    641         mx = 8;
   642    642       }
   643    643       for(i=iFirst; i<mx; i++){
   644    644         sqlite3VdbeSetColName(pParse->pVdbe, i-iFirst, COLNAME_NAME,

Changes to src/select.c.

   767    767         pInfo->aColl[i] = pColl;
   768    768         pInfo->aSortOrder[i] = pItem->sortOrder;
   769    769       }
   770    770     }
   771    771     return pInfo;
   772    772   }
   773    773   
          774  +#ifndef SQLITE_OMIT_EXPLAIN
          775  +static void explainTempTable(Parse *pParse, const char *zUsage){
          776  +  if( pParse->explain==2 ){
          777  +    Vdbe *v = pParse->pVdbe;
          778  +    char *zMsg = sqlite3MPrintf(pParse->db, "USE TEMP B-TREE FOR %s", zUsage);
          779  +    sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
          780  +  }
          781  +}
          782  +# define explainRestoreSelectId() pParse->iSelectId = iRestoreSelectId
          783  +#else
          784  +# define explainRestoreSelectId()
          785  +# define explainTempTable(y,z)
          786  +#endif
   774    787   
   775    788   /*
   776    789   ** If the inner loop was generated using a non-null pOrderBy argument,
   777    790   ** then the results were placed in a sorter.  After the loop is terminated
   778    791   ** we need to run the sorter and output the results.  The following
   779    792   ** routine generates the code needed to do that.
   780    793   */
................................................................................
  3585   3598     int isDistinct;        /* True if the DISTINCT keyword is present */
  3586   3599     int distinct;          /* Table to use for the distinct set */
  3587   3600     int rc = 1;            /* Value to return from this function */
  3588   3601     int addrSortIndex;     /* Address of an OP_OpenEphemeral instruction */
  3589   3602     AggInfo sAggInfo;      /* Information used by aggregate queries */
  3590   3603     int iEnd;              /* Address of the end of the query */
  3591   3604     sqlite3 *db;           /* The database connection */
         3605  +
         3606  +#ifndef SQLITE_OMIT_EXPLAIN
         3607  +  int iRestoreSelectId = pParse->iSelectId;
         3608  +  pParse->iSelectId = pParse->iNextSelectId++;
         3609  +#endif
  3592   3610   
  3593   3611     db = pParse->db;
  3594   3612     if( p==0 || db->mallocFailed || pParse->nErr ){
  3595   3613       return 1;
  3596   3614     }
  3597   3615     if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  3598   3616     memset(&sAggInfo, 0, sizeof(sAggInfo));
................................................................................
  3692   3710           pLoop->pRightmost = p;
  3693   3711           pLoop->pNext = pRight;
  3694   3712           pRight = pLoop;
  3695   3713         }
  3696   3714         mxSelect = db->aLimit[SQLITE_LIMIT_COMPOUND_SELECT];
  3697   3715         if( mxSelect && cnt>mxSelect ){
  3698   3716           sqlite3ErrorMsg(pParse, "too many terms in compound SELECT");
  3699         -        return 1;
         3717  +        goto select_end;
  3700   3718         }
  3701   3719       }
         3720  +    explainRestoreSelectId();
  3702   3721       return multiSelect(pParse, p, pDest);
  3703   3722     }
  3704   3723   #endif
  3705   3724   
  3706   3725     /* If possible, rewrite the query to use GROUP BY instead of DISTINCT.
  3707   3726     ** GROUP BY might use an index, DISTINCT never does.
  3708   3727     */
  3709   3728     assert( p->pGroupBy==0 || (p->selFlags & SF_Aggregate)!=0 );
  3710   3729     if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct ){
  3711   3730       p->pGroupBy = sqlite3ExprListDup(db, p->pEList, 0);
  3712   3731       pGroupBy = p->pGroupBy;
  3713   3732       p->selFlags &= ~SF_Distinct;
  3714         -    isDistinct = 0;
  3715   3733     }
  3716   3734   
  3717   3735     /* If there is both a GROUP BY and an ORDER BY clause and they are
  3718   3736     ** identical, then disable the ORDER BY clause since the GROUP BY
  3719   3737     ** will cause elements to come out in the correct order.  This is
  3720   3738     ** an optimization - the correct answer should result regardless.
  3721   3739     ** Use the SQLITE_GroupByOrder flag with SQLITE_TESTCTRL_OPTIMIZER
................................................................................
  3754   3772     /* Set the limiter.
  3755   3773     */
  3756   3774     iEnd = sqlite3VdbeMakeLabel(v);
  3757   3775     computeLimitRegisters(pParse, p, iEnd);
  3758   3776   
  3759   3777     /* Open a virtual index to use for the distinct set.
  3760   3778     */
  3761         -  if( isDistinct ){
         3779  +  if( p->selFlags & SF_Distinct ){
  3762   3780       KeyInfo *pKeyInfo;
  3763   3781       assert( isAgg || pGroupBy );
  3764   3782       distinct = pParse->nTab++;
  3765   3783       pKeyInfo = keyInfoFromExprList(pParse, p->pEList);
  3766   3784       sqlite3VdbeAddOp4(v, OP_OpenEphemeral, distinct, 0, 0,
  3767   3785                           (char*)pKeyInfo, P4_KEYINFO_HANDOFF);
  3768   3786       sqlite3VdbeChangeP5(v, BTREE_UNORDERED);
................................................................................
  3913   3931           ** in sorted order
  3914   3932           */
  3915   3933           int regBase;
  3916   3934           int regRecord;
  3917   3935           int nCol;
  3918   3936           int nGroupBy;
  3919   3937   
         3938  +        explainTempTable(pParse, 
         3939  +            isDistinct && !(p->selFlags&SF_Distinct)?"DISTINCT":"GROUP BY");
         3940  +
  3920   3941           groupBySort = 1;
  3921   3942           nGroupBy = pGroupBy->nExpr;
  3922   3943           nCol = nGroupBy + 1;
  3923   3944           j = nGroupBy+1;
  3924   3945           for(i=0; i<sAggInfo.nColumn; i++){
  3925   3946             if( sAggInfo.aCol[i].iSorterColumn>=j ){
  3926   3947               nCol++;
................................................................................
  4173   4194         selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, -1, 
  4174   4195                         pDest, addrEnd, addrEnd);
  4175   4196         sqlite3ExprListDelete(db, pDel);
  4176   4197       }
  4177   4198       sqlite3VdbeResolveLabel(v, addrEnd);
  4178   4199       
  4179   4200     } /* endif aggregate query */
         4201  +
         4202  +  if( distinct>=0 ){
         4203  +    explainTempTable(pParse, "DISTINCT");
         4204  +  }
  4180   4205   
  4181   4206     /* If there is an ORDER BY clause, then we need to sort the results
  4182   4207     ** and send them to the callback one by one.
  4183   4208     */
  4184   4209     if( pOrderBy ){
         4210  +    explainTempTable(pParse, "ORDER BY");
  4185   4211       generateSortTail(pParse, p, v, pEList->nExpr, pDest);
  4186   4212     }
  4187   4213   
  4188   4214     /* Jump here to skip this query
  4189   4215     */
  4190   4216     sqlite3VdbeResolveLabel(v, iEnd);
  4191   4217   
................................................................................
  4194   4220     */
  4195   4221     rc = 0;
  4196   4222   
  4197   4223     /* Control jumps to here if an error is encountered above, or upon
  4198   4224     ** successful coding of the SELECT.
  4199   4225     */
  4200   4226   select_end:
         4227  +  explainRestoreSelectId();
  4201   4228   
  4202   4229     /* Identify column names if results of the SELECT are to be output.
  4203   4230     */
  4204   4231     if( rc==SQLITE_OK && pDest->eDest==SRT_Output ){
  4205   4232       generateColumnNames(pParse, pTabList, pEList);
  4206   4233     }
  4207   4234   

Changes to src/sqliteInt.h.

  1855   1855   ** pTerm is only used when wsFlags&WHERE_MULTI_OR is true.  And pVtabIdx
  1856   1856   ** is only used when wsFlags&WHERE_VIRTUALTABLE is true.  It is never the
  1857   1857   ** case that more than one of these conditions is true.
  1858   1858   */
  1859   1859   struct WherePlan {
  1860   1860     u32 wsFlags;                   /* WHERE_* flags that describe the strategy */
  1861   1861     u32 nEq;                       /* Number of == constraints */
         1862  +  double nRow;                   /* Estimated number of rows (for EQP) */
  1862   1863     union {
  1863   1864       Index *pIdx;                   /* Index when WHERE_INDEXED is true */
  1864   1865       struct WhereTerm *pTerm;       /* WHERE clause term for OR-search */
  1865   1866       sqlite3_index_info *pVtabIdx;  /* Virtual table index to use */
  1866   1867     } u;
  1867   1868   };
  1868   1869   
................................................................................
  2209   2210     u8 declareVtab;            /* True if inside sqlite3_declare_vtab() */
  2210   2211     int nVtabLock;             /* Number of virtual tables to lock */
  2211   2212     Table **apVtabLock;        /* Pointer to virtual tables needing locking */
  2212   2213   #endif
  2213   2214     int nHeight;            /* Expression tree height of current sub-select */
  2214   2215     Table *pZombieTab;      /* List of Table objects to delete after code gen */
  2215   2216     TriggerPrg *pTriggerPrg;    /* Linked list of coded triggers */
         2217  +
         2218  +#ifndef SQLITE_OMIT_EXPLAIN
         2219  +  int iSelectId;
         2220  +  int iNextSelectId;
         2221  +#endif
  2216   2222   };
  2217   2223   
  2218   2224   #ifdef SQLITE_OMIT_VIRTUALTABLE
  2219   2225     #define IN_DECLARE_VTAB 0
  2220   2226   #else
  2221   2227     #define IN_DECLARE_VTAB (pParse->declareVtab)
  2222   2228   #endif

Changes to src/vdbeaux.c.

  1178   1178       pMem++;
  1179   1179   
  1180   1180       pMem->flags = MEM_Int;
  1181   1181       pMem->u.i = pOp->p2;                          /* P2 */
  1182   1182       pMem->type = SQLITE_INTEGER;
  1183   1183       pMem++;
  1184   1184   
  1185         -    if( p->explain==1 ){
  1186         -      pMem->flags = MEM_Int;
  1187         -      pMem->u.i = pOp->p3;                          /* P3 */
  1188         -      pMem->type = SQLITE_INTEGER;
  1189         -      pMem++;
  1190         -    }
         1185  +    pMem->flags = MEM_Int;
         1186  +    pMem->u.i = pOp->p3;                          /* P3 */
         1187  +    pMem->type = SQLITE_INTEGER;
         1188  +    pMem++;
  1191   1189   
  1192   1190       if( sqlite3VdbeMemGrow(pMem, 32, 0) ){            /* P4 */
  1193   1191         assert( p->db->mallocFailed );
  1194   1192         return SQLITE_ERROR;
  1195   1193       }
  1196   1194       pMem->flags = MEM_Dyn|MEM_Str|MEM_Term;
  1197   1195       z = displayP4(pOp, pMem->z, 32);
................................................................................
  1228   1226   #endif
  1229   1227         {
  1230   1228           pMem->flags = MEM_Null;                       /* Comment */
  1231   1229           pMem->type = SQLITE_NULL;
  1232   1230         }
  1233   1231       }
  1234   1232   
  1235         -    p->nResColumn = 8 - 5*(p->explain-1);
         1233  +    p->nResColumn = 8 - 4*(p->explain-1);
  1236   1234       p->rc = SQLITE_OK;
  1237   1235       rc = SQLITE_ROW;
  1238   1236     }
  1239   1237     return rc;
  1240   1238   }
  1241   1239   #endif /* SQLITE_OMIT_EXPLAIN */
  1242   1240   

Changes to src/where.c.

   188    188   /*
   189    189   ** A WhereCost object records a lookup strategy and the estimated
   190    190   ** cost of pursuing that strategy.
   191    191   */
   192    192   struct WhereCost {
   193    193     WherePlan plan;    /* The lookup strategy */
   194    194     double rCost;      /* Overall cost of pursuing this search strategy */
   195         -  double nRow;       /* Estimated number of output rows */
   196    195     Bitmask used;      /* Bitmask of cursors used by this plan */
   197    196   };
   198    197   
   199    198   /*
   200    199   ** Bitmasks for the operators that indices are able to exploit.  An
   201    200   ** OR-ed combination of these values can be used when searching for
   202    201   ** terms in the where clause.
................................................................................
  1617   1616             tempWC.a = pOrTerm;
  1618   1617             tempWC.nTerm = 1;
  1619   1618             bestIndex(pParse, &tempWC, pSrc, notReady, notValid, 0, &sTermCost);
  1620   1619           }else{
  1621   1620             continue;
  1622   1621           }
  1623   1622           rTotal += sTermCost.rCost;
  1624         -        nRow += sTermCost.nRow;
         1623  +        nRow += sTermCost.plan.nRow;
  1625   1624           used |= sTermCost.used;
  1626   1625           if( rTotal>=pCost->rCost ) break;
  1627   1626         }
  1628   1627   
  1629   1628         /* If there is an ORDER BY clause, increase the scan cost to account 
  1630   1629         ** for the cost of the sort. */
  1631   1630         if( pOrderBy!=0 ){
................................................................................
  1636   1635   
  1637   1636         /* If the cost of scanning using this OR term for optimization is
  1638   1637         ** less than the current cost stored in pCost, replace the contents
  1639   1638         ** of pCost. */
  1640   1639         WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n", rTotal, nRow));
  1641   1640         if( rTotal<pCost->rCost ){
  1642   1641           pCost->rCost = rTotal;
  1643         -        pCost->nRow = nRow;
  1644   1642           pCost->used = used;
         1643  +        pCost->plan.nRow = nRow;
  1645   1644           pCost->plan.wsFlags = flags;
  1646   1645           pCost->plan.u.pTerm = pTerm;
  1647   1646         }
  1648   1647       }
  1649   1648     }
  1650   1649   #endif /* SQLITE_OMIT_OR_OPTIMIZATION */
  1651   1650   }
................................................................................
  1721   1720     /* Search for any equality comparison term */
  1722   1721     pWCEnd = &pWC->a[pWC->nTerm];
  1723   1722     for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
  1724   1723       if( termCanDriveIndex(pTerm, pSrc, notReady) ){
  1725   1724         WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n",
  1726   1725                       pCost->rCost, costTempIdx));
  1727   1726         pCost->rCost = costTempIdx;
  1728         -      pCost->nRow = logN + 1;
         1727  +      pCost->plan.nRow = logN + 1;
  1729   1728         pCost->plan.wsFlags = WHERE_TEMP_INDEX;
  1730   1729         pCost->used = pTerm->prereqRight;
  1731   1730         break;
  1732   1731       }
  1733   1732     }
  1734   1733   }
  1735   1734   #else
................................................................................
  2794   2793         notReady, nRow, cost, used
  2795   2794       ));
  2796   2795   
  2797   2796       /* If this index is the best we have seen so far, then record this
  2798   2797       ** index and its cost in the pCost structure.
  2799   2798       */
  2800   2799       if( (!pIdx || wsFlags)
  2801         -     && (cost<pCost->rCost || (cost<=pCost->rCost && nRow<pCost->nRow))
         2800  +     && (cost<pCost->rCost || (cost<=pCost->rCost && nRow<pCost->plan.nRow))
  2802   2801       ){
  2803   2802         pCost->rCost = cost;
  2804         -      pCost->nRow = nRow;
  2805   2803         pCost->used = used;
         2804  +      pCost->plan.nRow = nRow;
  2806   2805         pCost->plan.wsFlags = (wsFlags&wsFlagMask);
  2807   2806         pCost->plan.nEq = nEq;
  2808   2807         pCost->plan.u.pIdx = pIdx;
  2809   2808       }
  2810   2809   
  2811   2810       /* If there was an INDEXED BY clause, then only that one index is
  2812   2811       ** considered. */
................................................................................
  3126   3125           }
  3127   3126         }
  3128   3127       }
  3129   3128     }
  3130   3129     *pzAff = zAff;
  3131   3130     return regBase;
  3132   3131   }
         3132  +
         3133  +#ifndef SQLITE_OMIT_EXPLAIN
         3134  +static char *indexRangeText(sqlite3 *db, WhereLevel *pLevel, Table *pTab){
         3135  +  WherePlan *pPlan = &pLevel->plan;
         3136  +  Index *pIndex = pPlan->u.pIdx;
         3137  +  int nEq = pPlan->nEq;
         3138  +  char *zRet = 0;
         3139  +  int i;
         3140  +
         3141  +  for(i=0; i<nEq; i++){
         3142  +    char *zCol = pTab->aCol[pIndex->aiColumn[i]].zName;
         3143  +    zRet = sqlite3MAppendf(db, zRet, 
         3144  +        "%s%s%s=?", (zRet?zRet:""), (zRet?" AND ":""), zCol);
         3145  +  }
         3146  +
         3147  +  if( pPlan->wsFlags&WHERE_BTM_LIMIT ){
         3148  +    zRet = sqlite3MAppendf(db, zRet,
         3149  +        "%s%s%s>?", (zRet?zRet:""), (zRet?" AND ":""), pTab->aCol[nEq].zName);
         3150  +  }
         3151  +  if( pPlan->wsFlags&WHERE_TOP_LIMIT ){
         3152  +    zRet = sqlite3MAppendf(db, zRet,
         3153  +        "%s%s%s<?", (zRet?zRet:""), (zRet?" AND ":""), pTab->aCol[nEq].zName);
         3154  +  }
         3155  +
         3156  +  if( zRet ){
         3157  +    zRet = sqlite3MAppendf(db, zRet, " (%s)", zRet);
         3158  +  }
         3159  +
         3160  +  return zRet;
         3161  +}
         3162  +
         3163  +static void codeOneLoopExplain(
         3164  +  Parse *pParse,                  /* Parse context */
         3165  +  SrcList *pTabList,              /* Table list this loop refers to */
         3166  +  WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
         3167  +  int iLevel,                     /* Value for "level" column of output */
         3168  +  int iFrom                       /* Value for "from" column of output */
         3169  +){
         3170  +  if( pParse->explain==2 ){
         3171  +    u32 flags = pLevel->plan.wsFlags;
         3172  +    struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
         3173  +    Vdbe *v = pParse->pVdbe;
         3174  +    sqlite3 *db = pParse->db;
         3175  +    char *zMsg;
         3176  +
         3177  +    if( flags & WHERE_MULTI_OR ) return;
         3178  +
         3179  +    zMsg = sqlite3MPrintf(db, "TABLE %s", pItem->zName);
         3180  +    if( pItem->zAlias ){
         3181  +      zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
         3182  +    }
         3183  +    if( (flags & WHERE_INDEXED)!=0 ){
         3184  +      char *zWhere = indexRangeText(db, pLevel, pItem->pTab);
         3185  +      zMsg = sqlite3MAppendf(db, zMsg, "%s WITH %s%sINDEX%s%s%s", zMsg, 
         3186  +          ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""),
         3187  +          ((flags & WHERE_IDX_ONLY)?"COVERING ":""),
         3188  +          ((flags & WHERE_TEMP_INDEX)?"":" "),
         3189  +          ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName),
         3190  +          zWhere
         3191  +      );
         3192  +      sqlite3DbFree(db, zWhere);
         3193  +    }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
         3194  +      zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg);
         3195  +
         3196  +      if( flags&WHERE_ROWID_EQ ){
         3197  +        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg);
         3198  +      }else if( flags&WHERE_BTM_LIMIT && flags&WHERE_TOP_LIMIT ){
         3199  +        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg);
         3200  +      }else if( flags&WHERE_BTM_LIMIT ){
         3201  +        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg);
         3202  +      }else if( flags&WHERE_TOP_LIMIT ){
         3203  +        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid<?)", zMsg);
         3204  +      }
         3205  +    }
         3206  +#ifndef SQLITE_OMIT_VIRTUALTABLE
         3207  +    else if( (flags & WHERE_VIRTUALTABLE)!=0 ){
         3208  +      sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx;
         3209  +      zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg,
         3210  +                  pVtabIdx->idxNum, pVtabIdx->idxStr);
         3211  +    }
         3212  +#endif
         3213  +    zMsg = sqlite3MAppendf(db, zMsg, 
         3214  +        "%s (~%lld rows)", zMsg, (sqlite3_int64)(pLevel->plan.nRow)
         3215  +    );
         3216  +    sqlite3VdbeAddOp4(
         3217  +        v, OP_Explain, pParse->iSelectId, iLevel, iFrom, zMsg, P4_DYNAMIC);
         3218  +  }
         3219  +}
         3220  +#else
         3221  +# define codeOneLoopExplain(w,x,y.z)
         3222  +#endif /* SQLITE_OMIT_EXPLAIN */
         3223  +
  3133   3224   
  3134   3225   /*
  3135   3226   ** Generate code for the start of the iLevel-th loop in the WHERE clause
  3136   3227   ** implementation described by pWInfo.
  3137   3228   */
  3138   3229   static Bitmask codeOneLoopStart(
  3139   3230     WhereInfo *pWInfo,   /* Complete information about the WHERE clause */
................................................................................
  3668   3759         if( pOrTerm->leftCursor==iCur || pOrTerm->eOperator==WO_AND ){
  3669   3760           WhereInfo *pSubWInfo;          /* Info for single OR-term scan */
  3670   3761           /* Loop through table entries that match term pOrTerm. */
  3671   3762           pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0,
  3672   3763                           WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE |
  3673   3764                           WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY);
  3674   3765           if( pSubWInfo ){
         3766  +          codeOneLoopExplain(
         3767  +              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom
         3768  +          );
  3675   3769             if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
  3676   3770               int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
  3677   3771               int r;
  3678   3772               r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, 
  3679   3773                                            regRowid);
  3680   3774               sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset,
  3681   3775                                    sqlite3VdbeCurrentAddr(v)+2, r, iSet);
................................................................................
  4180   4274           */
  4181   4275           if( (sCost.used&notReady)==0                       /* (1) */
  4182   4276               && (bestJ<0 || (notIndexed&m)!=0               /* (2) */
  4183   4277                   || (sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0)
  4184   4278               && (nUnconstrained==0 || pTabItem->pIndex==0   /* (3) */
  4185   4279                   || NEVER((sCost.plan.wsFlags & WHERE_NOT_FULLSCAN)!=0))
  4186   4280               && (bestJ<0 || sCost.rCost<bestPlan.rCost      /* (4) */
  4187         -                || (sCost.rCost<=bestPlan.rCost && sCost.nRow<bestPlan.nRow))
         4281  +                || (sCost.rCost<=bestPlan.rCost 
         4282  +                 && sCost.plan.nRow<bestPlan.plan.nRow))
  4188   4283           ){
  4189   4284             WHERETRACE(("=== table %d is best so far"
  4190   4285                         " with cost=%g and nRow=%g\n",
  4191         -                      j, sCost.rCost, sCost.nRow));
         4286  +                      j, sCost.rCost, sCost.plan.nRow));
  4192   4287             bestPlan = sCost;
  4193   4288             bestJ = j;
  4194   4289           }
  4195   4290           if( doNotReorder ) break;
  4196   4291         }
  4197   4292       }
  4198   4293       assert( bestJ>=0 );
  4199   4294       assert( notReady & getMask(pMaskSet, pTabList->a[bestJ].iCursor) );
  4200   4295       WHERETRACE(("*** Optimizer selects table %d for loop %d"
  4201   4296                   " with cost=%g and nRow=%g\n",
  4202         -                bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.nRow));
         4297  +                bestJ, pLevel-pWInfo->a, bestPlan.rCost, bestPlan.plan.nRow));
  4203   4298       if( (bestPlan.plan.wsFlags & WHERE_ORDERBY)!=0 ){
  4204   4299         *ppOrderBy = 0;
  4205   4300       }
  4206   4301       andFlags &= bestPlan.plan.wsFlags;
  4207   4302       pLevel->plan = bestPlan.plan;
  4208   4303       testcase( bestPlan.plan.wsFlags & WHERE_INDEXED );
  4209   4304       testcase( bestPlan.plan.wsFlags & WHERE_TEMP_INDEX );
................................................................................
  4210   4305       if( bestPlan.plan.wsFlags & (WHERE_INDEXED|WHERE_TEMP_INDEX) ){
  4211   4306         pLevel->iIdxCur = pParse->nTab++;
  4212   4307       }else{
  4213   4308         pLevel->iIdxCur = -1;
  4214   4309       }
  4215   4310       notReady &= ~getMask(pMaskSet, pTabList->a[bestJ].iCursor);
  4216   4311       pLevel->iFrom = (u8)bestJ;
  4217         -    if( bestPlan.nRow>=(double)1 ) pParse->nQueryLoop *= bestPlan.nRow;
         4312  +    if( bestPlan.plan.nRow>=(double)1 ){
         4313  +      pParse->nQueryLoop *= bestPlan.plan.nRow;
         4314  +    }
  4218   4315   
  4219   4316       /* Check that if the table scanned by this loop iteration had an
  4220   4317       ** INDEXED BY clause attached to it, that the named index is being
  4221   4318       ** used for the scan. If not, then query compilation has failed.
  4222   4319       ** Return an error.
  4223   4320       */
  4224   4321       pIdx = pTabList->a[bestJ].pIndex;
................................................................................
  4262   4359     */
  4263   4360     sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */
  4264   4361     notReady = ~(Bitmask)0;
  4265   4362     for(i=0, pLevel=pWInfo->a; i<nTabList; i++, pLevel++){
  4266   4363       Table *pTab;     /* Table to open */
  4267   4364       int iDb;         /* Index of database containing table/index */
  4268   4365   
  4269         -#ifndef SQLITE_OMIT_EXPLAIN
  4270         -    if( pParse->explain==2 ){
  4271         -      char *zMsg;
  4272         -      struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
  4273         -      zMsg = sqlite3MPrintf(db, "TABLE %s", pItem->zName);
  4274         -      if( pItem->zAlias ){
  4275         -        zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
  4276         -      }
  4277         -      if( (pLevel->plan.wsFlags & WHERE_TEMP_INDEX)!=0 ){
  4278         -        zMsg = sqlite3MAppendf(db, zMsg, "%s WITH AUTOMATIC INDEX", zMsg);
  4279         -      }else if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
  4280         -        zMsg = sqlite3MAppendf(db, zMsg, "%s WITH INDEX %s",
  4281         -           zMsg, pLevel->plan.u.pIdx->zName);
  4282         -      }else if( pLevel->plan.wsFlags & WHERE_MULTI_OR ){
  4283         -        zMsg = sqlite3MAppendf(db, zMsg, "%s VIA MULTI-INDEX UNION", zMsg);
  4284         -      }else if( pLevel->plan.wsFlags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
  4285         -        zMsg = sqlite3MAppendf(db, zMsg, "%s USING PRIMARY KEY", zMsg);
  4286         -      }
  4287         -#ifndef SQLITE_OMIT_VIRTUALTABLE
  4288         -      else if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){
  4289         -        sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx;
  4290         -        zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg,
  4291         -                    pVtabIdx->idxNum, pVtabIdx->idxStr);
  4292         -      }
  4293         -#endif
  4294         -      if( pLevel->plan.wsFlags & WHERE_ORDERBY ){
  4295         -        zMsg = sqlite3MAppendf(db, zMsg, "%s ORDER BY", zMsg);
  4296         -      }
  4297         -      sqlite3VdbeAddOp4(v, OP_Explain, i, pLevel->iFrom, 0, zMsg, P4_DYNAMIC);
  4298         -    }
  4299         -#endif /* SQLITE_OMIT_EXPLAIN */
  4300   4366       pTabItem = &pTabList->a[pLevel->iFrom];
  4301   4367       pTab = pTabItem->pTab;
  4302   4368       pLevel->iTabCur = pTabItem->iCursor;
  4303   4369       iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  4304   4370       if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){
  4305   4371         /* Do nothing */
  4306   4372       }else
................................................................................
  4351   4417   
  4352   4418     /* Generate the code to do the search.  Each iteration of the for
  4353   4419     ** loop below generates code for a single nested loop of the VM
  4354   4420     ** program.
  4355   4421     */
  4356   4422     notReady = ~(Bitmask)0;
  4357   4423     for(i=0; i<nTabList; i++){
         4424  +    if( (wctrlFlags&WHERE_ONETABLE_ONLY)==0 ){
         4425  +      codeOneLoopExplain(pParse, pTabList, &pWInfo->a[i],i,pWInfo->a[i].iFrom);
         4426  +    }
  4358   4427       notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady);
  4359   4428       pWInfo->iContinue = pWInfo->a[i].addrCont;
  4360   4429     }
  4361   4430   
  4362   4431   #ifdef SQLITE_TEST  /* For testing and debugging use only */
  4363   4432     /* Record in the query plan information about the current table
  4364   4433     ** and the index used to access it (if any).  If the table itself

Added test/eqp.test.

            1  +# 2010 November 6
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +set testdir [file dirname $argv0]
           14  +source $testdir/tester.tcl
           15  +
           16  +set testprefix eqp
           17  +
           18  +#-------------------------------------------------------------------------
           19  +#
           20  +# eqp-1.*:        Assorted tests.
           21  +# eqp-2.*:        Tests for single select statements.
           22  +# eqp-3.*:        Select statements that execute sub-selects.
           23  +# eqp-4.*:        Compound select statements.
           24  +#
           25  +
           26  +proc do_eqp_test {name sql res} {
           27  +  set res [list {*}$res]
           28  +  uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res]
           29  +}
           30  +
           31  +do_execsql_test 1.1 {
           32  +  CREATE TABLE t1(a, b);
           33  +  CREATE INDEX i1 ON t1(a);
           34  +  CREATE INDEX i2 ON t1(b);
           35  +  CREATE TABLE t2(a, b);
           36  +  CREATE TABLE t3(a, b);
           37  +}
           38  +
           39  +do_eqp_test 1.2 {
           40  +  SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
           41  +} {
           42  +  0 0 1 {TABLE t1 WITH INDEX i1 (a=?) (~10 rows)} 
           43  +  0 0 1 {TABLE t1 WITH INDEX i2 (b=?) (~10 rows)} 
           44  +  0 1 0 {TABLE t2 (~1000000 rows)}
           45  +}
           46  +do_eqp_test 1.3 {
           47  +  SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
           48  +} {
           49  +  0 0 0 {TABLE t2 (~1000000 rows)}
           50  +  0 1 1 {TABLE t1 WITH INDEX i1 (a=?) (~10 rows)} 
           51  +  0 1 1 {TABLE t1 WITH INDEX i2 (b=?) (~10 rows)} 
           52  +}
           53  +do_eqp_test 1.3 {
           54  +  SELECT a FROM t1 ORDER BY a
           55  +} {
           56  +  0 0 0 {TABLE t1 WITH COVERING INDEX i1 (~1000000 rows)}
           57  +}
           58  +do_eqp_test 1.4 {
           59  +  SELECT a FROM t1 ORDER BY +a
           60  +} {
           61  +  0 0 0 {TABLE t1 (~1000000 rows)}
           62  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
           63  +}
           64  +do_eqp_test 1.5 {
           65  +  SELECT a FROM t1 WHERE a=4
           66  +} {
           67  +  0 0 0 {TABLE t1 WITH COVERING INDEX i1 (a=?) (~10 rows)}
           68  +}
           69  +do_eqp_test 1.6 {
           70  +  SELECT DISTINCT count(*) FROM t3 GROUP BY a;
           71  +} {
           72  +  0 0 0 {TABLE t3 (~1000000 rows)}
           73  +  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
           74  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
           75  +}
           76  +
           77  +#-------------------------------------------------------------------------
           78  +# Test cases eqp-2.* - tests for single select statements.
           79  +#
           80  +drop_all_tables
           81  +do_execsql_test 2.1 {
           82  +  CREATE TABLE t1(x, y);
           83  +
           84  +  CREATE TABLE t2(x, y);
           85  +  CREATE INDEX t2i1 ON t2(x);
           86  +}
           87  +
           88  +do_eqp_test 2.2.1 {
           89  +  SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1
           90  +} {
           91  +  0 0 0 {TABLE t1 (~1000000 rows)}
           92  +  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
           93  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
           94  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
           95  +}
           96  +
           97  +do_eqp_test 2.2.2 {
           98  +  SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1
           99  +} {
          100  +  0 0 0 {TABLE t2 WITH COVERING INDEX t2i1 (~1000000 rows)}
          101  +  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
          102  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          103  +}
          104  +
          105  +#-------------------------------------------------------------------------
          106  +# Test cases eqp-3.* - tests for select statements that use sub-selects.
          107  +#
          108  +do_eqp_test 3.1.1 {
          109  +  SELECT (SELECT x FROM t1 AS sub) FROM t1;
          110  +} {
          111  +  0 0 0 {TABLE t1 (~1000000 rows)} 
          112  +  1 0 0 {TABLE t1 AS sub (~1000000 rows)}
          113  +}
          114  +
          115  +#-------------------------------------------------------------------------
          116  +# Test cases eqp-4.* - tests for select statements that use sub-selects.
          117  +#
          118  +do_eqp_test 4.1.1 {
          119  +  SELECT * FROM t1 UNION ALL SELECT * FROM t2
          120  +} {
          121  +  1 0 0 {TABLE t1 (~1000000 rows)} 
          122  +  2 0 0 {TABLE t2 (~1000000 rows)} 
          123  +}
          124  +do_eqp_test 4.1.2 {
          125  +  SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
          126  +} {
          127  +  1 0 0 {TABLE t1 (~1000000 rows)} 
          128  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          129  +  2 0 0 {TABLE t2 (~1000000 rows)} 
          130  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          131  +}
          132  +do_eqp_test 4.1.3 {
          133  +  SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
          134  +} {
          135  +  1 0 0 {TABLE t1 (~1000000 rows)} 
          136  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          137  +  2 0 0 {TABLE t2 (~1000000 rows)} 
          138  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          139  +}
          140  +do_eqp_test 4.1.4 {
          141  +  SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
          142  +} {
          143  +  1 0 0 {TABLE t1 (~1000000 rows)} 
          144  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          145  +  2 0 0 {TABLE t2 (~1000000 rows)} 
          146  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          147  +}
          148  +do_eqp_test 4.1.5 {
          149  +  SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
          150  +} {
          151  +  1 0 0 {TABLE t1 (~1000000 rows)} 
          152  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          153  +  2 0 0 {TABLE t2 (~1000000 rows)} 
          154  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          155  +}
          156  +
          157  +do_eqp_test 4.2.2 {
          158  +  SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
          159  +} {
          160  +  1 0 0 {TABLE t1 (~1000000 rows)} 
          161  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          162  +  2 0 0 {TABLE t2 WITH INDEX t2i1 (~1000000 rows)} 
          163  +}
          164  +
          165  +# Todo: Why are the following not the same as the UNION ALL case above?
          166  +do_eqp_test 4.2.3 {
          167  +  SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
          168  +} {
          169  +  1 0 0 {TABLE t1 (~1000000 rows)} 
          170  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          171  +  2 0 0 {TABLE t2 (~1000000 rows)} 
          172  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          173  +}
          174  +do_eqp_test 4.2.4 {
          175  +  SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
          176  +} {
          177  +  1 0 0 {TABLE t1 (~1000000 rows)} 
          178  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          179  +  2 0 0 {TABLE t2 (~1000000 rows)} 
          180  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          181  +}
          182  +do_eqp_test 4.2.5 {
          183  +  SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
          184  +} {
          185  +  1 0 0 {TABLE t1 (~1000000 rows)} 
          186  +  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
          187  +  2 0 0 {TABLE t2 (~1000000 rows)} 
          188  +  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
          189  +}
          190  +
          191  +
          192  +finish_test
          193  +