/ Check-in [1b25fa10]
Login

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

Overview
Comment:An experimental interface for retrieving the estimated cost and estimated number of output rows for a query.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | cost-est
Files: files | file ages | folders
SHA3-256: 1b25fa108ab2c4ada75935abf919de2b4c3b39553b2a0ab2a485645a02352e7e
User & Date: drh 2019-04-26 17:20:33
Context
2019-04-27
20:39
Add the shardvtab virtual table that uses the new cost estimation functions. Leaf check-in: 9404300a user: drh tags: cost-est
2019-04-26
17:20
An experimental interface for retrieving the estimated cost and estimated number of output rows for a query. check-in: 1b25fa10 user: drh tags: cost-est
2019-04-24
17:04
New test cases in test/fuzzdata8.db. check-in: 7be6222c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.in.

  1768   1768       }
  1769   1769     }
  1770   1770   }
  1771   1771   
  1772   1772   /*
  1773   1773   ** Display and reset the EXPLAIN QUERY PLAN data
  1774   1774   */
  1775         -static void eqp_render(ShellState *p){
         1775  +static void eqp_render(ShellState *p, sqlite3_stmt *pStmt){
  1776   1776     EQPGraphRow *pRow = p->sGraph.pRow;
  1777   1777     if( pRow ){
  1778   1778       if( pRow->zText[0]=='-' ){
  1779   1779         if( pRow->pNext==0 ){
  1780   1780           eqp_reset(p);
  1781   1781           return;
  1782   1782         }
  1783   1783         utf8_printf(p->out, "%s\n", pRow->zText+3);
  1784   1784         p->sGraph.pRow = pRow->pNext;
  1785   1785         sqlite3_free(pRow);
  1786   1786       }else{
  1787         -      utf8_printf(p->out, "QUERY PLAN\n");
         1787  +      int iCost, nRow;
         1788  +      iCost = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_EST_COST, 0);
         1789  +      nRow = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_EST_ROWS, 0);
         1790  +      utf8_printf(p->out, "QUERY PLAN (log est cost=%d rows=%d)\n", iCost, nRow);
  1788   1791       }
  1789   1792       p->sGraph.zPrefix[0] = 0;
  1790   1793       eqp_render_level(p, 0);
  1791   1794       eqp_reset(p);
  1792   1795     }
  1793   1796   }
  1794   1797   
................................................................................
  3071   3074           zEQP = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zStmtSql);
  3072   3075           rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
  3073   3076           if( rc==SQLITE_OK ){
  3074   3077             while( sqlite3_step(pExplain)==SQLITE_ROW ){
  3075   3078               const char *zEQPLine = (const char*)sqlite3_column_text(pExplain,3);
  3076   3079               int iEqpId = sqlite3_column_int(pExplain, 0);
  3077   3080               int iParentId = sqlite3_column_int(pExplain, 1);
  3078         -            if( zEQPLine[0]=='-' ) eqp_render(pArg);
         3081  +            if( zEQPLine[0]=='-' ) eqp_render(pArg, pExplain);
  3079   3082               eqp_append(pArg, iEqpId, iParentId, zEQPLine);
  3080   3083             }
  3081         -          eqp_render(pArg);
         3084  +          eqp_render(pArg, pExplain);
  3082   3085           }
  3083   3086           sqlite3_finalize(pExplain);
  3084   3087           sqlite3_free(zEQP);
  3085   3088           if( pArg->autoEQP>=AUTOEQP_full ){
  3086   3089             /* Also do an EXPLAIN for ".eqp full" mode */
  3087   3090             zEQP = sqlite3_mprintf("EXPLAIN %s", zStmtSql);
  3088   3091             rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0);
................................................................................
  3122   3125             explain_data_prepare(pArg, pStmt);
  3123   3126           }
  3124   3127         }
  3125   3128   
  3126   3129         bind_prepared_stmt(pArg, pStmt);
  3127   3130         exec_prepared_stmt(pArg, pStmt);
  3128   3131         explain_data_delete(pArg);
  3129         -      eqp_render(pArg);
         3132  +      eqp_render(pArg, pStmt);
  3130   3133   
  3131   3134         /* print usage stats if stats on */
  3132   3135         if( pArg && pArg->statsOn ){
  3133   3136           display_stats(db, pArg, 0);
  3134   3137         }
  3135   3138   
  3136   3139         /* print loop-counters if required */

