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.
The five-execution lifecycle The first five executions build custom plans and record their costs; from the sixth, the planner compares the average custom cost against a generic plan and keeps the cheaper. 12345 custom plans — parameters known cost comparison generic cheaper? keep it a regression that arrives on the sixth execution is this comparison, not your data

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.

One plan for very different parameter values A generic plan estimates from average selectivity, which suits mid-frequency values but badly misjudges both the rarest and the most common ones. generic plan's single assumption very common value rare value the flatter your distribution, the safer a generic plan is

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.

The five-execution rule exists to balance two real costs. Planning a statement repeatedly is wasteful when the parameters barely change the shape of the answer; reusing one plan is dangerous when they do. PostgreSQL resolves this empirically: it builds custom plans for the first five executions, records what they cost, and from the sixth compares their average against the cost of a single generic plan. If the generic plan is not more expensive, it wins and is reused from then on.

That comparison is made on estimated cost, which is the crux. The generic plan’s estimate is computed from average selectivity, so for a column whose values are evenly distributed it is a good approximation and the switch is invisible. For a skewed column — a tenant identifier where one tenant owns most of the rows, a status flag that is overwhelmingly one value — the average describes no real parameter, and the generic plan can be simultaneously cheap on paper and catastrophic in practice for the values that matter.

Recognising the regression is straightforward once you know the shape: performance is stable for the first five executions of a prepared statement and steps sharply afterwards, with no data change and no deployment. Reproducing it deliberately is a single setting, and the fix is usually to force custom plans for that statement’s role and accept the planning cost. Where planning cost genuinely matters — a statement executing thousands of times per second — the answer is instead to remove the skew from the equation, either with better statistics or by splitting the workload so that the pathological values are handled by their own query.

The setting can be applied at three scopes, and the narrowest one that works is always the right choice. A transaction-scoped SET LOCAL is a diagnostic, a role-level default confines the change to one workload, and a system-level change affects every statement on the server including the many that were behaving perfectly.

Fixing the symptom on one statement and leaving the pattern. Skewed parameter distributions are rarely confined to a single query; the column that made one statement regress usually appears in several. Diagnostic signal: a second regression on a different statement over the same column weeks later. Fix: treat the column as the problem — raise its statistics target, or split the pathological values into their own code path — rather than pinning plan modes one statement at a time.

Assuming the switch happens at a predictable moment. The count is per prepared statement per backend, so behind a pooler different connections cross the threshold at different times, and a rolling deployment resets some of them. The result is a regression that affects a fraction of traffic and moves around. Diagnostic signal: a latency histogram that develops a second mode rather than shifting as a whole. Fix: look at the plan on several backends, not one.

Assuming every client library prepares statements. Whether a statement enters the plan cache at all depends on the driver and its configuration: some prepare everything, some prepare after a threshold, and some send every statement unprepared. The five-execution rule only applies to the first two. Diagnostic signal: a search for a generic-plan regression on a workload that never prepares anything. Fix: confirm from the server log or pg_prepared_statements that the statement is prepared before investigating plan-cache behaviour at all.

Forgetting that the counter is per backend. Each connection maintains its own execution count for its own prepared statements, so behind a pool the transition happens independently on every backend. Diagnostic signal: a regression affecting a stable fraction of requests rather than all of them. Fix: sample plans across several backends before concluding the switch did or did not happen.

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