Prepared Statement Plan Pinning: Custom vs Generic Plans #

A prepared statement does not run the same plan forever. PostgreSQL quietly changes strategy after a warm-up period: the first executions are planned freshly against the real parameter values, and then — if it looks cheap enough — the server pins a single cached plan that ignores those values entirely. When the parameters are skewed, that transition turns a fast query into a slow one with no change to the SQL text, no deployment, and no obvious trigger.

This page walks the plan lifecycle, shows the exact difference between a custom and a generic plan in EXPLAIN output, and gives you the plan_cache_mode lever to control it. It is a core piece of the runtime environment story of why applications and psql disagree.

The Five-Execution Lifecycle #

When a statement is prepared — via explicit PREPARE or the extended query protocol your driver uses — PostgreSQL tracks how many times it has been executed and applies a specific rule:

  1. Executions 1 through 5 use a custom plan. Each execution is re-planned with the actual bind values substituted, so selectivity estimates reflect the real parameter. This costs planning time every call but produces a parameter-aware plan.
  2. From execution 6 onward PostgreSQL computes the average cost of the five custom plans and compares it to the cost of a generic plan (planned once, treating the parameter as an unknown using average selectivity). If the generic plan’s estimated cost is not greater than the average custom cost plus the estimated planning cost it would save, PostgreSQL pins the generic plan and stops re-planning.

The comparison is cost-based, so a statement whose custom and generic costs are similar will settle on the generic plan to save planning time — usually the right call. The danger is entirely in the skew case, where the average custom cost is low but specific parameters are expensive.

Custom-to-generic plan state machine Executions one through five each build a custom plan. At execution six a cost comparison node compares the average custom cost to the generic-plan cost, then branches: if the generic plan is not more expensive it pins the generic plan for all later executions, otherwise it keeps building custom plans. Exec 1–5 custom plan (per param) re-plan each time at exec 6 avg custom vs generic? generic ≤ custom Pin generic plan ignores param, no re-plan generic > custom Keep custom plan re-plan each exec

Why Generic Plans Are Dangerous for Skewed Data #

A generic plan is planned with the parameter as an unknown, so it estimates row counts from average selectivity — for an equality predicate, roughly 1 / n_distinct of the table, adjusted by the most-common-value list only in aggregate. That average is fine when the column is uniformly distributed. It is a landmine when the column is skewed.

Consider an events table where event_type = 'click' covers 60% of rows but event_type = 'refund' covers 0.01%. A custom plan for 'refund' sees the tiny selectivity and picks an index scan; a custom plan for 'click' sees the huge selectivity and picks a sequential scan. The generic plan sees only the average and commits to one strategy for both — and whichever it picks is catastrophically wrong for one of the two values. This is fundamentally a cost estimation failure driven by averaging over a non-uniform distribution, and it is the same mechanism behind an unexpected sequential scan replacing an index scan.

Annotated: Generic vs Custom in EXPLAIN #

The difference is visible in the Index Cond line. A generic plan shows the placeholder $1; a custom plan shows the literal.

PREPARE evt (text) AS
  SELECT event_id, payload FROM events WHERE event_type = $1;

-- Force the generic plan to inspect it:
SET plan_cache_mode = force_generic_plan;
EXPLAIN EXECUTE evt('refund');
Seq Scan on events  (cost=0.00..18450.00 rows=42000 width=64)
  Filter: (event_type = $1)
  -- $1 placeholder → GENERIC plan
  -- rows=42000 is AVERAGE selectivity across all event_type values.
  -- For 'refund' (0.01% of rows) the real count is ~40, so this Seq Scan
  -- reads the whole table when a 40-row index scan would do.
-- Now the custom plan for the same rare parameter:
SET plan_cache_mode = force_custom_plan;
EXPLAIN EXECUTE evt('refund');
Index Scan using events_event_type_idx on events
    (cost=0.43..162.05 rows=40 width=64)
  Index Cond: (event_type = 'refund'::text)
  -- literal 'refund' → CUSTOM plan, planned for THIS parameter
  -- rows=40 reflects the true rarity → cheap index scan chosen

You can watch the switch happen through the counters in pg_prepared_statements:

SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements
WHERE name = 'evt';
 name | generic_plans | custom_plans
