Statistics and the cost model
Per-column table statistics collected by ANALYZE, the stated cost model the planner ranks by, and joins that choose probing against building on the two sides' counts.
The planner has always ranked the paths a read could take. This page is what it knows today and where that knowledge comes from: statistics the engine collects itself, a cost model stated in named constants, and a plan that reports the freshness of the one number in it that may be wrong.
What exists, and where it lives
Two kinds of statistics, one discipline. Index statistics (since 0.22) summarise each plain value index: how many entries it holds, its most common values, equi-depth buckets over its first field. Table statistics (0.41) summarise each declared field of the table itself — for the questions an index cannot answer, a predicate on a field nothing indexes.
Both are the node's own. The walk that takes them reads this node's copy, the summary sits beside the data it describes, and nothing travels in the log or a backup: a replica walks its own entries and keeps its own summary. Two nodes with different statistics answer the same read with the same records — a statistic chooses a path and never an answer.
ANALYZE TABLE orders;
-- [{ index: 'by_city', entries: 50000, … }, { field: 'city', entries: 49750,
-- nulls: 250, distinct: 600, common: 16, buckets: 64 }, …]
INFO FOR TABLE orders;
-- the same, held, as counts and an age — never a counted value
Freshness, as a number a reader can see
Every write moves a change counter in the same commit batch as the records it
counts. A statistic is trusted while what changed since the walk is at most a
tenth of what it counted, or a thousand changes, whichever is more; past that
it is set aside and the planner counts, exactly as it did before statistics
existed. EXPLAIN reports the age beside any estimate that came from
statistics:
EXPLAIN SELECT * FROM orders WHERE city = 'tromsø';
-- { access: 'index', index: 'by_city', estimate: 3, estimated_by: 'statistics',
-- statistics_age: 0, … }
A serving node refreshes stale statistics itself, a few per pass — ANALYZE TABLE is how to have them now, after a bulk load or before measuring a plan.
The cost model, stated
The comparison the planner makes is a ratio between two costs the store can name: an index read costs an entry walk plus a record fetch for each record it names; a scan costs a record fetch per record. The thresholds are named constants with their units and rationale, not unnamed magic: an index is served up to a stated fraction of its table, an estimate inside a stated band of that fraction decides alone, and between the band's edges the planner pays a bounded probe. They are stated, not calibrated — the fractions are documented judgements a benchmark pass owes a measurement, and the model is built so a wrong estimate bounds its damage rather than chasing accuracy: a stale statistic is set aside, a missing measurement never overrules the ranking, and every estimate travels with where it came from.
Joins choose on the counts
Where both sides' row counts are known, a join weighs probing the right side's
key index per left record against scanning it once into the ordered map.
Probing costs the driving side's count times an entry walk plus a fetch;
building costs the right side's count times a fetch — so probing pays only when
the right side holds more than the stated factor times the left. Below that,
the map is built even though the index exists, and the answer is the one the
operator names either way. A FULL join never probes — it must read every
right record to answer the unmatched ones — and a driving side that is a read
or a join, not a table, keeps the index rule because no count exists to weigh.
EXPLAIN SELECT * FROM users JOIN orders ON users.name = orders.who;
-- { access: 'join', shape: 'probe', index: 'by_who', left_rows: 60000,
-- right_rows: 150000, … }
The search the choice is made in is bounded by construction: the statement's shape fixes the nesting, each join node costs the planner exactly one decision over the two counts, and no ordering of the tables is generated or compared.