I Filled sqlite_stat1 and Six Query Plans Did Not Change

This blog's articles are no longer served from files but from a single SQLite file: every deploy renders all the MDX into content.db, and the server opens it read-only. Today I asked that file a simple question — is the planner working with no statistics at all?

$ sqlite3 -readonly /opt/mustafaerbay/dist/content.db \
    "select coalesce((select group_concat(name) from sqlite_schema
       where name like 'sqlite_stat%'),'NO STAT TABLES');
     select count(*) from posts;"
NO STAT TABLES
2676

The answer is unambiguous: not a single statistics row in the live database. There is none in the repository either — the words ANALYZE, PRAGMA optimize and analysis_limit appear nowhere in the scripts, the deploy files or the application code. A 2,676-row, 58 MiB database has been telling SQLite's query planner "you figure it out" since the 29 June cutover, three and a half months now.

I did not fill those statistics in production — every measurement below ran on locally rebuilt copies of the same database, and the live file still has no stat tables as I write this. Filing that as a gap and adding ANALYZE to the pipeline would have taken ten minutes. But I wanted a measurement first, because "collect statistics and the planner decides better" is as true as it is empty: does any decision actually change in this schema, for these queries? An hour in the lab answers it: four statistics rows written, 80 histogram rows produced, and all six plans came back identical.

That is where the real subject begins. A "nothing changed" result counts as silence, not as a finding, until your measurement rig has proven it can see a change.

The rig: what was measured, and with what

I wrote nothing to the live database. Running node scripts/migrate-content.mjs in the repo rebuilt the same file locally — 2,676 rows (1,338 Turkish, 1,338 English) in 34.5 seconds, 60,690,432 bytes. Each phase got a fresh copy of that file, so the only difference between "before" and "after" was the ANALYZE statement.

The signal is the plan, not the clock: EXPLAIN QUERY PLAN. There is a measured reason for that choice near the end of this article, but briefly: on a 2,676-row table, wall time reports the mood of the page cache more than the shape of the plan.

The queries are not invented. The six read queries on the serving path came out of the code one by one: four in the data layer (src/lib/content-db.ts: the list, lookup by id, lookup by slug, category counts), one in the newsletter notifier (a date range), one in the search-index generator (a full scan). Five of them have a live caller; the category count does not — something this lab turned up, and I come back to it below. The write queries inside the migrate script that builds the database (a full scan to read source hashes, an UPDATE, a DELETE, batched INSERTs) are not in this set; they run at deploy time with no reader waiting on them. The whole schema is one table and three hand-written indexes:

CREATE INDEX idx_posts_lang_date ON posts(lang, publish_date DESC);
CREATE INDEX idx_posts_category  ON posts(category);
CREATE INDEX idx_posts_slug      ON posts(url_slug, lang);

There is a fourth, sqlite_autoindex_posts_1, created automatically by id TEXT PRIMARY KEY — a text primary key on a rowid table means a separate index, the small side discovery of this lab.

Plans without statistics: blind but lucky

On the fresh copy, the six plans came out like this:

Query	Plan (no statistics)
List (lang + ORDER BY publish_date DESC)	SEARCH posts USING INDEX idx_posts_lang_date (lang=?)
Single row by id	SEARCH posts USING INDEX sqlite_autoindex_posts_1 (id=?)
slug + language	SEARCH posts USING INDEX idx_posts_slug (url_slug=? AND lang=?)
Category counts (GROUP BY)	SEARCH ... idx_posts_lang_date (lang=?) + USE TEMP B-TREE FOR GROUP BY
Newsletter window (date range)	SEARCH ... idx_posts_lang_date (lang=? AND publish_date>? AND publish_date<?)
Search index (WHERE draft = 0)	SCAN posts

Five of the six picked the best of the indexes available. The sixth — the full scan — is also right, because draft = 0 selects every row and the columns it needs live in the table; there is nothing else to narrow.

