PostgreSQL query planner: fix silent p99 regressions
SEO Title: PostgreSQL Query Planner: Fix Silent p99 Regressions Meta Description: Stale pg_statistic data and cross-column correlations silently destroy your p99 latency. Learn how to diagnose and fix query plan regressions with extended statistics and pg_hint_plan.
TL;DR
Your PostgreSQL query planner is making decisions based on statistics that are often wrong, stale, or completely blind to column correlations. The result: catastrophic plan regressions that show up as p99 spikes — not errors, not crashes — just silent, creeping latency that’s nearly impossible to attribute. This post walks through the internals of pg_statistic, the cost model, and three fixes that have saved production systems I’ve worked on.
How the planner actually works (and where it goes wrong)
The PostgreSQL query planner is a cost-based optimizer. It generates candidate execution plans, estimates the cost of each using a statistical model, and picks the cheapest one. The critical word is estimates. Everything hinges on pg_statistic — a system catalog table populated by ANALYZE that stores column-level histograms, most-common values (MCVs), and null fractions.
What most teams miss: the planner never sees your actual data at query time. It sees a snapshot of statistics that may be hours or weeks old on a high-write table.
The cost model inputs
| Factor | Source | Risk |
|---|---|---|
| Row count estimate | pg_class.reltuples | Stale after bulk inserts |
| Selectivity per column | pg_statistic MCVs + histogram | Low statistics_target = coarse buckets |
| Multi-column selectivity | Assumed independent by default | Correlated columns = wildly wrong estimates |
| Join row estimates | Multiplied selectivities | Errors compound across joins |
When the planner estimates 50 rows but gets 50,000, it picks a Nested Loop over a Hash Join — and your query goes from 8ms to 4 seconds.
The three root causes
1. Stale statistics after high-write workloads
autovacuum triggers ANALYZE based on a threshold: autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples. On a 10M-row table with the default 0.2 scale factor, you need 2 million row changes before autovacuum analyzes. That’s an enormous window for plan drift.
-- Check how stale your stats are
SELECT schemaname, relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC
LIMIT 20;2. Low default_statistics_target
The default is 100 — PostgreSQL stores 100 histogram buckets and 100 MCVs per column. For a column with high cardinality or skewed distribution, that’s not enough. The planner interpolates between sparse buckets and gets selectivity estimates that are off by orders of magnitude.
-- Increase statistics target for a critical column
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;3. Cross-column correlations
This is the silent killer. The planner assumes column independence by default. If you filter on (country = 'US' AND city = 'Seattle'), it multiplies the selectivities: P(country) × P(city). But city is highly correlated with country, so the actual selectivity is just P(city) — and the planner dramatically underestimates the row count, triggering wrong join strategies.
The fix stack
Extended statistics (PostgreSQL 10+)
-- Create multivariate statistics for correlated columns
CREATE STATISTICS orders_country_city (dependencies, ndistinct)
ON country, city FROM orders;
ANALYZE orders;
-- Verify the planner is using it
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE country = 'US' AND city = 'Seattle';Look for Statistics objects: orders_country_city in the EXPLAIN output. The row estimate improvement is often 10–100x on correlated columns.
Manual ANALYZE tuning
For tables with time-series skew or write-heavy windows, schedule explicit ANALYZE via cron or a job queue — don’t rely solely on autovacuum.
-- Target specific tables in your maintenance window
ANALYZE VERBOSE orders, shipments, events;Plan pinning via pg_hint_plan
When you have a known-good plan and can’t wait for statistics to converge, pg_hint_plan lets you override the planner directly in application code or via query rewriting rules.
/*+ HashJoin(o s) SeqScan(o) */
SELECT o.id, s.status
FROM orders o
JOIN shipments s ON s.order_id = o.id
WHERE o.created_at > now() - interval '7 days';This is a last resort, not a first resort — but in production incidents, it’s invaluable. Document every hint and set a calendar reminder to remove it once statistics are properly calibrated.
Where to start
Run the pg_stat_user_tables query above. Any table with n_mod_since_analyze > 100k and no recent last_analyze is a plan regression waiting to happen.
From there, look for correlated column pairs — (status, type), (country, region), (user_id, tenant_id) are common. One CREATE STATISTICS and an ANALYZE can close the estimation gap entirely.
Finally, raise default_statistics_target to 200–500 on high-cardinality filter columns. The storage cost is negligible; the plan quality improvement on skewed distributions is not.
Tags: backend architecture microservices api saas