Generic Plan Nested Loop Explosion
A parameterized query has wildly bimodal latency: fast (sub-millisecond to low-millisecond) for almost every bind value, then suddenly catastrophic --...
In 10 seconds
A reporting query is fast for thousands of customers, then one customer ID makes the exact same query take minutes -- and nothing in the data or the code changed. The query is run as a SERVER-SIDE PREPARED statement (a JDBC PreparedStatement, a pgbouncer connection in statement mode, an ORM in named-statement mode, or a literal PREPARE), and that is the whole problem. When a prepared statement is executed repeatedly, PostgreSQL stops re-planning it with the actual bind value and instead caches a single GENERIC plan that treats the parameter as unknown, planned from the column's AVERAGE selectivity. On a heavily SKEWED column that average is a lie. The lab makes it exact: an `orders` table where customer_id=1 (the 'whale') owns 500,000 of the 1,000,000 rows while 500,000 other customers own exactly one each. The column's n_distinct is ~29,405, so the average customer owns about 34 rows -- and the generic plan, seeing only that average, builds a Nested Loop with a per-row index lookup into order_items, which is perfect for a typical customer. Then the application executes that same cached Nested Loop for the whale. The outer scan returns not 34 rows but 500,000, and the inner index-only scan runs once per row -- loops=500000 -- turning a sub-millisecond plan into a half-million-iteration storm. No error is raised; the statement simply falls off a latency cliff. The recipe shows the FREE diagnosis (plan the same statement both ways -- force_generic_plan yields the cached Nested Loop guessing ~34 rows, force_custom_plan yields the Hash Join sized for half a million), the PREMIUM diagnosis (a catalog-only scan that ranks every column by skew factor to find every predicate that can cache this trap, before any incident), the FIX (SET plan_cache_mode = force_custom_plan, which re-plans the whale as a Hash Join and erases the loops=500000 storm), and the PREVENTION (understand why PostgreSQL's auto mode is fooled into caching the generic plan, then pin force_custom_plan durably on the database with ALTER DATABASE).
Why This Happens
PostgreSQL plans a parameterized statement in one of two ways. A CUSTOM plan is built fresh for the specific bind values, so the planner can use the column's per-value statistics (the MCV list, the histogram) and estimate accurately. A GENERIC plan is built once with the parameters treated as unknown placeholders, using the column's AVERAGE selectivity, and is then reused for every execution -- it saves planning time but is blind to the actual value. By default (plan_cache_mode = auto) PostgreSQL custom-plans the first five executions, then builds the generic plan and compares its estimated cost to the running average of the custom-plan costs; if the generic plan is not meaningfully more expensive, it switches to it permanently to avoid re-planning.
On a skewed column this comparison backfires in two compounding ways. First, the generic estimate for customer_id = $1 is the average selectivity: with n_distinct ~29,405 over 1,000,000 rows the planner expects ~34 rows, so it chooses a Nested Loop with a per-row inner index lookup -- ideal for 34 rows. Second, BECAUSE the generic plan underestimates the row count, its self-reported cost (cost ~296) is far LOWER than the custom plan's honest cost for the whale (cost ~27,328 for the Hash Join that knows it is half a million rows). So the auto-mode cost comparison sees 'generic is cheaper' and locks the Nested Loop in -- the very plan that is catastrophic for the skewed value.
When the statement is then executed for customer_id=1, the cached Nested Loop's outer scan returns 500,000 rows instead of 34, and the inner Index Only Scan executes once per outer row: loops=500000, Heap Fetches=500000. A plan that costs nothing for a typical customer becomes a half-million-iteration scan for the whale, with no error and no warning -- a silent latency cliff. This is not a bug in the planner; it is the documented cost of caching a single plan for a column whose values are wildly unequal.
The custom plan for the whale is a Parallel Hash Join (estimate ~207,320 rows, the correct order of magnitude): it scans both tables once in parallel and joins by hashing, which is dramatically cheaper than 500,000 index descents.
The fix is to stop reusing the cached generic plan -- SET plan_cache_mode = force_custom_plan makes PostgreSQL re-plan with the real bind value on every execution, so the whale gets the Hash Join and every other value gets its own correct plan. The cost is a little extra planning time per execution; the benefit is that no single cached plan can be wrong for a skewed value. The deeper prevention is to pin force_custom_plan durably (e.g. ALTER DATABASE ... SET, or per-role, or via the application's connection options) for workloads that filter skewed columns through prepared statements, and -- predictively -- to find those columns BEFORE they explode by scanning pg_stats for a high skew factor (the heaviest value's frequency divided by the average frequency), which is exactly the difference between what a generic plan assumes and what a skewed value delivers. SET, or per-role, or via the application's connection options) for workloads that filter skewed columns through prepared statements, and -- predictively -- to find those columns BEFORE they explode by scanning pg_stats for a high skew factor (the heaviest value's frequency divided by the average frequency), which is exactly the difference between what a generic plan assumes and what a skewed value delivers.
Diagnose
Free Tier -- Basic Approach
The on-call's first read with zero special tooling: take the slow prepared statement and plan it BOTH ways -- as the cache reuses it (generic) and as a fresh re-plan would build it (custom) -- and compare. If the two plans disagree wildly, the cached plan is the problem.
PREPARE q(int) AS SELECT count(*) FROM orders o JOIN order_items i ON i.order_id = o.id WHERE o.customer_id = $1;
PREPARE
SET plan_cache_mode = force_generic_plan; EXPLAIN (COSTS ON) EXECUTE q(1);
SET
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Aggregate (cost=296.49..296.50 rows=1 width=8)
-> Nested Loop (cost=0.85..296.40 rows=34 width=0)
-> Index Scan using orders_customer_idx on orders o (cost=0.42..9.02 rows=34 width=8)
Index Cond: (customer_id = $1)
-> Index Only Scan using order_items_order_idx on order_items i (cost=0.42..8.44 rows=1 width=8)
Index Cond: (order_id = o.id)
(6 rows)
SET plan_cache_mode = force_custom_plan; EXPLAIN (COSTS ON) EXECUTE q(1); RESET plan_cache_mode;
SET
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=27328.77..27328.78 rows=1 width=8)
-> Gather (cost=27328.55..27328.76 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=26328.55..26328.56 rows=1 width=8)
-> Parallel Hash Join (cost=13205.83..25810.25 rows=207320 width=0)
Hash Cond: (i.order_id = o.id)
-> Parallel Seq Scan on order_items i (cost=0.00..11510.67 rows=416667 width=8)
-> Parallel Hash (cost=10614.33..10614.33 rows=207320 width=8)
-> Parallel Seq Scan on orders o (cost=0.00..10614.33 rows=207320 width=8)
Filter: (customer_id = 1)
(10 rows)
RESET
The free diagnostic PREPAREs the same statement and EXPLAINs it under each mode. Under SET plan_cache_mode = force_generic_plan it shows the plan the application is stuck with: 'Nested Loop (cost=0.85..296.40 rows=34 width=0)' over an Index Scan estimating 34 rows -- cheap, and catastrophically wrong for the whale.
Under SET plan_cache_mode = force_custom_plan it shows the plan the value actually deserves: 'Finalize Aggregate ... Gather ...
Parallel Hash Join (cost=13205.83..25810.25 rows=207320 width=0)' over parallel sequential scans of both tables -- sized for ~207,320 rows, the correct order of magnitude for a 500,000-row customer. Same SQL, two completely different plans: a Nested Loop guessing 34 rows versus a Hash Join built for half a million.
That gap IS the bug, and any operator can see it with two EXPLAINs and no extensions. What the free read does NOT tell you is which OTHER columns and statements across the database are sitting on the same trap -- that fleet-wide, predictive view is the premium step.
Fix
Stop reusing the cached generic plan: force the planner to re-plan with the real bind value. One session GUC does it, the whale re-plans as a Hash Join, and the loops=500000 inner-lookup storm is gone in a single pass.
PREPARE q(int) AS SELECT count(*) FROM orders o JOIN order_items i ON i.order_id = o.id WHERE o.customer_id = $1; SET plan_cache_mode = force_custom_plan;
PREPARE SET
EXPLAIN (COSTS ON) EXECUTE q(1);
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=27328.77..27328.78 rows=1 width=8)
-> Gather (cost=27328.55..27328.76 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=26328.55..26328.56 rows=1 width=8)
-> Parallel Hash Join (cost=13205.83..25810.25 rows=207320 width=0)
Hash Cond: (i.order_id = o.id)
-> Parallel Seq Scan on order_items i (cost=0.00..11510.67 rows=416667 width=8)
-> Parallel Hash (cost=10614.33..10614.33 rows=207320 width=8)
-> Parallel Seq Scan on orders o (cost=0.00..10614.33 rows=207320 width=8)
Filter: (customer_id = 1)
(10 rows)
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF, SUMMARY OFF, COSTS OFF) EXECUTE q(1); RESET plan_cache_mode;
QUERY PLAN
------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=1 loops=1)
-> Gather (actual rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (actual rows=1 loops=3)
-> Parallel Hash Join (actual rows=166667 loops=3)
Hash Cond: (i.order_id = o.id)
-> Parallel Seq Scan on order_items i (actual rows=333333 loops=3)
-> Parallel Hash (actual rows=166667 loops=3)
Buckets: 524288 Batches: 1 Memory Usage: 23712kB
-> Parallel Seq Scan on orders o (actual rows=166667 loops=3)
Filter: (customer_id = 1)
Rows Removed by Filter: 166667
(13 rows)
RESET
The fix is SET plan_cache_mode = force_custom_plan. The capture PREPAREs the statement, sets the GUC, and EXPLAINs the whale: it now plans as 'Finalize Aggregate ...
Gather ... Parallel Hash Join (cost=13205.83..25810.25 rows=207320 width=0)' over parallel sequential scans -- the plan sized for its true 500,000-row result, not the 34-row Nested Loop.
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF, SUMMARY OFF, COSTS OFF) confirms it runs in one pass: 'Parallel Hash Join (actual rows=166667 loops=3)' across two workers and the leader, a single hash build-and-probe with NO inner 'loops=500000' -- the half-million-iteration storm has vanished because the join is now done by hashing both relations once instead of descending an index per outer row. Nothing about the data, the indexes, or the statistics changed; the only change is that PostgreSQL is re-planning with the actual bind value on each execution, so a skewed value gets the plan it deserves.
The cost is a little planning time per execution; the payoff is that no single cached plan can be wrong for the whale.
Verify
Healthy-state proof: force_custom_plan is the right answer for EVERY bind value, not just the whale -- the heavy value gets a Hash Join and the common value still gets the cheap index lookup, with no penalty either way.
PREPARE q(int) AS SELECT count(*) FROM orders o JOIN order_items i ON i.order_id = o.id WHERE o.customer_id = $1; SET plan_cache_mode = force_custom_plan;
PREPARE SET
EXPLAIN (COSTS OFF) EXECUTE q(1);
QUERY PLAN
-------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 2
-> Partial Aggregate
-> Parallel Hash Join
Hash Cond: (i.order_id = o.id)
-> Parallel Seq Scan on order_items i
-> Parallel Hash
-> Parallel Seq Scan on orders o
Filter: (customer_id = 1)
(10 rows)
EXPLAIN (COSTS OFF) EXECUTE q(2); RESET plan_cache_mode;
QUERY PLAN
--------------------------------------------------------------------------
Aggregate
-> Nested Loop
-> Index Scan using orders_customer_idx on orders o
Index Cond: (customer_id = 2)
-> Index Only Scan using order_items_order_idx on order_items i
Index Cond: (order_id = o.id)
(6 rows)
RESET
The verify capture PREPAREs the statement under SET plan_cache_mode = force_custom_plan and EXPLAINs two different bind values. For the whale (customer_id=1, ~500,000 rows) it plans as the 'Finalize Aggregate ...
Gather ... Parallel Hash Join' -- correct for a huge result.
For a normal customer (customer_id=2, one row) it still plans as the cheap 'Aggregate -> Nested Loop -> Index Scan ... (customer_id = 2) -> Index Only Scan' lookup -- correct for a tiny result.
That is the whole point of custom planning: each bind value gets the plan that fits it, so there is no explosion for the whale AND no overhead for the singletons. Taken with the fix capture, this pins the diagnosis beyond doubt -- a single cached generic plan was the cause (it could only be right for one regime), and re-planning per value is the cure (it is right for both).
No index was added, no statistic was changed, no row was touched; only the planner's freedom to cache one plan was removed.
⏪ Rollback
Nothing here is destructive, and the entire reproduction runs against a throwaway database created and dropped on a PostgreSQL instance; the lab is torn down at the end and only the literal capture files are kept. No global server setting is touched: the fix and prevention both use plan_cache_mode, which is an ordinary planner GUC. SET plan_cache_mode = force_custom_plan applies only to the current session and reverts on disconnect (or RESET), so it is trivially reversible and safe to try live -- the worst case is slightly more planning time per execution, never wrong results. The durable prevention statement is ALTER DATABASE <db> SET plan_cache_mode = force_custom_plan: it stores a per-database default that NEW connections inherit, changes no data and no plan immediately, and is reversed at any time with ALTER DATABASE <db> RESET plan_cache_mode. The recipe deliberately scopes the durable setting to the throwaway database (so it disappears when the database is dropped) and never uses ALTER ROLE or ALTER SYSTEM, which would persist on the shared test environment. On a real system, prefer the narrowest scope that covers the affected workload -- a session SET in the offending code path, a per-role ALTER ROLE for the application user, or a per-database ALTER DATABASE -- rather than a cluster-wide ALTER SYSTEM, so unrelated workloads keep auto-mode's planning-time savings. None of these statements rewrite tables, move data, or alter schema; they only change which plan the planner is allowed to cache, so there is nothing to 'undo' beyond resetting the GUC.
📦 Version Matrix (PG 14--18)
The generic-plan-on-a-skewed-predicate regression is identical core behaviour on every supported major version. Each row is an independent run against a fresh throwaway database on that version's test environment node: build a scaled-down skew (the whale owns 200,000 rows, plus 100,000 singletons), PREPARE the join, then show the generic plan, execute it for the whale, and show the custom plan:
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
generic=Nested Loop whale-exec=loops=200000 | ✗ Not available -- use stats_reset age |
PG 15.18 |
generic=Nested Loop whale-exec=loops=200000 | ✗ Not available -- use stats_reset age |
PG 16.14 |
generic=Nested Loop whale-exec=loops=200000 | ✗ Not available -- use stats_reset age |
PG 17.10 |
generic=Nested Loop whale-exec=loops=200000 | ✗ Not available -- use stats_reset age |
PG 18.4 |
generic=Nested Loop whale-exec=loops=200000 | ✗ Not available -- use stats_reset age |
⚠ 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 is my query fast for almost every customer but takes minutes for one of them, with the same SQL?
The query is run as a server-side prepared statement, and after a few executions PostgreSQL caches a single GENERIC plan that ignores the bind value and uses the column's AVERAGE selectivity. On a skewed column (one customer owns half the rows) that average says 'a few rows', so the cached plan is a Nested Loop with a per-row inner lookup -- perfect for a typical customer. When you execute it for the heavy 'whale' value, that same Nested Loop runs its inner index scan once per matching row (loops=500000 in the lab) instead of switching to a Hash Join. No error is raised; the statement just falls off a latency cliff.
Why does EXPLAIN look fine when I paste the slow id in by hand?
Because a literal value is always CUSTOM-planned -- the planner sees the actual value, uses its per-value statistics, and picks the right plan (a Hash Join for the whale). The bad plan only exists on the prepared-statement path, where the bind value is unknown at plan time and the cached generic plan is reused. To see what the application actually runs, reproduce through PREPARE/EXECUTE or SET plan_cache_mode = force_generic_plan, not with a literal.
How do I fix it right now?
SET plan_cache_mode = force_custom_plan in the session (or for the affected workload). That tells PostgreSQL to re-plan with the real bind value on every execution, so the whale re-plans as a Hash Join sized for its true row count and the loops=500000 inner-lookup storm disappears. It is a session GUC, reverts on disconnect, never produces wrong results, and costs only a little extra planning time per execution.
Isn't PostgreSQL's auto mode supposed to protect me from this?
Auto mode custom-plans the first five executions, then compares the generic plan's cost to the average custom cost and keeps the generic plan if it isn't meaningfully more expensive. The trap is that the generic plan UNDERESTIMATES the skewed value's row count, so its self-reported cost is LOWER than the custom whale cost (in the lab, ~296 vs ~27,328). The cost comparison therefore concludes 'generic is cheaper' and caches the very plan that is catastrophic for the whale. For skewed predicates, auto mode is exactly what caches the bad plan -- so pin force_custom_plan.
How do I find these dangerous queries before they cause an incident?
Scan pg_stats for columns with a high skew factor -- the heaviest value's frequency divided by the average frequency, computed as top_value_freq * n_distinct. In the lab, orders.customer_id shows a skew_factor of ~14,631 (its top value is ~14,631x more common than average), which is precisely the gap a generic plan walks into. Any skewed column used in a prepared-statement WHERE clause is a candidate; the premium diagnostic ranks them all from catalog statistics alone, with no query execution and no incident required.
How do I prevent it durably, and at what scope?
Pin plan_cache_mode = force_custom_plan for the workload that filters the skewed column. Choose the narrowest scope that covers it: a session SET in the offending code path, a per-role ALTER ROLE for the application user, or a per-database ALTER DATABASE ... SET plan_cache_mode = force_custom_plan (which new connections inherit -- the lab proves the setting is stored as the database default). Avoid a cluster-wide ALTER SYSTEM, which would surrender auto mode's planning-time savings for every unrelated query on the server.
References
- PREPARE -- notes on generic vs custom plans and plan_cache_mode
- Server Configuration -- plan_cache_mode (auto / force_generic_plan / force_custom_plan)
- Using EXPLAIN -- reading actual rows, loops, and estimate vs actual
- Row Estimation Examples -- how selectivity is estimated from MCVs and n_distinct
- pg_stats -- n_distinct, most_common_vals, most_common_freqs
- Planner Statistics -- what ANALYZE collects and how the planner uses it
- Nested Loop / Hash Join -- join strategies and when each is chosen
- ALTER DATABASE -- SET a per-database configuration default
- ALTER ROLE -- SET a per-role configuration default