/ Check-in [bfbdd074]
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:The analyze_as_needed pragma now responds to table size growth and will automatically rerun the analysis after each 10x size increase.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | auto-analyze
Files: files | file ages | folders
SHA1: bfbdd07409688fac4ccddbab3639745f6152e23d
User & Date: drh 2017-02-17 19:24:06
Context
2017-02-18
02:19
In the analyze_as_needed pragma, avoid running unnecessary OP_LoadAnalysis and OP_Expire opcodes. Make the analyze_as_needed pragma responsive to the schema name. check-in: 882599a4 user: drh tags: auto-analyze
2017-02-17
19:24
The analyze_as_needed pragma now responds to table size growth and will automatically rerun the analysis after each 10x size increase. check-in: bfbdd074 user: drh tags: auto-analyze
16:26
Add the "PRAGMA analyze_as_needed" command. check-in: e93db237 user: drh tags: auto-analyze
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

954
955
956
957
958
959
960
961

962
963
964

965
966
967
968
969
970
971
....
1010
1011
1012
1013
1014
1015
1016



1017
1018
1019
1020
1021
1022
1023
....
1267
1268
1269
1270
1271
1272
1273

1274
1275
1276
1277
1278
1279
1280
....
1282
1283
1284
1285
1286
1287
1288
1289







1290
1291
1292
1293
1294








1295
1296


1297

1298
1299
1300
1301
1302
1303
1304
1305
....
1325
1326
1327
1328
1329
1330
1331

1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
....
1364
1365
1366
1367
1368
1369
1370
1371

1372
1373
1374
1375
1376
1377
1378
*/
static void analyzeOneTable(
  Parse *pParse,   /* Parser context */
  Table *pTab,     /* Table whose indices are to be analyzed */
  Index *pOnlyIdx, /* If not NULL, only analyze this one index */
  int iStatCur,    /* Index of VdbeCursor that writes the sqlite_stat1 table */
  int iMem,        /* Available memory locations begin here */
  int iTab         /* Next available cursor */

){
  sqlite3 *db = pParse->db;    /* Database handle */
  Index *pIdx;                 /* An index to being analyzed */

  int iIdxCur;                 /* Cursor open on index being analyzed */
  int iTabCur;                 /* Table cursor */
  Vdbe *v;                     /* The virtual machine being built up */
  int i;                       /* Loop counter */
  int jZeroRows = -1;          /* Jump from here if number of rows is zero */
  int iDb;                     /* Index of database containing pTab */
  u8 needTableCnt = 1;         /* True to count the table */
................................................................................
  ** to use for scanning indexes (iIdxCur). No index cursor is opened at
  ** this time though.  */
  sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
  iTabCur = iTab++;
  iIdxCur = iTab++;
  pParse->nTab = MAX(pParse->nTab, iTab);
  sqlite3OpenTable(pParse, iTabCur, iDb, pTab, OP_OpenRead);



  sqlite3VdbeLoadString(v, regTabname, pTab->zName);

  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    int nCol;                     /* Number of columns in pIdx. "N" */
    int addrRewind;               /* Address of "OP_Rewind iIdxCur" */
    int addrNextRow;              /* Address of "next_row:" */
    const char *zIdxName;         /* Name of the index */
................................................................................
    assert( "BBB"[0]==SQLITE_AFF_TEXT );
    sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0);
    sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
    sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid);
    sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
    sqlite3VdbeJumpHere(v, jZeroRows);
  }

}


/*
** Generate code that will cause the most recent index analysis to
** be loaded into internal hash tables where is can be used.
*/
................................................................................
  Vdbe *v = sqlite3GetVdbe(pParse);
  if( v ){
    sqlite3VdbeAddOp1(v, OP_LoadAnalysis, iDb);
  }
}

/*
** Return true if table pTab is in need of being reanalyzed.







*/
static int analyzeNeeded(Table *pTab){
  Index *pIdx;
  if( (pTab->tabFlags & TF_StatsUsed)==0 ) return 0;
  if( (pTab->tabFlags & TF_SizeChng)!=0 ) return 1;








  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    if( !pIdx->hasStat1 ) return 1;


  }

  return 0;
}

