Every plan Postgres picks is a bet on a number it guessed. Watch the planner give the identical estimate for 990,000 rows and 1,000 rows, then watch a single stale number turn a 374 ms query into a 1270 ms one — without a character of the SQL changing.
EXPLAIN prints the planner's guess as rows=; EXPLAIN ANALYZE prints the truth next to it as actual rows=. When the two agree, the plan is usually good. When they disagree by a factor of a million, the planner chooses a Nested Loop where a Hash Join belonged — with the SQL text, the schema, the indexes and the data all completely unchanged.
This exercise is about where that guess comes from. You'll watch the planner give the identical row estimate for a value matching 990,000 rows and one matching 1,000; read the statistics ANALYZE writes down; then let those statistics go stale and watch one wrong number cascade into two wrong plan decisions and a 12 MB temp file on disk.
ANALYZE takes a random sample of the table — 300 rows per unit of default_statistics_target, so 30,000 rows at the default of 100, regardless of whether the table has a million rows or a billion — and distills it into a handful of columns in the pg_stats view. That is the planner's entire picture of your data. Four things live there, and each rescues a different query shape:
| Statistic | What it holds | The query shape it rescues |
|---|---|---|
| n_distinct | Number of distinct values. Positive = an absolute count; negative = a ratio of the row count (-1 means "every value unique", and it stays correct as the table grows). |
GROUP BY col — how many groups to expect, and therefore how much hash memory. Also equality on any value the MCV list didn't capture: the estimate is just rows / n_distinct. |
| most_common_vals + most_common_freqs |
The top N values by frequency (N = the statistics target) and each one's measured share of the table. | WHERE col = 'x' on a skewed column. This is the only thing that lets the planner know 'archived' is 99% of the table and 'failed' is 0.1%. Without it, every value looks equally likely. |
| histogram_bounds | N+1 boundary values dividing the non-MCV rows into equal-population buckets. | Range predicates: WHERE col > 500, BETWEEN, <=. The planner interpolates within a bucket. |
| correlation | How closely the column's sort order matches physical row order on disk, from -1 to 1. |
Not a row estimate at all — a cost input. Near 1 means an index scan reads the heap almost sequentially, so index scans get cheap; near 0 means random I/O, so the planner leans toward a seq scan or a bitmap scan. |
Two things this table does not cover, and both bite in practice:
ANALYZE. Nothing recomputes them on write. In production the autovacuum daemon runs an "autoanalyze" once ~10% of the table has changed — which means a bulk load can leave a table's statistics describing a completely different table for as long as it takes that threshold to trip. Part 3 is that window.
WHERE a = 1 AND b = 2 by multiplying the two selectivities. When a and b are correlated, that multiplication is wrong by however correlated they are — and both individual estimates can be perfect while the combined one is off by 100×. Part 4 is that, and its fix is a fifth kind of statistic: CREATE STATISTICS, which you must ask for by name.
Finally, how to read the output. In EXPLAIN ANALYZE, every node prints both numbers:
Reading a plan is mostly scanning down the tree for the first node where those two diverge. Everything above it is built on that error.
One terminal. Everything below assumes you have Docker.
docker run --name pg-stats --rm -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16
docker exec -it pg-stats psql -U postgresCreate two tables — an events fact table with a deliberately skewed status, and a small customers table to join against:
CREATE TABLE customers (
id int PRIMARY KEY,
name text,
region text
) WITH (autovacuum_enabled = off);
CREATE TABLE events (
id bigint,
customer_id int,
status text,
city text,
country text
) WITH (autovacuum_enabled = off);
INSERT INTO customers
SELECT g, 'customer-' || g, 'region-' || (g % 8)
FROM generate_series(1, 20000) g;
INSERT INTO events
SELECT g,
1 + (g % 20000),
CASE WHEN g % 1000 = 0 THEN 'failed'
WHEN g % 100 = 0 THEN 'shipped'
ELSE 'archived' END,
'city-' || (g % 1000),
'country-' || ((g % 1000) / 10)
FROM generate_series(1, 1000000) g;The skew, which is the whole point:
SELECT status, count(*) FROM events GROUP BY status ORDER BY 2 DESC;Also note city and country: there are 1000 cities across 100 countries, and a city's name determines its country (city-7 is always in country-0). That's Part 4's material.
One setting before you start, purely for legibility:
SET max_parallel_workers_per_gather = 0;With parallelism on, a Seq Scan node reports rows per worker (rows=1155 ... actual rows=330000 loops=3) and you have to multiply in your head. Turning it off makes every number in this exercise a whole-query total. It changes none of the conclusions.
autovacuum_enabled = off storage parameter turns off autoanalyze as well as autovacuum — and autoanalyze is precisely the thing that would notice your bulk insert, quietly refresh the statistics behind your back, and delete the effect you're trying to observe. This is a lab setting only — never do this to a real table; in production autoanalyze is exactly what you want running. Confirm it never fires with SELECT relname, last_autoanalyze FROM pg_stat_user_tables WHERE relname IN ('events','customers'); — both stay NULL for the entire exercise, through two million inserted rows.
What this part tests: the claim in the mental model that the MCV list is "the only thing that lets the planner know 'archived' is 99% of the table and 'failed' is 0.1%". You've inserted a million rows but never run ANALYZE, so there is no MCV list. Watch what the planner does instead.
SELECT relname, relpages, reltuples FROM pg_class
WHERE relname IN ('events', 'customers');
SELECT count(*) AS stats_rows FROM pg_stats WHERE tablename = 'events';relpages = 0 and reltuples = -1 are the "never analyzed" sentinels, and pg_stats has not one row about this table.
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT count(*) FROM events WHERE status = 'archived';
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT count(*) FROM events WHERE status = 'failed';'archived' matches 990,000 rows and 'failed' matches 1,000. What will the two rows= estimates be? Click to check.rows=2773 both times. Not approximately the same — byte-for-byte identical, down to the cost estimate. One is wrong by 357× low, the other by 2.8× high, and the planner has no way to tell them apart, because as far as it knows status is a column of unknown contents and 'archived' and 'failed' are two opaque strings.
DEFAULT_EQ_SEL = 0.005 — the planner's blind guess that any = matches 0.5% of the table. And the table size is also guessed: with reltuples = -1 Postgres reads the file's actual page count and assumes an average row width, arriving at 554,640 rows (a bare EXPLAIN SELECT * FROM events; shows it). 0.005 × 554,640 = 2,773. Two guesses stacked, one of them a literal magic number.
ANALYZE supplies.
What this part tests: the four statistics in the mental-model table, read directly out of pg_stats the moment they come into existence.
ANALYZE VERBOSE events;
ANALYZE customers;30000 rows in sample — 300 × default_statistics_target (100), the number from the top of the mental model, right there in the log. Note also 1000000 estimated total rows: even the row count is called an estimate, because on a large table ANALYZE extrapolates from the sampled pages rather than counting.
\x on
SELECT attname, null_frac, n_distinct, most_common_vals,
most_common_freqs, correlation
FROM pg_stats WHERE tablename = 'events' AND attname = 'status';
\x offTrue frequencies are 0.990 / 0.009 / 0.001. From a 3% sample, the planner now has them to three decimal places. This pair of arrays is the fix for Part 1.
This one query is the habit worth keeping:
SELECT attname, n_distinct, correlation,
array_length(most_common_vals, 1) AS n_mcv,
array_length(histogram_bounds, 1) AS n_hist
FROM pg_stats WHERE tablename = 'events' ORDER BY attname;Five columns, five different shapes, and every cell is the mental-model table in miniature:
id: n_distinct = -1 — the negative encoding, meaning "distinct values = 100% of rows". Stored as a ratio precisely so it survives the table doubling in Part 3 without going stale. And correlation = 1: id was inserted in order, so it is perfectly correlated with physical layout, and an index scan on it would read the heap sequentially.status and country: no histogram at all. With 3 and 100 distinct values, the MCV list covers every value that exists, so there is no residue left to bucket. Histograms only describe the non-MCV tail.city: 1000 distinct, only 8 made the MCV list (they're all equally common, so the sample found no clear winners), and the other ~992 are summarised by a 101-boundary histogram.customer_id: 19913 — the true answer is 20,000. n_distinct is the hardest statistic to estimate from a sample, and Postgres is openly approximate about it. Part 5 returns to this.rows=990300 vs. 990,000, and rows=1100 vs. 1,000. Both within 10%, from a sample of 3% of the table. This is the planner working correctly, and it is what "good statistics" looks like: two estimates that differ from each other by three orders of magnitude, because the data does.
What this part tests: the first caveat under the mental-model table — statistics are a snapshot. The statistics you just admired are accurate. Now the table changes underneath them, exactly as it would during a bulk load or a backfill, and nothing recomputes anything.
A million new events, all in a status the table has never seen before:
INSERT INTO events
SELECT 1000000 + g, 1 + (g % 20000), 'pending',
'city-' || (g % 1000), 'country-' || ((g % 1000) / 10)
FROM generate_series(1, 1000000) g;The table is now 2,000,000 rows, exactly half of them 'pending'. Do not run ANALYZE.
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT count(*) FROM events WHERE status = 'pending';'pending' is not in the MCV list — it didn't exist when ANALYZE ran — and n_distinct says there are only 3 values, all 3 already in the MCV list, accounting for 100% of the table. So what selectivity is left for a fourth value? Click to check.rows=1 … actual rows=1000000. Six orders of magnitude. This is the arithmetic in the mental model running off a cliff: the selectivity for a value absent from the MCV list is (1 − Σ mcv_freqs) / (n_distinct − n_mcv), and here the MCV frequencies summed to essentially 1.0 while n_distinct − n_mcv is 0. There is no probability mass left to allocate. Postgres clamps the result to its floor: one row.
Notice what did not go wrong. The planner knows the table doubled — the cost rose from 21744 to 41347 because it re-checked the file size and scaled reltuples by the new page count. Table size self-heals; value distribution does not.
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.region, count(*)
FROM events e JOIN customers c ON c.id = e.customer_id
WHERE e.status = 'pending'
GROUP BY c.region;One wrong number, two wrong decisions:
customers is unbeatable — building a hash table over 20,000 customers to join one row would be absurd. So the planner picks it, and then executes loops=1000000. That Buffers: shared hit=3000000 on the inner node is the damage in its purest form: exactly 3 buffer accesses per probe (root, leaf, heap page) × a million probes.work_mem, giving Sort Method: external merge Disk: 12744kB. A misestimate wrote 12 MB of temp files to disk.ANALYZE events;
SELECT most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'events' AND attname = 'status';pending is now the first entry, at 49.9%.
| stale statistics | after ANALYZE | |
|---|---|---|
| scan estimate | rows=1 (actual 1,000,000) | rows=998400 (actual 1,000,000) |
| join | Nested Loop, 1,000,000 loops | Hash Join, one pass |
| grouping | GroupAggregate + Sort | HashAggregate, 24 kB |
| temp files | 12,744 kB spilled to disk | none |
| buffer accesses | 3,016,037 | 16,146 |
| wall clock | 1270 ms | 374 ms |
3.4× faster wall-clock, and — the more transferable number, since it doesn't depend on your laptop — 187× fewer buffer accesses. The only thing that changed is one row in a statistics catalog.
What this part tests: the second caveat under the mental-model table. Everything so far has been about a single column, where good statistics gave good estimates. Here both columns have excellent statistics and the estimate is still catastrophically wrong, because the error isn't in either column — it's in the assumption connecting them.
city and country are functionally dependent: a city-N is always in country-(N/10). Knowing the city tells you the country for free. The planner does not know that.
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT count(*) FROM events WHERE city = 'city-7';
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT count(*) FROM events WHERE country = 'country-0';1990 vs. 2000, and 18467 vs. 20000. Both within 8%. The statistics on these columns are fine.
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT count(*) FROM events
WHERE city = 'city-7' AND country = 'country-0';city = 'city-7' matches 2,000 rows, and every one of them is in country-0, so the second predicate removes nothing — the true answer is still 2,000. What will the planner estimate? Click to check.rows=18, actual 2,000. 111× too low, built entirely out of two accurate estimates. Do the arithmetic the planner did: selectivity of the city predicate is 1990/2,000,000 ≈ 0.000995, of the country predicate 18467/2,000,000 ≈ 0.00923. It multiplies them — 0.000995 × 0.00923 × 2,000,000 ≈ 18 — because the cost model assumes independent columns. Here the second predicate has a true conditional selectivity of 1.0 given the first, and the planner applied 0.009.
This is the failure mode that no amount of ANALYZE will fix, because nothing about it is stale. It's an error in the model, not the data. And it compounds: three correlated predicates multiply three times.
CREATE STATISTICS events_geo (dependencies) ON city, country FROM events;
ANALYZE events;
SELECT statistics_name, attnames, dependencies FROM pg_stats_ext
WHERE tablename = 'events';CREATE STATISTICS is a multivariate statistics object you must request by name — Postgres will never build one on its own, because tracking every column pair would be quadratic. What do you expect it to store about (city, country)? Click to check.One number. 4 => 5 is by attribute number — column 4 (city) determines column 5 (country) — and 1.000000 is the degree of that dependency, on a 0-to-1 scale. A perfect functional dependency, so the planner can now apply the country predicate's selectivity as 1.0 instead of 0.009. A partial dependency like 0.7 gets partially applied; this isn't an on/off switch.
Re-run the same two-predicate query:
rows=18 → rows=1989, against an actual 2,000. The query is unchanged, the data is unchanged, and the statistics on the individual columns are unchanged. All that changed is that the planner was told the two columns are related.
What this part tests: default_statistics_target, the dial controlling both the sample size and the length of the MCV list and histogram. The received wisdom is "raise it when estimates are bad". This part is worth doing because the result is not what that advice implies.
SHOW default_statistics_target;
SELECT attname, n_distinct,
array_length(most_common_vals, 1) AS n_mcv,
array_length(histogram_bounds, 1) AS n_hist
FROM pg_stats WHERE tablename = 'events'
AND attname IN ('customer_id', 'city') ORDER BY attname;1000 cities, of which 11 are individually tracked. customer_id is estimated at 20,032 distinct; the true value is exactly 20,000.
ALTER TABLE events ALTER COLUMN city SET STATISTICS 1000;
ALTER TABLE events ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE VERBOSE events;ANALYZE just sampled 300,000 rows instead of 30,000. What improves — the MCV list, n_distinct, or the row estimates? Click to check.Two real gains. city now has every one of its 1000 values in the MCV list — and consequently no histogram at all, because once the MCV list covers the entire column there is no tail left to bucket (exactly the status/country shape from Part 2, arrived at by turning a dial). And customer_id's n_distinct went from 20,032 to exactly 20,000, which matters for GROUP BY customer_id: the planner now sizes its hash table correctly.
Now check what it bought for the row estimate that motivated all this:
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT count(*) FROM events WHERE city = 'city-7';rows=1990 → rows=2047. Ten times the sample, ten times the ANALYZE cost, ten times the planning-time work of scanning that MCV array — and the estimate got very slightly worse. Both are within 3% of the truth; the difference is sampling noise.
GROUP BY whose hash sizing depends on n_distinct. On a uniform column — this one, by construction — it buys nothing but a slower ANALYZE. Raise it per-column, in response to a specific bad estimate you have measured, never globally on reflex.
A planner with no statistics giving the identical rows=2773 estimate for a value matching 990,000 rows and one matching 1,000 — the same number twice, because a hardcoded 0.005 is all it has. Then ANALYZE, and those estimates separating to 990,300 and 1,100 against actuals of 990,000 and 1,000.
Then a bulk insert going in behind the statistics' back, producing rows=1 ... actual rows=1000000 — and that one number driving the planner into a Nested Loop executed a million times (3,016,037 buffer accesses) and a sort that spills 12 MB to disk, 1270 ms in total. A bare ANALYZE — no change to the query, the schema, or the indexes — turning it into a Hash Join and a HashAggregate: 16,146 buffer accesses, no temp files, 374 ms.
Then the failure ANALYZE can't fix: two predicates estimated at 1990 and 18467 against actuals of 2000 and 20000, whose conjunction is estimated at 18 against an actual 2000, because the planner multiplied two selectivities that were not independent. CREATE STATISTICS ... (dependencies) records a single number — "4 => 5": 1.000000 — and the estimate becomes 1989.
And a control result: raising default_statistics_target from 100 to 1000 moves a row estimate from 1990 to 2047, i.e. nowhere, while making ANALYZE sample ten times as much data.
Because a query planner is a cost minimiser, and every cost it computes is a function of a row count it cannot know without running the query. So it estimates — and the estimate is built from a fixed-size random sample taken at some point in the past. Both of those phrases are load-bearing.
Fixed-size is why the sample is 30,000 rows whether the table has a million rows or a billion: statistics have to be cheap to maintain, so they buy accuracy on frequencies (which converge fast with sample size) at the cost of accuracy on cardinalities (n_distinct, which converges slowly and is why Part 5 showed 20,032 instead of 20,000).
In the past is Part 3. There is no incremental maintenance of a histogram; nothing on the write path touches pg_statistic. The reconciliation is a background job with a threshold, and between the write and the threshold tripping, the planner is confidently reasoning about a table that no longer exists. Every "the query was fast yesterday" incident after a bulk load is this.
rows=1 next to a big actual rows is the single most valuable pattern to recognise in an EXPLAIN ANALYZE.
Part 4 is a different species of wrong. Nothing is stale and no sample is too small; the model is wrong. Multiplying selectivities is the textbook independence assumption, and it is chosen because the alternative — storing joint distributions for every combination of columns — is combinatorially impossible to do by default. So Postgres does the cheap thing universally and offers CREATE STATISTICS as an opt-in for the specific column groups you know are related. That's a deliberate trade: the database will be wrong about your correlated columns until you tell it they're correlated.
SET enable_nestloop = off; before Part 3's stale-statistics join and re-run: the plan flips to a Hash Join even with rows=1, and you can compare the two execution times under identical (wrong) statistics. This separates "the estimate was wrong" from "the wrong estimate cost me time" — occasionally a bad estimate happens to pick the right plan anyway.n_distinct by hand: ALTER TABLE events ALTER COLUMN customer_id SET (n_distinct = -0.5); then ANALYZE. Postgres will use your value instead of its sampled one. This is the real fix for the sampling weakness in Part 5 — you often know a column's true cardinality (or its true ratio, which is what the negative form encodes) when the sampler can't find it.CREATE STATISTICS ... (ndistinct) instead of (dependencies) and run GROUP BY city, country. The independence assumption breaks the group count estimate the same way it broke the row estimate — the planner predicts 1000 × 100 = 100,000 groups where there are only 1000 — and ndistinct statistics are the fix. Then try (mcv), which stores a joint MCV list and handles inequality and OR predicates that dependencies can't.ALTER TABLE events SET (autovacuum_enabled = on)), insert another large batch, and poll SELECT last_autoanalyze, n_mod_since_analyze FROM pg_stat_user_tables WHERE relname = 'events'; until it fires. The threshold is autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × reltuples — 50 + 10% by default. Watching how many rows have to change before the daemon reacts is the concrete answer to "how long is my stale-statistics window in production?", and on a large table the answer is alarming.max_parallel_workers_per_gather back at its default and note that rows= and actual rows= on nodes below a Gather are per worker — a detail that has caused many people to believe an estimate was 3× worse than it was.Sources: PostgreSQL Docs: Statistics Used by the Planner · Row Estimation Examples, which works the selectivity arithmetic by hand · CREATE STATISTICS for the three kinds of extended statistics
docker stop pg-stats