Postgres Planner Statistics: Why the Query Plan Is Wrong

PostgreSQL's planner doesn't run your query to decide how to run it. It estimates how many rows each step will produce, prices the alternatives (sequential scan vs index scan, hash join vs nested loop), and picks the cheapest. Those estimates come from statistics collected by ANALYZE.

When a query is suddenly slow and EXPLAIN ANALYZE shows the planner expected 5 rows but got 500,000, the problem is almost never the planner's algorithm. It's the statistics. (Postgres EXPLAIN ANALYZE)

Spotting a misestimate

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending' AND region = 'eu';

Index Scan using orders_status_idx on orders  (cost=0.43..8.45 rows=3 width=96)
                                              (actual time=0.05..812.3 rows=184220 loops=1)

rows=3 estimated, rows=184220 actual. A planner that thinks it'll get 3 rows will happily choose a nested loop that's catastrophic for 184,000. Compare estimated vs actual at every node; the lowest node where they diverge by 10× or more is where to look.

How ANALYZE works

ANALYZE (run by autovacuum, or manually) reads a random sample of each table — 300 × default_statistics_target rows, so 30,000 rows at the default target of 100 — and stores per-column statistics in pg_statistic. You read them through the friendlier pg_stats view:

SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs,
       histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

Statistic	What it means
null_frac	Fraction of NULLs
n_distinct	Number of distinct values (negative = fraction of rows, e.g. -1 means unique)
most_common_vals / most_common_freqs	The MCV list — up to target frequent values and how often each appears
histogram_bounds	Equal-population buckets for values not in the MCV list
correlation	How well physical row order matches column order (affects index scan cost)

Plus table-level numbers in pg_class: reltuples (estimated rows) and relpages.

For WHERE status = 'pending': if 'pending' is in the MCV list, the estimate is its frequency × row count. If it isn't, the planner assumes the remaining rows are spread evenly over the remaining distinct values. For ranges (created_at > now() - interval '1 day'), it interpolates within histogram buckets.

Cause 1: stale statistics

Autovacuum runs ANALYZE on a table when changed rows exceed autovacuum_analyze_threshold (50) + autovacuum_analyze_scale_factor (10%) × table size. On a 100-million-row table that's 10 million changes — so a bulk load, a big backfill or a new burst of 'pending' orders can sit unanalysed for a long time. (Postgres vacuum and bloat)

Classic symptom: queries on recent data are slow because created_at values from today fall beyond the histogram's last bound, and the planner estimates almost nothing matches.

Fixes:

ANALYZE orders;   -- right after bulk loads, migrations, and pg_upgrade
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables ORDER BY n_mod_since_analyze DESC;

-- Analyse big, busy tables more often
ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.01);

Before Postgres 18, pg_upgrade didn't carry statistics over at all, so run vacuumdb --all --analyze-in-stages before sending traffic. From 18 it transfers most per-column statistics, but not those created with CREATE STATISTICS or added by extensions; the docs recommend vacuumdb --all --analyze-in-stages --missing-stats-only first, then vacuumdb --all --analyze-only to refresh the activity counters that trigger autovacuum. (Postgres major version upgrade)

Cause 2: the sample is too coarse

With the default target of 100, the MCV list holds at most 100 values and the histogram has 100 buckets. On a skewed column — a tenant_id where a few tenants own most of the rows and thousands own a handful — that isn't enough detail. (Multi-tenant Postgres patterns)

Raise the target for that column only:

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

The maximum is 10000. Higher targets mean slower ANALYZE and slightly slower planning, so raise it where it fixes a real misestimate, not globally.

Cause 3: correlated columns

The planner assumes columns are independent. For WHERE city = 'Berlin' AND country = 'DE', it multiplies the selectivities: 1% of rows are Berlin × 5% are DE = 0.05%. In reality every Berlin row is DE, so the true answer is 1% — a 20× underestimate. Multiply three correlated predicates together and you're off by thousands.

Extended statistics fix this:

CREATE STATISTICS orders_city_country (dependencies, ndistinct, mcv)
  ON city, country FROM orders;
ANALYZE orders;

• dependencies — functional dependencies (city determines country).
• ndistinct — the number of distinct combinations, which improves GROUP BY city, country estimates.
• mcv — a multi-column most-common-values list, the most precise of the three for equality filters.

You can also create statistics on an expression (Postgres 14+), which helps when you filter on lower(email) or date_trunc('day', created_at):

CREATE STATISTICS orders_day ON (date_trunc('day', created_at)) FROM orders;

Inspect what was collected in pg_stats_ext.

Cause 4: things statistics can't see

Some estimates are guesses no matter what:

• Functions in WHERE without expression statistics or an expression index — the planner falls back to fixed default selectivities.
• JSONB containment and key lookups have limited statistics; estimates on data->>'status' = 'x' are often poor. Expression statistics or an expression index on that path help. (Postgres JSONB)
• Joins compound errors: a 10× error at the scan becomes 100× after a join.
• Generic plans for prepared statements. After five executions, Postgres may switch to a generic plan that ignores the actual parameter values. With skewed data that's a disaster for the rare value. SET plan_cache_mode = force_custom_plan for those queries.
• CTEs, temporary tables created in the same transaction — temp tables have no statistics until you ANALYZE them, and autovacuum never touches them.

What not to do

• Don't disable plan types globally (SET enable_nestloop = off) to fix one query. It's fine as a diagnostic — if the query gets fast, you've confirmed a misestimate — but fix the statistics instead.
• Don't add indexes blindly. If the planner thinks a predicate matches 50% of the table, a new index won't be used; if it thinks 3 rows, an existing index is already being chosen badly. (Postgres index types)
• Don't trust EXPLAIN without ANALYZE for this — you need actual row counts to see the gap.

A debugging checklist

1. Run EXPLAIN (ANALYZE, BUFFERS) and find the lowest node with a big estimate/actual gap.
2. Check last_autoanalyze and n_mod_since_analyze — run ANALYZE and re-test.
3. Look at pg_stats for the filtered columns: is the value in the MCV list? Is the histogram stale?
4. If multiple filters on related columns, add CREATE STATISTICS.
5. If the column is skewed, raise its statistics target.
6. For prepared statements, compare custom vs generic plans.
7. Track the query in pg_stat_statements to confirm the fix holds over time. (pg_stat_statements)

---

EasySpawn runs Postgres on your own server with autovacuum on, and Claude Code can read EXPLAIN ANALYZE output and pg_stats with you to find the misestimate. See how it works or join the waitlist.

Related: Postgres EXPLAIN ANALYZE · pg_stat_statements · Postgres Vacuum and Bloat · Postgres Index Types