Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | 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. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | auto-analyze |
Files: | files | file ages | folders |
SHA1: |
882599a4a7ea92c9e7752e0745475508 |
User & Date: | drh 2017-02-18 02:19:02.183 |
Context
2017-02-18
| ||
02:42 | Fix errors in the table resize detection. (check-in: 4229caec0b user: drh tags: auto-analyze) | |
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: 882599a4a7 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: bfbdd07409 user: drh tags: auto-analyze) | |
Changes
Changes to src/analyze.c.
︙ | ︙ | |||
967 968 969 970 971 972 973 974 975 976 977 978 979 980 | 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 */ int regNewRowid = iMem++; /* Rowid for the inserted record */ int regStat4 = iMem++; /* Register to hold Stat4Accum object */ int regChng = iMem++; /* Index of changed index field */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int regRowid = iMem++; /* Rowid argument passed to stat_push() */ #endif int regTemp = iMem++; /* Temporary use register */ | > | 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 | 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 */ int regWorkDone = iMem++; /* Set to 1 if any work is done */ int regNewRowid = iMem++; /* Rowid for the inserted record */ int regStat4 = iMem++; /* Register to hold Stat4Accum object */ int regChng = iMem++; /* Index of changed index field */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int regRowid = iMem++; /* Rowid argument passed to stat_push() */ #endif int regTemp = iMem++; /* Temporary use register */ |
︙ | ︙ | |||
1014 1015 1016 1017 1018 1019 1020 | 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); | | | 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 | 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 */ |
︙ | ︙ | |||
1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 | 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); } | > > < < < < < < < < < < < < | 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 | 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); } sqlite3VdbeAddOp2(v, OP_Integer, 1, regWorkDone); VdbeComment((v, "work was done")); sqlite3VdbeJumpHere(v, addrSizeCk); } /* ** 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 |
︙ | ︙ | |||
1318 1319 1320 1321 1322 1323 1324 | break; } } return 1; } /* | | > > | < | > > > > | | | | | > > > | < | < < > > > > > > > < < > > > > | | < > > > > | > > | > > | < < | | | 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 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 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 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 | break; } } return 1; } /* ** Generate code that will do an analysis of an entire database, or ** all databases in the connection if iDbReq is negative. ** ** If onlyIfNeeded is true, then only run the analysis if SQLite thinks ** it is actually needed. */ void sqlite3AnalyzeDatabase( Parse *pParse, /* The parsing context */ int iDbReq, /* Which schema to analyze. -1 for all (except TEMP) */ int onlyIfNeeded /* Only do the analysis if needed, when true */ ){ sqlite3 *db = pParse->db; Schema *pSchema; HashElem *k; int iStatCur = 0; int iMem = 0; int iTab = 0; int iDb; /* Database currently being analyzed */ int iDbFirst, iDbLast; /* Range of databases to be analyzed */ int bStatTabs = 0; Vdbe *v = sqlite3GetVdbe(pParse); int nHit = 0; unsigned char aHit[SQLITE_MAX_ATTACHED+2]; if( v==0 ) return; if( iDbReq>=0 ){ iDbFirst = iDbLast = iDbReq; }else{ iDbFirst = 0; iDbLast = db->nDb-1; } for(iDb=iDbFirst; iDb<=iDbLast; iDb++, bStatTabs=0){ if( iDb==1 ) continue; /* Do not analyze the TEMP database */ pSchema = db->aDb[iDb].pSchema; assert( sqlite3SchemaMutexHeld(db, iDb, 0) ); for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){ Table *pTab = (Table*)sqliteHashData(k); LogEst szThreshold = 0; if( !onlyIfNeeded || analyzeNeeded(pTab, &szThreshold) ){ if( iMem==0 ){ iStatCur = pParse->nTab; pParse->nTab += 3; iMem = pParse->nMem+1; iTab = pParse->nTab; sqlite3VdbeAddOp2(v, OP_Integer, 0, iMem); } if( !bStatTabs ){ aHit[nHit++] = iDb; sqlite3BeginWriteOperation(pParse, 0, iDb); openStatTable(pParse, iDb, iStatCur, 0, 0); bStatTabs = 1; } analyzeOneTable(pParse, pTab, 0, iStatCur, iMem, iTab, szThreshold); } } } if( iMem ){ int addrTop = sqlite3VdbeAddOp1(v, OP_IfNot, iMem); VdbeCoverage(v); for(iDb=0; iDb<nHit; iDb++){ sqlite3VdbeAddOp1(v, OP_LoadAnalysis, aHit[iDb]); } sqlite3VdbeAddOp0(v, OP_Expire); sqlite3VdbeJumpHere(v, addrTop); } } /* ** 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. */ static void analyzeTable(Parse *pParse, Table *pTab, Index *pOnlyIdx){ int iDb; int iStatCur; Vdbe *v; assert( pTab!=0 ); assert( sqlite3BtreeHoldsAllMutexes(pParse->db) ); iDb = sqlite3SchemaToIndex(pParse->db, pTab->pSchema); sqlite3BeginWriteOperation(pParse, 0, iDb); 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); v = sqlite3GetVdbe(pParse); if( v ){ sqlite3VdbeAddOp1(v, OP_LoadAnalysis, iDb); sqlite3VdbeAddOp0(v, OP_Expire); } } /* ** Generate code for the ANALYZE command. The parser calls this routine ** when it recognizes an ANALYZE command. ** ** 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){ sqlite3 *db = pParse->db; int iDb; char *z, *zDb; Table *pTab; Index *pIdx; Token *pTableName; /* 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 */ sqlite3AnalyzeDatabase(pParse, -1, 0); }else if( pName2->n==0 ){ /* Form 2: Analyze the database or table named */ iDb = sqlite3FindDb(db, pName1); if( iDb>=0 ){ sqlite3AnalyzeDatabase(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); |
︙ | ︙ | |||
1463 1464 1465 1466 1467 1468 1469 | }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){ analyzeTable(pParse, pTab, 0); } sqlite3DbFree(db, z); } } } | < < < < | 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 | }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){ analyzeTable(pParse, pTab, 0); } sqlite3DbFree(db, z); } } } } /* ** 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);} cmd ::= ANALYZE nm(X) dbnm(Y). {sqlite3Analyze(pParse, &X, &Y);} %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.
︙ | ︙ | |||
1828 1829 1830 1831 1832 1833 1834 | break; } /* ** PRAGMA analyze_as_needed */ case PragTyp_ANALYZE_AS_NEEDED: { | | | 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 | break; } /* ** PRAGMA analyze_as_needed */ case PragTyp_ANALYZE_AS_NEEDED: { sqlite3AnalyzeDatabase(pParse, zDb ? iDb : -1, 1); break; } /* ** PRAGMA busy_timeout ** PRAGMA busy_timeout = N ** |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
4023 4024 4025 4026 4027 4028 4029 | 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*); | | > | 4023 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*); void sqlite3AnalyzeDatabase(Parse*,int,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); |
︙ | ︙ |