Changes to src/sqlite.h.in.

  7843   7843   ** cycle.
  7844   7844   **
  7845   7845   ** [[SQLITE_STMTSTATUS_MEMUSED]] <dt>SQLITE_STMTSTATUS_MEMUSED</dt>
  7846   7846   ** <dd>^This is the approximate number of bytes of heap memory
  7847   7847   ** used to store the prepared statement.  ^This value is not actually
  7848   7848   ** a counter, and so the resetFlg parameter to sqlite3_stmt_status()
  7849   7849   ** is ignored when the opcode is SQLITE_STMTSTATUS_MEMUSED.
         7850  +**
         7851  +** [[SQLITE_STMTSTATUS_EST_ROWS]] <dt>SQLITE_STMTSTATUS_EST_ROWS</dt>
         7852  +** <dd>^A return value of X indicates that the query planner estimated
         7853  +** that the query will return pow(2,X/10.0) rows.
         7854  +**
         7855  +** [[SQLITE_STMTSTATUS_EST_COST]] <dt>SQLITE_STMTSTATUS_EST_COST</dt>
         7856  +** <dd>^A return value of X indicates that the query planner estimated
         7857  +** the relative cost of running this statement to completion is
         7858  +** pow(2,X/10.0).
  7850   7859   ** </dd>
  7851   7860   ** </dl>
  7852   7861   */
  7853   7862   #define SQLITE_STMTSTATUS_FULLSCAN_STEP     1
  7854   7863   #define SQLITE_STMTSTATUS_SORT              2
  7855   7864   #define SQLITE_STMTSTATUS_AUTOINDEX         3
  7856   7865   #define SQLITE_STMTSTATUS_VM_STEP           4
  7857   7866   #define SQLITE_STMTSTATUS_REPREPARE         5
  7858   7867   #define SQLITE_STMTSTATUS_RUN               6
  7859   7868   #define SQLITE_STMTSTATUS_MEMUSED           99
         7869  +#define SQLITE_STMTSTATUS_EST_ROWS          100
         7870  +#define SQLITE_STMTSTATUS_EST_COST          101
  7860   7871   
  7861   7872   /*
  7862   7873   ** CAPI3REF: Custom Page Cache Object
  7863   7874   **
  7864   7875   ** The sqlite3_pcache type is opaque.  It is implemented by
  7865   7876   ** the pluggable module.  The SQLite core has no knowledge of
  7866   7877   ** its size or internal structure and never deals with the

Changes to src/vdbe.h.

   260    260   void sqlite3VdbeAddDblquoteStr(sqlite3*,Vdbe*,const char*);
   261    261   int sqlite3VdbeUsesDoubleQuotedString(Vdbe*,const char*);
   262    262   #endif
   263    263   void sqlite3VdbeSwap(Vdbe*,Vdbe*);
   264    264   VdbeOp *sqlite3VdbeTakeOpArray(Vdbe*, int*, int*);
   265    265   sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe*, int, u8);
   266    266   void sqlite3VdbeSetVarmask(Vdbe*, int);
          267  +void sqlite3VdbeUpdateCostEstimates(Parse*, LogEst, LogEst);
   267    268   #ifndef SQLITE_OMIT_TRACE
   268    269     char *sqlite3VdbeExpandSql(Vdbe*, const char*);
   269    270   #endif
   270    271   int sqlite3MemCompare(const Mem*, const Mem*, const CollSeq*);
   271    272   int sqlite3BlobCompare(const Mem*, const Mem*);
   272    273   
   273    274   void sqlite3VdbeRecordUnpack(KeyInfo*,int,const void*,UnpackedRecord*);

Changes to src/vdbeInt.h.

   421    421     bft explain:2;          /* True if EXPLAIN present on SQL command */
   422    422     bft doingRerun:1;       /* True if rerunning after an auto-reprepare */
   423    423     bft changeCntOn:1;      /* True to update the change-counter */
   424    424     bft runOnlyOnce:1;      /* Automatically expire on reset */
   425    425     bft usesStmtJournal:1;  /* True if uses a statement journal */
   426    426     bft readOnly:1;         /* True for statements that do not write */
   427    427     bft bIsReader:1;        /* True for statements that read */
          428  +  LogEst nRowEst;         /* Query planner of estimated number of output rows */
          429  +  LogEst iCostEst;        /* Query planner cost estimate */
   428    430     yDbMask btreeMask;      /* Bitmask of db->aDb[] entries referenced */
   429    431     yDbMask lockMask;       /* Subset of btreeMask that requires a lock */
   430    432     u32 aCounter[7];        /* Counters used by sqlite3_stmt_status() */
   431    433     char *zSql;             /* Text of the SQL statement that generated this */
   432    434   #ifdef SQLITE_ENABLE_NORMALIZE
   433    435     char *zNormSql;         /* Normalization of the associated SQL statement */
   434    436     DblquoteStr *pDblStr;   /* List of double-quoted string literals */

