# Postgres: query planner statistics ## Concept Every plan Postgres picks is a bet on a number it guessed. `EXPLAIN` prints that 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, and your query takes 1.3 seconds instead of 0.4 — 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 that matches 990,000 rows and one that matches 1,000; read the statistics `ANALYZE` writes down; then let those statistics go stale and watch a single wrong number cascade into two wrong plan decisions and a 12 MB temp file on disk. ## Mental model: what ANALYZE actually collects `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's the planner's entire picture of your data. Four things live there, and each one 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), and 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: - **Statistics are a snapshot, not a stream.** They describe the table as of the last `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. - **Every column is assumed independent of every other one.** Postgres estimates `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: ``` -> Seq Scan on events (cost=0.00..41347.48 rows=1 width=4) (actual rows=1000000 loops=1) ^^^^^^ ^^^^^^^^^^^^^^ the guess the truth ``` 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. ## Setup One terminal. Everything below assumes you have Docker. ```bash docker run --name pg-stats --rm -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16 docker exec -it pg-stats psql -U postgres ``` Create two tables — an `events` fact table with a deliberately skewed `status`, and a small `customers` table to join against: ```sql 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: ```sql SELECT status, count(*) FROM events GROUP BY status ORDER BY 2 DESC; ``` ``` status | count ----------+-------- archived | 990000 shipped | 9000 failed | 1000 ``` 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. Two settings before you start. First, in your session: ```sql SET max_parallel_workers_per_gather = 0; ``` This is purely for legibility. 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. Second, `autovacuum_enabled = off` on both tables is doing real work here. That 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. You can confirm it never fires: ```sql SELECT relname, last_autoanalyze FROM pg_stat_user_tables WHERE relname IN ('events', 'customers'); ``` Both `last_autoanalyze` values stay `NULL` for the entire exercise, through two million inserted rows. ## Steps ### Part 1 — a planner with no statistics at all 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. 1. Confirm the cupboard is bare: ```sql SELECT relname, relpages, reltuples FROM pg_class WHERE relname IN ('events', 'customers'); SELECT count(*) AS stats_rows FROM pg_stats WHERE tablename = 'events'; ``` ``` relname | relpages | reltuples -----------+----------+----------- customers | 0 | -1 events | 0 | -1 stats_rows ------------ 0 ``` `relpages = 0` and `reltuples = -1` are the "never analyzed" sentinels, and `pg_stats` has not one row about this table. 2. **Predict**: you're about to run the same query twice, changing only the literal — once for `'archived'` (990,000 rows) and once for `'failed'` (1,000 rows). What will the two `rows=` estimates be? 3. Run both: ```sql 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'; ``` 4. **Observe**: ``` Aggregate (cost=16183.93..16183.94 rows=1 width=8) (actual rows=1 loops=1) -> Seq Scan on events (cost=0.00..16177.00 rows=2773 width=0) (actual rows=990000 loops=1) Filter: (status = 'archived'::text) Rows Removed by Filter: 10000 Aggregate (cost=16183.93..16183.94 rows=1 width=8) (actual rows=1 loops=1) -> Seq Scan on events (cost=0.00..16177.00 rows=2773 width=0) (actual rows=1000 loops=1) Filter: (status = 'failed'::text) Rows Removed by Filter: 999000 ``` **`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. Where does `2773` come from? With no MCV list, an equality predicate falls back to the hardcoded constant `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 (run a bare `EXPLAIN SELECT * FROM events;` to see it). 0.005 × 554,640 = 2,773. Two guesses stacked, and one of them is a literal magic number. Note the row-count guess is at least in the right ballpark (554k vs. 1M) — Postgres always checks the physical file size, so it is never *wildly* wrong about how big a table is. It is the *distribution within* the table that it knows nothing about, and that is what `ANALYZE` supplies. ### Part 2 — what ANALYZE writes down What this part tests: the four statistics in the mental-model table, read directly out of `pg_stats` the moment they come into existence. 5. Run it, verbosely: ```sql ANALYZE VERBOSE events; ANALYZE customers; ``` ``` INFO: analyzing "public.events" INFO: "events": scanned 9244 of 9244 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows ``` `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. 6. Look at what it produced for the skewed column: ```sql \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 off ``` ``` -[ RECORD 1 ]-----+-------------------------- attname | status null_frac | 0 n_distinct | 3 most_common_vals | {archived,shipped,failed} most_common_freqs | {0.9903,0.0086,0.0011} correlation | 0.98001444 ``` True 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. 7. Now the whole table at a glance — this one query is the habit worth keeping: ```sql 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; ``` ``` attname | n_distinct | correlation | n_mcv | n_hist -------------+------------+-------------+-------+-------- city | 1000 | 0.010646787 | 8 | 101 country | 100 | 0.018614726 | 100 | customer_id | 19913 | 0.019655457 | | 101 id | -1 | 1 | | 101 status | 3 | 0.98001444 | 3 | ``` 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. 8. **Predict**: re-run Part 1's two queries now. Both estimates change — but does the planner get *both* the 990,000 and the 1,000 right? 9. **Observe**: ``` -> Seq Scan on events (cost=0.00..21744.00 rows=990300 width=0) (actual rows=990000 loops=1) Filter: (status = 'archived'::text) -> Seq Scan on events (cost=0.00..21744.00 rows=1100 width=0) (actual rows=1000 loops=1) Filter: (status = 'failed'::text) ``` `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. ### Part 3 — stale statistics, and the plan they buy 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. 10. A batch job lands a million new events, all in a status the table has never seen before: ```sql 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`. 11. **Predict**: `'pending'` is not in the MCV list, since it didn't exist when `ANALYZE` ran, and `n_distinct` says there are only 3 values — all 3 of which are already in the MCV list, accounting for 100% of the table. So what selectivity is left for a fourth value? Estimate the `rows=` for `WHERE status = 'pending'`. 12. Run it: ```sql EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT count(*) FROM events WHERE status = 'pending'; ``` 13. **Observe**: ``` Aggregate (cost=41347.48..41347.49 rows=1 width=8) (actual rows=1 loops=1) -> Seq Scan on events (cost=0.00..41347.48 rows=1 width=0) (actual rows=1000000 loops=1) Filter: (status = 'pending'::text) Rows Removed by Filter: 1000000 ``` **`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. 14. **Predict**: `rows=1` is only an annotation until something depends on it. Now join to `customers` and aggregate. If the planner believes one row will come out of that scan, which join algorithm looks cheapest — and what does it cost when a million rows actually arrive? ```sql 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; ``` 15. **Observe** (run it twice; the second run is warm): ``` GroupAggregate (cost=41355.79..41355.81 rows=1 width=17) (actual time=1133.075..1269.498 rows=8 loops=1) Group Key: c.region Buffers: shared hit=3016037 read=1544, temp read=1593 written=1605 -> Sort (cost=41355.79..41355.80 rows=1 width=9) (actual time=1115.167..1209.161 rows=1000000 loops=1) Sort Key: c.region Sort Method: external merge Disk: 12744kB -> Nested Loop (cost=0.29..41355.78 rows=1 width=9) (actual time=45.598..857.171 rows=1000000 loops=1) -> Seq Scan on events e (cost=0.00..41347.48 rows=1 width=4) (actual time=45.581..151.555 rows=1000000 loops=1) Filter: (status = 'pending'::text) Rows Removed by Filter: 1000000 -> Index Scan using customers_pkey on customers c (cost=0.29..8.30 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=1000000) Index Cond: (id = e.customer_id) Buffers: shared hit=3000000 Execution Time: 1270.444 ms ``` One wrong number, two wrong decisions: - **Nested Loop.** For a single outer row, one index probe into `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. - **GroupAggregate with a Sort.** For one row, sorting is free, and a sort avoids building a hash table. So it sorts — and a million rows don't fit in the default 4 MB `work_mem`, giving `Sort Method: external merge Disk: 12744kB`. A misestimate wrote 12 MB of temp files to disk. 16. **Predict**: fix it *without touching the query*. Run `ANALYZE events;` and re-run the identical SQL. What changes? 17. Run: ```sql ANALYZE events; SELECT most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = 'events' AND attname = 'status'; ``` ``` most_common_vals | most_common_freqs -----------------------------------+------------------------------------------ {pending,archived,shipped,failed} | {0.4992,0.49596667,0.0046,0.00023333334} ``` `pending` is now the *first* entry, at 49.9%. 18. **Observe** — same query, same text, same indexes: ``` HashAggregate (cost=50789.36..50789.44 rows=8 width=17) (actual time=374.382..374.384 rows=8 loops=1) Group Key: c.region Batches: 1 Memory Usage: 24kB Buffers: shared hit=16146 read=1580 -> Hash Join (cost=598.00..45797.36 rows=998400 width=9) (actual time=50.523..272.401 rows=1000000 loops=1) Hash Cond: (e.customer_id = c.id) -> Seq Scan on events e (cost=0.00..42578.00 rows=998400 width=4) (actual time=47.083..129.153 rows=1000000 loops=1) Filter: (status = 'pending'::text) Rows Removed by Filter: 1000000 -> Hash (cost=348.00..348.00 rows=20000 width=13) (actual time=3.359..3.360 rows=20000 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1194kB -> Seq Scan on customers c (cost=0.00..348.00 rows=20000 width=13) (actual time=0.014..1.401 rows=20000 loops=1) Execution Time: 374.473 ms ``` | | 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. ### Part 4 — two right answers that multiply into a wrong one 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. 19. Check each predicate on its own first: ```sql 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'; ``` ``` -> Seq Scan on events (cost=0.00..42578.00 rows=1990 width=0) (actual rows=2000 loops=1) Filter: (city = 'city-7'::text) -> Seq Scan on events (cost=0.00..42578.00 rows=18467 width=0) (actual rows=20000 loops=1) Filter: (country = 'country-0'::text) ``` 1990 vs. 2000, and 18467 vs. 20000. Both within 8%. The statistics on these columns are fine. 20. **Predict**: `city = 'city-7'` matches 2,000 rows. Every one of those rows *is* in `country-0`, so adding `AND country = 'country-0'` removes nothing — the true answer is still 2,000. What will the planner estimate? 21. Run: ```sql EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT count(*) FROM events WHERE city = 'city-7' AND country = 'country-0'; ``` 22. **Observe**: ``` -> Seq Scan on events (cost=0.00..47578.00 rows=18 width=0) (actual rows=2000 loops=1) Filter: ((city = 'city-7'::text) AND (country = 'country-0'::text)) Rows Removed by Filter: 1998000 ``` **`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. 23. **Predict**: the fix is `CREATE STATISTICS`, 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)`? 24. Run: ```sql CREATE STATISTICS events_geo (dependencies) ON city, country FROM events; ANALYZE events; SELECT statistics_name, attnames, dependencies FROM pg_stats_ext WHERE tablename = 'events'; ``` 25. **Observe**: ``` statistics_name | attnames | dependencies -----------------+----------------+---------------------- events_geo | {city,country} | {"4 => 5": 1.000000} ``` 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.) 26. Re-run the same two-predicate query: ``` -> Seq Scan on events (cost=0.00..47578.00 rows=1989 width=0) (actual rows=2000 loops=1) Filter: ((city = 'city-7'::text) AND (country = 'country-0'::text)) ``` `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. ### Part 5 — how much detail is ANALYZE keeping? 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. 27. The current resolution, at the default target of 100: ```sql 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; ``` ``` attname | n_distinct | n_mcv | n_hist -------------+------------+-------+-------- city | 1000 | 11 | 101 customer_id | 20032 | 2 | 101 ``` 1000 cities, of which 11 are individually tracked. `customer_id` is estimated at 20,032 distinct; the true value is exactly 20,000. 28. **Predict**: raise the target 10× on these two columns. `ANALYZE` will sample 300,000 rows instead of 30,000. What improves — the MCV list, `n_distinct`, or the row estimates? 29. Run: ```sql ALTER TABLE events ALTER COLUMN city SET STATISTICS 1000; ALTER TABLE events ALTER COLUMN customer_id SET STATISTICS 1000; ANALYZE VERBOSE events; ``` ``` INFO: "events": scanned 17578 of 17578 pages, containing 2000000 live rows and 0 dead rows; 300000 rows in sample, 2000000 estimated total rows ``` 30. **Observe**: ``` attname | n_distinct | n_mcv | n_hist -------------+------------+-------+-------- city | 1000 | 1000 | customer_id | 20000 | 104 | 1001 ``` 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. 31. Now check what it bought for the row estimate that motivated all this: ```sql EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF) SELECT count(*) FROM events WHERE city = 'city-7'; ``` ``` -> Seq Scan on events (cost=0.00..42578.00 rows=2047 width=0) (actual rows=2000 loops=1) ``` **`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. That is the honest lesson, and it's the opposite of the folklore. A bigger statistics target buys resolution, and resolution only helps where there is *structure* too fine for 100 buckets to capture: a long-tailed column where the 150th most common value is still 100× more frequent than average, or a `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. ## What you should see 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. ## Why 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 words 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. The `rows=1` floor deserves its own note, because it is where the design turns adversarial. Postgres clamps every estimate to at least one row, so the *most* wrong an estimate can be on the low side is unbounded while the plan it produces looks maximally attractive: nested loops, index probes, no hash tables, no sorting — all the plans that are brilliant for one row and ruinous for a million. Underestimation is therefore much more dangerous than overestimation, and it's why `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. ## Go deeper - Ask the planner what it *would* have done. `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 the answer is that a bad estimate happened to pick the right plan anyway. - Override `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. - Extend Part 4: `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. - Turn autovacuum back on (`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. - Re-read Part 1's plans with `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. ## Cleanup ```bash docker stop pg-stats ```