Reading these lines is a small skill of its own, so a short detour. SCAN means the table is read end to end, SEARCH means SQLite dives in through an index, and the part in parentheses shows which conditions were handed to that index. USE TEMP B-TREE FOR GROUP BY means "the index could not provide the ordering, so a temporary tree went up in memory" — wherever that line appears, there is work being done.

There is also an invisible word: COVERING. A slightly modified query on the same database shows it:

SELECT publish_date FROM posts WHERE lang='tr' ORDER BY publish_date DESC LIMIT 20
  → SEARCH posts USING COVERING INDEX idx_posts_lang_date (lang=?)

SELECT title FROM posts WHERE lang='tr' ORDER BY publish_date DESC LIMIT 20
  → SEARCH posts USING INDEX idx_posts_lang_date (lang=?)

The only difference is the selected column. publish_date lives inside the index, so SQLite never touches the table; ask for title and every row costs one more hop into the table. My list query asks for 21 columns, which means 1,338 table lookups for 1,338 rows. Statistics do not change that cost — the column list does. The presence or absence of COVERING on a plan line is usually a far bigger deal than statistics.

And that table taught the main lesson: the planner needs statistics where it has to choose between two indexes. If an equality condition points at a single index, statistics only refine the cost estimate; they do not change the decision. In my schema lang, url_slug, id and category never compete with each other.

ANALYZE: one millisecond, 20 kibibytes, four rows

ANALYZE duration : 1 ms
File             : 60,690,432 → 60,710,912 bytes (+20 KiB)
sqlite_stat1     : 4 rows
sqlite_stat4     : 80 rows

The cost debate ends right there. Thinking about whether one millisecond and 20 kibibytes are worth it takes longer than measuring them. What is interesting is not the cost but how readable the written rows are:

idx_posts_category        2676 669
idx_posts_lang_date       2676 1338 7
idx_posts_slug            2676 2 1
sqlite_autoindex_posts_1  2676 1

SQLite's file format documentation explains the list: the first integer is the approximate number of rows in the index, and the Nth integer is the average number of rows whose first N-1 columns match. So those four rows are the blog's own fact sheet:

• 2676 2 1 — each url_slug maps to exactly two rows. Correct: every article has a Turkish and an English version, so all 1,338 slugs appear twice. The trailing 1 says the (url_slug, lang) pair is unique.
• 2676 1338 7 — 1,338 rows per language, and an average of 7 rows sharing the same (lang, publish_date) pair. The seven is rounding at work: there are 430 distinct (language, date) pairs, 2,676 / 430 = 6.22, and SQLite rounds the average up. What pushes the average near six is April's bulk import: 17 April carries 31 articles in one language, 62 across both. Today's pace of three a day will drag that average down over the years.
• 2676 669 — four categories, 669 rows per category on average. The real distribution is 940 / 784 / 536 / 416.

The histogram table's distribution teaches something too: of the 80 rows, 24 belong to idx_posts_lang_date, 24 to idx_posts_slug, 24 to the automatic primary key index, and only 8 to idx_posts_category. The documentation says an index typically gets 10 to 40 samples; the category index sits below that range because it only carries four distinct values — the sample count is bounded by the variety in the index.

This is why reading sqlite_stat1 is pleasant: there are no guesses in it, only counts. A table built for the planner hands you a summary of your corpus as a side effect.

And the plans? All six identical, line for line. The category count still builds its temporary B-tree, the list still uses the same index, the full scan is still a full scan. Repeating the comparison with statistics produced by PRAGMA optimize and by analysis_limit=100 gave the same answer: no difference.

"Is the rig broken?" — testing it with a deliberate positive

It would have been easy to stop here and publish "ANALYZE is unnecessary". But a measurement that shows nothing has two explanations: there is no effect, or the instrument is broken. The only way to separate them is to prove the instrument can see a known positive.

So a deliberately bad index went onto a copy of the same database. The draft column holds a single value (all 2,676 rows are draft = 0), so the index filters nothing at all:

CREATE INDEX idx_draft_test ON posts(draft);

Plans without statistics:

SELECT count(*) FROM posts WHERE draft = 0 AND lang = ?
  → SEARCH posts USING INDEX idx_draft_test (draft=?)
SELECT id FROM posts WHERE draft = 0 AND category = ?
  → SEARCH posts USING INDEX idx_draft_test (draft=?)

The planner walked into the trap. With no statistics, SQLite assumes every equality condition narrows the row set meaningfully; draft = 0 looked like a filter, so both queries bound themselves to a useless index. After ANALYZE:

sqlite_stat1: idx_draft_test  2676 2676
  → SEARCH posts USING INDEX idx_posts_lang_date (lang=?)
  → SEARCH posts USING INDEX idx_posts_category (category=?)

The row 2676 2676 says "one value of this index returns 2,676 rows", and the planner abandoned it instantly, moving to two different correct indexes. The rig works. Which means the stillness across those six queries is a real finding.

The second check is built on one sentence from the documentation: statistics are loaded into memory when the schema is read, and manual changes to those tables are not noticed right away. Writing the unordered flag into sqlite_stat1 by hand tells the planner, in the documentation's words, to use that index neither for a range query nor for sorting:

UPDATE sqlite_stat1 SET stat = '2676 1338 7 unordered'
 WHERE idx = 'idx_posts_lang_date';

On the same connection, the plan did not move a muscle. After ANALYZE sqlite_schema forced a reread, the sorting cost surfaced:

SEARCH posts USING INDEX idx_posts_lang_date (lang=?) | USE TEMP B-TREE FOR ORDER BY

A freshly opened connection gave the same plan. So the documented "loaded when the schema is read" line is a concrete trap for any script that writes statistics and then expects the effect in the same process: the statistics you just wrote will not change that connection's plans.

PRAGMA optimize: the 2000 that is not in the docs

The modern advice is not ANALYZE but PRAGMA optimize: short-lived connections should run it just before closing, long-lived ones should run PRAGMA optimize=0x10002 on open and then repeat it periodically. Neither works in my pipeline, because the application opens the database read-only:

PRAGMA optimize    → SqliteError: attempt to write a readonly database
PRAGMA optimize(-1) → ANALYZE "main"."posts"

The dry run (-1) works even on a read-only connection and reports what it would do — a useful split for diagnosis: I can read the recommendation, I just cannot apply it. The recommendation is not an accident either; the documentation requires five conditions to hold before a table is analyzed, and what opens the gate here is that indexes have no sqlite_stat1 entry at all. Once the statistics are in place that gate closes and the dry run comes back empty.

More interesting is that the statistics PRAGMA optimize produces are not the same as a full ANALYZE:

Index	Full ANALYZE	PRAGMA optimize
idx_posts_category	2676 669	2676 501
idx_posts_lang_date	2676 1338 7	2676 1001 6
sqlite_stat4 rows	80	0

Why did the numbers get coarser? The documentation says that since 3.46.0 PRAGMA optimize sets its own temporary scan limit, but it does not say what that limit is. Setting limits by hand and matching the output pins it down: the four rows produced by analysis_limit=2000 are byte-for-byte what PRAGMA optimize writes, and three separate fresh copies gave the same result. The source agrees — the SQLITE_DEFAULT_OPTIMIZE_LIMIT macro is 2000, and the comment beside it explains the choice: it was picked empirically so that the worst case on a Raspberry Pi 4 stays under 100 milliseconds. The same comment adds that an analysis limit of 2000 is almost always enough for the planner to characterise an index fully. The limit is scaled down further when a schema holds more than 100 b-trees; mine has one table and four indexes, far below a hundred, so the full 2000 applied.

Which matches my own finding: the numbers got coarser, the decision did not change.

