SQLite makes planner statistics cheaper to maintain

Statistics maintenance has an awkward dependency on database size. The information helps a planner choose efficient queries, but collecting it can become expensive as the database grows. SQLite 3.46.0 changes PRAGMA optimize so an application can request useful maintenance without first managing a separate analysis limit. The pragma temporarily limits the work it performs when it decides to run analysis.

The May 23 release also adds an option to consider all tables and improves handling of tables without sqlite_stat1 entries. Together, those changes address both the cost of collecting statistics and the question of which tables the connection knows enough to consider. The result is more useful than a blanket instruction to analyze everything periodically.

I like SQLite because an application can keep substantial database capability inside its own process. Routine maintenance should fit that arrangement. A bounded operation that decides whether statistics need attention is easier to include in an application's lifecycle than a maintenance job whose cost grows without a deliberate limit.

Selection and analysis are separate decisions

The 3.46.0 implementation documents the pragma's selection rules beside the code. It considers ordinary application tables, excluding views, virtual tables, and SQLite's own named system tables. Candidate selection then depends on missing statistics, connection history, or the explicit all-tables option.

The connection's history matters because SQLite can record that planning a query used statistics for a table. That gives PRAGMA optimize a reason to inspect whether the statistics still describe its size. A connection that has never queried a table does not have the same evidence about its relevance. Missing index statistics provide another reason to consider a table even without that history.

Considering a table does not mean unconditionally analyzing it. The implementation can compare its current size with the size represented in the statistics. In this release, a roughly tenfold increase or decrease provides a reason to refresh existing statistics. An unanalyzed index is another trigger. These rules avoid equating every invocation with a full pass through all data.

That distinction explains why the no-argument command can often do little work. A request for optimization asks SQLite to apply a policy. It does not command the engine to rewrite every statistic. Calling it repeatedly therefore has different implications from explicitly requesting a full analysis each time.

PRAGMA optimize;

The command above uses the default mask. Its usefulness comes partly from the implementation being allowed to improve that policy over time. The source comments explicitly tell applications to expect future optimizations. Application code can express the maintenance intent while SQLite controls the current selection rules.

The new limit belongs to this maintenance operation

Version 3.46.0 enables a temporary analysis limit by default within the pragma. The source defines a default optimization limit of 2,000 and respects a smaller positive analysis limit already configured on the connection. It also reduces the temporary limit when a schema has a large number of tables and indexes to consider.

These details matter because a row limit is a control over analysis effort, not a promise that the entire operation completes within an exact wall-clock interval. Storage performance, database shape, and the number of candidates still affect elapsed time. The source explains the empirical motivation for the chosen default, but an application should not turn that motivation into a universal timing guarantee.

The mechanism accepts less exhaustive collection in exchange for bounded work. SQLite's comments argue that the resulting information is usually sufficient to characterize an index. That is an engineering choice about the value of additional sampling, rather than a claim that approximate and exhaustive analysis produce identical statistics.

I consider that tradeoff appropriate for routine embedded database maintenance. The application gets a practical default, while the engine keeps the detailed policy near the planner and the statistics implementation. The release removes a configuration decision that many callers would otherwise repeat without much information about the right value.

All tables does not mean analyze all tables

The new 0x10000 flag broadens which tables SQLite inspects for changes. It allows inspection even when the current connection has not queried a table in a way that would mark it for consideration. That is relevant at the beginning of a connection's lifetime, when its query history says little about the workload.

The flag supplements the normal operation mask. Using it alone would omit the bit that enables analysis. A form that retains the default operations and adds the broader inspection is explicit in its intent.

PRAGMA optimize(0x1fffe);

The larger mask includes the new high bit and the default lower bits. Size-change checks and missing-statistics checks still determine whether analysis runs. The flag changes the set of candidates, while the refresh policy continues to decide what each candidate needs.

For a long-lived connection, this separates an initial broad inspection from later calls informed by actual use. It also clarifies what a new connection can know. Statistics persist in the database, but the connection's record of queries is specific to that connection. The high bit gives the pragma permission to look beyond that temporary history.

Other changes make semantics more explicit

The same release adds floor and ceiling date modifiers for ambiguous shifts by whole months or years. Calendar arithmetic can reach a destination month without the original day, so the operation needs a policy for choosing the resulting date. Naming that policy in SQL makes a previously implicit decision part of the expression.

The release also adds more strftime() conversions, including ISO week-related forms, and permits underscores between digits in numeric literals. Those changes affect how programs express dates and numbers. They are separate from statistics maintenance, but share the practical benefit of reducing application-side work around SQL expressions.

Planner statistics still deserve to be understood as estimates used in choosing execution strategies. Updating them is different from repairing an index or validating stored data. The new pragma behavior makes it easier to keep that planning information useful without treating every maintenance call as a full database analysis. Its temporary limit and candidate rules define how much work SQLite chooses to do for that request.