When estimates go wrong · PostgreSQL

The planner is not intelligent and does not need to be. It is arithmetic over statistics, and when its plans are bad the statistics were wrong.

This lesson is about finding out which statistic, because the four common causes have four different remedies and applying the wrong one is how a query stays slow through three rounds of tuning.

The measurement

Two columns, a and b, both computed as g % 100, so a = 7 implies b = 7 and exactly 1,000 rows of 100,000 match. The planner assumes the columns are independent.

Read-only / Safethe plan before extended statistics— Captured on 18.6. Read the Seq Scan line: estimated 12, actual 1000.

$ psql -U postgres -c "EXPLAIN (ANALYZE, BUFFERS, COSTS ON, TIMING ON, SUMMARY ON) SELECT count(*) FROM ev JOIN dim ON dim.id=ev.dim_id WHERE dim.a=7 AND dim.b=7"

 ->  Seq Scan on dim  (cost=0.00..2041.00 rows=12 width=4) (actual time=0.028..4.929 rows=1000.00 loops=1)
     Filter: ((a = 7) AND (b = 7))
     Rows Removed by Filter: 99000
->  Bitmap Heap Scan on ev  (cost=4.66..122.37 rows=30 width=4) (actual time=0.061..0.276 rows=30.00 loops=1000)
     Recheck Cond: (dim_id = dim.id)
     Heap Blocks: exact=30000
     Buffers: shared hit=6082 read=26918 written=14884
Execution Time: 289.282 ms

The planner took 1/100 for a=7, 1/100 for b=7, multiplied them and estimated 1/10,000 of the table — twelve rows. A nested loop over twelve rows is obviously right. A nested loop over a thousand rows is not, and it ran a thousand times, touching about 33,000 buffers.

Configuration changethe same query after teaching the planner the columns are dependent— Captured on 18.6. Nothing else changed: same data, same indexes, same settings.

$ psql -U postgres -c "CREATE STATISTICS dim_ab (dependencies, ndistinct, mcv) ON a, b FROM dim" -c "ANALYZE dim" -c "EXPLAIN (ANALYZE, BUFFERS, COSTS ON, TIMING ON, SUMMARY ON) SELECT count(*) FROM ev JOIN dim ON dim.id=ev.dim_id WHERE dim.a=7 AND dim.b=7"

 ->  Hash Join  (cost=2053.84..51926.22 rows=12769 width=0) (actual time=2.793..94.136 rows=10000.00 loops=3)
     Hash Cond: (ev.dim_id = dim.id)
     ->  Parallel Seq Scan on ev  (cost=0.00..46591.00 rows=1250000 width=4) (actual rows=1000000.00 loops=3)
     ->  Hash  (cost=2041.00..2041.00 rows=1027 width=4) (actual rows=1000.00 loops=3)
           ->  Seq Scan on dim  (cost=0.00..2041.00 rows=1027 width=4) (actual rows=1000.00 loops=3)
                 Filter: ((a = 7) AND (b = 7))
Execution Time: 101.732 ms

Estimate 1,027 against actual 1,000 — 2.7% out, from 83× out. The plan changed from a nested loop to a hash join, and the query from 289.282 ms to 101.732 ms.

Why nested loops turn estimate errors into outages

The other join types degrade gracefully. A hash join on more rows than expected builds a bigger hash table and may spill to disk; a merge join sorts more rows. Both get worse roughly in proportion.

A nested loop’s cost is outer_rows × cost_of_one_inner_lookup. The error is multiplied. An outer estimate that is 83 times low produces 83 times the work — not 83 times slower than expected, but 83 times the work that was costed, and the planner chose the nested loop because it believed the small number.

This is the shape behind most “the query ran fine for months and then suddenly took twenty minutes” reports. Nothing about the query changed. The data drifted until the estimate crossed the threshold where a nested loop looked cheapest, and then the plan fell off a cliff.

The tell in EXPLAIN ANALYZE is a Nested Loop whose inner node shows a large loops count against a small estimate. From the capture above: rows=30 … loops=1000 on a node costed for 12 iterations.

Classifying the error

Read the plan bottom up. Find the first node where estimate and actual diverge materially. Everything above it is downstream of that error and will look wrong as a consequence.

Then classify:

Class 1 — stale statistics

Signature. A single-column predicate, off by any amount, and last_autoanalyze is old or n_mod_since_analyze is large.

SELECT relname, n_mod_since_analyze, last_autoanalyze, last_analyze
  FROM pg_stat_user_tables WHERE relname = 'orders';

Fix. ANALYZE. If it recurs, lower autovacuum_analyze_scale_factor for that table — Part VIII covers the append-only-table case where 10% of a billion rows is never reached in time.

Class 2 — inadequate sample