/*
** Generate code that will do an analysis of an entire database.
**
** Return a count of the number of tables actually analyzed.  Return 0
** if nothing was analyzed.
................................................................................
  for(iDb=iDbFirst; iDb<=iDbLast; iDb++){
    if( iDb==1 ) continue;  /* Do not analyze the TEMP database */
    pSchema = db->aDb[iDb].pSchema;
    assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
    cnt = 0;
    for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){
      Table *pTab = (Table*)sqliteHashData(k);

      if( !onlyIfNeeded || analyzeNeeded(pTab) ){
        if( cnt==0 ){
          sqlite3BeginWriteOperation(pParse, 0, iDb);
          iStatCur = pParse->nTab;
          pParse->nTab += 3;
          openStatTable(pParse, iDb, iStatCur, 0, 0);
          iMem = pParse->nMem+1;
          iTab = pParse->nTab;
        }
        analyzeOneTable(pParse, pTab, 0, iStatCur, iMem, iTab);
        cnt++;
        allCnt++;
      }
    }
    if( cnt ) loadAnalysis(pParse, iDb);
  }
  return allCnt;
................................................................................
  iStatCur = pParse->nTab;
  pParse->nTab += 3;
  if( pOnlyIdx ){
    openStatTable(pParse, iDb, iStatCur, pOnlyIdx->zName, "idx");
  }else{
    openStatTable(pParse, iDb, iStatCur, pTab->zName, "tbl");
  }
  analyzeOneTable(pParse, pTab, pOnlyIdx, iStatCur,pParse->nMem+1,pParse->nTab);

  loadAnalysis(pParse, iDb);
}

/*
** Generate code for the ANALYZE command.  The parser calls this routine
** when it recognizes an ANALYZE command.
**







|
>



>







 







>
>
>







 







>







 







|
>
>
>
>
>
>
>

|


<
>
>
>
>
>
>
>
>

|
>
>
|
>
|







 







>
|








|







 







|
>







954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
....
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
....
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
....
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306

1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
....
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
....
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
*/
static void analyzeOneTable(
  Parse *pParse,   /* Parser context */
  Table *pTab,     /* Table whose indices are to be analyzed */
  Index *pOnlyIdx, /* If not NULL, only analyze this one index */
  int iStatCur,    /* Index of VdbeCursor that writes the sqlite_stat1 table */
  int iMem,        /* Available memory locations begin here */
  int iTab,        /* Next available cursor */
  LogEst szOld     /* Run the analysis if table row count is larger than this */
){
  sqlite3 *db = pParse->db;    /* Database handle */
  Index *pIdx;                 /* An index to being analyzed */
  int addrSizeCk = 0;          /* Address of the IfSmaller */
  int iIdxCur;                 /* Cursor open on index being analyzed */
  int iTabCur;                 /* Table cursor */
  Vdbe *v;                     /* The virtual machine being built up */
  int i;                       /* Loop counter */
  int jZeroRows = -1;          /* Jump from here if number of rows is zero */
  int iDb;                     /* Index of database containing pTab */
  u8 needTableCnt = 1;         /* True to count the table */
................................................................................
  ** to use for scanning indexes (iIdxCur). No index cursor is opened at
  ** this time though.  */
  sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
  iTabCur = iTab++;
  iIdxCur = iTab++;
  pParse->nTab = MAX(pParse->nTab, iTab);
  sqlite3OpenTable(pParse, iTabCur, iDb, pTab, OP_OpenRead);
  if( szOld>0 ){
    addrSizeCk = sqlite3VdbeAddOp3(v, OP_IfSmaller, iTabCur, 0, szOld);
   }
  sqlite3VdbeLoadString(v, regTabname, pTab->zName);

  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    int nCol;                     /* Number of columns in pIdx. "N" */
    int addrRewind;               /* Address of "OP_Rewind iIdxCur" */
    int addrNextRow;              /* Address of "next_row:" */
    const char *zIdxName;         /* Name of the index */
................................................................................
    assert( "BBB"[0]==SQLITE_AFF_TEXT );
    sqlite3VdbeAddOp4(v, OP_MakeRecord, regTabname, 3, regTemp, "BBB", 0);
    sqlite3VdbeAddOp2(v, OP_NewRowid, iStatCur, regNewRowid);
    sqlite3VdbeAddOp3(v, OP_Insert, iStatCur, regTemp, regNewRowid);
    sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
    sqlite3VdbeJumpHere(v, jZeroRows);
  }
  sqlite3VdbeJumpHere(v, addrSizeCk);
}


