/ Check-in [eacbbd88]
Login

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

Overview
Comment:Changes the formatting of ".scanstats on" in the shell so that the stats for subqueries are grouped together and occur after the main query.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | scanstatus
Files: files | file ages | folders
SHA1: eacbbd8849db9b023eff15ef1cb42ec941299433
User & Date: drh 2014-11-06 12:08:21
Context
2014-11-06
12:17
On the ".scanstats on" output in the shell, initialize the estimated count for the first loop of each subquery to the actual loop count. check-in: d1c51c84 user: drh tags: scanstatus
12:08
Changes the formatting of ".scanstats on" in the shell so that the stats for subqueries are grouped together and occur after the main query. check-in: eacbbd88 user: drh tags: scanstatus
04:42
Add the SQLITE_SCANSTAT_SELECTID metric. Use it to improve the ".stmtscan on" output in the shell. check-in: 64ad5761 user: drh tags: scanstatus
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/shell.c.

1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200


1201
1202
1203
1204
1205
1206
1207
1208
1209




1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224

1225
1226
1227
1228
1229

1230
1231
1232
1233
1234
1235
1236
** Display scan stats.
*/
static void display_scanstats(
  sqlite3 *db,                    /* Database to query */
  ShellState *pArg                /* Pointer to ShellState */
){
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  int i;
  double *arEstLoop = 0;
  int nEstLoop = 0;
  fprintf(pArg->out, "-------- scanstats --------\n");


  for(i=0; 1; i++){
    sqlite3_stmt *p = pArg->pStmt;
    sqlite3_int64 nLoop, nVisit;
    double rEst, rLoop;
    int iSid;
    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*)&rEst);
    sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain);
    sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_SELECTID, (void*)&iSid);
    if( iSid>=nEstLoop ){
      arEstLoop = sqlite3_realloc(arEstLoop, sizeof(arEstLoop[0])*(iSid+1) );
      while( nEstLoop<=iSid ) arEstLoop[nEstLoop++] = 1.0; 
    }
    if( iSid>=0 ){
      arEstLoop[iSid] *= rEst;
      rLoop = arEstLoop[iSid];
    }else{
      rLoop = rEst;
    }
    fprintf(pArg->out, "Loop %2d: \"%s\"\n", i, zExplain);

    fprintf(pArg->out, "        nLoop=%-8lld nRow=%-8lld estRow=%-8lld estRow/Loop=%-8g\n",
        nLoop, nVisit, (sqlite3_int64)rLoop, rEst
    );
  }
  sqlite3_free(arEstLoop);

#else
  fprintf(pArg->out, "-------- scanstats --------\n");
  fprintf(pArg->out,
      "sqlite3_stmt_scanstatus() unavailable - "
      "rebuild with SQLITE_ENABLE_STMT_SCANSTATUS\n"
  );
#endif







|
<
<

>
>
|
|
|
|
|
|
|
|
|
>
>
>
>
|
|
|
<
<
<
<
<
<
<
<
<
<
<
|
>
|
|
|
|
<
>







1190
1191
1192
1193
1194
1195
1196
1197


1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216











1217
1218
1219
1220
1221
1222

1223
1224
1225
1226
1227
1228
1229
1230
** Display scan stats.
*/
static void display_scanstats(
  sqlite3 *db,                    /* Database to query */
  ShellState *pArg                /* Pointer to ShellState */
){
#ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  int i, k, n = 1;


  fprintf(pArg->out, "-------- scanstats --------\n");
  for(k=0; n>0; 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!=k ) continue;
      if( n==0 && 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
      );
    }

  }
#else
  fprintf(pArg->out, "-------- scanstats --------\n");
  fprintf(pArg->out,
      "sqlite3_stmt_scanstatus() unavailable - "
      "rebuild with SQLITE_ENABLE_STMT_SCANSTATUS\n"
  );
#endif