PostgreSQL planner stats: why queries break at scale
Tags: postgresql database performance sql backend
TL;DR
PostgreSQL’s query planner relies on statistics collected by ANALYZE. When your backend grows 10x — and ANALYZE hasn’t kept pace — the planner’s row estimates become wildly wrong, turning millisecond index scans into second-long sequential scans. The fix isn’t just “run ANALYZE more.” It’s understanding n_distinct, correlation coefficients, and per-column stat targets, then surgically tuning them.
The silent degradation you don’t see coming
Your P95 API latency drifts from 40ms to 4 seconds over three months. No error, no crash, no deployment change. Your mobile team files bug reports blaming the app. The real culprit is a stale statistics snapshot inside pg_statistic that’s steering your query planner off a cliff.
Most teams get this wrong: PostgreSQL doesn’t re-examine your actual data at query time. It consults pg_statistic — a snapshot taken the last time ANALYZE ran. If your users table had 50,000 rows when those stats were collected and now has 5 million, the planner is making decisions based on a 100x outdated model.
By default, autovacuum triggers ANALYZE when roughly 20% of a table changes (controlled by autovacuum_analyze_scale_factor = 0.2). At 50K rows, that’s 10K changed rows. At 5M rows, that same threshold requires 1 million changes before statistics refresh. In practice, large tables can go weeks with stale stats.
How to detect it
Before tuning anything, confirm the diagnosis. Two queries tell you most of what you need.
First, check when your busiest tables last saw an ANALYZE:
SELECT relname, last_autoanalyze, last_analyze, n_live_tup, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname IN ('events', 'users', 'sessions')
ORDER BY n_mod_since_analyze DESC;A high n_mod_since_analyze relative to n_live_tup is a red flag. A last_autoanalyze timestamp from weeks ago on a high-traffic table is a confirmed problem.
Second, expose the row-estimate divergence directly with EXPLAIN (ANALYZE):
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id = 12345 AND event_type = 'purchase';Look for the gap between rows=X (planner estimate) and actual rows=Y. An estimate of 12 rows against an actual of 48,000 is a planner flying blind, and that divergence is what flips an index scan to a sequential scan.
Inside pg_statistic: what the planner actually sees
PostgreSQL stores per-column statistics in pg_statistic, which you can inspect more cleanly via pg_stats:
SELECT attname, n_distinct, correlation, null_frac, most_common_vals
FROM pg_stats
WHERE tablename = 'events' AND attname = 'user_id';The critical fields:
| Field | What it means | Danger when wrong |
|---|---|---|
n_distinct | Estimated unique values | Negative = fraction of total rows |
correlation | Physical vs logical row order | Near 0 = scattered, near 1 = sorted |
most_common_vals | Top N values by frequency | MCV misses → bad selectivity estimates |
null_frac | Fraction of NULLs | Affects join and filter estimates |
When n_distinct is stale, the planner misjudges selectivity. A filter on device_type might correctly use an index when the table had 5 types of devices. After international expansion brings 50 values, the planner’s cached n_distinct = 5 makes every query look 10x more selective than it is — until the estimate flips the plan entirely.
The correlation coefficient trap
correlation is underappreciated and rarely tuned. It measures how physically ordered rows are relative to the indexed column’s logical sort order. A value near 1.0 means rows with similar values sit together on disk — ideal for index scans. Near 0 means they’re scattered, and a sequential scan may genuinely be faster.
After heavy write loads — push notifications, event streams, session logs — tables fragment. Correlation on created_at drops from 0.95 to 0.2. The planner correctly abandons the index. But the root cause is degraded physical layout, not the query. pg_repack (when run with --order-by created_at) restores physical ordering without a full table lock, which CLUSTER requires.
Per-column stat targets and expression statistics
The default statistics target is 100 histogram buckets per column. For high-cardinality columns like user_id, that’s often insufficient:
ALTER TABLE events ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE events;More impactful and almost universally ignored: extended statistics. If your queries filter on (country, platform) together, the planner treats them as independent — badly wrong if they’re correlated:
CREATE STATISTICS events_country_platform (dependencies)
ON country, platform FROM events;
ANALYZE events;This teaches the planner about multivariate correlations. In production systems with composite filters, this alone restores correct plan selection without any index changes.
Manual ANALYZE strategies that actually work
Don’t schedule ANALYZE on a cron and call it done. Use a tiered approach:
-- Targeted ANALYZE on hot tables after bulk loads
ANALYZE events (user_id, created_at, event_type);
-- Increase autovacuum aggressiveness per table
ALTER TABLE events SET (
autovacuum_analyze_scale_factor = 0.01,
autovacuum_analyze_threshold = 1000
);Setting autovacuum_analyze_scale_factor = 0.01 means stats refresh after 1% of rows change — 20x more responsive than the default, targeted only at your highest-traffic tables.
What to actually do
Run pg_stat_user_tables.n_mod_since_analyze and EXPLAIN (ANALYZE) on your critical paths after every significant growth milestone. Stale stats are invisible until they’re catastrophic.
On high-cardinality filter columns — user_id, device_id — raise STATISTICS to 300–500 and re-run ANALYZE. Compare plans before and after. The difference is often dramatic.
If you have composite filters like WHERE country = X AND platform = Y, create extended statistics with dependencies. Zero schema cost, measurable plan improvement in multi-tenant backends.
The query planner is only as good as the statistics you give it. At scale, that’s an active responsibility — not a default.