Changes to src/vdbeapi.c.

  1654   1654   }
  1655   1655   
  1656   1656   /*
  1657   1657   ** Return the value of a status counter for a prepared statement
  1658   1658   */
  1659   1659   int sqlite3_stmt_status(sqlite3_stmt *pStmt, int op, int resetFlag){
  1660   1660     Vdbe *pVdbe = (Vdbe*)pStmt;
  1661         -  u32 v;
  1662         -#ifdef SQLITE_ENABLE_API_ARMOR
  1663         -  if( !pStmt 
  1664         -   || (op!=SQLITE_STMTSTATUS_MEMUSED && (op<0||op>=ArraySize(pVdbe->aCounter)))
  1665         -  ){
         1661  +  u32 v = 0;
         1662  +  if( !pStmt ){
  1666   1663       (void)SQLITE_MISUSE_BKPT;
  1667   1664       return 0;
  1668   1665     }
  1669         -#endif
  1670         -  if( op==SQLITE_STMTSTATUS_MEMUSED ){
  1671         -    sqlite3 *db = pVdbe->db;
  1672         -    sqlite3_mutex_enter(db->mutex);
  1673         -    v = 0;
  1674         -    db->pnBytesFreed = (int*)&v;
  1675         -    sqlite3VdbeClearObject(db, pVdbe);
  1676         -    sqlite3DbFree(db, pVdbe);
  1677         -    db->pnBytesFreed = 0;
  1678         -    sqlite3_mutex_leave(db->mutex);
  1679         -  }else{
  1680         -    v = pVdbe->aCounter[op];
  1681         -    if( resetFlag ) pVdbe->aCounter[op] = 0;
         1666  +  switch( op ){
         1667  +    case SQLITE_STMTSTATUS_MEMUSED: {
         1668  +      sqlite3 *db = pVdbe->db;
         1669  +      sqlite3_mutex_enter(db->mutex);
         1670  +      v = 0;
         1671  +      db->pnBytesFreed = (int*)&v;
         1672  +      sqlite3VdbeClearObject(db, pVdbe);
         1673  +      sqlite3DbFree(db, pVdbe);
         1674  +      db->pnBytesFreed = 0;
         1675  +      sqlite3_mutex_leave(db->mutex);
         1676  +      break;
         1677  +    }
         1678  +    case SQLITE_STMTSTATUS_EST_ROWS: {
         1679  +      v = pVdbe->nRowEst;
         1680  +      break;
         1681  +    }
         1682  +    case SQLITE_STMTSTATUS_EST_COST: {
         1683  +      v = pVdbe->iCostEst;
         1684  +      break;
         1685  +    }
         1686  +    default: {
         1687  +      if( op>=0 && op<ArraySize(pVdbe->aCounter) ){
         1688  +        v = pVdbe->aCounter[op];
         1689  +        if( resetFlag ) pVdbe->aCounter[op] = 0;
         1690  +      }else{
         1691  +        (void)SQLITE_MISUSE_BKPT;
         1692  +      }
         1693  +    }
  1682   1694     }
  1683   1695     return (int)v;
  1684   1696   }
  1685   1697   
  1686   1698   /*
  1687   1699   ** Return the SQL associated with a prepared statement
  1688   1700   */

Changes to src/vdbeaux.c.

  4843   4843     assert( (v->db->flags & SQLITE_EnableQPSG)==0 );
  4844   4844     if( iVar>=32 ){
  4845   4845       v->expmask |= 0x80000000;
  4846   4846     }else{
  4847   4847       v->expmask |= ((u32)1 << (iVar-1));
  4848   4848     }
  4849   4849   }
         4850  +
         4851  +/*
         4852  +** Update the estimated cost fields
         4853  +*/
         4854  +void sqlite3VdbeUpdateCostEstimates(Parse *pParse, LogEst iCost, LogEst nRow){
         4855  +  Vdbe *v = pParse->pVdbe;
         4856  +  if( v->iCostEst ){
         4857  +    v->iCostEst = sqlite3LogEstAdd(v->iCostEst, iCost+pParse->nQueryLoop) + 1;
         4858  +    if( nRow > v->nRowEst ) v->nRowEst = nRow;
         4859  +  }else{
         4860  +    v->nRowEst = nRow;
         4861  +    v->iCostEst = iCost + 1;
         4862  +  }
         4863  +}
  4850   4864   
  4851   4865   /*
  4852   4866   ** Cause a function to throw an error if it was call from OP_PureFunc
  4853   4867   ** rather than OP_Function.
  4854   4868   **
  4855   4869   ** OP_PureFunc means that the function must be deterministic, and should
  4856   4870   ** throw an error if it is given inputs that would make it non-deterministic.

Changes to src/where.c.

  4359   4359           pWInfo->revMask = revMask;
  4360   4360         }
  4361   4361       }
  4362   4362     }
  4363   4363   
  4364   4364   
  4365   4365     pWInfo->nRowOut = pFrom->nRow;
         4366  +  pWInfo->iTotalCost = pFrom->rCost;
  4366   4367   
  4367   4368     /* Free temporary memory and return success */
  4368   4369     sqlite3DbFreeNN(db, pSpace);
  4369   4370     return SQLITE_OK;
  4370   4371   }
  4371   4372   
  4372   4373   /*
................................................................................
  5141   5142     WhereLevel *pLevel;
  5142   5143     WhereLoop *pLoop;
  5143   5144     SrcList *pTabList = pWInfo->pTabList;
  5144   5145     sqlite3 *db = pParse->db;
  5145   5146   
  5146   5147     /* Generate loop termination code.
  5147   5148     */
         5149  +  sqlite3VdbeUpdateCostEstimates(pParse, pWInfo->iTotalCost, pWInfo->nRowOut);
  5148   5150     VdbeModuleComment((v, "End WHERE-core"));
  5149   5151     for(i=pWInfo->nLevel-1; i>=0; i--){
  5150   5152       int addr;
  5151   5153       pLevel = &pWInfo->a[i];
  5152   5154       pLoop = pLevel->pWLoop;
  5153   5155       if( pLevel->op!=OP_Noop ){
  5154   5156   #ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT

Changes to src/whereInt.h.

   458    458     u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
   459    459     u8 eDistinct;             /* One of the WHERE_DISTINCT_* values */
   460    460     u8 bOrderedInnerLoop;     /* True if only the inner-most loop is ordered */
   461    461     int iTop;                 /* The very beginning of the WHERE loop */
   462    462     WhereLoop *pLoops;        /* List of all WhereLoop objects */
   463    463     Bitmask revMask;          /* Mask of ORDER BY terms that need reversing */
   464    464     LogEst nRowOut;           /* Estimated number of output rows */
          465  +  LogEst iTotalCost;        /* Cost estimate for the whole plan */
   465    466     WhereClause sWC;          /* Decomposition of the WHERE clause */
   466    467     WhereMaskSet sMaskSet;    /* Map cursor numbers to bitmasks */
   467    468     WhereLevel a[1];          /* Information about each nest loop in WHERE */
   468    469   };
   469    470   
   470    471   /*
   471    472   ** Private interfaces - callable only by other where.c routines.