ANALYZE and Table Statistics

Why this stage exists

The stages so far chose plans by rule: an index whenever the WHERE lets you use one. That rule is wrong as often as it is right. An index on city is a gift for city = 'Oslo' in a table of a thousand cities, and a trap in a table where every row says 'Oslo': every row it finds costs a descent of the table, and a full scan would have read each page once.

To tell the two apart, the planner needs to know things about the data: how many rows, how many pages, how many different cities. Nothing knows that unless something counts it. That is ANALYZE. This stage counts and stores; the next one uses the numbers.

What is counted

>analyze t;
Analyze statement executed successfully
>stats t
[STATS] t rows=1000 pages=42
[STATS]   id ndv=1000 min=1 max=1000 nulls=0
[STATS]   city ndv=10 min='c0' max='c9' nulls=5
[STATS]   age ndv=100 min=0 max=99 nulls=0

Counting distinct values is the one part that needs memory: you have to remember which values you have seen. A set of the values is fine at this size. Real engines estimate it from a sample instead, because a set of a billion strings does not fit anywhere.

Never automatic

ANALYZE runs when someone types it, and at no other time. Insert a thousand more rows and stats t still says 1000. Delete every row and it still says 1000. This is deliberate. Statistics that change behind your back make plans change behind your back, and a test, or a person reading explain, could never predict which plan a query gets. The cost-model stage shows the price of the other side: a plan chosen from stale numbers.

Statistics are kept in the catalog next to the table they describe. So they survive reopening, ROLLBACK takes back an ANALYZE run inside the transaction, and DROP TABLE takes them with the table.

Output contract for this stage

These are the exact lines the tests check. The full definition is on the output contract page, section 59.

analyze is a keyword. explain takes no ANALYZE statement: explain analyze will mean something else in the explain-analyze stage.

What you should see when done

$droid --db stats.db
Welcome to droid!
>create table t (id int, city varchar(10));
Create table statement executed successfully
>insert into t (id, city) values (1, 'oslo');
Insert statement executed successfully (1 rows)
>insert into t (id, city) values (2, 'oslo');
Insert statement executed successfully (1 rows)
>insert into t (id) values (3);
Insert statement executed successfully (1 rows)
>stats t
[STATS] t no statistics
>analyze t;
Analyze statement executed successfully
>stats t
[STATS] t rows=3 pages=1
[STATS]   id ndv=3 min=1 max=3 nulls=0
[STATS]   city ndv=1 min='oslo' max='oslo' nulls=1
>insert into t (id, city) values (4, 'rome');
Insert statement executed successfully (1 rows)
>stats t
[STATS] t rows=3 pages=1
[STATS]   id ndv=3 min=1 max=3 nulls=0
[STATS]   city ndv=1 min='oslo' max='oslo' nulls=1
>.exit
exiting.. good bye!

Common bugs in this stage

• Counting NULL as a distinct value, or letting it be the minimum. NULLs are counted only in nulls.
• Comparing strings for min and max with a locale. Use the byte order of the index stage, so 'B' is below 'a'.
• Updating statistics on INSERT. They change only when ANALYZE runs.
• Keeping statistics in memory. They vanish on reopen, and survive a ROLLBACK they should not.
• Counting only leaves for pages. Internal nodes and overflow pages are pages of the tree too.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: for distinct values, sort a copy of the column's values and count the changes; it needs no hash table.
• C++: a std::set per column, or sort a std::vector and use std::unique.
• Rust: a BTreeSet<Value> per column orders and deduplicates in one go.
• Zig: sort a slice with std.sort.pdq and count where neighbours differ.