There is a quiet side effect too. Whenever analysis_limit is non-zero, sqlite_stat4 is never computed. Trying 100, 500, 1000, 2000, 2676 and 5000 gave zero histogram rows in all six cases. Only the default limit (analysis_limit = 0, meaning a full scan) produced the 80 rows. Histograms are what help with LIKE prefixes and skewed distributions, so whoever sets a limit "to keep it fast" is quietly switching off the most detailed statistics available.

Going stale: nobody tells you

Writing statistics once and relaxing is tempting, so the next thing on the bench was how long that lasts. After ANALYZE, adding 60 rows left PRAGMA optimize(-1) with nothing to propose. Pushing the row count to 26,736 — 9.99 times 2,676 — produced the recommendation:

2,736 rows  → PRAGMA optimize(-1) → (empty)
26,736 rows → PRAGMA optimize(-1) → ANALYZE "main"."posts"
sqlite_stat1 → still "2676 1338 7"

The documented condition is explicit: a table is re-analyzed if an index is missing its sqlite_stat1 entry, or if the row count has changed tenfold since the last ANALYZE. That tenfold is not exactly ten. Scanning the threshold by adding rows: at 25,422 and 25,449 rows (9.50 and 9.51 times) no recommendation came; at 25,690 (9.60 times) it did. The reason sits in the source — the comparison runs through const LogEst iRange = 33; /* 10x size change */, and a LogEst is one tenth of a base-2 logarithm, so 33 means 2^3.3 ≈ 9.85 times, on top of a table size that is itself a rounded estimate. In practice the gate opens around nine and a half. A blog that publishes three articles a day takes years to get there. In my pipeline, PRAGMA optimize would be a watchman that does nothing ever again after its first round.

On top of that there is the pipeline's own behaviour. A deploy does not rebuild content.db from scratch; it takes the previous file as a baseline and re-renders only the changed MDX. The statistics travel along with that baseline copy — measured: handing an analyzed file in as the baseline, the new database inherited all four sqlite_stat1 rows and all 80 sqlite_stat4 rows without re-rendering a single article. So once ANALYZE runs, those statistics ride from deploy to deploy for months, and nothing attaches a note saying "measured when this table held 2,676 rows".

The SQLite documentation has a recipe for exactly this, and it fits my pipeline surprisingly well: applications that want stable plans should run ANALYZE during development, save the contents of sqlite_stat1 as a script, replay it into deployed copies, and not run a full ANALYZE or PRAGMA optimize inside the application. My database is a build artefact — regenerated on every deploy, then mounted read-only. "Measure in development, replay on deployment" is precisely what the migrate script does.

Why timing misleads here

Here is the measured reason for picking the plan as the signal. Running the six queries 31 times each against the unanalyzed and the fully analyzed copies, comparing medians, the first run said:

Q6 search (2,676 rows)  no statistics 1.243 ms   ANALYZE 0.774 ms

A 38 percent "improvement" — with identical plans. Running the same script again immediately:

Q6 search (2,676 rows)  no statistics 0.750 ms   ANALYZE 0.747 ms

The difference evaporated. What the first run captured was a freshly copied file entering the page cache for the first time, which is by definition not reproducible. For the other five queries the noise is larger than any effect: the list query sits at roughly 5 ms on both sides (a separate run put the medians at 5.4 and 5.6 ms, with individual samples wandering between 4.9 and 7.7), and the single-row lookups are in the microsecond band.

The rule I take from this: if you can measure a timing difference from an intervention that did not change the plan, the thing you measured is not the intervention. The EXPLAIN QUERY PLAN output, by contrast, is reproducible — the same statistics always yield the same lines. The run where I measured this blog's WAL file carried a similar distinction: what the counters count and what you are curious about are not obliged to be the same thing.

The real gap the measurement exposed

Going hunting for statistics and coming back empty-handed had a pleasant side effect: reading the plans line by line exposed a schema gap. The category count query filters on lang and groups by category, and there is no index holding both, which is why every call builds a temporary B-tree. Trying it:

CREATE INDEX idx_lang_cat ON posts(lang, category);