/*
** Generate code that will cause the most recent index analysis to
** be loaded into internal hash tables where is can be used.
*/
................................................................................
  Vdbe *v = sqlite3GetVdbe(pParse);
  if( v ){
    sqlite3VdbeAddOp1(v, OP_LoadAnalysis, iDb);
  }
}

/*
** Return true if table pTab might need to being reanalyzed.  Return
** false if we know that pTab should not be reanalyzed.
**
** If returning true, also set *pThreshold to a size threshold that
** will determine at run-time whether or not the reanalysis occurs.
** The reanalysis will only occur if the size of the table is greater
** than the threshold. Not that the threshold is a logarithmic LogEst
** value.
*/
static int analyzeNeeded(Table *pTab, LogEst *pThreshold){
  Index *pIdx;
  if( (pTab->tabFlags & TF_StatsUsed)==0 ) return 0;


  /* If TF_StatsUsed is true, then we might need to reanalyze.  But
  ** only reanalyze if the table size has grown by a factor of 10 or more */
  *pThreshold = pTab->nRowLogEst + 33;  assert( sqlite3LogEst(10)==33 );

  /* Except, if any of the indexes of the table do not have valid
  ** sqlite_stat1 entries, then set the size threshold to zero to
  ** ensure the analysis will always occur. */
  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
    if( !pIdx->hasStat1 ){
      *pThreshold = 0;
      break;
    }
  }
  return 1;
}

/*
** Generate code that will do an analysis of an entire database.
**
** Return a count of the number of tables actually analyzed.  Return 0
** if nothing was analyzed.
................................................................................
  for(iDb=iDbFirst; iDb<=iDbLast; iDb++){
    if( iDb==1 ) continue;  /* Do not analyze the TEMP database */
    pSchema = db->aDb[iDb].pSchema;
    assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
    cnt = 0;
    for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){
      Table *pTab = (Table*)sqliteHashData(k);
      LogEst szThreshold = 0;
      if( !onlyIfNeeded || analyzeNeeded(pTab, &szThreshold) ){
        if( cnt==0 ){
          sqlite3BeginWriteOperation(pParse, 0, iDb);
          iStatCur = pParse->nTab;
          pParse->nTab += 3;
          openStatTable(pParse, iDb, iStatCur, 0, 0);
          iMem = pParse->nMem+1;
          iTab = pParse->nTab;
        }
        analyzeOneTable(pParse, pTab, 0, iStatCur, iMem, iTab, szThreshold);
        cnt++;
        allCnt++;
      }
    }
    if( cnt ) loadAnalysis(pParse, iDb);
  }
  return allCnt;
................................................................................
  iStatCur = pParse->nTab;
  pParse->nTab += 3;
  if( pOnlyIdx ){
    openStatTable(pParse, iDb, iStatCur, pOnlyIdx->zName, "idx");
  }else{
    openStatTable(pParse, iDb, iStatCur, pTab->zName, "tbl");
  }
  analyzeOneTable(pParse, pTab, pOnlyIdx, iStatCur,pParse->nMem+1,
                  pParse->nTab, 0);
  loadAnalysis(pParse, iDb);
}

/*
** Generate code for the ANALYZE command.  The parser calls this routine
** when it recognizes an ANALYZE command.
**

Changes to src/btree.c.

5314
5315
5316
5317
5318
5319
5320



















5321
5322
5323
5324
5325
5326
5327
int sqlite3BtreeEof(BtCursor *pCur){
  /* TODO: What if the cursor is in CURSOR_REQUIRESEEK but all table entries
  ** have been deleted? This API will need to change to return an error code
  ** as well as the boolean result value.
  */
  return (CURSOR_VALID!=pCur->eState);
}




















