SQLite adds selective statistics maintenance with PRAGMA optimize

Refreshing planner statistics poses a scheduling problem for an embedded database. An application knows when it has finished a unit of work, but it need not know which tables would benefit from analysis. The query planner has information about the tables involved in its decisions, but it does not decide when the application can afford maintenance. SQLite 3.18.0, released on 30 March, introduces PRAGMA optimize as a way to connect those two kinds of knowledge.

The command has a broad name, but its initial implementation has a precise task. It selectively runs ANALYZE for eligible tables, using information accumulated by the current connection. Understanding those eligibility rules is more useful than assuming that the command performs every operation that might make a database faster.

I like this division of responsibility for SQLite. The application can choose a maintenance opportunity while leaving table selection to database code. That fits a library database whose caller already owns the surrounding process and its lifecycle.

The connection records which statistics mattered

The released implementation in pragma.c describes the conditions for analysis. Analysis must be enabled in the operation mask. The planner must have used sqlite_stat1-style statistics for an index of the table during the lifetime of the connection. The table must also have an unanalyzed index or have grown enough since its previous analysis.

The source uses a table flag named TF_StatsUsed to decide whether a table has participated in planning in the relevant way. Tables without that flag are skipped. The accompanying comment explains that this also skips virtual tables and views. Consequently, opening a connection and immediately invoking the new command does not supply the same history as invoking it after that connection has prepared useful queries.

This is an important property of the initial design. Eligibility depends on connection-local experience. A separate maintenance connection does not automatically inherit another connection's record of planning activity. The code comments discuss storing usage information in the database for other connections, but mark that operation as not yet implemented.

That difference affects how an embedding application can integrate the command. A connection that has done work has evidence about the tables relevant to its workload. A fresh connection has less evidence. The command is deliberately selective, so absence of maintenance output does not imply that the entire database has current statistics.

Missing statistics and substantial growth trigger analysis

For an eligible table, the implementation walks its indexes. If any index lacks sqlite_stat1 information, the size threshold becomes zero. Otherwise, the code checks whether the table is approximately 25 times larger than the size associated with the prior analysis. This is a substantial growth condition, not a promise to reanalyze after every small change.

The threshold calculation uses SQLite's logarithmic estimate representation. In this representation, the source adds 46 to the table's row estimate and asserts that sqlite3LogEst(25) equals 46. The resulting bytecode checks the table size before executing analysis. Reading this code gives a concrete explanation for the factor in the implementation comment, rather than treating it as an informal recommendation.

Missing index statistics take a different path. Once the loop finds an index without statistics, it can request analysis without requiring the growth test. That distinction makes sense within the command's purpose. Newly missing information and old information made less representative by substantial growth are different reasons to refresh planner data.

The command generates an ANALYZE statement naming the selected schema and table. It does not implement a separate statistics format. Its contribution is the selection policy and the opportunity to invoke established analysis work through one maintenance command. This also explains why the cost can vary between calls. A call that selects no tables and a call that analyzes a substantial table do different amounts of work.

The mask permits inspection

The simplest form requests the default set of operations.

PRAGMA optimize;

The 3.18.0 source sets that default mask to 0xfffe. Bit 0x0002 enables analysis, while bit 0x0001 enables a debugging mode that reports the operations without performing them. Since debugging and analysis are separate bits, inspecting the analysis decisions requires both.

PRAGMA optimize(3);

In that form, an eligible operation returns the generated ANALYZE statement as a text row. The debugging bit alone does not enable analysis selection. The implementation exits when bit 0x0002 is absent, so PRAGMA optimize(1) would not provide the intended inspection of analysis work in this version.

A schema-qualified form limits the operation to that schema. The unqualified form considers the schemas handled by the implementation, while its loop skips the temporary schema. These details matter for applications with attached databases, since a connection may own more than one named database and maintenance selection needs a defined scope.

I find the inspection mode useful because it exposes the selected operation itself. An application author can see that the command proposes analysis of a particular table. That is more specific than a generic success result, and it keeps discussion of maintenance tied to an actual statement.

The name allows the implementation to develop

The source comments say that the optimizations performed by the command are expected to change. They list persistent usage information and automatic index creation as possible mask operations, but explicitly mark both as unimplemented. Neither belongs in a description of what 3.18.0 actually does.

This distinction is easy to lose when a command name describes an intention. PRAGMA optimize sounds like an all-purpose database tuning request. In this release, it is a selective analysis policy with a debugging mode. The wider mask design leaves room for additional work, but the present implementation determines what a caller can rely on today.

The default mask includes bits reserved for possible additional default operations. Applications that supply an explicit mask therefore make a different choice from applications that accept the default. The source explains that operations intended to remain off by default would use higher bits. This is an interface design decision about how a maintenance request can develop while keeping a short invocation useful.

For a caller using the new command now, the concrete contract remains narrow enough to explain. The connection remembers relevant planning activity. The command checks index statistics and table growth. Selected tables receive ANALYZE, unless debugging mode requests only the corresponding statements. No part of that description requires attributing unimplemented index creation to the release.

Source identification changes separately

SQLite 3.18.0 also changes the hash portion of its source identifier from a 40-digit SHA-1 value to a 64-digit SHA3-256 value. The release names the SQL function sqlite_source_id(), the C interface sqlite3_sourceid() and the SQLITE_SOURCE_ID macro as affected interfaces. Code that assumes the previous hash length therefore has a concrete compatibility issue to address.

The source identifier identifies the library source revision. It is separate from planner statistics stored for application tables. A change in its hash representation does not explain a query plan change, and running PRAGMA optimize does not change which SQLite source revision the application embeds. Both changes belong in the release, but they solve different problems.

The release page also publishes a SHA3-256 hash for sqlite3.c. That provides a specific artifact digest in addition to the source identifier. Keeping the two concepts distinct helps build tooling record what it is actually checking. A revision identifier and the digest of a generated source artifact are related provenance information, but they are not interchangeable fields.

Integrity checking covers more of the schema

Another change makes PRAGMA integrity_check and PRAGMA quick_check verify CHECK constraints. This expands the meaning of a successful integrity check to include an additional class of declared data rules. It is a correctness improvement that is independent of statistics maintenance, despite both operations often appearing in administrative code.

The release also begins enforcing SQLITE_LIMIT_VDBE_OP. The notes describe limiting excessively large prepared statements in systems that accept untrusted SQL as one application. That limit concerns the size of the prepared program. It should not be described as a general limit on all resources a query might consume.

These distinctions are why the original PRAGMA optimize code is worth reading. Maintenance, integrity and resource limits all affect how an application runs a database, but their mechanisms differ. The new command gives the application a specific entry point for selected statistics work. Its 2017 implementation bases that selection on the current connection's planning history, missing index statistics and a substantial table-growth threshold.