/ Check-in [f86b75b6]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Adjustments to #ifdefs in analyze.c to all a clean compile with no extra code with both ENABLE_STAT3 and ENABLE_STAT4 and with neither.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sqlite_stat4
Files: files | file ages | folders
SHA1: f86b75b6c7290ee6ddb3636090b00e99fc68c45e
User & Date: drh 2013-08-17 18:57:15
Context
2013-08-26
23:18
Merge the STAT4 capability into trunk. check-in: a32af0ab user: drh tags: trunk
2013-08-17
18:57
Adjustments to #ifdefs in analyze.c to all a clean compile with no extra code with both ENABLE_STAT3 and ENABLE_STAT4 and with neither. Closed-Leaf check-in: f86b75b6 user: drh tags: sqlite_stat4
2013-08-16
17:46
Add a test for the problem fixed by [91733bc485]. check-in: 5c591104 user: dan tags: sqlite_stat4
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

   137    137   ** all contain just a single integer which is the same as the first
   138    138   ** integer in the equivalent columns in sqlite_stat4.
   139    139   */
   140    140   #ifndef SQLITE_OMIT_ANALYZE
   141    141   #include "sqliteInt.h"
   142    142   
   143    143   #if defined(SQLITE_ENABLE_STAT4)
   144         -# define IsStat4 1
   145         -# define IsStat3 0
          144  +# define IsStat4     1
          145  +# define IsStat3     0
          146  +# define SQLITE_ENABLE_STAT34 1
   146    147   #elif defined(SQLITE_ENABLE_STAT3)
   147         -# define IsStat4 0
   148         -# define IsStat3 1
          148  +# define IsStat4     0
          149  +# define IsStat3     1
          150  +# define SQLITE_ENABLE_STAT34 1
   149    151   #else
   150         -# define IsStat4 0
   151         -# define IsStat3 0
          152  +# define IsStat4     0
          153  +# define IsStat3     0
          154  +# undef SQLITE_ENABLE_STAT34
   152    155   #endif
   153    156   
   154    157   /*
   155    158   ** This routine generates code that opens the sqlite_stat1 table for
   156    159   ** writing with cursor iStatCur. If the library was built with the
   157    160   ** SQLITE_ENABLE_STAT4 macro defined, then the sqlite_stat4 table is
   158    161   ** opened for writing using cursor (iStatCur+1)
................................................................................
   362    365     0,               /* xStep */
   363    366     0,               /* xFinalize */
   364    367     "stat_init",     /* zName */
   365    368     0,               /* pHash */
   366    369     0                /* pDestructor */
   367    370   };
   368    371   
          372  +#ifdef SQLITE_ENABLE_STAT34
   369    373   /*
   370    374   ** Return true if pNew is to be preferred over pOld.
   371    375   */
   372    376   static int sampleIsBetter(Stat4Sample *pNew, Stat4Sample *pOld){
   373    377     tRowcnt nEqNew = pNew->anEq[pNew->iCol];
   374    378     tRowcnt nEqOld = pOld->anEq[pOld->iCol];
   375    379   
................................................................................
   483    487           iMin = i;
   484    488         }
   485    489       }
   486    490       assert( iMin>=0 );
   487    491       p->iMin = iMin;
   488    492     }
   489    493   }
          494  +#endif /* SQLITE_ENABLE_STAT34 */
   490    495   
   491    496   /*
   492    497   ** Field iChng of the index being scanned has changed. So at this point
   493    498   ** p->current contains a sample that reflects the previous row of the
   494    499   ** index. The value of anEq[iChng] and subsequent anEq[] elements are
   495    500   ** correct at this point.
   496    501   */
   497    502   static void samplePushPrevious(Stat4Accum *p, int iChng){
   498         -  if( IsStat4 ){
   499         -    int i;
   500         -
   501         -    /* Check if any samples from the aBest[] array should be pushed
   502         -    ** into IndexSample.a[] at this point.  */
   503         -    for(i=(p->nCol-2); i>=iChng; i--){
   504         -      Stat4Sample *pBest = &p->aBest[i];
   505         -      if( p->nSample<p->mxSample
   506         -       || sampleIsBetter(pBest, &p->a[p->iMin])
   507         -      ){
   508         -        sampleInsert(p, pBest, i);
   509         -      }
   510         -    }
   511         -
   512         -    /* Update the anEq[] fields of any samples already collected. */
   513         -    for(i=p->nSample-1; i>=0; i--){
   514         -      int j;
   515         -      for(j=iChng; j<p->nCol; j++){
   516         -        if( p->a[i].anEq[j]==0 ) p->a[i].anEq[j] = p->current.anEq[j];
   517         -      }
   518         -    }
   519         -  }
   520         -
   521         -  if( IsStat3 && iChng==0 ){
          503  +#ifdef SQLITE_ENABLE_STAT4
          504  +  int i;
          505  +
          506  +  /* Check if any samples from the aBest[] array should be pushed
          507  +  ** into IndexSample.a[] at this point.  */
          508  +  for(i=(p->nCol-2); i>=iChng; i--){
          509  +    Stat4Sample *pBest = &p->aBest[i];
          510  +    if( p->nSample<p->mxSample
          511  +     || sampleIsBetter(pBest, &p->a[p->iMin])
          512  +    ){
          513  +      sampleInsert(p, pBest, i);
          514  +    }
          515  +  }
          516  +
          517  +  /* Update the anEq[] fields of any samples already collected. */
          518  +  for(i=p->nSample-1; i>=0; i--){
          519  +    int j;
          520  +    for(j=iChng; j<p->nCol; j++){
          521  +      if( p->a[i].anEq[j]==0 ) p->a[i].anEq[j] = p->current.anEq[j];
          522  +    }
          523  +  }
          524  +#endif
          525  +
          526  +#if defined(SQLITE_ENABLE_STAT3) && !defined(SQLITE_ENABLE_STAT4)
          527  +  if( iChng==0 ){
   522    528       tRowcnt nLt = p->current.anLt[0];
   523    529       tRowcnt nEq = p->current.anEq[0];
   524    530   
   525    531       /* Check if this is to be a periodic sample. If so, add it. */
   526    532       if( (nLt/p->nPSample)!=(nLt+nEq)/p->nPSample ){
   527    533         p->current.isPSample = 1;
   528    534         sampleInsert(p, &p->current, 0);
................................................................................
   530    536       }else 
   531    537   
   532    538       /* Or if it is a non-periodic sample. Add it in this case too. */
   533    539       if( p->nSample<p->mxSample || sampleIsBetter(&p->current, &p->a[p->iMin]) ){
   534    540         sampleInsert(p, &p->current, 0);
   535    541       }
   536    542     }
          543  +#endif
   537    544   }
   538    545   
   539    546   /*
   540    547   ** Implementation of the stat_push SQL function. 
   541    548   **
   542    549   **    stat_push(P,R,C)
   543    550   **
................................................................................
   579    586     }
   580    587   
   581    588     if( IsStat4 || IsStat3 ){
   582    589       p->current.iRowid = rowid;
   583    590       p->current.iHash = p->iPrn = p->iPrn*1103515245 + 12345;
   584    591     }
   585    592   
   586         -  if( IsStat4 ){
          593  +#ifdef SQLITE_ENABLE_STAT4
          594  +  {
   587    595       tRowcnt nLt = p->current.anLt[p->nCol-1];
   588    596   
   589    597       /* Check if this is to be a periodic sample. If so, add it. */
   590    598       if( (nLt/p->nPSample)!=(nLt+1)/p->nPSample ){
   591    599         p->current.isPSample = 1;
   592    600         p->current.iCol = 0;
   593    601         sampleInsert(p, &p->current, p->nCol-1);
................................................................................
   598    606       for(i=0; i<(p->nCol-1); i++){
   599    607         p->current.iCol = i;
   600    608         if( i>=iChng || sampleIsBetter(&p->current, &p->aBest[i]) ){
   601    609           sampleCopy(p, &p->aBest[i], &p->current);
   602    610         }
   603    611       }
   604    612     }
          613  +#endif
   605    614   }
   606    615   static const FuncDef statPushFuncdef = {
   607    616     3,               /* nArg */
   608    617     SQLITE_UTF8,     /* iPrefEnc */
   609    618     0,               /* flags */
   610    619     0,               /* pUserData */
   611    620     0,               /* pNext */
................................................................................
   620    629   #define STAT_GET_STAT1 0          /* "stat" column of stat1 table */
   621    630   #define STAT_GET_ROWID 1          /* "rowid" column of stat[34] entry */
   622    631   #define STAT_GET_NEQ   2          /* "neq" column of stat[34] entry */
   623    632   #define STAT_GET_NLT   3          /* "nlt" column of stat[34] entry */
   624    633   #define STAT_GET_NDLT  4          /* "ndlt" column of stat[34] entry */
   625    634   
   626    635   /*
   627         -** Implementation of the stat3_get(P,N,...) SQL function.  This routine is
   628         -** used to query the results.  Content is returned for the Nth sqlite_stat3
   629         -** row where N is between 0 and S-1 and S is the number of samples.  The
   630         -** value returned depends on the number of arguments.
          636  +** Implementation of the stat_get(P,J) SQL function.  This routine is
          637  +** used to query the results.  Content is returned for parameter J
          638  +** which is one of the STAT_GET_xxxx values defined above.
   631    639   **
   632         -**   argc==2    result:  rowid
   633         -**   argc==3    result:  nEq
   634         -**   argc==4    result:  nLt
   635         -**   argc==5    result:  nDLt
          640  +** If neither STAT3 nor STAT4 are enabled, then J is always
          641  +** STAT_GET_STAT1 and is hence omitted and this routine becomes
          642  +** a one-parameter function, stat_get(P), that always returns the
          643  +** stat1 table entry information.
   636    644   */
   637    645   static void statGet(
   638    646     sqlite3_context *context,
   639    647     int argc,
   640    648     sqlite3_value **argv
   641    649   ){
   642    650     Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]);
          651  +#ifdef SQLITE_ENABLE_STAT34
          652  +  /* STAT3 and STAT4 have a parameter on this routine. */
   643    653     int eCall = sqlite3_value_int(argv[1]);
          654  +  assert( argc==2 );
   644    655     assert( eCall==STAT_GET_STAT1 || eCall==STAT_GET_NEQ 
   645    656          || eCall==STAT_GET_ROWID || eCall==STAT_GET_NLT
   646    657          || eCall==STAT_GET_NDLT 
   647    658     );
   648         -
   649         -  if( eCall==STAT_GET_STAT1 ){
          659  +  if( eCall==STAT_GET_STAT1 )
          660  +#else
          661  +  assert( argc==1 );
          662  +#endif
          663  +  {
   650    664       /* Return the value to store in the "stat" column of the sqlite_stat1
   651    665       ** table for this index.
   652    666       **
   653    667       ** The value is a string composed of a list of integers describing 
   654    668       ** the index. The first integer in the list is the total number of 
   655    669       ** entries in the index. There is one additional integer in the list 
   656    670       ** for each indexed column. This additional integer is an estimate of
................................................................................
   685    699         sqlite3_snprintf(24, z, " %lld", iVal);
   686    700         z += sqlite3Strlen30(z);
   687    701         assert( p->current.anEq[i] );
   688    702       }
   689    703       assert( z[0]=='\0' && z>zRet );
   690    704   
   691    705       sqlite3_result_text(context, zRet, -1, sqlite3_free);
   692         -  }else if( eCall==STAT_GET_ROWID ){
          706  +  }
          707  +#ifdef SQLITE_ENABLE_STAT34
          708  +  else if( eCall==STAT_GET_ROWID ){
   693    709       if( p->iGet<0 ){
   694    710         samplePushPrevious(p, 0);
   695    711         p->iGet = 0;
   696    712       }
   697    713       if( p->iGet<p->nSample ){
   698    714         sqlite3_result_int64(context, p->a[p->iGet].iRowid);
   699    715       }
................................................................................
   726    742           }
   727    743           assert( z[0]=='\0' && z>zRet );
   728    744           z[-1] = '\0';
   729    745           sqlite3_result_text(context, zRet, -1, sqlite3_free);
   730    746         }
   731    747       }
   732    748     }
          749  +#endif /* SQLITE_ENABLE_STAT34 */
   733    750   }
   734    751   static const FuncDef statGetFuncdef = {
   735    752     2,               /* nArg */
   736    753     SQLITE_UTF8,     /* iPrefEnc */
   737    754     0,               /* flags */
   738    755     0,               /* pUserData */
   739    756     0,               /* pNext */
................................................................................
   743    760     "stat_get",      /* zName */
   744    761     0,               /* pHash */
   745    762     0                /* pDestructor */
   746    763   };
   747    764   
   748    765   static void callStatGet(Vdbe *v, int regStat4, int iParam, int regOut){
   749    766     assert( regOut!=regStat4 && regOut!=regStat4+1 );
          767  +#ifdef SQLITE_ENABLE_STAT34
   750    768     sqlite3VdbeAddOp2(v, OP_Integer, iParam, regStat4+1);
          769  +#else
          770  +  assert( iParam==STAT_GET_STAT1 );
          771  +#endif
   751    772     sqlite3VdbeAddOp3(v, OP_Function, 0, regStat4, regOut);
   752    773     sqlite3VdbeChangeP4(v, -1, (char*)&statGetFuncdef, P4_FUNCDEF);
   753         -  sqlite3VdbeChangeP5(v, 2);
          774  +  sqlite3VdbeChangeP5(v, 1 + IsStat3 + IsStat4);
   754    775   }
   755    776   
   756    777   /*
   757    778   ** Generate code to do an analysis of all indices associated with
   758    779   ** a single table.
   759    780   */
   760    781   static void analyzeOneTable(
................................................................................
  1244   1265   }
  1245   1266   
  1246   1267   /*
  1247   1268   ** If the Index.aSample variable is not NULL, delete the aSample[] array
  1248   1269   ** and its contents.
  1249   1270   */
  1250   1271   void sqlite3DeleteIndexSamples(sqlite3 *db, Index *pIdx){
  1251         -#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
         1272  +#ifdef SQLITE_ENABLE_STAT34
  1252   1273     if( pIdx->aSample ){
  1253   1274       int j;
  1254   1275       for(j=0; j<pIdx->nSample; j++){
  1255   1276         IndexSample *p = &pIdx->aSample[j];
  1256   1277         sqlite3DbFree(db, p->p);
  1257   1278       }
  1258   1279       sqlite3DbFree(db, pIdx->aSample);
................................................................................
  1260   1281     if( db && db->pnBytesFreed==0 ){
  1261   1282       pIdx->nSample = 0;
  1262   1283       pIdx->aSample = 0;
  1263   1284     }
  1264   1285   #else
  1265   1286     UNUSED_PARAMETER(db);
  1266   1287     UNUSED_PARAMETER(pIdx);
  1267         -#endif
         1288  +#endif /* SQLITE_ENABLE_STAT34 */
  1268   1289   }
  1269   1290   
  1270         -#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
  1271         -
         1291  +#ifdef SQLITE_ENABLE_STAT34
  1272   1292   /*
  1273   1293   ** Populate the pIdx->aAvgEq[] array based on the samples currently
  1274   1294   ** stored in pIdx->aSample[]. 
  1275   1295   */
  1276   1296   static void initAvgEq(Index *pIdx){
  1277   1297     if( pIdx ){
  1278   1298       IndexSample *aSample = pIdx->aSample;
................................................................................
  1458   1478         "SELECT idx,neq,nlt,ndlt,sqlite_record(sample) FROM %Q.sqlite_stat3",
  1459   1479         zDb
  1460   1480       );
  1461   1481     }
  1462   1482   
  1463   1483     return rc;
  1464   1484   }
  1465         -#endif /* SQLITE_ENABLE_STAT4 */
         1485  +#endif /* SQLITE_ENABLE_STAT34 */
  1466   1486   
  1467   1487   /*
  1468         -** Load the content of the sqlite_stat1 and sqlite_stat4 tables. The
         1488  +** Load the content of the sqlite_stat1 and sqlite_stat3/4 tables. The
  1469   1489   ** contents of sqlite_stat1 are used to populate the Index.aiRowEst[]
  1470         -** arrays. The contents of sqlite_stat4 are used to populate the
         1490  +** arrays. The contents of sqlite_stat3/4 are used to populate the
  1471   1491   ** Index.aSample[] arrays.
  1472   1492   **
  1473   1493   ** If the sqlite_stat1 table is not present in the database, SQLITE_ERROR
  1474         -** is returned. In this case, even if SQLITE_ENABLE_STAT4 was defined 
  1475         -** during compilation and the sqlite_stat4 table is present, no data is 
         1494  +** is returned. In this case, even if SQLITE_ENABLE_STAT3/4 was defined 
         1495  +** during compilation and the sqlite_stat3/4 table is present, no data is 
  1476   1496   ** read from it.
  1477   1497   **
  1478         -** If SQLITE_ENABLE_STAT4 was defined during compilation and the 
         1498  +** If SQLITE_ENABLE_STAT3/4 was defined during compilation and the 
  1479   1499   ** sqlite_stat4 table is not present in the database, SQLITE_ERROR is
  1480   1500   ** returned. However, in this case, data is read from the sqlite_stat1
  1481   1501   ** table (if it is present) before returning.
  1482   1502   **
  1483   1503   ** If an OOM error occurs, this function always sets db->mallocFailed.
  1484   1504   ** This means if the caller does not care about other errors, the return
  1485   1505   ** code may be ignored.
................................................................................
  1494   1514     assert( db->aDb[iDb].pBt!=0 );
  1495   1515   
  1496   1516     /* Clear any prior statistics */
  1497   1517     assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
  1498   1518     for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){
  1499   1519       Index *pIdx = sqliteHashData(i);
  1500   1520       sqlite3DefaultRowEst(pIdx);
  1501         -#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
         1521  +#ifdef SQLITE_ENABLE_STAT34
  1502   1522       sqlite3DeleteIndexSamples(db, pIdx);
  1503   1523       pIdx->aSample = 0;
  1504   1524   #endif
  1505   1525     }
  1506   1526   
  1507   1527     /* Check to make sure the sqlite_stat1 table exists */
  1508   1528     sInfo.db = db;
................................................................................
  1519   1539     }else{
  1520   1540       rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0);
  1521   1541       sqlite3DbFree(db, zSql);
  1522   1542     }
  1523   1543   
  1524   1544   
  1525   1545     /* Load the statistics from the sqlite_stat4 table. */
  1526         -#if defined(SQLITE_ENABLE_STAT4) || defined(SQLITE_ENABLE_STAT3)
         1546  +#ifdef SQLITE_ENABLE_STAT34
  1527   1547     if( rc==SQLITE_OK ){
  1528   1548       int lookasideEnabled = db->lookaside.bEnabled;
  1529   1549       db->lookaside.bEnabled = 0;
  1530   1550       rc = loadStat4(db, sInfo.zDatabase);
  1531   1551       db->lookaside.bEnabled = lookasideEnabled;
  1532   1552     }
  1533   1553   #endif