/*
** Advance the cursor to the next entry in the database.  If
** successful then set *pRes=0.  If the cursor
** was already pointing to the last entry in the database before
** this routine was called, then set *pRes=1.
**







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







5314
5315
5316
5317
5318
5319
5320
5321
5322
5323
5324
5325
5326
5327
5328
5329
5330
5331
5332
5333
5334
5335
5336
5337
5338
5339
5340
5341
5342
5343
5344
5345
5346
int sqlite3BtreeEof(BtCursor *pCur){
  /* TODO: What if the cursor is in CURSOR_REQUIRESEEK but all table entries
  ** have been deleted? This API will need to change to return an error code
  ** as well as the boolean result value.
  */
  return (CURSOR_VALID!=pCur->eState);
}

/*
** Return an estimate for the number of rows in the table that pCur is
** pointing to.  Return a negative number if no estimate is currently 
** available.
*/
i64 sqlite3BtreeRowCountEst(BtCursor *pCur){
  i64 n;
  u8 i;

  assert( cursorOwnsBtShared(pCur) );
  assert( sqlite3_mutex_held(pCur->pBtree->db->mutex) );
  if( pCur->eState!=CURSOR_VALID ) return -1;
  if( pCur->apPage[pCur->iPage-1]->leaf==0 ) return -1;
  for(n=1, i=0; i<pCur->iPage; i++){
    n *= pCur->apPage[i]->nCell;
  }
  return n;
}

/*
** Advance the cursor to the next entry in the database.  If
** successful then set *pRes=0.  If the cursor
** was already pointing to the last entry in the database before
** this routine was called, then set *pRes=1.
**

Changes to src/btree.h.

292
293
294
295
296
297
298

299
300
301
302
303
304
305
i64 sqlite3BtreeIntegerKey(BtCursor*);
int sqlite3BtreePayload(BtCursor*, u32 offset, u32 amt, void*);
const void *sqlite3BtreePayloadFetch(BtCursor*, u32 *pAmt);
u32 sqlite3BtreePayloadSize(BtCursor*);

char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*);
struct Pager *sqlite3BtreePager(Btree*);


#ifndef SQLITE_OMIT_INCRBLOB
int sqlite3BtreePayloadChecked(BtCursor*, u32 offset, u32 amt, void*);
int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*);
void sqlite3BtreeIncrblobCursor(BtCursor *);
#endif
void sqlite3BtreeClearCursor(BtCursor *);







>







292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
i64 sqlite3BtreeIntegerKey(BtCursor*);
int sqlite3BtreePayload(BtCursor*, u32 offset, u32 amt, void*);
const void *sqlite3BtreePayloadFetch(BtCursor*, u32 *pAmt);
u32 sqlite3BtreePayloadSize(BtCursor*);

char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*);
struct Pager *sqlite3BtreePager(Btree*);
i64 sqlite3BtreeRowCountEst(BtCursor*);

#ifndef SQLITE_OMIT_INCRBLOB
int sqlite3BtreePayloadChecked(BtCursor*, u32 offset, u32 amt, void*);
int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*);
void sqlite3BtreeIncrblobCursor(BtCursor *);
#endif
void sqlite3BtreeClearCursor(BtCursor *);

Changes to src/sqliteInt.h.

1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
#define TF_Ephemeral       0x0002    /* An ephemeral table */
#define TF_HasPrimaryKey   0x0004    /* Table has a primary key */
#define TF_Autoincrement   0x0008    /* Integer primary key is autoincrement */
#define TF_HasStat1        0x0010    /* nRowLogEst set from sqlite_stat1 */
#define TF_WithoutRowid    0x0020    /* No rowid.  PRIMARY KEY is the key */
#define TF_NoVisibleRowid  0x0040    /* No user-visible "rowid" column */
#define TF_OOOHidden       0x0080    /* Out-of-Order hidden columns */
#define TF_SizeChng        0x0100    /* nRowLogEst might be inaccurate */
#define TF_StatsUsed       0x0200    /* Query planner decisions affected by
                                     ** Index.aiRowLogEst[] values */

/*
** Test to see whether or not a table is a virtual table.  This is
** done as a macro so that it will be optimized out when virtual
** table support is omitted from the build.
*/







<
|







1883
1884
1885
1886
1887
1888
1889

