---
title: "PostgreSQL Query Planner Statistics: Why Your Backend Queries Break After 10x Growth"
published: true
description: "Stale pg_statistic data flips index scans to seq scans as tables grow. Diagnose and fix the ANALYZE gap with n_distinct, correlation coefficients, and per-column stat targets."
tags: postgresql, performance, architecture, api
canonical_url: https://mvpfactory.co/blog/postgresql-planner-stats-analyze-gap
---
## What We Will Build
By the end of this walkthrough, you will diagnose stale PostgreSQL planner statistics, understand what `n_distinct` and `correlation` actually mean at the storage level, and surgically tune `ANALYZE` so your query planner stops flying blind after your tables grow 10x.
No schema changes. No new indexes. Just teaching the planner the truth about your data.
## Prerequisites
- PostgreSQL 12+ (extended statistics require 10+, but 12 is where they mature)
- `psql` or any client with access to `pg_stats` and `pg_stat_user_tables`
- A table with real traffic — ideally one where P95 latency has been quietly drifting
---
## The Problem: A Silent Degradation
Let me show you a pattern I see in every production system that hits scale.
Your API P95 drifts from 40ms to 4 seconds over three months. No deployment change. No error logs. Your mobile team files bug reports blaming the app.
The real culprit is `pg_statistic` — PostgreSQL's internal snapshot of your data distribution — last updated when your `users` table had 50,000 rows. It now has 5 million.
Here is the gotcha that will save you hours: PostgreSQL does **not** re-examine your actual data at query time. It consults `pg_statistic`. If that snapshot is stale, every plan is built on a 100x outdated model.
By default, `autovacuum` triggers `ANALYZE` when roughly 20% of a table changes (`autovacuum_analyze_scale_factor = 0.2`). At 50K rows, that is 10K changed rows. At 5M rows, the same threshold requires **1 million changes** before statistics refresh. High-traffic tables can go weeks stale.
---
## Step 1: Confirm the Diagnosis
Check when your busiest tables last saw an `ANALYZE`:
sql
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;
High `n_mod_since_analyze` relative to `n_live_tup` is a red flag. A `last_autoanalyze` from weeks ago on a high-traffic table is a confirmed problem.
Then expose the row-estimate divergence directly:
sql
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events WHERE user_id = 12345 AND event_type = 'purchase';
A planner estimate of 12 rows against an actual 48,000 is the planner flying blind — and that divergence is what flips an index scan to a sequential scan.
## Step 2: Inspect pg_statistic Directly
sql
SELECT attname, n_distinct, correlation, null_frac, most_common_vals
FROM pg_stats
WHERE tablename = 'events' AND attname = 'user_id';
| 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 |
The docs do not mention this prominently, but `correlation` is the field most teams completely ignore. 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 --order-by created_at` restores physical ordering without a full table lock that `CLUSTER` requires.
## Step 3: Raise Per-Column Stat Targets
The default is 100 histogram buckets per column. For high-cardinality columns, that is often not enough:
sql
ALTER TABLE events ALTER COLUMN user_id SET STATISTICS 500;
ANALYZE events;
Compare `EXPLAIN` output before and after. On columns like `user_id` and `device_id`, the difference is often dramatic.
## Step 4: Extended Statistics for Composite Filters
Here is the minimal setup to get this working for multi-column queries — and it is almost universally skipped:
sql
CREATE STATISTICS events_country_platform (dependencies)
ON country, platform FROM events;
ANALYZE events;
If your queries filter on `(country, platform)` together, the planner assumes independence — badly wrong if the two columns are correlated. This teaches the planner about multivariate dependencies. Zero schema cost, measurable plan improvement in multi-tenant backends.
## Step 5: Tune autovacuum Per Table
Do not schedule `ANALYZE` on a cron and call it done. Use a tiered approach:
sql
-- Targeted ANALYZE on hot tables after bulk loads
ANALYZE events (user_id, created_at, event_type);
-- Make autovacuum 20x more responsive for high-traffic tables
ALTER TABLE events SET (
autovacuum_analyze_scale_factor = 0.01,
autovacuum_analyze_threshold = 1000
);
Setting `autovacuum_analyze_scale_factor = 0.01` triggers after 1% of rows change — 20x more responsive than the default, targeted only at your highest-traffic tables.
---
## Gotchas
**Stale stats are invisible until catastrophic.** There is no built-in alert for "your planner estimates are wrong." Make `n_mod_since_analyze` part of your growth-milestone checklist.
**`n_distinct` can go negative — that is intentional.** A value of `-0.5` means approximately half the rows are unique. Confusing the first time you see it, but correct behavior.
**`pg_repack` requires the extension to be installed.** Verify with `\dx` in psql before relying on it in any runbook.
**Extended statistics need a follow-up `ANALYZE`.** Creating the statistics object alone does nothing. The planner only uses it after you explicitly run `ANALYZE`.
**Do not raise `STATISTICS` to 500 everywhere.** Only raise it on high-cardinality columns you actively filter on. More buckets mean longer `ANALYZE` runs and a larger `pg_statistic` footprint across the board.
---
## Conclusion
The query planner is only as good as the statistics you give it. At scale, that is an active responsibility — not a default you set up once at launch.
Run `pg_stat_user_tables` checks and `EXPLAIN (ANALYZE)` on your critical paths after every significant growth milestone. Raise `STATISTICS` targets surgically on `user_id`, `device_id`, and similar high-cardinality columns. Add extended statistics for composite filters. These are zero-downtime, zero-schema-change interventions that restore correct plan selection without touching your indexes or application code.
The mobile team will stop filing those latency bug reports.
Top comments (0)