------+---------------+--------------
 evt  |            37 |            5
-- 5 custom plans during warm-up, then 37 generic executions after pinning.
-- A climbing generic_plans column on a skewed statement is the smell to chase.

Numbered Diagnostic Workflow #

Step 1 — Reproduce the generic plan.

SET plan_cache_mode = force_generic_plan;
PREPARE evt (text) AS SELECT event_id, payload FROM events WHERE event_type = $1;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE evt('click');
EXPLAIN (ANALYZE, BUFFERS) EXECUTE evt('refund');
-- Note actual time for BOTH a common and a rare parameter under the generic plan.

Step 2 — Reproduce the custom plan with a skewed parameter.

SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE evt('click');
EXPLAIN (ANALYZE, BUFFERS) EXECUTE evt('refund');
-- The custom plan should adapt: seq scan for 'click', index scan for 'refund'.

Step 3 — Compare actual time and row estimates.

Line up the two runs. If the generic plan’s actual time is dramatically worse for one parameter class, and the custom plan’s extra planning time is small relative to that gap, the custom plan wins on real traffic. Quantify the planning overhead from the Planning Time line.

Step 4 — Set plan_cache_mode for the workload.

-- Pin the decision so it survives reconnects and does not depend on
-- the per-backend execution counter.
ALTER ROLE app_user SET plan_cache_mode = 'force_custom_plan';

Use auto (the default) when custom and generic costs are close; use force_custom_plan when skew makes the generic plan misfire. The forcing a custom plan with plan_cache_mode page covers scoping the override safely, and generic vs custom plan selection covers the cost comparison in depth.

Common Pitfalls #

The sixth-execution regression. Diagnostic: a prepared statement is fast five times then abruptly slow, repeatably. Fix: PostgreSQL pinned a generic plan at execution six; set plan_cache_mode = force_custom_plan for the statement or role, or confirm the parameter distribution is uniform enough that the generic plan is safe.

Skewed IN lists under a generic plan. Diagnostic: WHERE id = ANY($1) where the array length varies wildly; the generic plan assumes a fixed selectivity. Fix: force a custom plan so the plan adapts to array length, or split hot and cold cases into separate statements.

Partition pruning weakens under a generic plan. Diagnostic: a partitioned table scans all partitions under the generic plan but only one under a custom plan. Fix: run-time pruning does still occur for generic plans on modern PostgreSQL, but plan-time pruning does not, so the plan can list every partition; force a custom plan where plan-time pruning matters, and verify pruning in EXPLAIN.

Extended-query protocol vs explicit PREPARE confusion. Diagnostic: you cannot reproduce the app’s generic plan with PREPARE in psql. Fix: the app reaches the five-execution threshold through the driver’s protocol-level Parse/Bind, not PREPARE; reproduce by executing the prepared statement six or more times, or force the generic plan explicitly to inspect it.

Frequently Asked Questions #

Why does my prepared statement get slow on the sixth execution? #

For the first five executions PostgreSQL builds a custom plan each time using the real parameter values. On the sixth it compares the average custom-plan cost to the generic-plan cost and, if the generic plan is not estimated to be more expensive, pins the generic plan. If your parameters are skewed, the generic plan built from average selectivity can be far worse than the custom plans, so latency jumps exactly at the sixth execution. Setting plan_cache_mode = force_custom_plan removes the cliff.

What is the difference between a custom plan and a generic plan? #

A custom plan is re-planned for each execution using the actual bind values, so its row estimates reflect the specific parameter. A generic plan is planned once with the parameter treated as an unknown, using average column selectivity from pg_statistic. Custom plans cost planning time on every call but adapt to skew; generic plans are planned once but ignore the parameter value entirely. The planner’s auto mode tries to pick whichever is cheaper on average.

How do I force PostgreSQL to keep using a custom plan? #

Set plan_cache_mode to force_custom_plan, either for the session with SET or pinned for the workload with ALTER ROLE. This disables the switch to a generic plan and re-plans with the real parameters on every execution. It trades a little extra planning time for parameter-aware plans, which is usually the right choice for queries over skewed columns. See forcing a custom plan with plan_cache_mode for how to scope the override without slowing down uniform queries.

Up: Runtime Environment