Generic vs Custom Plan Selection in PostgreSQL #

When you PREPARE a statement, PostgreSQL does not commit to one plan for its lifetime. For the first five executions it builds a fresh custom plan tailored to the exact parameter values you pass. Only then does it decide whether to freeze a single generic plan that ignores the values entirely. That decision — invisible in ordinary application logs — is the root cause of a whole class of “it was fast until it wasn’t” regressions. This page walks through the exact mechanism, how to observe the switch, and how to reproduce and repair a skew-driven blowup.

The Five-Execution Rule #

The choice between plan types is part of Prepared Statement Plan Pinning, and it follows a fixed algorithm inside the plan cache. For a statement with bound parameters:

  1. Executions 1 through 5 always produce a custom plan, re-planned with the literal parameter values each time. PostgreSQL records the cost of each of these plans.
  2. Starting with the sixth execution, PostgreSQL computes the average custom plan cost across those runs and compares it to the cost of a single generic plan — one planned with the parameters left symbolic.
  3. If the generic plan cost is not greater than the average custom cost (plus a small allowance for the planning time custom plans keep paying), PostgreSQL switches to the generic plan and caches it. From then on, every execution reuses that frozen plan and skips planning entirely.
  4. If the generic plan is more expensive, PostgreSQL keeps building custom plans indefinitely.

The trap is step 3. The generic plan is built without knowing your values, so for a predicate like status = $1 it falls back to default selectivity derived from the column’s average distribution. When your real values are uniform, that estimate is close enough and the generic plan is both cheap and correct. When your values are skewed — some appear far more often than others — the generic estimate is an average that matches no individual value, and the frozen plan can be catastrophically wrong for the common ones.

Generic versus custom plan decision flow A timeline showing executions one through five each building a custom plan, then at execution six PostgreSQL comparing the average custom plan cost to the generic plan cost and switching to the generic plan only if it is not more expensive. Executions 1–5: always custom exec 1 custom exec 2 custom exec 3 custom exec 4 custom exec 5 custom generic cost ≤ avg custom cost? YES Freeze generic plan default selectivity, no re-plan NO Keep custom plans re-plan every execution The frozen generic plan is where skew-driven regressions appear

Observing the Switch #

The plan cache exposes its counters through pg_prepared_statements. Prepare a query against an orders table whose status column is heavily skewed — the vast majority of rows are 'shipped', and only a sliver are 'refunded':

PREPARE orders_by_status AS
  SELECT order_id, customer_id, total_amount
  FROM orders
  WHERE status = $1;

-- Run it six times so the cache crosses the five-execution threshold
EXECUTE orders_by_status('refunded');   -- exec 1: custom
EXECUTE orders_by_status('refunded');   -- exec 2: custom
EXECUTE orders_by_status('refunded');   -- exec 3: custom
EXECUTE orders_by_status('refunded');   -- exec 4: custom
EXECUTE orders_by_status('refunded');   -- exec 5: custom
EXECUTE orders_by_status('refunded');   -- exec 6: generic decision happens here

Now read the counters:

SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements
WHERE name = 'orders_by_status';
      name        | generic_plans | custom_plans
------------------+---------------+--------------
 orders_by_status |             1 |            5   -- ← 5 custom then switched to generic

The custom_plans counter stopped at 5 and generic_plans began climbing. The cache has frozen a generic plan built with status = $1 left symbolic. To see what that plan looks like, ask the planner directly:

EXPLAIN EXECUTE orders_by_status('refunded');
Index Scan using idx_orders_status on orders
  (cost=0.42..8.55 rows=430 width=24)          -- ← rows=430 is the AVERAGE selectivity
  Index Cond: (status = $1)                    -- ← $1 placeholder = generic plan, value ignored

The $1 in Index Cond is the tell: the plan is generic and the rows=430 estimate is the average number of rows per distinct status value, not the count for 'refunded'. The planner literally cannot see which value you will pass. Contrast that with a custom plan, which the cost estimation models feed with the real literal and its column statistics.

Reproducing the Regression #

The skew makes the average a lie. Suppose orders holds 4.3 million rows across ten statuses. Nine of them are rare, but 'shipped' alone accounts for 3.9 million rows and sits at the top of the column’s most-common-values list. Confirm that:

SELECT most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
 most_common_vals |   most_common_freqs
------------------+-----------------------
 {shipped, ...}   | {0.907, ...}          -- ← 'shipped' is ~91% of the table

The generic plan’s rows=430 estimate is fine for 'refunded' but wildly wrong for 'shipped'. Because the cached generic plan chose an Index Scan based on that tiny estimate, executing it with the common value forces the planner-blind path through the index for nearly the whole table:

EXPLAIN (ANALYZE, BUFFERS) EXECUTE orders_by_status('shipped');
Index Scan using idx_orders_status on orders
  (cost=0.42..8.55 rows=430 width=24)                 -- ← still estimates 430 rows (generic)
  (actual time=0.03..2140.7 rows=3900000 loops=1)     -- ← actual 3.9M rows: 9000x off
  Index Cond: (status = $1)
  Buffers: shared hit=210 read=1240500                 -- ← massive random heap fetches

