Stale Statistics Bad Query Plan
One specific query -- frequently a reporting or join query that touches a recently loaded table -- becomes dramatically slower with no code change and no...
In 10 seconds
A query that was instant last week suddenly takes seconds, nothing in the application changed, and the only thing that 'fixes' it is running ANALYZE. This is the canonical stale-statistics outage, and it almost always follows a bulk load. The PostgreSQL planner does not execute your query to see how many rows match a predicate; it ESTIMATES, using per-column statistics (the most-common-values list and histogram in pg_statistic, surfaced via pg_stats) that are gathered by ANALYZE -- either manually or by autovacuum's autoanalyze. Immediately after a large INSERT, those column statistics either do not exist yet or are badly out of date, and autoanalyze lags: by default it only fires once roughly 10% of the table has changed (autovacuum_analyze_scale_factor = 0.1) and only after its naptime, so a freshly loaded million-row table can sit for a long window with no usable column statistics. During that window the planner is flying blind on data distribution. The trap is subtle because the planner is NOT missing the row count -- CREATE INDEX and the physical relation size give it a perfectly good reltuples -- it is missing the DISTRIBUTION. With no most-common-values list for a column, an equality predicate falls back to a single hard-wired constant, the default equality selectivity of 0.005. The lab makes this concrete and deterministic: a 1,000,000-row orders table is loaded with two skewed columns (95% of rows are status='completed', 95% are channel='web'), indexed, and left un-analyzed (autovacuum is turned off on the table so the post-load window never closes on its own). A routine reporting query -- count the completed web orders joined to their customers -- asks for status='completed' AND channel='web'. The planner, with no statistics, estimates 1,000,000 * 0.005 * 0.005 = 25 matching rows when ~902,000 actually match: a 36,000x underestimate. Believing only 25 rows survive the filter, it chooses a Nested Loop and probes the customers primary key once per surviving row -- ~902,000 index probes, ~2.7 million buffer accesses -- when a Hash Join would have done the whole thing in a single pass. We read the FREE signal (the table has never been analyzed; the plan's estimate is absurdly below reality), the PREMIUM signal (pg_stats has zero rows for the table while the row count is known, proving it is a missing-DISTRIBUTION problem and pinning the 0.005-fallback math), then prove the FIX (one ANALYZE flips the plan to a Hash Join and collapses 2.7M buffer hits to ~9k) and the PREVENTION (ANALYZE as the final step of every bulk load, plus a lower autovacuum_analyze_scale_factor so autoanalyze fires long before 10% of a huge table has changed).
Why This Happens
The planner's job is to pick the cheapest plan, and 'cheapest' is computed from estimated row counts (cardinalities), not actual ones. Cardinality estimation for a column predicate needs the column's statistics: the fraction of rows equal to a given value comes from the most-common-values (MCV) list, and range predicates use the histogram -- both produced by ANALYZE and stored in pg_statistic / pg_stats. When those statistics are absent, the planner cannot know that 'completed' covers 95% of the table; it falls back to a built-in constant, DEFAULT_EQ_SEL = 0.005, for an equality on a column with no MCV. Two such equalities are assumed independent and their selectivities multiply: 0.005 * 0.005 = 0.000025, and 0.000025 * 1,000,000 reltuples = 25 estimated rows. That is the entire bug in one line of arithmetic, and the lab prints exactly that product (25.00).
The crucial nuance -- the one that separates this from a 'the table looks empty' misconception -- is that the planner was NOT missing the row count. CREATE INDEX scans the whole table and updates pg_class.reltuples and relpages, so reltuples is a correct 1,000,000; the captures show it. A freshly loaded table is therefore estimated to have the right NUMBER of rows but an unknown DISTRIBUTION, and it is the distribution the predicate selectivity depends on. Given a 25-row estimate for the filtered side, the planner reasons that a Nested Loop -- scan the (tiny) filtered set, and for each of its rows do one index lookup into customers -- is cheapest, because 25 index probes is nothing.
The cost model is right about the per-row cost and catastrophically wrong about the row count, so it commits to a plan whose true cost is ~902,000 index probes and ~2.7M buffer hits. ANALYZE fixes it by giving the planner an MCV list in which 'completed' and 'web' each carry a frequency near 0.95: the estimate for the filtered side jumps from 25 to hundreds of thousands, a Nested Loop now looks absurd, and the planner switches to a Hash Join (build a hash of the 50,000 customers once, stream the filtered orders through it) -- a single linear pass. The reproduction is deterministic because the 0.005 fallback and the reltuples-from-CREATE-INDEX behaviour are fixed, version-stable mechanics; nothing about the misestimate is staged.
The only artificial element is disabling autovacuum on orders, which simply holds open the same post-bulk-load window that exists in production before autoanalyze catches up -- it does not change WHAT goes wrong, only guarantees we observe it. The only artificial element is disabling autovacuum on orders, which simply holds open the same post-bulk-load window that exists in production before autoanalyze catches up -- it does not change WHAT goes wrong, only guarantees we observe it.
Diagnose
Free Tier -- Basic Approach
The on-call's first read with zero special tooling: has this table ever been analyzed, and is the plan's estimate anywhere near reality? Both questions are answered without any premium knowledge -- pg_stat_user_tables plus a single EXPLAIN (ANALYZE).
SELECT relname,
last_analyze,
last_autoanalyze,
n_live_tup,
n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname IN ('orders','customers')
ORDER BY relname;
relname | last_analyze | last_autoanalyze | n_live_tup | n_mod_since_analyze -----------+-------------------------------+------------------+------------+--------------------- customers | 2026-06-21 11:41:51.502831+00 | | 50000 | 0 orders | | | 1000000 | 1000000 (2 rows)
EXPLAIN (ANALYZE, BUFFERS, SUMMARY OFF) SELECT count(*) FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'completed' AND o.channel = 'web';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=408.12..408.13 rows=1 width=8) (actual time=918.540..918.542 rows=1 loops=1)
Buffers: shared hit=2717860
-> Nested Loop (cost=116.40..408.06 rows=25 width=0) (actual time=20.631..882.062 rows=902644 loops=1)
Buffers: shared hit=2717860
-> Bitmap Heap Scan on orders o (cost=116.11..212.37 rows=25 width=4) (actual time=20.588..88.713 rows=902644 loops=1)
Recheck Cond: ((channel = 'web'::text) AND (status = 'completed'::text))
Heap Blocks: exact=8311
Buffers: shared hit=9928
-> BitmapAnd (cost=116.11..116.11 rows=25 width=0) (actual time=19.763..19.764 rows=0 loops=1)
Buffers: shared hit=1617
-> Bitmap Index Scan on idx_orders_channel (cost=0.00..57.92 rows=5000 width=0) (actual time=10.085..10.085 rows=950075 loops=1)
Index Cond: (channel = 'web'::text)
Buffers: shared hit=802
-> Bitmap Index Scan on idx_orders_status (cost=0.00..57.92 rows=5000 width=0) (actual time=9.488..9.488 rows=950141 loops=1)
Index Cond: (status = 'completed'::text)
Buffers: shared hit=815
-> Index Only Scan using customers_pkey on customers c (cost=0.29..7.83 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=902644)
Index Cond: (id = o.customer_id)
Heap Fetches: 902644
Buffers: shared hit=2707932
Planning:
Buffers: shared hit=190
(22 rows)
The first query reads pg_stat_user_tables for the two tables. customers shows a recent last_analyze and n_mod_since_analyze = 0 -- it is up to date. orders, by contrast, shows last_analyze and last_autoanalyze both NULL, n_live_tup = 1000000, and n_mod_since_analyze = 1000000: a million rows changed and the planner has never gathered statistics for any of them. That single line -- a large, never-analyzed table -- is enough for an on-call to suspect stale statistics.
The second query, EXPLAIN (ANALYZE) of the slow report, confirms it from the other direction: the Nested Loop is estimated at rows=25 but returns 902,644 actual rows, a textbook estimated-vs-actual blowout, with the inner index scanned 902,644 times. Seeing 'never analyzed' in the catalog and 'estimate 36,000x below actual' in the plan together is the at-a-glance diagnosis: the planner is choosing a plan for a table it has no distribution data about.
What this free read does NOT show is the precise root cause -- whether the column statistics are merely stale or entirely absent, and the exact selectivity math that produced 25 -- and that is the premium step.
Fix
The fix is the safest statement in PostgreSQL: a single ANALYZE. It samples the table, builds the missing MCV list and histogram for every column, and the planner immediately re-estimates the skewed predicates correctly.
ANALYZE orders;
ANALYZE
SELECT tablename, count(*) AS columns_with_stats FROM pg_stats WHERE schemaname='public' AND tablename='orders' GROUP BY tablename;
tablename | columns_with_stats -----------+-------------------- orders | 5 (1 row)
Running ANALYZE orders gathers statistics for all of the table's columns; the capture confirms orders now has statistics for 5 columns where moments ago it had none. ANALYZE only reads a sample of the table and writes rows into the statistics catalog -- it takes a light ShareUpdateExclusiveLock that does not block reads or writes, changes no user data, and is fully idempotent -- so it is safe to run at any time, including on a busy production table.
The effect on planning is immediate and is proven in the verify step: with an MCV list that records 'completed' and 'web' at ~0.95 each, the planner's estimate for the filtered side jumps from 25 into the hundreds of thousands, a Nested Loop is no longer remotely attractive, and the same query is replanned as a single-pass Hash Join. No query text changed, no index was added, and no global setting was touched -- the only thing that changed is that the planner can finally see the shape of the data.
Verify
Healthy-state proof: re-run the EXACT same query with fresh statistics. The estimate is now the right order of magnitude, the strategy is a Hash Join, and the ~2.7 million buffer hits collapse to ~9,000.
EXPLAIN (ANALYZE, BUFFERS, SUMMARY OFF) SELECT count(*) FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'completed' AND o.channel = 'web';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=18845.26..18845.27 rows=1 width=8) (actual time=121.025..123.970 rows=1 loops=1)
Buffers: shared hit=9099
-> Gather (cost=18845.04..18845.25 rows=2 width=8) (actual time=120.511..123.959 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=9099
-> Partial Aggregate (cost=17845.04..17845.05 rows=1 width=8) (actual time=115.106..115.109 rows=1 loops=3)
Buffers: shared hit=9099
-> Hash Join (cost=1347.00..16900.19 rows=377942 width=0) (actual time=12.082..102.681 rows=300881 loops=3)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=9099
-> Parallel Seq Scan on orders o (cost=0.00..14561.00 rows=377942 width=4) (actual time=0.016..35.278 rows=300881 loops=3)
Filter: ((status = 'completed'::text) AND (channel = 'web'::text))
Rows Removed by Filter: 32452
Buffers: shared hit=8311
-> Hash (cost=722.00..722.00 rows=50000 width=4) (actual time=11.332..11.333 rows=50000 loops=3)
Buckets: 65536 Batches: 1 Memory Usage: 2270kB
Buffers: shared hit=666
-> Seq Scan on customers c (cost=0.00..722.00 rows=50000 width=4) (actual time=0.038..4.144 rows=50000 loops=3)
Buffers: shared hit=666
Planning:
Buffers: shared hit=260 read=5
I/O Timings: shared read=0.027
(23 rows)
SELECT relname, last_analyze IS NOT NULL AS has_fresh_stats, n_mod_since_analyze FROM pg_stat_user_tables WHERE relname='orders';
relname | has_fresh_stats | n_mod_since_analyze ---------+-----------------+--------------------- orders | t | 0 (1 row)
After ANALYZE, the same EXPLAIN (ANALYZE, BUFFERS) of the report query shows a completely different plan: a 'Hash Join (cost=1347.00..16900.19 rows=377942 ...) (actual ... rows=300881 loops=3)' fed by a Parallel Seq Scan on orders, with 'Buffers: shared hit=9099'. Three things changed and they are all consequences of the new statistics.
The estimate moved from 25 to ~378,000 -- not exact (the planner still estimates the two predicates independently, so it lands a bit under the ~902,000 actual), but the right order of magnitude, which is all it needs to reject the Nested Loop. The strategy moved from probing the customers index ~902,000 times to building one hash of the 50,000 customers and streaming the filtered orders through it in a single pass (PostgreSQL even parallelises it across workers).
And the physical cost moved from ~2.7 million buffer hits to ~9,099 -- roughly a 300x reduction in work for an identical query. The companion read of pg_stat_user_tables confirms the table is now healthy: has_fresh_stats = t and n_mod_since_analyze = 0.
This is the contrast that pins the diagnosis: the query, the data, and the indexes are unchanged -- only the statistics differ -- so the statistics were unambiguously the cause.
⏪ Rollback
Nothing here is destructive and the test environment is never touched. The whole reproduction runs against a throwaway database created and dropped on a PostgreSQL instance; the lab is torn down at the end and the only artifacts kept are the literal capture files. No global server setting is changed: the one table-level option the setup uses, ALTER TABLE orders SET (autovacuum_enabled = off), is a <a class="sev1-termlink" href="https://thesev1database.com/glossary/fillfactor/">storage parameter on a table that is about to be dropped, and the prevention step re-enables it (autovacuum_enabled = true) anyway. The 'fix' itself -- ANALYZE -- is the safest operation in PostgreSQL: it only reads a sample of the table and writes new rows into the statistics catalog; it acquires a light ShareUpdateExclusiveLock (it does NOT block reads or writes), changes no user data, and is fully idempotent (running it again just refreshes the stats). There is nothing to 'undo' about gathering statistics; if anything, the only way to get back to the broken state is to delete statistics, which you would never do in production. The skewed data itself is generated with random into the throwaway table and is discarded with the database. In short, the only thing this recipe mutates that matters is the contents of pg_statistic for one table, and that mutation is precisely the cure -- there is no risky change to walk back.
📦 Version Matrix (PG 14--18)
The same missing-statistics misestimate produces the identical plan regression -- and the same ANALYZE flips it back -- on every supported major version. Each row is an independent run against a fresh throwaway database on that version's test environment node, building a skewed un-analyzed table and capturing the JOIN strategy before and after ANALYZE:
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
Why did one query get slow when nothing in my application changed?
Because the PostgreSQL planner chooses a plan from ESTIMATED row counts, and those estimates depend on per-column statistics gathered by ANALYZE. After a bulk load (ETL, restore, backfill, big INSERT) the new table either has no column statistics yet or badly stale ones, and autoanalyze hasn't caught up. With no statistics for a column, the planner can't tell that a value is common, so it guesses with a hard-wired default selectivity -- here it estimated 25 matching rows when ~902,000 matched. Believing almost nothing survives the filter, it picked a Nested Loop that probes an index hundreds of thousands of times instead of a single-pass Hash Join. The query itself didn't change; the planner's information about your data did.
Why does running ANALYZE fix it instantly?
ANALYZE samples the table and builds the statistics the planner was missing -- principally a most-common-values (MCV) list that records how frequent each value is. Once orders has stats, the planner knows status='completed' and channel='web' each cover ~95% of the table, so its estimate for the filtered side jumps from 25 to hundreds of thousands of rows. At that cardinality a Nested Loop is obviously wrong, so the planner switches to a Hash Join. In the lab that one ANALYZE takes the same query from a Nested Loop with ~2.7 million buffer hits to a Hash Join with ~9,000 -- roughly a 300x reduction in work -- with no change to the query or the data.
If the table was never analyzed, doesn't the planner think it's empty?
No -- this is the key misconception. CREATE INDEX scans the whole table and updates pg_class.reltuples, and the planner also derives row counts from the physical file size, so it knows the table has ~1,000,000 rows (the premium capture shows reltuples = 1000000). What it lacks is the column DISTRIBUTION -- the MCV list and histogram that only ANALYZE produces. So the planner has the right row COUNT but no idea how selective a given predicate is, and for an equality with no MCV it falls back to a fixed 0.005 default selectivity. Two such equalities multiply to 0.000025, and 0.000025 x 1,000,000 = the 25-row estimate behind the bad plan. It's a missing-distribution problem, not a missing-count problem.
How do I confirm this is a statistics problem and not a missing index?
Run EXPLAIN (ANALYZE, BUFFERS) and compare estimated vs actual rows. A statistics problem shows a node whose estimate is wildly below reality (here rows=25 estimated, ~902,000 actual) feeding a Nested Loop with a large loops= on its inner side. Then check pg_stat_user_tables: last_analyze and last_autoanalyze NULL (or old) with a big n_mod_since_analyze means the planner is working from stale or absent stats. For the root cause, query pg_stats for the table -- if it returns no rows for the columns in your predicate, there is no distribution for the planner to use. A missing-index problem looks different: the estimates are roughly right but the plan does a sequential scan because no suitable index exists. Here the indexes can even be present and used in the bad plan -- the issue is the cardinality estimate, not index availability.
Why are skewed columns the ones that blow up?
Because the planner's fallback assumes a value is rare. Without an MCV list, an equality predicate gets the fixed 0.005 default selectivity -- i.e. the planner assumes the value matches 0.5% of rows. If the value is actually rare, that guess is roughly fine. But if the value is COMMON -- like status='completed' at 95% of the table -- the planner underestimates by ~190x for that one predicate, and combining two skewed predicates multiplies the error (0.005 x 0.005) into the tens-of-thousands range. Uniform columns suffer far less because their true selectivity is closer to the default. ANALYZE captures the skew in the MCV list and the underestimate disappears.
How do I stop this from recurring after every load?
Two complementary controls, both shown in the prevention capture. First, make ANALYZE the explicit final step of every bulk load / ETL / restore -- never rely on autoanalyze to beat the first query, because by default autoanalyze only fires after ~10% of the table has changed (autovacuum_analyze_scale_factor = 0.1) plus the naptime, which on a large table is a long, query-exposed window. Second, for big tables lower that threshold per-table -- ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02) makes autoanalyze fire after 2% of changes instead of 10%, roughly 5x sooner -- and keep autovacuum enabled on the table. For specific skewed or correlated columns you can additionally raise default_statistics_target or add extended statistics (CREATE STATISTICS), but a plain post-load ANALYZE already prevents the catastrophic case here.
References
- Statistics Used by the Planner (pg_statistic, MCV, histogram)
- Row Estimation Examples (how selectivity is computed)
- ANALYZE
- Automatic Vacuuming -- autovacuum_analyze_scale_factor / autovacuum_analyze_threshold
- pg_stats -- the planner-statistics view
- The Statistics Collector / Cumulative Statistics -- pg_stat_all_tables (last_analyze, n_mod_since_analyze)
- Extended Statistics -- CREATE STATISTICS (correlated columns)
- default_statistics_target configuration parameter