Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch better-pragma-optimize Excluding Merge-Ins
This is equivalent to a diff from f18b2524 to 6c5a0c85
2024-02-20
| ||
12:48 | Enhancements to PRAGMA optimize and ANALYZE. Add the 0x10000 flag to PRAGMA optimize. ANALYZE now records zero-size partial indexes in the sqlite_stat1 table. PRAGMA optimize looks for both growth and shrinkage in table sizes, and uses tighter bounds (10x rather than 25x) to trigger a re-analyze. PRAGMA optimize automatically uses are reasonable analysis_limit to prevent excessive runtimes. (check-in: 63ef234e user: drh tags: trunk) | |
12:14 | Another simplification of the PRAGMA optimize logic for improved coverage. (Closed-Leaf check-in: 6c5a0c85 user: drh tags: better-pragma-optimize) | |
2024-02-19
| ||
23:58 | Minor fixes. (check-in: ed5afebc user: drh tags: better-pragma-optimize) | |
18:55 | Merge trunk fixes into the better-pragma-optimize branch. (check-in: 2cf78a5b user: drh tags: better-pragma-optimize) | |
12:20 | Extend the strerror_r() result type discrepancy check in os_unix.c to include the Android NDK, as reported in forum post 3f13857fa4062301. The NDK's strerror_r() signature is version-dependent, whereas this change assumes int return (POSIX semantic) across all versions. (check-in: f18b2524 user: stephan tags: trunk) | |
2024-02-17
| ||
03:32 | Fix rounding in zero-precision %f and %g printf conversions. Forum post 393708f4a8. This bug was introduced by check-in [32befb224b254639] and first appeared in version 3.43.0. (check-in: 7fca1bc4 user: drh tags: trunk) | |
Changes to ext/session/sessionstat1.test.
︙ | ︙ | |||
88 89 90 91 92 93 94 | ) INSERT INTO t1 SELECT i, i%8, i%2 FROM s; ANALYZE; } } {} do_execsql_test -db db2 2.2 { | | | | 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 | ) INSERT INTO t1 SELECT i, i%8, i%2 FROM s; ANALYZE; } } {} do_execsql_test -db db2 2.2 { SELECT * FROM sqlite_stat1 ORDER BY tbl, idx } { t1 sqlite_autoindex_t1_1 {32 1} t1 t1b {32 4} t1 t1c {32 16} } do_test 2.3 { do_then_apply_sql -ignorenoop { DROP INDEX t1c } } {} do_execsql_test -db db2 2.4 { SELECT * FROM sqlite_stat1 ORDER BY tbl, idx; } { t1 sqlite_autoindex_t1_1 {32 1} t1 t1b {32 4} } do_test 2.3 { do_then_apply_sql -ignorenoop { DROP TABLE t1 } |
︙ | ︙ |
Changes to src/analyze.c.
︙ | ︙ | |||
868 869 870 871 872 873 874 | p->nSkipAhead ? (u64)p->nEst : (u64)p->nRow); for(i=0; i<p->nKeyCol; i++){ u64 nDistinct = p->current.anDLt[i] + 1; u64 iVal = (p->nRow + nDistinct - 1) / nDistinct; if( iVal==2 && p->nRow*10 <= nDistinct*11 ) iVal = 1; sqlite3_str_appendf(&sStat, " %llu", iVal); #ifdef SQLITE_ENABLE_STAT4 | | | 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 | p->nSkipAhead ? (u64)p->nEst : (u64)p->nRow); for(i=0; i<p->nKeyCol; i++){ u64 nDistinct = p->current.anDLt[i] + 1; u64 iVal = (p->nRow + nDistinct - 1) / nDistinct; if( iVal==2 && p->nRow*10 <= nDistinct*11 ) iVal = 1; sqlite3_str_appendf(&sStat, " %llu", iVal); #ifdef SQLITE_ENABLE_STAT4 assert( p->current.anEq[i] || p->nRow==0 ); #endif } sqlite3ResultStrAccum(context, &sStat); } #ifdef SQLITE_ENABLE_STAT4 else if( eCall==STAT_GET_ROWID ){ if( p->iGet<0 ){ |
︙ | ︙ | |||
1053 1054 1055 1056 1057 1058 1059 | 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" */ | | | 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 | 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 addrGotoEnd; /* Address of "OP_Rewind iIdxCur" */ int addrNextRow; /* Address of "next_row:" */ const char *zIdxName; /* Name of the index */ int nColTest; /* Number of columns to test for changes */ if( pOnlyIdx && pOnlyIdx!=pIdx ) continue; if( pIdx->pPartIdxWhere==0 ) needTableCnt = 0; if( !HasRowid(pTab) && IsPrimaryKeyIndex(pIdx) ){ |
︙ | ︙ | |||
1077 1078 1079 1080 1081 1082 1083 1084 | /* Populate the register containing the index name. */ sqlite3VdbeLoadString(v, regIdxname, zIdxName); VdbeComment((v, "Analysis for %s.%s", pTab->zName, zIdxName)); /* ** Pseudo-code for loop that calls stat_push(): ** ** Rewind csr | > | > > | > > | 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 | /* Populate the register containing the index name. */ sqlite3VdbeLoadString(v, regIdxname, zIdxName); VdbeComment((v, "Analysis for %s.%s", pTab->zName, zIdxName)); /* ** Pseudo-code for loop that calls stat_push(): ** ** regChng = 0 ** Rewind csr ** if eof(csr){ ** stat_init() with count = 0; ** goto end_of_scan; ** } ** count() ** stat_init() ** goto chng_addr_0; ** ** next_row: ** regChng = 0 ** if( idx(0) != regPrev(0) ) goto chng_addr_0 ** regChng = 1 ** if( idx(1) != regPrev(1) ) goto chng_addr_1 |
︙ | ︙ | |||
1118 1119 1120 1121 1122 1123 1124 | /* Open a read-only cursor on the index being analyzed. */ assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) ); sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb); sqlite3VdbeSetP4KeyInfo(pParse, pIdx); VdbeComment((v, "%s", pIdx->zName)); | | | > > > > > > > > > > > > > > | < < < < < < < < < < < | < | < > > < < < < < < < < | 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 | /* Open a read-only cursor on the index being analyzed. */ assert( iDb==sqlite3SchemaToIndex(db, pIdx->pSchema) ); sqlite3VdbeAddOp3(v, OP_OpenRead, iIdxCur, pIdx->tnum, iDb); sqlite3VdbeSetP4KeyInfo(pParse, pIdx); VdbeComment((v, "%s", pIdx->zName)); /* Implementation of the following: ** ** regChng = 0 ** Rewind csr ** if eof(csr){ ** stat_init() with count = 0; ** goto end_of_scan; ** } ** count() ** stat_init() ** goto chng_addr_0; */ assert( regTemp2==regStat+4 ); sqlite3VdbeAddOp2(v, OP_Integer, db->nAnalysisLimit, regTemp2); /* Arguments to stat_init(): ** (1) the number of columns in the index including the rowid ** (or for a WITHOUT ROWID table, the number of PK columns), ** (2) the number of columns in the key without the rowid/pk ** (3) estimated number of rows in the index. */ sqlite3VdbeAddOp2(v, OP_Integer, nCol, regStat+1); assert( regRowid==regStat+2 ); sqlite3VdbeAddOp2(v, OP_Integer, pIdx->nKeyCol, regRowid); sqlite3VdbeAddOp3(v, OP_Count, iIdxCur, regTemp, OptimizationDisabled(db, SQLITE_Stat4)); sqlite3VdbeAddFunctionCall(pParse, 0, regStat+1, regStat, 4, &statInitFuncdef, 0); addrGotoEnd = sqlite3VdbeAddOp1(v, OP_Rewind, iIdxCur); VdbeCoverage(v); sqlite3VdbeAddOp2(v, OP_Integer, 0, regChng); addrNextRow = sqlite3VdbeCurrentAddr(v); if( nColTest>0 ){ int endDistinctTest = sqlite3VdbeMakeLabel(pParse); int *aGotoChng; /* Array of jump instruction addresses */ aGotoChng = sqlite3DbMallocRawNN(db, sizeof(int)*nColTest); |
︙ | ︙ | |||
1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 | sqlite3VdbeJumpHere(v, j3); }else{ sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, addrNextRow); VdbeCoverage(v); } } /* Add the entry to the stat1 table. */ callStatGet(pParse, regStat, STAT_GET_STAT1, regStat1); 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); #ifdef SQLITE_ENABLE_PREUPDATE_HOOK sqlite3VdbeChangeP4(v, -1, (char*)pStat1, P4_TABLE); | > > > > > > | 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 | sqlite3VdbeJumpHere(v, j3); }else{ sqlite3VdbeAddOp2(v, OP_Next, iIdxCur, addrNextRow); VdbeCoverage(v); } } /* Add the entry to the stat1 table. */ if( pIdx->pPartIdxWhere ){ /* Partial indexes might get a zero-entry in sqlite_stat1. But ** an empty table is omitted from sqlite_stat1. */ sqlite3VdbeJumpHere(v, addrGotoEnd); addrGotoEnd = 0; } callStatGet(pParse, regStat, STAT_GET_STAT1, regStat1); 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); #ifdef SQLITE_ENABLE_PREUPDATE_HOOK sqlite3VdbeChangeP4(v, -1, (char*)pStat1, P4_TABLE); |
︙ | ︙ | |||
1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 | int regDLt = regStat1+2; int regSample = regStat1+3; int regCol = regStat1+4; int regSampleRowid = regCol + nCol; int addrNext; int addrIsNull; u8 seekOp = HasRowid(pTab) ? OP_NotExists : OP_NotFound; if( doOnce ){ int mxCol = nCol; Index *pX; /* Compute the maximum number of columns in any index */ for(pX=pTab->pIndex; pX; pX=pX->pNext){ | > > > > > > | 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 | int regDLt = regStat1+2; int regSample = regStat1+3; int regCol = regStat1+4; int regSampleRowid = regCol + nCol; int addrNext; int addrIsNull; u8 seekOp = HasRowid(pTab) ? OP_NotExists : OP_NotFound; /* No STAT4 data is generated if the number of rows is zero */ if( addrGotoEnd==0 ){ sqlite3VdbeAddOp2(v, OP_Cast, regStat1, SQLITE_AFF_INTEGER); addrGotoEnd = sqlite3VdbeAddOp1(v, OP_IfNot, regStat1); } if( doOnce ){ int mxCol = nCol; Index *pX; /* Compute the maximum number of columns in any index */ for(pX=pTab->pIndex; pX; pX=pX->pNext){ |
︙ | ︙ | |||
1334 1335 1336 1337 1338 1339 1340 | sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regTemp, regNewRowid); sqlite3VdbeAddOp2(v, OP_Goto, 1, addrNext); /* P1==1 for end-of-loop */ sqlite3VdbeJumpHere(v, addrIsNull); } #endif /* SQLITE_ENABLE_STAT4 */ /* End of analysis */ | | | 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 | sqlite3VdbeAddOp3(v, OP_Insert, iStatCur+1, regTemp, regNewRowid); sqlite3VdbeAddOp2(v, OP_Goto, 1, addrNext); /* P1==1 for end-of-loop */ sqlite3VdbeJumpHere(v, addrIsNull); } #endif /* SQLITE_ENABLE_STAT4 */ /* End of analysis */ if( addrGotoEnd ) sqlite3VdbeJumpHere(v, addrGotoEnd); } /* Create a single sqlite_stat1 entry containing NULL as the index ** name and the row count as the content. */ if( pOnlyIdx==0 && needTableCnt ){ |
︙ | ︙ |
Changes to src/btree.c.
︙ | ︙ | |||
6178 6179 6180 6181 6182 6183 6184 | i64 sqlite3BtreeRowCountEst(BtCursor *pCur){ i64 n; u8 i; assert( cursorOwnsBtShared(pCur) ); assert( sqlite3_mutex_held(pCur->pBtree->db->mutex) ); | | > | < | | 6178 6179 6180 6181 6182 6183 6184 6185 6186 6187 6188 6189 6190 6191 6192 6193 6194 6195 | i64 sqlite3BtreeRowCountEst(BtCursor *pCur){ i64 n; u8 i; assert( cursorOwnsBtShared(pCur) ); assert( sqlite3_mutex_held(pCur->pBtree->db->mutex) ); /* Currently this interface is only called by the OP_IfSizeBetween ** opcode and the OP_Count opcode with P3=1. In either case, ** the cursor will always be valid unless the btree is empty. */ if( pCur->eState!=CURSOR_VALID ) return 0; if( NEVER(pCur->pPage->leaf==0) ) return -1; n = pCur->pPage->nCell; for(i=0; i<pCur->iPage; i++){ n *= pCur->apPage[i]->nCell; } return n; |
︙ | ︙ |
Changes to src/build.c.
︙ | ︙ | |||
2919 2920 2921 2922 2923 2924 2925 | /* Reparse everything to update our internal data structures */ sqlite3VdbeAddParseSchemaOp(v, iDb, sqlite3MPrintf(db, "tbl_name='%q' AND type!='trigger'", p->zName),0); /* Test for cycles in generated columns and illegal expressions ** in CHECK constraints and in DEFAULT clauses. */ if( p->tabFlags & TF_HasGenerated ){ | | | | 2919 2920 2921 2922 2923 2924 2925 2926 2927 2928 2929 2930 2931 2932 2933 2934 2935 2936 2937 | /* Reparse everything to update our internal data structures */ sqlite3VdbeAddParseSchemaOp(v, iDb, sqlite3MPrintf(db, "tbl_name='%q' AND type!='trigger'", p->zName),0); /* Test for cycles in generated columns and illegal expressions ** in CHECK constraints and in DEFAULT clauses. */ if( p->tabFlags & TF_HasGenerated ){ sqlite3VdbeAddOp4(v, OP_SqlExec, 0x0001, 0, 0, sqlite3MPrintf(db, "SELECT*FROM\"%w\".\"%w\"", db->aDb[iDb].zDbSName, p->zName), P4_DYNAMIC); } sqlite3VdbeAddOp4(v, OP_SqlExec, 0x0001, 0, 0, sqlite3MPrintf(db, "PRAGMA \"%w\".integrity_check(%Q)", db->aDb[iDb].zDbSName, p->zName), P4_DYNAMIC); } /* Add the table to the in-memory representation of the database. */ if( db->init.busy ){ |
︙ | ︙ |
Changes to src/pragma.c.
︙ | ︙ | |||
26 27 28 29 30 31 32 33 34 35 36 37 38 39 | ** that includes the PragType_XXXX macro definitions and the aPragmaName[] ** object. This ensures that the aPragmaName[] table is arranged in ** lexicographical order to facility a binary search of the pragma name. ** Do not edit pragma.h directly. Edit and rerun the script in at ** ../tool/mkpragmatab.tcl. */ #include "pragma.h" /* ** Interpret the given string as a safety level. Return 0 for OFF, ** 1 for ON or NORMAL, 2 for FULL, and 3 for EXTRA. Return 1 for an empty or ** unrecognized string argument. The FULL and EXTRA option is disallowed ** if the omitFull parameter it 1. ** ** Note that the values returned are one less that the values that | > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 59 60 61 62 63 64 65 66 67 | ** that includes the PragType_XXXX macro definitions and the aPragmaName[] ** object. This ensures that the aPragmaName[] table is arranged in ** lexicographical order to facility a binary search of the pragma name. ** Do not edit pragma.h directly. Edit and rerun the script in at ** ../tool/mkpragmatab.tcl. */ #include "pragma.h" /* ** When the 0x10 bit of PRAGMA optimize is set, any ANALYZE commands ** will be run with an analysis_limit set to the lessor of the value of ** the following macro or to the actual analysis_limit if it is non-zero, ** in order to prevent PRAGMA optimize from running for too long. ** ** The value of 2000 is chosen emperically so that the worst-case run-time ** for PRAGMA optimize does not exceed 100 milliseconds against a variety ** of test databases on a RaspberryPI-4 compiled using -Os and without ** -DSQLITE_DEBUG. Of course, your mileage may vary. For the purpose of ** his paragraph, "worst-case" means that ANALYZE ends up being ** run on every table in the database. The worst case typically only ** happens if PRAGMA optimize is run on a database file for which ANALYZE ** has not been previously run and the 0x10000 flag is included so that ** all tables are analyzed. The usual case for PRAGMA optimize is that ** no ANALYZE commands will be run at all, or if any ANALYZE happens it ** will be against a single table, so that expected timing for PRAGMA ** optimize on a PI-4 is more like 1 millisecond or less with the 0x10000 ** flag or less than 100 microseconds without the 0x10000 flag. ** ** An analysis limit of 2000 is almost always sufficient for the query ** planner to fully characterize an index. The additional accuracy from ** a larger analysis is not usually helpful. */ #ifndef SQLITE_DEFAULT_OPTIMIZE_LIMIT # define SQLITE_DEFAULT_OPTIMIZE_LIMIT 2000 #endif /* ** Interpret the given string as a safety level. Return 0 for OFF, ** 1 for ON or NORMAL, 2 for FULL, and 3 for EXTRA. Return 1 for an empty or ** unrecognized string argument. The FULL and EXTRA option is disallowed ** if the omitFull parameter it 1. ** ** Note that the values returned are one less that the values that |
︙ | ︙ | |||
2379 2380 2381 2382 2383 2384 2385 | ** ** The details of optimizations performed by this pragma are expected ** to change and improve over time. Applications should anticipate that ** this pragma will perform new optimizations in future releases. ** ** The optional argument is a bitmask of optimizations to perform: ** | | | | | | | | | > > > > | | | > > > > | > | | < | | > > > > > > > > | | | > | > | | > > | > > > > > > > > > > > > > > > | > | | | | > > > | > > > | > > | > > | > > > > > > > > > > > > | | > > > > > > > | > > > | > > > > > > > > | | | > > > > > > > > > > > > > > > > | 2407 2408 2409 2410 2411 2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567 2568 2569 2570 2571 2572 2573 2574 2575 2576 2577 2578 2579 2580 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 | ** ** The details of optimizations performed by this pragma are expected ** to change and improve over time. Applications should anticipate that ** this pragma will perform new optimizations in future releases. ** ** The optional argument is a bitmask of optimizations to perform: ** ** 0x00001 Debugging mode. Do not actually perform any optimizations ** but instead return one line of text for each optimization ** that would have been done. Off by default. ** ** 0x00002 Run ANALYZE on tables that might benefit. On by default. ** See below for additional information. ** ** 0x00010 Run all ANALYZE operations using an analysis_limit that ** is the lessor of the current analysis_limit and the ** SQLITE_DEFAULT_OPTIMIZE_LIMIT compile-time option. ** The default value of SQLITE_DEFAULT_OPTIMIZE_LIMIT is ** currently (2024-02-19) set to 2000, which is such that ** the worst case run-time for PRAGMA optimize on a 100MB ** database will usually be less than 100 milliseconds on ** a RaspberryPI-4 class machine. On by default. ** ** 0x00020 Run ANALYZE on any table that has a index that lacks an ** entry in the sqlite_stat1 table. On by default. ** ** 0x10000 Look at tables to see if they need to be reanalyzed ** due to growth or shrinkage even if they have not been ** queried during the current connection. Off by default. ** ** The default MASK is and always shall be 0x0fffe. In the current ** implementation, the default mask only covers the 0x00002 optimization, ** though additional optimizations that are covered by 0x0fffe might be ** added in the future. Optimizations that are off by default and must ** be explicitly requested have masks of 0x10000 or greater. ** ** DETERMINATION OF WHEN TO RUN ANALYZE ** ** In the current implementation, a table is analyzed if only if all of ** the following are true: ** ** (1) MASK bit 0x00002 is set. ** ** (2) The table is an ordinary table, not a virtual table or view. ** ** (3) The table name does not begin with "sqlite_". ** ** (4) One or more of the following is true: ** (4a) The 0x10000 MASK bit is set. ** (4b) One or more indexes on the table lacks an entry ** in the sqlite_stat1 table. ** (4c) The query planner used sqlite_stat1-style statistics for one ** or more indexes of the tableat some point during the lifetime ** of the current connection. ** ** (5) One or more of the following is true: ** (5a) One or more indexes on the table lacks an entry ** in the sqlite_stat1 table. (Same as 4a) ** (5b) The number of rows in the table has increased or decreased by ** 10-fold. In other words, the current size of the table is ** 10 times larger than the size in sqlite_stat1 or else the ** current size is less than 1/10th the size in sqlite_stat1. ** ** The rules for when tables are analyzed are likely to change in ** future releases. Future versions of SQLite might accept a string ** literal argument to this pragma that contains a mnemonic description ** of the options rather than a bitmap. */ case PragTyp_OPTIMIZE: { int iDbLast; /* Loop termination point for the schema loop */ int iTabCur; /* Cursor for a table whose size needs checking */ HashElem *k; /* Loop over tables of a schema */ Schema *pSchema; /* The current schema */ Table *pTab; /* A table in the schema */ Index *pIdx; /* An index of the table */ LogEst szThreshold; /* Size threshold above which reanalysis needed */ char *zSubSql; /* SQL statement for the OP_SqlExec opcode */ u32 opMask; /* Mask of operations to perform */ int nLimit; /* Analysis limit to use */ int nCheck = 0; /* Number of tables to be optimized */ int nBtree = 0; /* Number of btrees to scan */ int nIndex; /* Number of indexes on the current table */ int hasStat1; /* True if any STAT1 info available for the table */ if( zRight ){ opMask = (u32)sqlite3Atoi(zRight); if( (opMask & 0x02)==0 ) break; }else{ opMask = 0xfffe; } if( (opMask & 0x10)==0 ){ nLimit = 0; }else if( db->nAnalysisLimit>0 && db->nAnalysisLimit<SQLITE_DEFAULT_OPTIMIZE_LIMIT ){ nLimit = 0; }else{ nLimit = SQLITE_DEFAULT_OPTIMIZE_LIMIT; } iTabCur = pParse->nTab++; for(iDbLast = zDb?iDb:db->nDb-1; iDb<=iDbLast; iDb++){ if( iDb==1 ) continue; sqlite3CodeVerifySchema(pParse, iDb); pSchema = db->aDb[iDb].pSchema; for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){ pTab = (Table*)sqliteHashData(k); /* This only works for ordinary tables */ if( !IsOrdinaryTable(pTab) ) continue; /* Do not scan system tables */ if( 0==sqlite3StrNICmp(pTab->zName, "sqlite_", 7) ) continue; /* Find the size of the table as last recorded in sqlite_stat1. ** If any index is unanalyzed, then the threshold is -1 to ** indicate a new, unanalyzed index */ szThreshold = pTab->nRowLogEst; hasStat1 = (pTab->tabFlags & TF_HasStat1)!=0; nIndex = 0; for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ nIndex++; if( pIdx->hasStat1 ){ hasStat1 = 1; }else{ szThreshold = -1; /* Always analyze if any index lacks statistics */ } } /* If table pTab has not been used in a way that would benefit from ** having analysis statistics during the current session, then skip it, ** unless the 0x10000 MASK bit is set. */ if( (pTab->tabFlags & TF_MaybeReanalyze)!=0 ){ /* Check for size change if stat1 has been used for a query */ }else if( opMask & 0x10000 ){ /* Check for size change if 0x10000 is set */ }else if( pTab->pIndex!=0 && szThreshold<0 ){ /* Do analysis if unanalyzed indexes exists */ }else{ /* Otherwise, we can skip this table */ continue; } nCheck++; if( nCheck==2 ){ /* If ANALYZE might be invoked two or more times, hold a write ** transaction for efficiency */ sqlite3BeginWriteOperation(pParse, 0, iDb); } nBtree += nIndex+1; /* Reanalyze if the table is 10 times larger or smaller than ** the last analysis. Unconditional reanalysis if there are ** unanalyzed indexes. */ sqlite3OpenTable(pParse, iTabCur, iDb, pTab, OP_OpenRead); if( szThreshold>=0 ){ const LogEst iRange = 33; /* 10x size change */ sqlite3VdbeAddOp4Int(v, OP_IfSizeBetween, iTabCur, sqlite3VdbeCurrentAddr(v)+2+(opMask&1), szThreshold>=iRange ? szThreshold-iRange : -1, szThreshold+iRange); VdbeCoverage(v); }else{ sqlite3VdbeAddOp2(v, OP_Rewind, iTabCur, sqlite3VdbeCurrentAddr(v)+2+(opMask&1)); VdbeCoverage(v); } zSubSql = sqlite3MPrintf(db, "ANALYZE \"%w\".\"%w\"", db->aDb[iDb].zDbSName, pTab->zName); if( opMask & 0x01 ){ int r1 = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp4(v, OP_String8, 0, r1, 0, zSubSql, P4_DYNAMIC); sqlite3VdbeAddOp2(v, OP_ResultRow, r1, 1); }else{ sqlite3VdbeAddOp4(v, OP_SqlExec, nLimit ? 0x02 : 00, nLimit, 0, zSubSql, P4_DYNAMIC); } } } sqlite3VdbeAddOp0(v, OP_Expire); /* In a schema with a large number of tables and indexes, scale back ** the analysis_limit to avoid excess run-time in the worst case. */ if( !db->mallocFailed && nLimit>0 && nBtree>100 ){ int iAddr, iEnd; VdbeOp *aOp; nLimit = 100*nLimit/nBtree; if( nLimit<100 ) nLimit = 100; aOp = sqlite3VdbeGetOp(v, 0); iEnd = sqlite3VdbeCurrentAddr(v); for(iAddr=0; iAddr<iEnd; iAddr++){ if( aOp[iAddr].opcode==OP_SqlExec ) aOp[iAddr].p2 = nLimit; } } break; } /* ** PRAGMA busy_timeout ** PRAGMA busy_timeout = N ** |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
2082 2083 2084 2085 2086 2087 2088 | sqlite3VdbeMemRealify(pIn1); REGISTER_TRACE(pOp->p1, pIn1); } break; } #endif | | | 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 | sqlite3VdbeMemRealify(pIn1); REGISTER_TRACE(pOp->p1, pIn1); } break; } #endif #if !defined(SQLITE_OMIT_CAST) && !defined(SQLITE_OMIT_ANALYZE) /* Opcode: Cast P1 P2 * * * ** Synopsis: affinity(r[P1]) ** ** Force the value in register P1 to be the type defined by P2. ** ** <ul> ** <li> P2=='A' → BLOB |
︙ | ︙ | |||
6188 6189 6190 6191 6192 6193 6194 | if( pOp->p2>0 ){ VdbeBranchTaken(res!=0,2); if( res ) goto jump_to_p2; } break; } | | | | > > | > > > | > > > | > | 6188 6189 6190 6191 6192 6193 6194 6195 6196 6197 6198 6199 6200 6201 6202 6203 6204 6205 6206 6207 6208 6209 6210 6211 6212 6213 6214 6215 6216 6217 6218 6219 6220 6221 6222 6223 6224 6225 6226 6227 6228 6229 6230 6231 6232 | if( pOp->p2>0 ){ VdbeBranchTaken(res!=0,2); if( res ) goto jump_to_p2; } break; } /* Opcode: IfSizeBetween P1 P2 P3 P4 * ** ** Let N be the approximate number of rows in the table or index ** with cursor P1 and let X be 10*log2(N) if N is positive or -1 ** if N is zero. Thus X will be within the range of -1 to 640, inclusive ** Jump to P2 if X is in between P3 and P4, inclusive. */ case OP_IfSizeBetween: { /* jump */ VdbeCursor *pC; BtCursor *pCrsr; int res; i64 sz; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); assert( pOp->p4type==P4_INT32 ); assert( pOp->p3>=-1 && pOp->p3<=640 ); assert( pOp->p4.i>=-1 && pOp->p4.i<=640 ); 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 = -1; /* -Infinity encoding */ }else{ sz = sqlite3BtreeRowCountEst(pCrsr); assert( sz>0 ); sz = sqlite3LogEst((u64)sz); } res = sz>=pOp->p3 && sz<=pOp->p4.i; VdbeBranchTaken(res!=0,2); if( res ) goto jump_to_p2; break; } /* Opcode: SorterSort P1 P2 * * * |
︙ | ︙ | |||
6909 6910 6911 6912 6913 6914 6915 | assert( pDb->pBt!=0 ); rc = sqlite3BtreeCreateTable(pDb->pBt, &pgno, pOp->p3); if( rc ) goto abort_due_to_error; pOut->u.i = pgno; break; } | | > > > | > | > > > > > | > > > > | 6918 6919 6920 6921 6922 6923 6924 6925 6926 6927 6928 6929 6930 6931 6932 6933 6934 6935 6936 6937 6938 6939 6940 6941 6942 6943 6944 6945 6946 6947 6948 6949 6950 6951 6952 6953 6954 6955 6956 6957 6958 6959 6960 6961 6962 6963 6964 6965 6966 6967 6968 6969 6970 6971 6972 6973 6974 6975 6976 | assert( pDb->pBt!=0 ); rc = sqlite3BtreeCreateTable(pDb->pBt, &pgno, pOp->p3); if( rc ) goto abort_due_to_error; pOut->u.i = pgno; break; } /* Opcode: SqlExec P1 P2 * P4 * ** ** Run the SQL statement or statements specified in the P4 string. ** ** The P1 parameter is a bitmask of options: ** ** 0x0001 Disable Auth and Trace callbacks while the statements ** in P4 are running. ** ** 0x0002 Set db->nAnalysisLimit to P2 while the statements in ** P4 are running. ** */ case OP_SqlExec: { char *zErr; #ifndef SQLITE_OMIT_AUTHORIZATION sqlite3_xauth xAuth; #endif u8 mTrace; int savedAnalysisLimit; sqlite3VdbeIncrWriteCounter(p, 0); db->nSqlExec++; zErr = 0; #ifndef SQLITE_OMIT_AUTHORIZATION xAuth = db->xAuth; #endif mTrace = db->mTrace; savedAnalysisLimit = db->nAnalysisLimit; if( pOp->p1 & 0x0001 ){ #ifndef SQLITE_OMIT_AUTHORIZATION db->xAuth = 0; #endif db->mTrace = 0; } if( pOp->p1 & 0x0002 ){ db->nAnalysisLimit = pOp->p2; } rc = sqlite3_exec(db, pOp->p4.z, 0, 0, &zErr); db->nSqlExec--; #ifndef SQLITE_OMIT_AUTHORIZATION db->xAuth = xAuth; #endif db->mTrace = mTrace; db->nAnalysisLimit = savedAnalysisLimit; if( zErr || rc ){ sqlite3VdbeError(p, "%s", zErr); sqlite3_free(zErr); if( rc==SQLITE_NOMEM ) goto no_mem; goto abort_due_to_error; } break; |
︙ | ︙ |
Changes to test/busy.test.
︙ | ︙ | |||
102 103 104 105 106 107 108 | SELECT count(*) FROM sqlite_master; } db2 } {6} proc busy_handler {n} { return 1 } do_test 3.5 { catchsql { PRAGMA optimize } | | | 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 | SELECT count(*) FROM sqlite_master; } db2 } {6} proc busy_handler {n} { return 1 } do_test 3.5 { catchsql { PRAGMA optimize } } {1 {database is locked}} do_test 3.6 { execsql { COMMIT } db2 execsql { WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000 ) |
︙ | ︙ |