Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the "PRAGMA analyze_as_needed" command. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | auto-analyze |
Files: | files | file ages | folders |
SHA1: |
e93db2373127d31d33ec46ef918fa938 |
User & Date: | drh 2017-02-17 16:26:34.780 |
Context
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: bfbdd07409 user: drh tags: auto-analyze) | |
16:26 | Add the "PRAGMA analyze_as_needed" command. (check-in: e93db23731 user: drh tags: auto-analyze) | |
15:26 | Set the TF_StatsUsed flag on tables when the query planner outcome is affected by the sqlite_stat1 data. Also, change the column names of the "PRAGMA stats" command so that they are not keywords. (check-in: fb2b8ae831 user: drh tags: auto-analyze) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
1282 1283 1284 1285 1286 1287 1288 | Vdbe *v = sqlite3GetVdbe(pParse); if( v ){ sqlite3VdbeAddOp1(v, OP_LoadAnalysis, iDb); } } /* | > > > > > > > > > > > > > | > > > | | > > > > > > > > > > > > > > > > > > > | | | | | | < < < > | > > | > | > > | 1282 1283 1284 1285 1286 1287 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 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 | 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. */ static int analyzeDatabase(Parse *pParse, int iDbReq, int onlyIfNeeded){ sqlite3 *db = pParse->db; Schema *pSchema; HashElem *k; int iStatCur; int iMem; int iTab; int iDb; /* Database currently being analyzed */ int iDbFirst, iDbLast; /* Range of databases to be analyzed */ int cnt; /* Number of tables analyzed in a single database */ int allCnt = 0; /* Number of tables analyzed across all databases */ if( iDbReq>=0 ){ iDbFirst = iDbLast = iDbReq; }else{ iDbFirst = 0; iDbLast = db->nDb-1; } 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; } /* ** Generate code that will do an analysis of a single table in ** a database. If pOnlyIdx is not NULL then it is a single index ** in pTab that should be analyzed. */ |
︙ | ︙ | |||
1341 1342 1343 1344 1345 1346 1347 1348 | ** ANALYZE -- 1 ** ANALYZE <database> -- 2 ** ANALYZE ?<database>.?<tablename> -- 3 ** ** Form 1 causes all indices in all attached databases to be analyzed. ** Form 2 analyzes all indices the single database named. ** Form 3 analyzes all indices associated with the named table. */ | > > > > > | < > < < | < | | 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 | ** ANALYZE -- 1 ** ANALYZE <database> -- 2 ** ANALYZE ?<database>.?<tablename> -- 3 ** ** Form 1 causes all indices in all attached databases to be analyzed. ** Form 2 analyzes all indices the single database named. ** Form 3 analyzes all indices associated with the named table. ** ** If pName1 and pName2 are both NULL and if the ifNeeded flag is true, ** this routine computes an conditional ANALYZE on only those tables ** are believed to be in need of analysis. The conditional analysis ** might well be a no-op. */ void sqlite3Analyze(Parse *pParse, Token *pName1, Token *pName2, int ifNeeded){ sqlite3 *db = pParse->db; int iDb; char *z, *zDb; Table *pTab; Index *pIdx; Token *pTableName; Vdbe *v; int needExpire = 1; /* Read the database schema. If an error occurs, leave an error message ** and code in pParse and return NULL. */ assert( sqlite3BtreeHoldsAllMutexes(pParse->db) ); if( SQLITE_OK!=sqlite3ReadSchema(pParse) ){ return; } assert( pName2!=0 || pName1==0 ); if( pName1==0 ){ /* Form 1: Analyze everything */ needExpire = analyzeDatabase(pParse, -1, ifNeeded) || ifNeeded==0; }else if( pName2->n==0 ){ /* Form 2: Analyze the database or table named */ iDb = sqlite3FindDb(db, pName1); if( iDb>=0 ){ analyzeDatabase(pParse, iDb, 0); }else{ z = sqlite3NameFromToken(db, pName1); if( z ){ if( (pIdx = sqlite3FindIndex(db, z, 0))!=0 ){ analyzeTable(pParse, pIdx->pTable, pIdx); }else if( (pTab = sqlite3LocateTable(pParse, 0, z, 0))!=0 ){ analyzeTable(pParse, pTab, 0); |
︙ | ︙ | |||
1398 1399 1400 1401 1402 1403 1404 | }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){ analyzeTable(pParse, pTab, 0); } sqlite3DbFree(db, z); } } } | > | | > | 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 | }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){ analyzeTable(pParse, pTab, 0); } sqlite3DbFree(db, z); } } } if( needExpire ){ v = sqlite3GetVdbe(pParse); if( v ) sqlite3VdbeAddOp0(v, OP_Expire); } } /* ** Used to pass information from the analyzer reader through to the ** callback routine. */ typedef struct analysisInfo analysisInfo; |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
1508 1509 1510 1511 1512 1513 1514 | %ifndef SQLITE_OMIT_REINDEX cmd ::= REINDEX. {sqlite3Reindex(pParse, 0, 0);} cmd ::= REINDEX nm(X) dbnm(Y). {sqlite3Reindex(pParse, &X, &Y);} %endif SQLITE_OMIT_REINDEX /////////////////////////////////// ANALYZE /////////////////////////////////// %ifndef SQLITE_OMIT_ANALYZE | | | | 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 | %ifndef SQLITE_OMIT_REINDEX cmd ::= REINDEX. {sqlite3Reindex(pParse, 0, 0);} cmd ::= REINDEX nm(X) dbnm(Y). {sqlite3Reindex(pParse, &X, &Y);} %endif SQLITE_OMIT_REINDEX /////////////////////////////////// ANALYZE /////////////////////////////////// %ifndef SQLITE_OMIT_ANALYZE cmd ::= ANALYZE. {sqlite3Analyze(pParse, 0, 0, 0);} cmd ::= ANALYZE nm(X) dbnm(Y). {sqlite3Analyze(pParse, &X, &Y, 0);} %endif //////////////////////// ALTER TABLE table ... //////////////////////////////// %ifndef SQLITE_OMIT_ALTERTABLE cmd ::= ALTER TABLE fullname(X) RENAME TO nm(Z). { sqlite3AlterRenameTable(pParse,X,&Z); } |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 | ** connection on which it is invoked to free up as much memory as it ** can, by calling sqlite3_db_release_memory(). */ case PragTyp_SHRINK_MEMORY: { sqlite3_db_release_memory(db); break; } /* ** PRAGMA busy_timeout ** PRAGMA busy_timeout = N ** ** Call sqlite3_busy_timeout(db, N). Return the current timeout value ** if one is set. If no busy handler or a different busy handler is set | > > > > > > > > | 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 | ** connection on which it is invoked to free up as much memory as it ** can, by calling sqlite3_db_release_memory(). */ case PragTyp_SHRINK_MEMORY: { sqlite3_db_release_memory(db); break; } /* ** PRAGMA analyze_as_needed */ case PragTyp_ANALYZE_AS_NEEDED: { sqlite3Analyze(pParse, 0, 0, 1); break; } /* ** PRAGMA busy_timeout ** PRAGMA busy_timeout = N ** ** Call sqlite3_busy_timeout(db, N). Return the current timeout value ** if one is set. If no busy handler or a different busy handler is set |
︙ | ︙ |
Changes to src/pragma.h.
1 2 3 4 5 6 7 | /* DO NOT EDIT! ** This file is automatically generated by the script at ** ../tool/mkpragmatab.tcl. To update the set of pragmas, edit ** that script and rerun it. */ /* The various pragma types */ | > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | /* DO NOT EDIT! ** This file is automatically generated by the script at ** ../tool/mkpragmatab.tcl. To update the set of pragmas, edit ** that script and rerun it. */ /* The various pragma types */ #define PragTyp_ANALYZE_AS_NEEDED 0 #define PragTyp_HEADER_VALUE 1 #define PragTyp_AUTO_VACUUM 2 #define PragTyp_FLAG 3 #define PragTyp_BUSY_TIMEOUT 4 #define PragTyp_CACHE_SIZE 5 #define PragTyp_CACHE_SPILL 6 #define PragTyp_CASE_SENSITIVE_LIKE 7 #define PragTyp_COLLATION_LIST 8 #define PragTyp_COMPILE_OPTIONS 9 #define PragTyp_DATA_STORE_DIRECTORY 10 #define PragTyp_DATABASE_LIST 11 #define PragTyp_DEFAULT_CACHE_SIZE 12 #define PragTyp_ENCODING 13 #define PragTyp_FOREIGN_KEY_CHECK 14 #define PragTyp_FOREIGN_KEY_LIST 15 #define PragTyp_INCREMENTAL_VACUUM 16 #define PragTyp_INDEX_INFO 17 #define PragTyp_INDEX_LIST 18 #define PragTyp_INTEGRITY_CHECK 19 #define PragTyp_JOURNAL_MODE 20 #define PragTyp_JOURNAL_SIZE_LIMIT 21 #define PragTyp_LOCK_PROXY_FILE 22 #define PragTyp_LOCKING_MODE 23 #define PragTyp_PAGE_COUNT 24 #define PragTyp_MMAP_SIZE 25 #define PragTyp_PAGE_SIZE 26 #define PragTyp_SECURE_DELETE 27 #define PragTyp_SHRINK_MEMORY 28 #define PragTyp_SOFT_HEAP_LIMIT 29 #define PragTyp_SYNCHRONOUS 30 #define PragTyp_TABLE_INFO 31 #define PragTyp_TEMP_STORE 32 #define PragTyp_TEMP_STORE_DIRECTORY 33 #define PragTyp_THREADS 34 #define PragTyp_WAL_AUTOCHECKPOINT 35 #define PragTyp_WAL_CHECKPOINT 36 #define PragTyp_ACTIVATE_EXTENSIONS 37 #define PragTyp_HEXKEY 38 #define PragTyp_KEY 39 #define PragTyp_REKEY 40 #define PragTyp_LOCK_STATUS 41 #define PragTyp_PARSER_TRACE 42 #define PragTyp_STATS 43 /* Property flags associated with various pragma. */ #define PragFlg_NeedSchema 0x01 /* Force schema load before running */ #define PragFlg_NoColumns 0x02 /* OP_ResultRow called with zero columns */ #define PragFlg_NoColumns1 0x04 /* zero columns if RHS argument is present */ #define PragFlg_ReadOnly 0x08 /* Read-only HEADER_VALUE */ #define PragFlg_Result0 0x10 /* Acts as query when no argument */ |
︙ | ︙ | |||
128 129 130 131 132 133 134 135 136 137 138 139 140 141 | #if defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD) {/* zName: */ "activate_extensions", /* ePragTyp: */ PragTyp_ACTIVATE_EXTENSIONS, /* ePragFlg: */ 0, /* ColNames: */ 0, 0, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS) {/* zName: */ "application_id", /* ePragTyp: */ PragTyp_HEADER_VALUE, /* ePragFlg: */ PragFlg_NoColumns1|PragFlg_Result0, /* ColNames: */ 0, 0, /* iArg: */ BTREE_APPLICATION_ID }, #endif | > > > > > | 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 | #if defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD) {/* zName: */ "activate_extensions", /* ePragTyp: */ PragTyp_ACTIVATE_EXTENSIONS, /* ePragFlg: */ 0, /* ColNames: */ 0, 0, /* iArg: */ 0 }, #endif {/* zName: */ "analyze_as_needed", /* ePragTyp: */ PragTyp_ANALYZE_AS_NEEDED, /* ePragFlg: */ PragFlg_NoColumns, /* ColNames: */ 0, 0, /* iArg: */ 0 }, #if !defined(SQLITE_OMIT_SCHEMA_VERSION_PRAGMAS) {/* zName: */ "application_id", /* ePragTyp: */ PragTyp_HEADER_VALUE, /* ePragFlg: */ PragFlg_NoColumns1|PragFlg_Result0, /* ColNames: */ 0, 0, /* iArg: */ BTREE_APPLICATION_ID }, #endif |
︙ | ︙ | |||
601 602 603 604 605 606 607 | {/* zName: */ "writable_schema", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ SQLITE_WriteSchema|SQLITE_RecoveryMode }, #endif }; | | | 607 608 609 610 611 612 613 614 | {/* zName: */ "writable_schema", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ SQLITE_WriteSchema|SQLITE_RecoveryMode }, #endif }; /* Number of pragmas: 60 on by default, 74 total. */ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
4024 4025 4026 4027 4028 4029 4030 | void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*); int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*); void sqlite3ColumnDefault(Vdbe *, Table *, int, int); void sqlite3AlterFinishAddColumn(Parse *, Token *); void sqlite3AlterBeginAddColumn(Parse *, SrcList *); CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*); char sqlite3AffinityType(const char*, u8*); | | | 4024 4025 4026 4027 4028 4029 4030 4031 4032 4033 4034 4035 4036 4037 4038 | void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*); int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*); void sqlite3ColumnDefault(Vdbe *, Table *, int, int); void sqlite3AlterFinishAddColumn(Parse *, Token *); void sqlite3AlterBeginAddColumn(Parse *, SrcList *); CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*); char sqlite3AffinityType(const char*, u8*); void sqlite3Analyze(Parse*, Token*, Token*, int); int sqlite3InvokeBusyHandler(BusyHandler*); int sqlite3FindDb(sqlite3*, Token*); int sqlite3FindDbName(sqlite3 *, const char *); int sqlite3AnalysisLoad(sqlite3*,int iDB); void sqlite3DeleteIndexSamples(sqlite3*,Index*); void sqlite3DefaultRowEst(Index*); void sqlite3RegisterLikeFunctions(sqlite3*, int); |
︙ | ︙ |
Changes to tool/mkpragmatab.tcl.
︙ | ︙ | |||
357 358 359 360 361 362 363 364 365 366 367 368 369 370 | IF: defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD) NAME: soft_heap_limit FLAG: Result0 NAME: threads FLAG: Result0 } # Open the output file # set destfile "[file dir [file dir [file normal $argv0]]]/src/pragma.h" puts "Overwriting $destfile with new pragma table..." set fd [open $destfile wb] | > > > | 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 | IF: defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD) NAME: soft_heap_limit FLAG: Result0 NAME: threads FLAG: Result0 NAME: analyze_as_needed FLAG: NoColumns } # Open the output file # set destfile "[file dir [file dir [file normal $argv0]]]/src/pragma.h" puts "Overwriting $destfile with new pragma table..." set fd [open $destfile wb] |
︙ | ︙ |