before     : SEARCH ... idx_posts_lang_date (lang=?) | USE TEMP B-TREE FOR GROUP BY
after index: SEARCH ... idx_lang_cat (lang=?)
median     : 0.331 ms → 0.204 ms

The temporary tree is gone and the median of 31 runs drops from 0.33 milliseconds to 0.20. The new index's sqlite_stat1 row reads 2676 1338 335 — 1,338 rows per language, 335 per language-category pair. Before and after ANALYZE the plan was the same again; the gain comes from the index, not from statistics.

So will the index go in? No — and the reason is more embarrassing than the measurement. Looking for the query's caller turned up nothing: category counts on the pages are computed in JavaScript over the cached article list rather than in SQL — getAllCategories is called from seven places across both languages, from the footer and the home page to the blog listing and the category pages. The GROUP BY query sitting in the data layer is dead code right now. Adding an index would have sped up a query nobody calls.

That may be the most practical lesson of this run: before deciding to improve a query's plan, verify that the query is called at all. The gain was computed as 130 microseconds per call, and 130 microseconds multiplied by zero calls is zero. The price would have been one more index written for 2,676 rows on every deploy. It stays as a note: if the count ever moves back to the SQL path, this line is where to look first.

One more detail: the new plan has no COVERING, because draft = 0 sits outside the index and every row still needs a table hop. A three-column index would have closed that too — at the cost of writing three columns for the gain measured here.

Which binary runs ANALYZE, and what it writes

A last detail that can bite in mixed-binary environments. The application uses the SQLite embedded in better-sqlite3; the version installed locally is 3.53.0, compiled with ENABLE_STAT4. macOS ships its own sqlite3 at 3.51.0, built without ENABLE_STAT4. Running both against the same file:

• ANALYZE with the Apple binary: the same four sqlite_stat1 rows, and no sqlite_stat4 table at all.
• ANALYZE with better-sqlite3 first (80 histogram rows), then with the Apple binary: the histogram rows are cleared, not left stale.

The second result is a relief, because stale histograms lying around was the scenario I feared. They do not linger; a build without STAT4 support empties the table. The server's sqlite3 is 3.45.1 — older than 3.46, so a hand-run PRAGMA optimize there would not set its own temporary limit. Nothing is at risk today: nobody invokes that shell for maintenance, and the serving process uses the SQLite embedded in better-sqlite3. Still, knowing which binary performs your maintenance is the same thing as knowing what the maintenance does.

Trying the line I recommend

Saying "I will put an ANALYZE at the end of migrate" is easy; whether that line gets along with the rest of the pipeline is a separate question. When the migrate script finishes writing it calls wal_checkpoint(TRUNCATE) and switches the journal mode to DELETE, and the file is then mounted read-only. Imitating that exact order — ANALYZE → checkpoint → journal_mode = DELETE → open read-only:

journal_mode         : delete
sqlite_stat1         : 4 rows
sqlite_stat4         : 80 rows
plan (list query)    : SEARCH posts USING INDEX idx_posts_lang_date (lang=?)

The statistics survive the journal switch and are read on a read-only open. The placement is settled too: before the checkpoint, otherwise the analysis writes land in the journal and have to be moved afterwards.

The cost claim deserves more than one data point as well. One millisecond is the price for a 2,676-row table. On the 25,690-row copy left over from the staleness experiment, the same ANALYZE took 25.1 ms — ten times the rows, twenty-five times the duration. Building histograms means a full scan, and the bill grows faster than linearly; at hundreds of thousands of rows the "run it on every build" advice needs re-measuring, while at my scale it is not worth a debate.

One more fear went onto the bench. Since the incremental pipeline copies a baseline, does a dropped index leave its statistics row haunting the file? Creating a temporary index, analyzing, then issuing DROP INDEX: the sqlite_stat1 row left with the index. SQLite does that cleanup itself; what goes stale is the content of the rows, not their existence.

