/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell.c.

  1190   1190   ** Display scan stats.
  1191   1191   */
  1192   1192   static void display_scanstats(
  1193   1193     sqlite3 *db,                    /* Database to query */
  1194   1194     ShellState *pArg                /* Pointer to ShellState */
  1195   1195   ){
  1196   1196   #ifdef SQLITE_ENABLE_STMT_SCANSTATUS
  1197         -  int i;
  1198         -  double *arEstLoop = 0;
  1199         -  int nEstLoop = 0;
         1197  +  int i, k, n = 1;
  1200   1198     fprintf(pArg->out, "-------- scanstats --------\n");
  1201         -  for(i=0; 1; i++){
  1202         -    sqlite3_stmt *p = pArg->pStmt;
  1203         -    sqlite3_int64 nLoop, nVisit;
  1204         -    double rEst, rLoop;
  1205         -    int iSid;
  1206         -    const char *zExplain;
  1207         -    if( sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NLOOP, (void*)&nLoop) ){
  1208         -      break;
  1209         -    }
  1210         -    sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NVISIT, (void*)&nVisit);
  1211         -    sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EST, (void*)&rEst);
  1212         -    sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain);
  1213         -    sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_SELECTID, (void*)&iSid);
  1214         -    if( iSid>=nEstLoop ){
  1215         -      arEstLoop = sqlite3_realloc(arEstLoop, sizeof(arEstLoop[0])*(iSid+1) );
  1216         -      while( nEstLoop<=iSid ) arEstLoop[nEstLoop++] = 1.0; 
  1217         -    }
  1218         -    if( iSid>=0 ){
  1219         -      arEstLoop[iSid] *= rEst;
  1220         -      rLoop = arEstLoop[iSid];
  1221         -    }else{
  1222         -      rLoop = rEst;
  1223         -    }
  1224         -    fprintf(pArg->out, "Loop %2d: \"%s\"\n", i, zExplain);
  1225         -    fprintf(pArg->out, "        nLoop=%-8lld nRow=%-8lld estRow=%-8lld estRow/Loop=%-8g\n",
  1226         -        nLoop, nVisit, (sqlite3_int64)rLoop, rEst
  1227         -    );
  1228         -  }
  1229         -  sqlite3_free(arEstLoop);
         1199  +  for(k=0; n>0; k++){
         1200  +    double rEstLoop = 1.0;
         1201  +    for(i=n=0; 1; i++){
         1202  +      sqlite3_stmt *p = pArg->pStmt;
         1203  +      sqlite3_int64 nLoop, nVisit;
         1204  +      double rEst;
         1205  +      int iSid;
         1206  +      const char *zExplain;
         1207  +      if( sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NLOOP, (void*)&nLoop) ){
         1208  +        break;
         1209  +      }
         1210  +      sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_SELECTID, (void*)&iSid);
         1211  +      if( iSid!=k ) continue;
         1212  +      if( n==0 && k>0 ) fprintf(pArg->out, "-------- subquery %d --------\n", k);
         1213  +      n++;
         1214  +      sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_NVISIT, (void*)&nVisit);
         1215  +      sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EST, (void*)&rEst);
         1216  +      sqlite3_stmt_scanstatus(p, i, SQLITE_SCANSTAT_EXPLAIN, (void*)&zExplain);
         1217  +      fprintf(pArg->out, "Loop %2d: %s\n", n, zExplain);
         1218  +      rEstLoop *= rEst;
         1219  +      fprintf(pArg->out, "         nLoop=%-8lld nRow=%-8lld estRow=%-8lld estRow/Loop=%-8g\n",
         1220  +          nLoop, nVisit, (sqlite3_int64)rEstLoop, rEst
         1221  +      );
         1222  +    }
         1223  +  }
  1230   1224   #else
  1231   1225     fprintf(pArg->out, "-------- scanstats --------\n");
  1232   1226     fprintf(pArg->out,
  1233   1227         "sqlite3_stmt_scanstatus() unavailable - "
  1234   1228         "rebuild with SQLITE_ENABLE_STMT_SCANSTATUS\n"
  1235   1229     );
  1236   1230   #endif