/ Check-in [956fef36]
Login

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

Overview
Comment:Optimizations to the new EQP framework.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rework-EQP
Files: files | file ages | folders
SHA3-256: 956fef361a795bd081d8e23ce4075dc8aafcee63ab7275d13b657b529d185b30
User & Date: drh 2018-05-03 19:47:14
Context
2018-05-03
19:56
Overhaul of EXPLAIN QUERY PLAN. The output is now in the form of a tree. More details of the query plan are shown, and what is shown is truer to what actually happens. check-in: ff01bbda user: drh tags: trunk
19:47
Optimizations to the new EQP framework. Closed-Leaf check-in: 956fef36 user: drh tags: rework-EQP
01:37
Enhance EXPLAIN QUERY PLAN to report the generation of constant rows using VALUES or just a SELECT without FROM. check-in: c75eee69 user: drh tags: rework-EQP
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  2642   2642           */
  2643   2643           Select *pSelect = pExpr->x.pSelect;
  2644   2644           ExprList *pEList = pSelect->pEList;
  2645   2645   
  2646   2646           ExplainQueryPlan((pParse, 1, "%sLIST SUBQUERY",
  2647   2647               jmpIfDynamic>=0?"":"CORRELATED "
  2648   2648           ));
  2649         -        ExplainQueryPlanSetId(pParse, pSelect);
  2650   2649           assert( !isRowid );
  2651   2650           /* If the LHS and RHS of the IN operator do not match, that
  2652   2651           ** error will have been caught long before we reach this point. */
  2653   2652           if( ALWAYS(pEList->nExpr==nVal) ){
  2654   2653             SelectDest dest;
  2655   2654             int i;
  2656   2655             sqlite3SelectDestInit(&dest, SRT_Set, pExpr->iTable);
................................................................................
  2766   2765         testcase( pExpr->op==TK_SELECT );
  2767   2766         assert( pExpr->op==TK_EXISTS || pExpr->op==TK_SELECT );
  2768   2767         assert( ExprHasProperty(pExpr, EP_xIsSelect) );
  2769   2768   
  2770   2769         pSel = pExpr->x.pSelect;
  2771   2770         ExplainQueryPlan((pParse, 1, "%sSCALAR SUBQUERY",
  2772   2771               jmpIfDynamic>=0?"":"CORRELATED "));
  2773         -      ExplainQueryPlanSetId(pParse, pSel);
  2774   2772         nReg = pExpr->op==TK_SELECT ? pSel->pEList->nExpr : 1;
  2775   2773         sqlite3SelectDestInit(&dest, 0, pParse->nMem+1);
  2776   2774         pParse->nMem += nReg;
  2777   2775         if( pExpr->op==TK_SELECT ){
  2778   2776           dest.eDest = SRT_Mem;
  2779   2777           dest.iSdst = dest.iSDParm;
  2780   2778           dest.nSdst = nReg;

Changes to src/select.c.

   144    144     pNew->op = TK_SELECT;
   145    145     pNew->selFlags = selFlags;
   146    146     pNew->iLimit = 0;
   147    147     pNew->iOffset = 0;
   148    148   #if SELECTTRACE_ENABLED
   149    149     pNew->zSelName[0] = 0;
   150    150   #endif
   151         -#if SELECTTRACE_ENABLED || !defined(SQLITE_OMIT_EXPLAIN)
   152         -  pNew->iSelectId = 0;
   153         -#endif
   154    151     pNew->addrOpenEphm[0] = -1;
   155    152     pNew->addrOpenEphm[1] = -1;
   156    153     pNew->nSelectRow = 0;
   157    154     if( pSrc==0 ) pSrc = sqlite3DbMallocZero(pParse->db, sizeof(*pSrc));
   158    155     pNew->pSrc = pSrc;
   159    156     pNew->pWhere = pWhere;
   160    157     pNew->pGroupBy = pGroupBy;
................................................................................
  2287   2284   
  2288   2285     /* Detach the ORDER BY clause from the compound SELECT */
  2289   2286     p->pOrderBy = 0;
  2290   2287   
  2291   2288     /* Store the results of the setup-query in Queue. */
  2292   2289     pSetup->pNext = 0;
  2293   2290     ExplainQueryPlan((pParse, 1, "SETUP"));
  2294         -  ExplainQueryPlanSetId(pParse, pSetup);
  2295   2291     rc = sqlite3Select(pParse, pSetup, &destQueue);
  2296   2292     pSetup->pNext = p;
  2297   2293     if( rc ) goto end_of_recursive_query;
  2298   2294   
  2299   2295     /* Find the next row in the Queue and output that row */
  2300   2296     addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iQueue, addrBreak); VdbeCoverage(v);
  2301   2297   
................................................................................
  2323   2319     ** the value for the recursive-table. Store the results in the Queue.
  2324   2320     */
  2325   2321     if( p->selFlags & SF_Aggregate ){
  2326   2322       sqlite3ErrorMsg(pParse, "recursive aggregate queries not supported");
  2327   2323     }else{
  2328   2324       p->pPrior = 0;
  2329   2325       ExplainQueryPlan((pParse, 1, "RECURSIVE STEP"));
  2330         -    ExplainQueryPlanSetId(pParse, p);
  2331   2326       sqlite3Select(pParse, p, &destQueue);
  2332   2327       assert( p->pPrior==0 );
  2333   2328       p->pPrior = pSetup;
  2334   2329     }
  2335   2330   
  2336   2331     /* Keep running the loop until the Queue is empty */
  2337   2332     sqlite3VdbeGoto(v, addrTop);
................................................................................
  2488   2483     /* Compound SELECTs that have an ORDER BY clause are handled separately.
  2489   2484     */
  2490   2485     if( p->pOrderBy ){
  2491   2486       return multiSelectOrderBy(pParse, p, pDest);
  2492   2487     }else{
  2493   2488   
  2494   2489   #ifndef SQLITE_OMIT_EXPLAIN
  2495         -    if( p->pNext==0 ){
         2490  +    if( pPrior->pPrior==0 ){
  2496   2491         ExplainQueryPlan((pParse, 1, "COMPOUND QUERY"));
  2497         -    }
  2498         -    if( pPrior->pPrior==0 ){
  2499   2492         ExplainQueryPlan((pParse, 1, "LEFT-MOST SUBQUERY"));
  2500         -      ExplainQueryPlanSetId(pParse, pPrior);
  2501   2493       }
  2502   2494   #endif
  2503   2495   
  2504   2496       /* Generate code for the left and right SELECT statements.
  2505   2497       */
  2506   2498       switch( p->op ){
  2507   2499         case TK_ALL: {
................................................................................
  2524   2516             VdbeComment((v, "Jump ahead if LIMIT reached"));
  2525   2517             if( p->iOffset ){
  2526   2518               sqlite3VdbeAddOp3(v, OP_OffsetLimit,
  2527   2519                                 p->iLimit, p->iOffset+1, p->iOffset);
  2528   2520             }
  2529   2521           }
  2530   2522           ExplainQueryPlan((pParse, 1, "UNION ALL"));
  2531         -        ExplainQueryPlanSetId(pParse, p);
  2532   2523           rc = sqlite3Select(pParse, p, &dest);
  2533   2524           testcase( rc!=SQLITE_OK );
  2534   2525           pDelete = p->pPrior;
  2535   2526           p->pPrior = pPrior;
  2536   2527           p->nSelectRow = sqlite3LogEstAdd(p->nSelectRow, pPrior->nSelectRow);
  2537   2528           if( pPrior->pLimit
  2538   2529            && sqlite3ExprIsInteger(pPrior->pLimit->pLeft, &nLimit)
................................................................................
  2595   2586           }
  2596   2587           p->pPrior = 0;
  2597   2588           pLimit = p->pLimit;
  2598   2589           p->pLimit = 0;
  2599   2590           uniondest.eDest = op;
  2600   2591           ExplainQueryPlan((pParse, 1, "%s USING TEMP B-TREE",
  2601   2592                             selectOpName(p->op)));
  2602         -        ExplainQueryPlanSetId(pParse, p);
  2603   2593           rc = sqlite3Select(pParse, p, &uniondest);
  2604   2594           testcase( rc!=SQLITE_OK );
  2605   2595           /* Query flattening in sqlite3Select() might refill p->pOrderBy.
  2606   2596           ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */
  2607   2597           sqlite3ExprListDelete(db, p->pOrderBy);
  2608   2598           pDelete = p->pPrior;
  2609   2599           p->pPrior = pPrior;
................................................................................
  2674   2664           p->addrOpenEphm[1] = addr;
  2675   2665           p->pPrior = 0;
  2676   2666           pLimit = p->pLimit;
  2677   2667           p->pLimit = 0;
  2678   2668           intersectdest.iSDParm = tab2;
  2679   2669           ExplainQueryPlan((pParse, 1, "%s USING TEMP B-TREE",
  2680   2670                             selectOpName(p->op)));
  2681         -        ExplainQueryPlanSetId(pParse, p);
  2682   2671           rc = sqlite3Select(pParse, p, &intersectdest);
  2683   2672           testcase( rc!=SQLITE_OK );
  2684   2673           pDelete = p->pPrior;
  2685   2674           p->pPrior = pPrior;
  2686   2675           if( p->nSelectRow>pPrior->nSelectRow ){
  2687   2676             p->nSelectRow = pPrior->nSelectRow;
  2688   2677           }
................................................................................
  3182   3171     ** left of the compound operator - the "A" select.
  3183   3172     */
  3184   3173     addrSelectA = sqlite3VdbeCurrentAddr(v) + 1;
  3185   3174     addr1 = sqlite3VdbeAddOp3(v, OP_InitCoroutine, regAddrA, 0, addrSelectA);
  3186   3175     VdbeComment((v, "left SELECT"));
  3187   3176     pPrior->iLimit = regLimitA;
  3188   3177     ExplainQueryPlan((pParse, 1, "LEFT"));
  3189         -  ExplainQueryPlanSetId(pParse, pPrior);
  3190   3178     sqlite3Select(pParse, pPrior, &destA);
  3191   3179     sqlite3VdbeEndCoroutine(v, regAddrA);
  3192   3180     sqlite3VdbeJumpHere(v, addr1);
  3193   3181   
  3194   3182     /* Generate a coroutine to evaluate the SELECT statement on 
  3195   3183     ** the right - the "B" select
  3196   3184     */
................................................................................
  3198   3186     addr1 = sqlite3VdbeAddOp3(v, OP_InitCoroutine, regAddrB, 0, addrSelectB);
  3199   3187     VdbeComment((v, "right SELECT"));
  3200   3188     savedLimit = p->iLimit;
  3201   3189     savedOffset = p->iOffset;
  3202   3190     p->iLimit = regLimitB;
  3203   3191     p->iOffset = 0;  
  3204   3192     ExplainQueryPlan((pParse, 1, "RIGHT"));
  3205         -  ExplainQueryPlanSetId(pParse, p);
  3206   3193     sqlite3Select(pParse, p, &destB);
  3207   3194     p->iLimit = savedLimit;
  3208   3195     p->iOffset = savedOffset;
  3209   3196     sqlite3VdbeEndCoroutine(v, regAddrB);
  3210   3197   
  3211   3198     /* Generate a subroutine that outputs the current row of the A
  3212   3199     ** select as the next output row of the compound select.
................................................................................
  5571   5558        
  5572   5559         pItem->regReturn = ++pParse->nMem;
  5573   5560         sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);
  5574   5561         VdbeComment((v, "%s", pItem->pTab->zName));
  5575   5562         pItem->addrFillSub = addrTop;
  5576   5563         sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn);
  5577   5564         ExplainQueryPlan((pParse, 1, "CO-ROUTINE 0x%p", pSub));
  5578         -      ExplainQueryPlanSetId(pParse, pSub);
  5579   5565         sqlite3Select(pParse, pSub, &dest);
  5580   5566         pItem->pTab->nRowLogEst = pSub->nSelectRow;
  5581   5567         pItem->fg.viaCoroutine = 1;
  5582   5568         pItem->regResult = dest.iSdst;
  5583   5569         sqlite3VdbeEndCoroutine(v, pItem->regReturn);
  5584   5570         sqlite3VdbeJumpHere(v, addrTop-1);
  5585   5571         sqlite3ClearTempRegCache(pParse);
................................................................................
  5611   5597         if( pPrior ){
  5612   5598           sqlite3VdbeAddOp2(v, OP_OpenDup, pItem->iCursor, pPrior->iCursor);
  5613   5599           assert( pPrior->pSelect!=0 );
  5614   5600           pSub->nSelectRow = pPrior->pSelect->nSelectRow;
  5615   5601         }else{
  5616   5602           sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
  5617   5603           ExplainQueryPlan((pParse, 1, "MATERIALIZE 0x%p", pSub));
  5618         -        ExplainQueryPlanSetId(pParse,pSub);
  5619   5604           sqlite3Select(pParse, pSub, &dest);
  5620   5605         }
  5621   5606         pItem->pTab->nRowLogEst = pSub->nSelectRow;
  5622   5607         if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
  5623   5608         retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
  5624   5609         VdbeComment((v, "end %s", pItem->pTab->zName));
  5625   5610         sqlite3VdbeChangeP1(v, topAddr, retAddr);

Changes to src/sqliteInt.h.

  2777   2777     u8 op;                 /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  2778   2778     LogEst nSelectRow;     /* Estimated number of result rows */
  2779   2779     u32 selFlags;          /* Various SF_* values */
  2780   2780     int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  2781   2781   #if SELECTTRACE_ENABLED
  2782   2782     char zSelName[12];     /* Symbolic name of this SELECT use for debugging */
  2783   2783   #endif
  2784         -#if defined(SQLITETRACE_ENABLED) || !defined(SQLITE_OMIT_EXPLAIN)
  2785         -  u32 iSelectId;         /* EXPLAIN QUERY PLAN select ID */
  2786         -#endif
  2787   2784     int addrOpenEphm[2];   /* OP_OpenEphem opcodes related to this select */
  2788   2785     SrcList *pSrc;         /* The FROM clause */
  2789   2786     Expr *pWhere;          /* The WHERE clause */
  2790   2787     ExprList *pGroupBy;    /* The GROUP BY clause */
  2791   2788     Expr *pHaving;         /* The HAVING clause */
  2792   2789     ExprList *pOrderBy;    /* The ORDER BY clause */
  2793   2790     Select *pPrior;        /* Prior select in a compound select statement */

Changes to src/treeview.c.

   137    137       sqlite3TreeViewWith(pView, p->pWith, 1);
   138    138       cnt = 1;
   139    139       sqlite3TreeViewPush(pView, 1);
   140    140     }
   141    141     do{
   142    142   #if SELECTTRACE_ENABLED
   143    143       sqlite3TreeViewLine(pView,
   144         -      "SELECT%s%s (%s/%d/%p) selFlags=0x%x nSelectRow=%d",
          144  +      "SELECT%s%s (%s/%p) selFlags=0x%x nSelectRow=%d",
   145    145         ((p->selFlags & SF_Distinct) ? " DISTINCT" : ""),
   146    146         ((p->selFlags & SF_Aggregate) ? " agg_flag" : ""),
   147         -      p->zSelName, p->iSelectId, p, p->selFlags,
          147  +      p->zSelName, p, p->selFlags,
   148    148         (int)p->nSelectRow
   149    149       );
   150    150   #else
   151    151       sqlite3TreeViewLine(pView, "SELECT%s%s (0x%p) selFlags=0x%x nSelectRow=%d",
   152    152         ((p->selFlags & SF_Distinct) ? " DISTINCT" : ""),
   153    153         ((p->selFlags & SF_Aggregate) ? " agg_flag" : ""), p, p->selFlags,
   154    154         (int)p->nSelectRow

Changes to src/vdbe.h.

   201    201   #ifndef SQLITE_OMIT_EXPLAIN
   202    202     void sqlite3VdbeExplain(Parse*,u8,const char*,...);
   203    203     void sqlite3VdbeExplainPop(Parse*);
   204    204     int sqlite3VdbeExplainParent(Parse*);
   205    205   # define ExplainQueryPlan(P)        sqlite3VdbeExplain P
   206    206   # define ExplainQueryPlanPop(P)     sqlite3VdbeExplainPop(P)
   207    207   # define ExplainQueryPlanParent(P)  sqlite3VdbeExplainParent(P)
   208         -# define ExplainQueryPlanSetId(P,S) (S)->iSelectId=(P)->addrExplain
   209    208   #else
   210    209   # define ExplainQueryPlan(P)
   211    210   # define ExplainQueryPlanPop(P)
   212    211   # define ExplainQueryPlanParent(P) 0
   213         -# define ExplainQueryPlanSetId(P,S)
   214    212   #endif
   215    213   void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*);
   216    214   void sqlite3VdbeChangeOpcode(Vdbe*, u32 addr, u8);
   217    215   void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1);
   218    216   void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2);
   219    217   void sqlite3VdbeChangeP3(Vdbe*, u32 addr, int P3);
   220    218   void sqlite3VdbeChangeP5(Vdbe*, u16 P5);