Which suggests a monitoring rule: after migrate finishes, check that the sqlite_stat1 row count equals the index count. There is no other way to notice an ANALYZE that quietly did not run — no plan changes, so nothing gets slower, your insurance is simply cancelled.

If you want to repeat it: a ten-line rig

SQLite's own shell is enough to repeat this measurement on your database. Leave the live file alone and work on copies:

cp app.db /tmp/before.db && cp app.db /tmp/after.db

# 1) are there any statistics
sqlite3 /tmp/before.db \
  "select coalesce((select group_concat(name) from sqlite_schema
     where name like 'sqlite_stat%'),'NONE');"

# 2) let the planner say what it would do (without touching the app)
sqlite3 -readonly /tmp/before.db "pragma optimize(-1);"

# 3) capture the unanalyzed plan into a file
sqlite3 /tmp/before.db ".eqp on" "select ... ;" > /tmp/plan-before.txt

# 4) analyze, capture the same query again
sqlite3 /tmp/after.db "ANALYZE; select idx||' = '||stat from sqlite_stat1;"
sqlite3 /tmp/after.db ".eqp on" "select ... ;" > /tmp/plan-after.txt

# 5) decision: an empty diff means statistics change no decision here
diff /tmp/plan-before.txt /tmp/plan-after.txt

Keeping the plan output from .eqp on in a file in the repository has a second benefit: when the schema or a query changes, diff tells you the plan changed too. The plan lines from this run are now noted for comparison at the next schema change; wiring the same diff into continuous integration amounts to the same thing. Cases where adding an index quietly breaks a plan — a query losing its covering index, for instance — are only caught this way.

A decision framework

For anyone about to look at their own setup, in order:

1. Check whether they exist at all. select name from sqlite_schema where name like 'sqlite_stat%' — if that is empty, the planner has been working from its own assumptions rather than your measurements.
2. Record the plan rather than the clock. Pull the queries the application actually runs out of the code and write the EXPLAIN QUERY PLAN output to a file. That file becomes your regression test later.
3. Run ANALYZE, capture the plans again, diff them. If there is no difference, test the rig with a deliberate positive: index a single-valued column, watch the plan move, then drop the index.
4. Work out where write access lives. PRAGMA optimize cannot run against a read-only database; put the statistics step into whatever builds the database.
5. Re-run it on every build. When the cost is a millisecond, never having to ask "when did this go stale" is cheaper. In incremental pipelines that copy a baseline, statistics travel silently.
6. Refresh statistics when the schema changes. A new index increases the number of candidates the planner weighs — and that is the one case where statistics genuinely change a decision. The documentation calls out CREATE INDEX specifically.
7. Do not set a limit if you want histograms. With a skewed distribution, leave analysis_limit at zero, or sqlite_stat4 is never produced.
8. Verify that it ran. After the build step, compare the sqlite_stat1 row count against the index count; an ANALYZE that was silently skipped trips no alarm.

What is going into my own pipeline is items 4 and 5 of that list: one ANALYZE line at the end of the migrate script, before the journal mode is switched. I know by measurement that it changes none of today's plans. It is there for tomorrow, when a non-selective index joins the schema — and the assumption that an index helps every query is one of the costlier assumptions around — so the planner has counts in hand.

Conclusion

What this lab taught is not really about SQLite. Collecting statistics is not a performance tweak but insurance against the planner: the premium is one millisecond, and the risk covered is the bad index your schema will acquire later. Cancelling the policy because "nothing changed" treats the absence of a claim as evidence that no claim will come.

The second lesson is about measuring. Output showing that nothing changed is the least exciting result to publish and the most reassuring one to own — provided you first proved your rig could see a change. I no longer believe a "no difference" sentence of my own unless there is a deliberate positive next to it.

Official Sources

• SQLite — ANALYZE
• SQLite — PRAGMA optimize and analysis_limit
• SQLite — File format: sqlite_stat1 and sqlite_stat4
• better-sqlite3