---
title: "PostgreSQL Query Planner: Fix Silent p99 Regressions"
published: true
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."
tags: postgresql, performance, architecture, cloud
canonical_url: https://mvpfactory.co/blog/postgresql-query-planner-p99-regressions
---
What You Will Learn
By the end of this tutorial you will understand why your PostgreSQL queries degrade silently — no errors, no crashes, just creeping p99 spikes — and you will have three concrete fixes you can apply today. We are going deep into pg_statistic, the planner cost model, and the cross-column correlation trap that kills production systems.
Prerequisites
- A running PostgreSQL 10+ instance
- Access to
pg_stat_user_tablesandEXPLAIN (ANALYZE, BUFFERS) - Optionally:
pg_hint_planextension installed for plan pinning
How the Planner Actually Works
The PostgreSQL query planner is a cost-based optimizer. It generates candidate execution plans, estimates each one's cost from a statistical model, and picks the cheapest. The critical word is estimates.
Everything hinges on pg_statistic — a system catalog populated by ANALYZE that stores column-level histograms, most-common values (MCVs), and null fractions. Here is the part the docs gloss over: the planner never sees your actual data at query time. It sees a snapshot that may be hours or weeks old on a high-write table.
| 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. Your query goes from 8ms to 4 seconds.
Step 1 — Find Your Stale Tables
Let me show you a pattern I use in every project. Run this first.
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;
Any table with n_mod_since_analyze > 100k and no recent last_analyze is a plan regression waiting to happen. The autovacuum trigger is 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 is an enormous drift window.
Step 2 — Raise statistics_target on Hot Columns
The default is 100 — PostgreSQL stores 100 histogram buckets and 100 MCVs per column. For high-cardinality or skewed distributions, the planner interpolates between sparse buckets and gets selectivity estimates off by orders of magnitude.
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;
Raise this to 200–500 on any column that appears in a WHERE clause or join condition on a large table. Storage cost is negligible. Plan quality improvement on skewed distributions is not.
Step 3 — Create Extended Statistics for Correlated Columns
Here is the gotcha that will save you hours.
The planner assumes column independence by default. If you filter on (country = 'US' AND city = 'Seattle'), it computes P(country) × P(city). But city is highly correlated with country, so the actual selectivity is roughly just P(city) — and the planner dramatically underestimates the row count, triggering wrong join strategies.
The fix, available since PostgreSQL 10:
CREATE STATISTICS orders_country_city (dependencies, ndistinct)
ON country, city FROM orders;
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE country = 'US' AND city = 'Seattle';
Look for Statistics objects: orders_country_city in the EXPLAIN output. Row estimate improvement is often 10–100x on correlated columns. Common pairs to audit: (status, type), (country, region), (user_id, tenant_id).
Step 4 — Pin Plans in a Production Incident
This is a last resort, not a first resort. But when you have a known-good plan and cannot wait for statistics to converge, pg_hint_plan lets you override the planner directly.
/*+ 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';
Document every hint you place and set a calendar reminder to remove it once statistics are properly calibrated. Hints that outlive their usefulness become the next regression.
Gotchas
Autovacuum is not enough on write-heavy tables. Schedule explicit ANALYZE via cron or your job queue for any table taking bulk inserts or deletes on a time-series pattern:
ANALYZE VERBOSE orders, shipments, events;
Extended statistics require a follow-up ANALYZE. CREATE STATISTICS does nothing on its own — you must run ANALYZE on the table afterward or the new statistics object has no data.
pg_hint_plan hints in ORM-generated queries are fragile. If your ORM rewrites the query structure, the hint may no longer match and will silently be ignored. Validate with EXPLAIN after any ORM upgrade.
Conclusion
The docs do not make this obvious, but every p99 regression investigation should start with pg_stat_user_tables. From there, the path is: raise statistics_target on hot columns, create extended statistics for correlated column pairs, and reach for pg_hint_plan only when the production incident demands it. These three levers have stabilized more production systems than any index change I have made.
Further reading:
Top comments (0)