ANALYZE, sqlite_stat Tables, PRAGMA optimize, and Planner Statistics

Learning outcomes

The planner must estimate how much work each valid plan will require. Schema shape alone does not reveal whether an index key has two distinct values or two million. SQLite can store distribution summaries produced by ANALYZE, and current SQLite recommends PRAGMA optimize as the normal application-facing way to keep useful planner statistics current. This lesson makes the effect visible with a deliberately skewed index.

Why the planner needs distribution information

Imagine an index on (role, height). A query filters only height, so the leading role column is unconstrained. If role has millions of distinct values, repeatedly searching each role would be absurd. If role has only two values with thousands of rows each, SQLite can potentially perform a skip-scan: seek into the height range once per role value. The planner needs statistics to know which world it is in.

ANALYZE writes planner statistics

ANALYZE examines indexes/tables and records statistics in internal tables whose names begin with sqlite_stat. sqlite_stat1 is the core statistics table used by the planner after analysis. Its stat strings encode estimated row counts and average rows per left-prefix key. They are engine metadata, not a user-maintained configuration format.

ANALYZE;SELECT tbl, idx, statFROM sqlite_stat1ORDER BY tbl, idx;-- Do not UPDATE/INSERT these rows manually as ordinary tuning practice.

A plan-change experiment: statistics enable skip-scan

This disposable table intentionally has only two role values and many rows per role. Before ANALYZE, SQLite uses conservative default assumptions about duplicates in an unanalyzed index. After ANALYZE reveals very high duplication in the leftmost role key, skip-scan can become profitable for a query that constrains only height.

DROP TABLE IF EXISTS person_probe;CREATE TABLE person_probe(  person_id INTEGER PRIMARY KEY,  role TEXT NOT NULL,  height_cm INTEGER NOT NULL);CREATE INDEX idx_person_role_heightON person_probe(role, height_cm);WITH RECURSIVE seq(x) AS (  VALUES(1)  UNION ALL  SELECT x+1 FROM seq WHERE x<20000)INSERT INTO person_probe(role,height_cm)SELECT CASE WHEN x%2=0 THEN 'student' ELSE 'teacher' END,       150 + (x%61)FROM seq;-- Capture this plan BEFORE ANALYZE:EXPLAIN QUERY PLANSELECT count(*) FROM person_probe WHERE height_cm>=205;ANALYZE person_probe;SELECT tbl, idx, statFROM sqlite_stat1WHERE tbl='person_probe';-- Capture this plan AFTER ANALYZE:EXPLAIN QUERY PLANSELECT count(*) FROM person_probe WHERE height_cm>=205;

On the executable 3.46.1 validation runtime used to test this chapter, the plan changes from SCAN person_probe before ANALYZE to a covering-index SEARCH with an ANY(role)-style detail after ANALYZE—evidence of skip-scan. The observed sqlite_stat1 row was 20000 10000 164. Exact plan wording and cost choices may differ on another SQLite version/build, so your lab requirement is to save both actual plans. If a future planner makes the same choice both times, document that result rather than falsifying a change.

PRAGMA optimize is the current application-facing recommendation

Since SQLite 3.46.0, official guidance recommends PRAGMA optimize as the normal way for applications to trigger bounded, targeted analysis work rather than running unrestricted full ANALYZE routinely. It is usually a no-op or nearly so and applies a temporary analysis limit when it decides analysis is useful.

-- Short-lived connection, before close:PRAGMA optimize;-- Long-lived connection, at open:PRAGMA optimize=0x10002;-- Later, periodically and after CREATE INDEX:PRAGMA optimize;-- Current diagnostic form: report optimizations without doing them.PRAGMA optimize(-1);

ANALYZE still matters as a concept and a controlled tool

PRAGMA optimize may internally decide to run ANALYZE on selected tables. Direct ANALYZE remains useful in controlled development experiments—like the skip-scan lab—and for specialized plan-stability workflows. The key production lesson is not “ANALYZE is forbidden”; it is “do not blindly perform expensive global analysis on every startup when SQLite offers a bounded optimizer-maintenance interface.”

Plan stability versus evolving statistics

Statistics improve cost estimates, but changing them can also change plans. That is normally desirable: a database whose distribution has evolved may need a different algorithm. Therefore, performance tests must resemble production scale and skew. A plan tested on 100 uniformly distributed rows may tell you little about 50 million rows where one status value dominates.

Query Planner Stability Guarantee: optional advanced production control

SQLite offers an optional Query Planner Stability Guarantee (QPSG). When enabled, the same SQL is designed to keep the same plan as long as the relevant schema does not change, ANALYZE is not rerun, and the SQLite version remains the same. QPSG is disabled by default and is enabled through compile-time/runtime C APIs, not a beginner PRAGMA.

This can be attractive for tightly controlled embedded appliances where reproducing field plans matters more than adapting to evolving distributions. It is not a substitute for good indexes or representative testing, and upgrading SQLite or refreshing statistics still changes the assumptions under which the guarantee applies.

Do not hand-edit sqlite_stat tables as ordinary tuning

There are specialized deployment techniques that capture known-good sqlite_stat1 contents during development to reproduce plans in deployed instances. SQLite documents such workflows for applications that intentionally want fixed statistics. That is an advanced release-engineering decision. Beginners and ordinary applications should let ANALYZE/PRAGMA optimize maintain statistics and should never “improve a plan” by guessing replacement stat strings.

Production statistics checklist

1. Record the SQLite version/source ID and compile options in diagnostics.
2. Use representative data volume and skew in performance tests.
3. Inspect critical-query EQP before and after schema/statistics changes.
4. Prefer current PRAGMA optimize lifecycle guidance for application maintenance.
5. Run PRAGMA optimize after important CREATE INDEX/schema changes.
6. Do not assume sqlite_stat4 exists; detect ENABLE_STAT4.
7. Never make application correctness depend on a particular plan.
8. Do not parse EQP text as a stable API.
9. Re-test critical plans after changing SQLite versions.
10. Consider QPSG only when plan reproducibility is a conscious production requirement.

End-of-chapter lab and cleanup

Run the skip-scan experiment from a fresh connection, save both EQP outputs, inspect sqlite_stat1, then run PRAGMA optimize; on the FieldNotes database. Finally remove the probe table. Do not delete sqlite_stat1 manually as cleanup; dropping the analyzed probe object and allowing normal statistics maintenance is safer.

DROP TABLE IF EXISTS person_probe;PRAGMA optimize;SELECT name, typeFROM sqlite_schemaWHERE type='index'ORDER BY name;PRAGMA compile_options;SELECT sqlite_version();

Production judgment and Chapter 11 bridge

You now have the complete index-tuning loop: access pattern → candidate index → EQP → realistic data/statistics → measurement → write/storage review → maintenance policy. Chapter 11 descends one layer into the database file itself—header fields, page size, table/index B-trees, cells, overflow, freelist, and the pager—so the page-level costs behind these planner choices become concrete.

Authoritative references

• SQLite — ANALYZE.
• SQLite — PRAGMA optimize.
• SQLite — Query Optimizer Overview: skip-scan.
• SQLite — Next-Generation Query Planner: QPSG.
• SQLite — Current release history.