1890
1891
1892
1893
1894
1895
1896
1897
#define TF_Ephemeral       0x0002    /* An ephemeral table */
#define TF_HasPrimaryKey   0x0004    /* Table has a primary key */
#define TF_Autoincrement   0x0008    /* Integer primary key is autoincrement */
#define TF_HasStat1        0x0010    /* nRowLogEst set from sqlite_stat1 */
#define TF_WithoutRowid    0x0020    /* No rowid.  PRIMARY KEY is the key */
#define TF_NoVisibleRowid  0x0040    /* No user-visible "rowid" column */
#define TF_OOOHidden       0x0080    /* Out-of-Order hidden columns */

#define TF_StatsUsed       0x0100    /* Query planner decisions affected by
                                     ** Index.aiRowLogEst[] values */

/*
** Test to see whether or not a table is a virtual table.  This is
** done as a macro so that it will be optimized out when virtual
** table support is omitted from the build.
*/

Changes to src/vdbe.c.

4842
4843
4844
4845
4846
4847
4848



























4849
4850
4851
4852
4853
4854
4855
      if( res ) goto jump_to_p2;
    }
  }else{
    assert( pOp->p2==0 );
  }
  break;
}





























/* Opcode: SorterSort P1 P2 * * *
**
** After all records have been inserted into the Sorter object
** identified by P1, invoke this opcode to actually do the sorting.
** Jump to P2 if there are no records to be sorted.







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







4842
4843
4844
4845
4846
4847
4848
4849
4850
4851
4852
4853
4854
4855
4856
4857
4858
4859
4860
4861
4862
4863
4864
4865
4866
4867
4868
4869
4870
4871
4872
4873
4874
4875
4876
4877
4878
4879
4880
4881
4882
      if( res ) goto jump_to_p2;
    }
  }else{
    assert( pOp->p2==0 );
  }
  break;
}

/* Opcode: IfSmaller P1 P2 P3 * *
**
** Estimate the number of rows in the table P1.  Jump to P2 if that
** estimate is less than approximately 2**(0.1*P3).
*/
case OP_IfSmaller: {        /* jump */
  VdbeCursor *pC;
  BtCursor *pCrsr;
  int res;
  i64 sz;

  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  pCrsr = pC->uc.pCursor;
  assert( pCrsr );
  rc = sqlite3BtreeFirst(pCrsr, &res);
  if( rc ) goto abort_due_to_error;
  if( res==0 ){
    sz = sqlite3BtreeRowCountEst(pCrsr);
    if( ALWAYS(sz>=0) && sqlite3LogEst((u64)sz)<pOp->p3 ) res = 1;
  }
  VdbeBranchTaken(res!=0,2);
  if( res ) goto jump_to_p2;
  break;
}


