Index: src/shell.c
==================================================================
--- src/shell.c
+++ src/shell.c
@@ -1192,36 +1192,44 @@
static void display_scanstats(
sqlite3 *db, /* Database to query */
ShellState *pArg /* Pointer to ShellState */
){
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
- int i;
- fprintf(pArg->out, "-------- scanstats --------\n");
- for(i=0; 1; i++){
- sqlite3_stmt *p = pArg->pStmt;
- sqlite3_int64 nEst, nLoop, nVisit;
- const char *zExplain;
- if( sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NLOOP, (void*)&nLoop) ){
- break;
- }
- sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NVISIT, (void*)&nVisit);
- sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EST, (void*)&nEst);
- sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain);
-
- fprintf(pArg->out, "Loop %d: \"%s\"\n", i, zExplain);
- fprintf(pArg->out, " nLoop=%-8lld nVisit=%-8lld nEst=%-8lld\n",
- nLoop, nVisit, nEst
- );
- }
-#else
- fprintf(pArg->out, "-------- scanstats --------\n");
- fprintf(pArg->out,
- "sqlite3_stmt_scanstatus() unavailable - "
- "rebuild with SQLITE_ENABLE_STMT_SCANSTATUS\n"
- );
-#endif
- fprintf(pArg->out, "---------------------------\n");
+ int i, k, n, mx;
+ fprintf(pArg->out, "-------- scanstats --------\n");
+ mx = 0;
+ for(k=0; k<=mx; k++){
+ double rEstLoop = 1.0;
+ for(i=n=0; 1; i++){
+ sqlite3_stmt *p = pArg->pStmt;
+ sqlite3_int64 nLoop, nVisit;
+ double rEst;
+ int iSid;
+ const char *zExplain;
+ if( sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NLOOP, (void*)&nLoop) ){
+ break;
+ }
+ sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_SELECTID, (void*)&iSid);
+ if( iSid>mx ) mx = iSid;
+ if( iSid!=k ) continue;
+ if( n==0 ){
+ rEstLoop = (double)nLoop;
+ if( k>0 ) fprintf(pArg->out, "-------- subquery %d -------\n", k);
+ }
+ n++;
+ sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NVISIT, (void*)&nVisit);
+ sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EST, (void*)&rEst);
+ sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain);
+ fprintf(pArg->out, "Loop %2d: %s\n", n, zExplain);
+ rEstLoop *= rEst;
+ fprintf(pArg->out, " nLoop=%-8lld nRow=%-8lld estRow=%-8lld estRow/Loop=%-8g\n",
+ nLoop, nVisit, (sqlite3_int64)rEstLoop, rEst
+ );
+ }
+ }
+ fprintf(pArg->out, "---------------------------\n");
+#endif
}
/*
** Parameter azArray points to a zero-terminated array of strings. zStr
** points to a single nul-terminated string. Return non-zero if zStr
@@ -1673,10 +1681,11 @@
".prompt MAIN CONTINUE Replace the standard prompts\n"
".quit Exit this program\n"
".read FILENAME Execute SQL in FILENAME\n"
".restore ?DB? FILE Restore content of DB (default \"main\") from FILE\n"
".save FILE Write in-memory database into FILE\n"
+ ".scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off\n"
".schema ?TABLE? Show the CREATE statements\n"
" If TABLE specified, only show tables matching\n"
" LIKE pattern TABLE.\n"
".separator STRING ?NL? Change separator used by output mode and .import\n"
" NL is the end-of-line mark for CSV\n"
@@ -3058,10 +3067,13 @@
if( c=='s' && strncmp(azArg[0], "scanstats", n)==0 ){
if( nArg==2 ){
p->scanstatsOn = booleanValue(azArg[1]);
+#ifndef SQLITE_ENABLE_STMT_SCANSTATUS
+ fprintf(stderr, "Warning: .scanstats not available in this build.\n");
+#endif
}else{
fprintf(stderr, "Usage: .scanstats on|off\n");
rc = 1;
}
}else
Index: src/sqlite.h.in
==================================================================
--- src/sqlite.h.in
+++ src/sqlite.h.in
@@ -7438,17 +7438,19 @@
** ^The [sqlite3_int64] variable pointed to by the T parameter will be set to the
** total number of times that the X-th loop has run.
**
** [[SQLITE_SCANSTAT_NVISIT]] SQLITE_SCANSTAT_NVISIT
** ^The [sqlite3_int64] variable pointed to by the T parameter will be set to the
-** total number of rows visited by the X-th loop.
+** total number of rows examined by all iterations of the X-th loop.
**
** [[SQLITE_SCANSTAT_EST]] SQLITE_SCANSTAT_EST
-** ^The [sqlite3_int64] variable pointed to by the T parameter will be set to the
-** query planner's estimate for the number of rows visited for each
-** iteration of the X-th loop. If the query planner's estimate was accurate,
-** then this value should be approximately NVISIT/NLOOP.
+** ^The "double" variable pointed to by the T parameter will be set to the
+** query planner's estimate for the average number of rows output from each
+** iteration of the X-th loop. If the query planner's estimates was accurate,
+** then this value will approximate the quotient NVISIT/NLOOP and the
+** product of this value for all prior loops with the same SELECTID will
+** be the NLOOP value for the current loop.
**
** [[SQLITE_SCANSTAT_NAME]] SQLITE_SCANSTAT_NAME
** ^The "const char *" variable pointed to by the T parameter will be set to
** a zero-terminated UTF-8 string containing the name of the index or table used
** for the X-th loop.
@@ -7455,17 +7457,25 @@
**
** [[SQLITE_SCANSTAT_EXPLAIN]] SQLITE_SCANSTAT_EXPLAIN
** ^The "const char *" variable pointed to by the T parameter will be set to
** a zero-terminated UTF-8 string containing the [EXPLAIN QUERY PLAN] description
** for the X-th loop.
+**
+** [[SQLITE_SCANSTAT_SELECTID]] SQLITE_SCANSTAT_SELECT
+** ^The "int" variable pointed to by the T parameter will be set to the
+** "select-id" for the X-th loop. The select-id identifies which query or
+** subquery the loop is part of. The main query has a select-id of zero.
+** The select-id is the same value as is output in the first column
+** of an [EXPLAIN QUERY PLAN] query.
**
*/
#define SQLITE_SCANSTAT_NLOOP 0
#define SQLITE_SCANSTAT_NVISIT 1
#define SQLITE_SCANSTAT_EST 2
#define SQLITE_SCANSTAT_NAME 3
#define SQLITE_SCANSTAT_EXPLAIN 4
+#define SQLITE_SCANSTAT_SELECTID 5
/*
** CAPI3REF: Prepared Statement Scan Status
**
** Return status data for a single loop within query pStmt.
Index: src/test1.c
==================================================================
--- src/test1.c
+++ src/test1.c
@@ -2316,11 +2316,11 @@
const char *zName;
const char *zExplain;
sqlite3_int64 nLoop;
sqlite3_int64 nVisit;
- sqlite3_int64 nEst;
+ double rEst;
int res;
if( objc!=3 ){
Tcl_WrongNumArgs(interp, 1, objv, "STMT IDX");
return TCL_ERROR;
@@ -2334,13 +2334,13 @@
Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nLoop", -1));
Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nLoop));
sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_NVISIT, (void*)&nVisit);
Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nVisit", -1));
Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nVisit));
- sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_EST, (void*)&nEst);
+ sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_EST, (void*)&rEst);
Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("nEst", -1));
- Tcl_ListObjAppendElement(0, pRet, Tcl_NewWideIntObj(nEst));
+ Tcl_ListObjAppendElement(0, pRet, Tcl_NewDoubleObj(rEst));
sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_NAME, (void*)&zName);
Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zName", -1));
Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj(zName, -1));
sqlite3_stmt_scanstatus(pStmt, idx, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain);
Tcl_ListObjAppendElement(0, pRet, Tcl_NewStringObj("zExplain", -1));
Index: src/vdbe.h
==================================================================
--- src/vdbe.h
+++ src/vdbe.h
@@ -281,11 +281,11 @@
# define VdbeCoverageNeverTaken(v)
# define VDBE_OFFSET_LINENO(x) 0
#endif
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
-void sqlite3VdbeScanStatus(Vdbe*, int, int, int, i64, const char*);
+void sqlite3VdbeScanStatus(Vdbe*, int, int, int, LogEst, const char*);
#else
# define sqlite3VdbeScanStatus(a,b,c,d,e)
#endif
#endif
Index: src/vdbeInt.h
==================================================================
--- src/vdbeInt.h
+++ src/vdbeInt.h
@@ -300,11 +300,12 @@
typedef struct ScanStatus ScanStatus;
struct ScanStatus {
int addrExplain; /* OP_Explain for loop */
int addrLoop; /* Address of "loops" counter */
int addrVisit; /* Address of "rows visited" counter */
- i64 nEst; /* Estimated rows per loop */
+ int iSelectID; /* The "Select-ID" for this loop */
+ LogEst nEst; /* Estimated output rows per loop */
char *zName; /* Name of table or index */
};
/*
** An instance of the virtual machine. This structure contains the complete
Index: src/vdbeapi.c
==================================================================
--- src/vdbeapi.c
+++ src/vdbeapi.c
@@ -1498,11 +1498,17 @@
case SQLITE_SCANSTAT_NVISIT: {
*(sqlite3_int64*)pOut = p->anExec[pScan->addrVisit];
break;
}
case SQLITE_SCANSTAT_EST: {
- *(sqlite3_int64*)pOut = pScan->nEst;
+ double r = 1.0;
+ LogEst x = pScan->nEst;
+ while( x<100 ){
+ x += 10;
+ r *= 0.5;
+ }
+ *(double*)pOut = r*sqlite3LogEstToInt(x);
break;
}
case SQLITE_SCANSTAT_NAME: {
*(const char**)pOut = pScan->zName;
break;
@@ -1512,10 +1518,18 @@
*(const char**)pOut = p->aOp[ pScan->addrExplain ].p4.z;
}else{
*(const char**)pOut = 0;
}
break;
+ }
+ case SQLITE_SCANSTAT_SELECTID: {
+ if( pScan->addrExplain ){
+ *(int*)pOut = p->aOp[ pScan->addrExplain ].p1;
+ }else{
+ *(int*)pOut = -1;
+ }
+ break;
}
default: {
return 1;
}
}
Index: src/vdbeaux.c
==================================================================
--- src/vdbeaux.c
+++ src/vdbeaux.c
@@ -604,11 +604,11 @@
void sqlite3VdbeScanStatus(
Vdbe *p, /* VM to add scanstatus() to */
int addrExplain, /* Address of OP_Explain (or 0) */
int addrLoop, /* Address of loop counter */
int addrVisit, /* Address of rows visited counter */
- i64 nEst, /* Estimated number of rows */
+ LogEst nEst, /* Estimated number of output rows */
const char *zName /* Name of table or index being scanned */
){
int nByte = (p->nScan+1) * sizeof(ScanStatus);
ScanStatus *aNew;
aNew = (ScanStatus*)sqlite3DbRealloc(p->db, p->aScan, nByte);
Index: src/where.c
==================================================================
--- src/where.c
+++ src/where.c
@@ -2944,22 +2944,18 @@
SrcList *pSrclist, /* FROM clause pLvl reads data from */
WhereLevel *pLvl, /* Level to add scanstatus() entry for */
int addrExplain /* Address of OP_Explain (or 0) */
){
const char *zObj = 0;
- i64 nEst = 1;
WhereLoop *pLoop = pLvl->pWLoop;
if( (pLoop->wsFlags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0 ){
zObj = pLoop->u.btree.pIndex->zName;
}else{
zObj = pSrclist->a[pLvl->iFrom].zName;
}
- if( pLoop->nOut>=10 ){
- nEst = sqlite3LogEstToInt(pLoop->nOut);
- }
sqlite3VdbeScanStatus(
- v, addrExplain, pLvl->addrBody, pLvl->addrVisit, nEst, zObj
+ v, addrExplain, pLvl->addrBody, pLvl->addrVisit, pLoop->nOut, zObj
);
}
#else
# define addScanStatus(a, b, c, d) ((void)d)
#endif
Index: test/scanstatus.test
==================================================================
--- test/scanstatus.test
+++ test/scanstatus.test
@@ -43,51 +43,51 @@
uplevel [list do_test $tn [list set {} $ret] [list {*}$res]]
}
do_execsql_test 1.1 { SELECT count(*) FROM t1, t2; } 6
do_scanstatus_test 1.2 {
- nLoop 1 nVisit 2 nEst 1048576 zName t1 zExplain {SCAN TABLE t1}
- nLoop 2 nVisit 6 nEst 1048576 zName t2 zExplain {SCAN TABLE t2}
+ nLoop 1 nVisit 2 nEst 1048576.0 zName t1 zExplain {SCAN TABLE t1}
+ nLoop 2 nVisit 6 nEst 1048576.0 zName t2 zExplain {SCAN TABLE t2}
}
do_execsql_test 1.3 {
ANALYZE;
SELECT count(*) FROM t1, t2;
} 6
do_scanstatus_test 1.4 {
- nLoop 1 nVisit 2 nEst 2 zName t1 zExplain {SCAN TABLE t1}
- nLoop 2 nVisit 6 nEst 3 zName t2 zExplain {SCAN TABLE t2}
+ nLoop 1 nVisit 2 nEst 2.0 zName t1 zExplain {SCAN TABLE t1}
+ nLoop 2 nVisit 6 nEst 3.0 zName t2 zExplain {SCAN TABLE t2}
}
do_execsql_test 1.5 { ANALYZE }
do_execsql_test 1.6 {
SELECT count(*) FROM t1, t2 WHERE t2.rowid>1;
} 4
do_scanstatus_test 1.7 {
- nLoop 1 nVisit 2 nEst 2 zName t2 zExplain
+ nLoop 1 nVisit 2 nEst 2.0 zName t2 zExplain
{SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
- nLoop 2 nVisit 4 nEst 2 zName t1 zExplain {SCAN TABLE t1}
+ nLoop 2 nVisit 4 nEst 2.0 zName t1 zExplain {SCAN TABLE t1}
}
do_execsql_test 1.8 {
SELECT count(*) FROM t1, t2 WHERE t2.rowid>1;
} 4
do_scanstatus_test 1.9 {
- nLoop 2 nVisit 4 nEst 2 zName t2 zExplain
+ nLoop 2 nVisit 4 nEst 2.0 zName t2 zExplain
{SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
- nLoop 4 nVisit 8 nEst 2 zName t1 zExplain {SCAN TABLE t1}
+ nLoop 4 nVisit 8 nEst 2.0 zName t1 zExplain {SCAN TABLE t1}
}
do_test 1.9 {
sqlite3_stmt_scanstatus_reset [db_last_stmt_ptr db]
} {}
do_scanstatus_test 1.10 {
- nLoop 0 nVisit 0 nEst 2 zName t2 zExplain
+ nLoop 0 nVisit 0 nEst 2.0 zName t2 zExplain
{SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)}
- nLoop 0 nVisit 0 nEst 2 zName t1 zExplain {SCAN TABLE t1}
+ nLoop 0 nVisit 0 nEst 2.0 zName t1 zExplain {SCAN TABLE t1}
}
#-------------------------------------------------------------------------
# Try a few different types of scans.
#
@@ -102,43 +102,43 @@
SELECT * FROM x1 WHERE i=2;
} {2 two}
do_scanstatus_test 2.2 {
- nLoop 1 nVisit 1 nEst 1 zName x1
+ nLoop 1 nVisit 1 nEst 1.0 zName x1
zExplain {SEARCH TABLE x1 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_execsql_test 2.3.1 {
SELECT * FROM x1 WHERE j='two'
} {2 two}
do_scanstatus_test 2.3.2 {
- nLoop 1 nVisit 1 nEst 10 zName x1j
+ nLoop 1 nVisit 1 nEst 10.0 zName x1j
zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j=?)}
}
do_execsql_test 2.4.1 {
SELECT * FROM x1 WHERE j<'two'
} {4 four 1 one 3 three}
do_scanstatus_test 2.4.2 {
- nLoop 1 nVisit 3 nEst 262144 zName x1j
+ nLoop 1 nVisit 3 nEst 262144.0 zName x1j
zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j='two'
} {2 two}
do_scanstatus_test 2.5.2 {
- nLoop 1 nVisit 1 nEst 262144 zName x1j
+ nLoop 1 nVisit 1 nEst 262144.0 zName x1j
zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>?)}
}
do_execsql_test 2.6.1 {
SELECT * FROM x1 WHERE j BETWEEN 'three' AND 'two'
} {3 three 2 two}
do_scanstatus_test 2.6.2 {
- nLoop 1 nVisit 2 nEst 16384 zName x1j
+ nLoop 1 nVisit 2 nEst 16384.0 zName x1j
zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>? AND j? AND j? AND a? AND b? AND b? AND a