SQLite bounds the work of collecting planner statistics

Collecting statistics for a query planner can become expensive enough that applications avoid doing it. SQLite's ANALYZE normally scans every index, which creates an awkward maintenance cost as a database grows. SQLite 3.32.0 introduces an approximate form controlled by PRAGMA analysis_limit. The May 22 release notes also add a bytecode virtual table, giving SQL queries access to the instructions SQLite generates for prepared statements.

The analysis change addresses a specific tradeoff. More complete inspection costs more work, while avoiding statistics collection can leave the planner without useful information. A configurable limit gives the application another option between those outcomes. It limits how much of each index ANALYZE examines, with an approximation whose behavior the release documentation explains.

I value SQLite's ability to keep database operations inside the application, but that also makes maintenance work part of the application's resource use. An explicit analysis limit is useful because the application can express how much statistical inspection it intends to request. It does not need to pretend that a full index scan is always a negligible background task.

The limit applies to each index

The release-date ANALYZE documentation explains that the command stores statistics in internal tables for the query optimizer. With no argument, it analyzes attached databases. An argument can narrow the operation to a schema, table or index. Those existing choices select what to analyze, while the new pragma changes the approximate amount of scanning within each index.

An application can request approximate analysis with a positive limit. The documentation gives 1000 as an example and recommends values between 100 and 1000.

PRAGMA analysis_limit=1000;
ANALYZE;

This is not a wall-clock timeout or a total budget of 1000 rows across the whole database. Each index has its own scan. A database with many indexes therefore still requires work on many objects, even when each scan uses a limit. Describing the setting as a total maintenance deadline would misstate its mechanism.

The approximation also explains why the limit is not an exact row count. Analysis begins by scanning the index normally. After the configured number of visits, it can stop if the leftmost index column has changed at least once. If that column has remained constant, analysis skips to the first entry with a different leftmost value and reads another limited group before stopping.

That behavior avoids deriving the whole estimate from one repeated leading value when the initial range contains no change. The documentation describes the algorithm as striving for approximately the requested number of visits and explicitly leaves its details subject to change. An application should consequently treat the limit as the documented approximation, not as an exact guarantee about individual index operations.

Precision has a concrete cost

The pragma documentation at release makes zero the way to disable the analysis limit. The default is zero for compatibility, so upgrading alone does not opt an application into approximate collection. A negative or noninteger argument leaves the setting unchanged. Querying the pragma without an argument also leaves the limit unchanged and returns its current value.

Approximate analysis changes the statistics that appear in sqlite_stat1. The documentation says these generally remain useful for planning, while acknowledging that pathological cases can make approximate results inferior to a full scan. That is a claim about the usefulness of estimates, not a claim that the two procedures produce identical data. The reason to choose approximation is that collection becomes feasible more often.

There is a further restriction for builds using SQLITE_ENABLE_STAT4. The additional histogram data in sqlite_stat4 requires a full scan, so a nonzero analysis limit prevents that table from being computed. This gives the precision tradeoff a specific representation. The application is choosing limited collection with a known omission, not merely an unspecified faster implementation of the same statistics.

Statistics also have a lifecycle after collection. The release-date documentation says database changes do not automatically update the stored statistics. Changes in data or schema can make another analysis useful. Reducing collection work helps make that repeated maintenance practical, while leaving the distinction between current data and previously collected estimates intact.

The connection already knows something about its workload

The same documentation describes PRAGMA optimize, which uses information accumulated by a database connection to decide when analysis would be useful. It usually does nothing. On some occasions it runs ANALYZE for tables where new statistics are likely to help. A limit can constrain analysis initiated through that operation too.

The release-date recommended usage for a connection about to close combines the two settings. Its example chooses a smaller limit than the direct analysis example.

PRAGMA analysis_limit=400;
PRAGMA optimize;

Deferring optimization until closing lets the connection accumulate information from its work. For long-lived connections, the documentation also allows periodic optimization. The relevant distinction is that optimize decides whether particular analysis work seems useful, while analysis_limit constrains the approximate scanning that analysis performs. Neither operation needs to be described as unconditional full-database maintenance.

I prefer this combination because it gives the application a documented way to request useful planner maintenance with limited inspection work. The limit and the decision to analyze remain separate controls. That makes their behavior easier to explain when a connection has little useful work to suggest or a database contains large indexes.

Bytecode becomes queryable data

The bytecode documentation at release describes a table-valued function that returns one row per virtual-machine instruction in a prepared statement. Its instruction columns correspond to the output of EXPLAIN. Using a table-valued function makes those rows available to ordinary SQL filtering and projection.

SELECT addr, opcode, p1, p2, p3
FROM bytecode('SELECT 1');

Availability depends on the SQLITE_ENABLE_BYTECODE_VTAB compile-time option. The release documentation says the command-line shell includes it, which provides a place to inspect the facility without assuming every embedded build has enabled it. The bytecode table also includes subprogram instructions used for triggers and foreign key actions. Its subprog column distinguishes those instructions from the main statement body, so inspection need not stop at the SQL text that initiated the operation.