/* Opcode: SorterSort P1 P2 * * *
**
** After all records have been inserted into the Sorter object
** identified by P1, invoke this opcode to actually do the sorting.
** Jump to P2 if there are no records to be sorted.

Changes to test/autoanalyze1.test.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
..
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
# This file implements tests for the logic used to estimate when
# running ANALYZE would be beneficial.
#
# Note that this test uses some hard-coded bitmask values from sqliteInt.h.
# If any of the following constants changes:
#
#    define TF_HasStat1   0x0010
#    define TF_SizeChng   0x0100
#    define TF_StatsUsed  0x0200
#
# then some of the magic numbers in test results below might need to be
# adjusted.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
................................................................................
   ORDER BY idx;
  -- Verify that the TF_HasStat1 flag is clear on the table
  SELECT tbl, (flgs & 0x10)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {t1bc 0 t1d 0 t1 0}

# No use of stat1 recorded so far
do_execsql_test autoanalyze1-110 {
  SELECT (flgs & 0x0200)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}

# Access using a unique index does not set the TF_StatsUsed flag.
#
do_execsql_test autoanalyze1-200 {
  SELECT * FROM t1 WHERE a=55;
} {55 55 55 55}
do_execsql_test autoanalyze1-201 {
  SELECT (flgs & 0x0200)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}

do_execsql_test autoanalyze1-210 {
  SELECT * FROM t1 WHERE a IN (55,199,299);
} {55 55 55 55}
do_execsql_test autoanalyze1-211 {
  SELECT (flgs & 0x0200)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}

do_execsql_test autoanalyze1-220 {
  SELECT * FROM t1 WHERE (b,c)=(45,45);
} {45 45 45 45}
do_execsql_test autoanalyze1-221 {
  SELECT (flgs & 0x0200)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}

# Any use of the non-unique t1d index triggers the use of stats.
#
sqlite3 db test.db
do_execsql_test autoanalyze1-300 {
  SELECT * FROM t1 WHERE d=45;
} {45 45 45 45}
do_execsql_test autoanalyze1-301 {
  SELECT (flgs & 0x0200)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {1}

sqlite3 db test.db
do_execsql_test autoanalyze1-310 {
  SELECT * FROM t1 WHERE d=45 AND a=45;
} {45 45 45 45}
do_execsql_test autoanalyze1-311 {
  SELECT (flgs & 0x0200)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}  ;# The ROWID lookup short-circuits the d=45 constraint

sqlite3 db test.db
do_execsql_test autoanalyze1-320 {
  SELECT * FROM t1 WHERE d=45 AND a IN (45,46);
} {45 45 45 45}
do_execsql_test autoanalyze1-321 {
  SELECT (flgs & 0x0200)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {1}

# Any use of prefix of a unique index triggers the use of stats
#
sqlite3 db test.db
do_execsql_test autoanalyze1-400 {
  SELECT * FROM t1 WHERE b=45;
} {45 45 45 45}
do_execsql_test autoanalyze1-401 {
  SELECT (flgs & 0x0200)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {1}

# The TF_StatsUsed flag is reset when the database is reopened
#
sqlite3 db test.db
do_execsql_test autoanalyze1-500 {
  SELECT (flgs & 0x0200)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}

finish_test







<
|







 







|








|






|






|









|







|







|









|






|



12
13
14
15
16
17
18

19
20
21
22
23
24
25
26
..
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
# This file implements tests for the logic used to estimate when
# running ANALYZE would be beneficial.
#
# Note that this test uses some hard-coded bitmask values from sqliteInt.h.
# If any of the following constants changes:
#
#    define TF_HasStat1   0x0010

#    define TF_StatsUsed  0x0100
#
# then some of the magic numbers in test results below might need to be
# adjusted.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
................................................................................
   ORDER BY idx;
  -- Verify that the TF_HasStat1 flag is clear on the table
  SELECT tbl, (flgs & 0x10)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {t1bc 0 t1d 0 t1 0}

# No use of stat1 recorded so far
do_execsql_test autoanalyze1-110 {
  SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}

# Access using a unique index does not set the TF_StatsUsed flag.
#
do_execsql_test autoanalyze1-200 {
  SELECT * FROM t1 WHERE a=55;
} {55 55 55 55}
do_execsql_test autoanalyze1-201 {
  SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}

do_execsql_test autoanalyze1-210 {
  SELECT * FROM t1 WHERE a IN (55,199,299);
} {55 55 55 55}
do_execsql_test autoanalyze1-211 {
  SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}

do_execsql_test autoanalyze1-220 {
  SELECT * FROM t1 WHERE (b,c)=(45,45);
} {45 45 45 45}
do_execsql_test autoanalyze1-221 {
  SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}

# Any use of the non-unique t1d index triggers the use of stats.
#
sqlite3 db test.db
do_execsql_test autoanalyze1-300 {
  SELECT * FROM t1 WHERE d=45;
} {45 45 45 45}
do_execsql_test autoanalyze1-301 {
  SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {1}

sqlite3 db test.db
do_execsql_test autoanalyze1-310 {
  SELECT * FROM t1 WHERE d=45 AND a=45;
} {45 45 45 45}
do_execsql_test autoanalyze1-311 {
  SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}  ;# The ROWID lookup short-circuits the d=45 constraint

sqlite3 db test.db
do_execsql_test autoanalyze1-320 {
  SELECT * FROM t1 WHERE d=45 AND a IN (45,46);
} {45 45 45 45}
do_execsql_test autoanalyze1-321 {
  SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {1}

# Any use of prefix of a unique index triggers the use of stats
#
sqlite3 db test.db
do_execsql_test autoanalyze1-400 {
  SELECT * FROM t1 WHERE b=45;
} {45 45 45 45}
do_execsql_test autoanalyze1-401 {
  SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {1}

# The TF_StatsUsed flag is reset when the database is reopened
#
sqlite3 db test.db
do_execsql_test autoanalyze1-500 {
  SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL;
} {0}

finish_test