The estimate is off by roughly four orders of magnitude, and the plan pays for it with millions of random heap fetches where a sequential or bitmap scan would have streamed the table far more cheaply. This is the signature of a generic plan meeting skewed data.

Before/After: Rows Estimate and Plan #

Forcing a custom plan re-plans with the literal 'shipped', so the estimate reflects the true frequency and the planner picks the appropriate scan:

-- BEFORE (frozen generic plan, $1 symbolic, default selectivity):
Index Scan using idx_orders_status on orders
  (cost=0.42..8.55 rows=430 ...)                       -- estimate: 430 rows
  (actual rows=3900000)  Buffers: read=1240500          -- reality: 3.9M rows, random I/O

-- AFTER (custom plan, literal 'shipped' visible to the planner):
Seq Scan on orders
  (cost=0.00..79210.00 rows=3900000 ...)               -- estimate: 3.9M rows (accurate)
  (actual rows=3900000)  Filter: (status = 'shipped')   -- sequential streaming, no index thrash

The estimate lines up with reality and the plan switches from a value-blind Index Scan to a Seq Scan that reads the table in physical order. The fix, covered in depth under Forcing a Custom Plan with plan_cache_mode, is to keep PostgreSQL re-planning with the real value for this workload.

Step-by-Step Diagnosis and Fix #

Step 1 — Confirm the plan type from the cache #

Execute the statement at least six times and read the counters:

SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements
WHERE name = 'orders_by_status';

A nonzero generic_plans means the cache has frozen a value-blind plan.

Step 2 — Inspect the frozen plan #

EXPLAIN EXECUTE orders_by_status('shipped');   -- look for $1 in the conditions

A $1 placeholder and a suspiciously round rows estimate confirm you are looking at a generic plan using default selectivity.

Step 3 — Quantify the misestimate #

EXPLAIN (ANALYZE, BUFFERS) EXECUTE orders_by_status('shipped');

Compare estimated rows to actual rows. A gap of one or more orders of magnitude on a common value is the regression.

Step 4 — Verify the skew in statistics #

SELECT most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

If the parameter value appears in most_common_vals with a high frequency, the generic average cannot represent it.

Step 5 — Force custom plans for the workload #

SET plan_cache_mode = force_custom_plan;   -- re-plan with the real value every execution

Scope this to the session, role, or connection serving the skewed queries rather than the whole cluster.

Step 6 — Validate the fix #

EXPLAIN EXECUTE orders_by_status('shipped');   -- now shows the literal and accurate estimate

The conditions should now show status = 'shipped' with a rows estimate close to the true count, and the scan node should match the data volume.

Common Pitfalls #

Partition pruning is deferred under generic plans. On a partitioned shipments table, a custom plan prunes partitions at plan time using the literal value. A generic plan defers to runtime pruning, which still works but shows all partitions in EXPLAIN (without ANALYZE) and can defeat plan-time optimizations that depend on knowing the target partition. If you rely on aggressive pruning, verify it survives the generic switch.

IN-list length changes the plan and defeats caching. A statement like status IN ($1, $2, $3) is a different prepared statement from one with four placeholders. ORMs that build IN-lists dynamically generate a new statement for every distinct list length, so none of them ever reach the five-execution threshold and each pays full planning cost. Watch for a proliferation of nearly identical entries in pg_prepared_statements.

ORM auto-prepare hides the behavior. Drivers such as the PostgreSQL JDBC driver and many pooled ORMs promote frequently executed queries to server-side prepared statements automatically. The five-execution switch then happens without any explicit PREPARE in your application code, so a query that was fast in testing regresses in production once it crosses the threshold. This effect is amplified by connection pooling; see Prepared Statement Plan Pinning and the broader Runtime Environment pillar for how pooling and the plan cache interact.

Assuming the switch is permanent. The generic decision is re-evaluated if the statement is re-prepared, and ANALYZE refreshing statistics can change both the generic and custom cost estimates. A regression that appears after an autovacuum-triggered ANALYZE may trace back to a shifted generic plan cost crossing the comparison threshold.

Frequently Asked Questions #

Why does my prepared statement get slower after the fifth execution? After five custom-plan executions PostgreSQL compares the average custom plan cost to the generic plan cost and switches to the generic plan if it is not more expensive. The generic plan uses default selectivity for the bound parameters, so if your parameter values hit a most-common-value the estimate collapses and the wrong scan is chosen. The sixth execution reuses that cached generic plan and runs slowly.

How can I see whether a statement is using a generic or custom plan? Query pg_prepared_statements and read the generic_plans and custom_plans counters for the statement. You can also run EXPLAIN EXECUTE stmt(...): a generic plan shows $1-style parameter placeholders and default-selectivity row estimates, while a custom plan shows literal values and value-specific estimates.

Does setting plan_cache_mode to force_custom_plan hurt performance? It adds planning cost to every execution because the statement is re-planned with the actual parameter values each time. For skewed data where the generic plan misestimates badly this is a net win, but for uniform data with a high execution rate the repeated planning overhead can outweigh the benefit. Scope the setting to the affected workload rather than applying it globally.

Related