Index: src/shell.c.in
==================================================================
--- src/shell.c.in
+++ src/shell.c.in
@@ -1770,11 +1770,11 @@
}
/*
** Display and reset the EXPLAIN QUERY PLAN data
*/
-static void eqp_render(ShellState *p){
+static void eqp_render(ShellState *p, sqlite3_stmt *pStmt){
EQPGraphRow *pRow = p->sGraph.pRow;
if( pRow ){
if( pRow->zText[0]=='-' ){
if( pRow->pNext==0 ){
eqp_reset(p);
@@ -1782,11 +1782,14 @@
}
utf8_printf(p->out, "%s\n", pRow->zText+3);
p->sGraph.pRow = pRow->pNext;
sqlite3_free(pRow);
}else{
- utf8_printf(p->out, "QUERY PLAN\n");
+ int iCost, nRow;
+ iCost = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_EST_COST, 0);
+ nRow = sqlite3_stmt_status(pStmt, SQLITE_STMTSTATUS_EST_ROWS, 0);
+ utf8_printf(p->out, "QUERY PLAN (log est cost=%d rows=%d)\n", iCost, nRow);
}
p->sGraph.zPrefix[0] = 0;
eqp_render_level(p, 0);
eqp_reset(p);
}
@@ -3073,14 +3076,14 @@
if( rc==SQLITE_OK ){
while( sqlite3_step(pExplain)==SQLITE_ROW ){
const char *zEQPLine = (const char*)sqlite3_column_text(pExplain,3);
int iEqpId = sqlite3_column_int(pExplain, 0);
int iParentId = sqlite3_column_int(pExplain, 1);
- if( zEQPLine[0]=='-' ) eqp_render(pArg);
+ if( zEQPLine[0]=='-' ) eqp_render(pArg, pExplain);
eqp_append(pArg, iEqpId, iParentId, zEQPLine);
}
- eqp_render(pArg);
+ eqp_render(pArg, pExplain);
}
sqlite3_finalize(pExplain);
sqlite3_free(zEQP);
if( pArg->autoEQP>=AUTOEQP_full ){
/* Also do an EXPLAIN for ".eqp full" mode */
@@ -3124,11 +3127,11 @@
}
bind_prepared_stmt(pArg, pStmt);
exec_prepared_stmt(pArg, pStmt);
explain_data_delete(pArg);
- eqp_render(pArg);
+ eqp_render(pArg, pStmt);
/* print usage stats if stats on */
if( pArg && pArg->statsOn ){
display_stats(db, pArg, 0);
}
Index: src/sqlite.h.in
==================================================================
--- src/sqlite.h.in
+++ src/sqlite.h.in
@@ -7845,10 +7845,19 @@
** [[SQLITE_STMTSTATUS_MEMUSED]] SQLITE_STMTSTATUS_MEMUSED
** ^This is the approximate number of bytes of heap memory
** used to store the prepared statement. ^This value is not actually
** a counter, and so the resetFlg parameter to sqlite3_stmt_status()
** is ignored when the opcode is SQLITE_STMTSTATUS_MEMUSED.
+**
+** [[SQLITE_STMTSTATUS_EST_ROWS]] SQLITE_STMTSTATUS_EST_ROWS
+** ^A return value of X indicates that the query planner estimated
+** that the query will return pow(2,X/10.0) rows.
+**
+** [[SQLITE_STMTSTATUS_EST_COST]] SQLITE_STMTSTATUS_EST_COST
+** ^A return value of X indicates that the query planner estimated
+** the relative cost of running this statement to completion is
+** pow(2,X/10.0).
**
**
*/
#define SQLITE_STMTSTATUS_FULLSCAN_STEP 1
#define SQLITE_STMTSTATUS_SORT 2
@@ -7855,10 +7864,12 @@
#define SQLITE_STMTSTATUS_AUTOINDEX 3
#define SQLITE_STMTSTATUS_VM_STEP 4
#define SQLITE_STMTSTATUS_REPREPARE 5
#define SQLITE_STMTSTATUS_RUN 6
#define SQLITE_STMTSTATUS_MEMUSED 99
+#define SQLITE_STMTSTATUS_EST_ROWS 100
+#define SQLITE_STMTSTATUS_EST_COST 101
/*
** CAPI3REF: Custom Page Cache Object
**
** The sqlite3_pcache type is opaque. It is implemented by
Index: src/vdbe.h
==================================================================
--- src/vdbe.h
+++ src/vdbe.h
@@ -262,10 +262,11 @@
#endif
void sqlite3VdbeSwap(Vdbe*,Vdbe*);
VdbeOp *sqlite3VdbeTakeOpArray(Vdbe*, int*, int*);
sqlite3_value *sqlite3VdbeGetBoundValue(Vdbe*, int, u8);
void sqlite3VdbeSetVarmask(Vdbe*, int);
+void sqlite3VdbeUpdateCostEstimates(Parse*, LogEst, LogEst);
#ifndef SQLITE_OMIT_TRACE
char *sqlite3VdbeExpandSql(Vdbe*, const char*);
#endif
int sqlite3MemCompare(const Mem*, const Mem*, const CollSeq*);
int sqlite3BlobCompare(const Mem*, const Mem*);
Index: src/vdbeInt.h
==================================================================
--- src/vdbeInt.h
+++ src/vdbeInt.h
@@ -423,10 +423,12 @@
bft changeCntOn:1; /* True to update the change-counter */
bft runOnlyOnce:1; /* Automatically expire on reset */
bft usesStmtJournal:1; /* True if uses a statement journal */
bft readOnly:1; /* True for statements that do not write */
bft bIsReader:1; /* True for statements that read */
+ LogEst nRowEst; /* Query planner of estimated number of output rows */
+ LogEst iCostEst; /* Query planner cost estimate */
yDbMask btreeMask; /* Bitmask of db->aDb[] entries referenced */
yDbMask lockMask; /* Subset of btreeMask that requires a lock */
u32 aCounter[7]; /* Counters used by sqlite3_stmt_status() */
char *zSql; /* Text of the SQL statement that generated this */
#ifdef SQLITE_ENABLE_NORMALIZE
Index: src/vdbeapi.c
==================================================================
--- src/vdbeapi.c
+++ src/vdbeapi.c
@@ -1656,31 +1656,43 @@
/*
** Return the value of a status counter for a prepared statement
*/
int sqlite3_stmt_status(sqlite3_stmt *pStmt, int op, int resetFlag){
Vdbe *pVdbe = (Vdbe*)pStmt;
- u32 v;
-#ifdef SQLITE_ENABLE_API_ARMOR
- if( !pStmt
- || (op!=SQLITE_STMTSTATUS_MEMUSED && (op<0||op>=ArraySize(pVdbe->aCounter)))
- ){
+ u32 v = 0;
+ if( !pStmt ){
(void)SQLITE_MISUSE_BKPT;
return 0;
}
-#endif
- if( op==SQLITE_STMTSTATUS_MEMUSED ){
- sqlite3 *db = pVdbe->db;
- sqlite3_mutex_enter(db->mutex);
- v = 0;
- db->pnBytesFreed = (int*)&v;
- sqlite3VdbeClearObject(db, pVdbe);
- sqlite3DbFree(db, pVdbe);
- db->pnBytesFreed = 0;
- sqlite3_mutex_leave(db->mutex);
- }else{
- v = pVdbe->aCounter[op];
- if( resetFlag ) pVdbe->aCounter[op] = 0;
+ switch( op ){
+ case SQLITE_STMTSTATUS_MEMUSED: {
+ sqlite3 *db = pVdbe->db;
+ sqlite3_mutex_enter(db->mutex);
+ v = 0;
+ db->pnBytesFreed = (int*)&v;
+ sqlite3VdbeClearObject(db, pVdbe);
+ sqlite3DbFree(db, pVdbe);
+ db->pnBytesFreed = 0;
+ sqlite3_mutex_leave(db->mutex);
+ break;
+ }
+ case SQLITE_STMTSTATUS_EST_ROWS: {
+ v = pVdbe->nRowEst;
+ break;
+ }
+ case SQLITE_STMTSTATUS_EST_COST: {
+ v = pVdbe->iCostEst;
+ break;
+ }
+ default: {
+ if( op>=0 && opaCounter) ){
+ v = pVdbe->aCounter[op];
+ if( resetFlag ) pVdbe->aCounter[op] = 0;
+ }else{
+ (void)SQLITE_MISUSE_BKPT;
+ }
+ }
}
return (int)v;
}
/*
Index: src/vdbeaux.c
==================================================================
--- src/vdbeaux.c
+++ src/vdbeaux.c
@@ -4845,10 +4845,24 @@
v->expmask |= 0x80000000;
}else{
v->expmask |= ((u32)1 << (iVar-1));
}
}
+
+/*
+** Update the estimated cost fields
+*/
+void sqlite3VdbeUpdateCostEstimates(Parse *pParse, LogEst iCost, LogEst nRow){
+ Vdbe *v = pParse->pVdbe;
+ if( v->iCostEst ){
+ v->iCostEst = sqlite3LogEstAdd(v->iCostEst, iCost+pParse->nQueryLoop) + 1;
+ if( nRow > v->nRowEst ) v->nRowEst = nRow;
+ }else{
+ v->nRowEst = nRow;
+ v->iCostEst = iCost + 1;
+ }
+}
/*
** Cause a function to throw an error if it was call from OP_PureFunc
** rather than OP_Function.
**
Index: src/where.c
==================================================================
--- src/where.c
+++ src/where.c
@@ -4361,10 +4361,11 @@
}
}
pWInfo->nRowOut = pFrom->nRow;
+ pWInfo->iTotalCost = pFrom->rCost;
/* Free temporary memory and return success */
sqlite3DbFreeNN(db, pSpace);
return SQLITE_OK;
}
@@ -5143,10 +5144,11 @@
SrcList *pTabList = pWInfo->pTabList;
sqlite3 *db = pParse->db;
/* Generate loop termination code.
*/
+ sqlite3VdbeUpdateCostEstimates(pParse, pWInfo->iTotalCost, pWInfo->nRowOut);
VdbeModuleComment((v, "End WHERE-core"));
for(i=pWInfo->nLevel-1; i>=0; i--){
int addr;
pLevel = &pWInfo->a[i];
pLoop = pLevel->pWLoop;
Index: src/whereInt.h
==================================================================
--- src/whereInt.h
+++ src/whereInt.h
@@ -460,10 +460,11 @@
u8 bOrderedInnerLoop; /* True if only the inner-most loop is ordered */
int iTop; /* The very beginning of the WHERE loop */
WhereLoop *pLoops; /* List of all WhereLoop objects */
Bitmask revMask; /* Mask of ORDER BY terms that need reversing */
LogEst nRowOut; /* Estimated number of output rows */
+ LogEst iTotalCost; /* Cost estimate for the whole plan */
WhereClause sWC; /* Decomposition of the WHERE clause */
WhereMaskSet sMaskSet; /* Map cursor numbers to bitmasks */
WhereLevel a[1]; /* Information about each nest loop in WHERE */
};