Signature. A single-column predicate on a high-cardinality, skewed column. Statistics are fresh and still wrong.

Fix. Raise the sample for that column only.

ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;

The default target of 100 keeps 100 most-common values and 100 histogram buckets. A column with thousands of distinct values and heavy skew needs more of them.

Class 3 — correlated columns

Signature. Each predicate is estimated well alone; combining them is badly wrong. Check by running each half separately.

Fix. CREATE STATISTICS, as measured above.

CREATE STATISTICS orders_geo (dependencies, ndistinct, mcv)
    ON city, postcode FROM orders;
ANALYZE orders;

Kind	Fixes
`dependencies`	`WHERE a = x AND b = y` where `a` determines `b`
`ndistinct`	`GROUP BY a, b` estimating the wrong number of groups
`mcv`	Skew in specific combinations that `dependencies` averages over

Class 4 — the planner cannot know

Signature. The predicate is opaque to statistics.

A function result. WHERE lower(email) = $1 has no statistics for lower(email). An expression index creates them: CREATE INDEX ON users (lower(email)) — the index maintains statistics on the expression as a side effect.
A join across tables. Correlation between columns in different tables is not modelled by anything. Extended statistics are per table.
A LIKE '%…%'. Leading wildcards defeat histogram reasoning.
A VOLATILE function. The planner cannot assume anything about its output.

Fix. Restructure the query, or accept it and give the planner a better path — an expression index, a materialised column, a different join order.

Diagnosing in four queries

-- 1. is the plan's first divergence at a single-column predicate?
EXPLAIN (ANALYZE, BUFFERS) SELECT … ;

-- 2. are the statistics current?
SELECT relname, n_mod_since_analyze, last_autoanalyze
  FROM pg_stat_user_tables WHERE relname = 'the_table';

-- 3. does each predicate estimate correctly on its own?
EXPLAIN SELECT count(*) FROM t WHERE a = 7;
EXPLAIN SELECT count(*) FROM t WHERE b = 7;
-- both good, combination bad  ->  class 3

-- 4. what does the planner think the column looks like?
SELECT attname, n_distinct, null_frac,
       array_length(most_common_vals, 1) AS mcv_count
  FROM pg_stats WHERE tablename = 'the_table';

Query 3 is the one that separates class 2 from class 3, and it takes seconds. Skipping it is how people end up raising default_statistics_target cluster-wide to fix a correlation problem that more sampling cannot touch.

Join order, and the collapse limits that silently change planning

For a query joining N tables the number of possible join orders grows factorially. PostgreSQL uses exhaustive dynamic programming while N is small and switches to a genetic algorithm beyond a threshold.

Three settings govern this, and their interaction produces behaviour that looks like a bug.

from_collapse_limit (default 8) and join_collapse_limit (default 8) control whether subqueries and explicit JOIN clauses are flattened into one list the planner can reorder freely. Below the limit they are flattened and every order is considered; at or above it, the written order is preserved.

geqo_threshold (default 12) is where the genetic algorithm takes over. The genetic planner is non-deterministic, so above this many tables the same query can produce different plans on different executions.

The practical consequences.

A query joining nine tables may be planned as written, because it hit join_collapse_limit. Rewriting the FROM clause in a better order then changes the plan, which looks like superstition and is not.

Raising the limits improves plans and costs planning time, which grows fast. Raising both to 12 or so is common and defensible; raising them to 20 can make planning dominate execution for short queries.

Above geqo_threshold plans become non-deterministic. A report that is usually fast and occasionally terrible, with no data change, on a query joining more than a dozen tables, is this. SET geqo = off forces exhaustive search and makes it deterministic again, at whatever planning cost that implies — worth testing before assuming it is too expensive.

Check what you have:

SELECT name, setting FROM pg_settings
 WHERE name IN ('from_collapse_limit','join_collapse_limit','geqo','geqo_threshold');

What to take from this

Find the first diverging node, bottom up. Everything above it is a consequence.
Nested loops multiply estimate errors. A Nested Loop with a large loops count and a small estimate is the signature.
Four classes: stale statistics, inadequate sample, correlated columns, and opaque predicates. Different fixes.
Running each predicate separately separates class 2 from class 3 in seconds.
Extended statistics moved a measured estimate from 12 to 1,027 against an actual of 1,000, and the query from 289 ms to 102 ms.
Beyond join_collapse_limit the written join order is preserved; beyond geqo_threshold plans become non-deterministic.

Cross-course references

Observability for Production Sysadmins — Part LIX (Database observability) covers exporting n_mod_since_analyze, which is where a stale-statistics estimate error is visible before the plan flips.
Linux for Production Sysadmins — Part LXXXII (Root cause analysis) covers distinguishing the estimate error that caused the regression from the several that did not.