Caching Plans: Prepared Statements and the Generic Plan

The one thing to understand first

Every SQL statement goes through parse → analyze → rewrite → plan → execute. For repeated queries that differ only in parameter values, redoing parse and plan each time is wasted work. Prepared statements cache the parsed and (sometimes) planned form so subsequent executions skip that overhead. The logic lives in src/backend/utils/cache/plancache.c.

Plan caching is a bet that one plan fits all parameter values — and on skewed data that bet eventually loses. The classic failure is a query that is fast for the first five executions, then "warms up" into a generic plan and becomes pathologically slow for certain inputs.

Prepared statements

PREPARE getcust (int) AS
  SELECT * FROM orders WHERE customer_id = $1;

EXECUTE getcust(42);

Most applications use the protocol-level equivalent automatically — drivers like JDBC and libpq issue Parse/Bind/Execute messages, creating server-side prepared statements without explicit PREPARE.

Custom vs generic plans

A prepared statement can be planned two ways:

• A custom plan is planned with the actual parameter values, so the planner uses real selectivity from statistics — best plan, but pays planning cost each time.
• A generic plan is planned once, parameter-agnostic, and reused — no planning cost, but it cannot exploit value-specific selectivity.

The five-execution heuristic

PostgreSQL does not commit to generic immediately. By default it builds a custom plan for the first five executions, recording their cost. From the sixth execution onward, if the estimated cost of a generic plan is not significantly higher than the average custom-plan cost, it switches to the generic plan to save planning time. If custom plans are consistently much cheaper, it keeps re-planning. This adaptive behaviour is in choose_custom_plan().

When generic plans hurt

The danger case is skewed data with a parameterised predicate. Suppose status = $1: for status = 'archived' (99% of rows) a seq scan is right; for status = 'error' (0.01%) an index scan is right. A single generic plan must pick one shape for all values and will be wrong for some. The symptom is a query that is fast for most inputs and pathologically slow for a few — and only after it "warms up" past five executions.

Layer 3 — Watch it happen on your own database

PREPARE p (text) AS SELECT * FROM orders WHERE status = $1;

-- First five executions: custom plans (planned with the value)
EXPLAIN (ANALYZE) EXECUTE p('error');     -- index scan, value-appropriate
-- ... repeat a few times ...
-- Sixth onward may flip to a generic plan:
EXPLAIN (ANALYZE) EXECUTE p('error');     -- watch the plan shape change

-- Force each mode and compare directly
SET plan_cache_mode = 'force_generic_plan';
EXPLAIN (ANALYZE) EXECUTE p('error');     -- one plan for all values
SET plan_cache_mode = 'force_custom_plan';
EXPLAIN (ANALYZE) EXECUTE p('error');     -- re-planned with the value

Run the same EXECUTE repeatedly and watch the plan shape. Under auto, the first five are custom; from the sixth, choose_custom_plan() may switch to a generic plan if it isn't much more expensive on average. Toggling plan_cache_mode between force_generic_plan and force_custom_plan lets you see, side by side, whether the generic plan is the one hurting a skewed parameter.

Layer 4 — The levers this hands you

• **plan_cache_mode** — force_custom_plan trades a little planning CPU for stable, value-appropriate plans on skewed parameters; force_generic_plan cuts latency for uniform high-frequency lookups; auto is the five-execution heuristic.
• Let drivers prepare hot queries — JDBC/libpq issue Parse/Bind/Execute automatically; the parse/plan savings are real at high QPS.
• Watch the "fast then suddenly slow after warmup" signature — that's a bad generic plan; reach for force_custom_plan on that path.
• Coordinate with the pooler. In PgBouncer transaction mode a server-side prepared statement may land on a backend that never prepared it → prepared statement "xxx" does not exist. Fix with max_prepared_statements, a re-preparing driver, or disabling server-side prepares for that path.
• Don't cache one-off analytic queries — custom planning with real values is what you want there.

Layer 5 — What an Oracle DBA should expect vs what they get

Oracle DBAs have fought this exact battle under different names:

• Generic plan ≈ bind peeking gone stale. A Postgres generic plan that's wrong for skewed values is the direct analogue of Oracle's classic bind-variable peeking problem — one shared plan that suits some binds and ruins others.
• Custom plans ≈ Adaptive Cursor Sharing's intent, made explicit. Oracle's ACS detects bind-sensitive cursors and spawns multiple child cursors automatically; Postgres instead lets you pin behaviour with plan_cache_mode — more manual, but predictable.
• No shared plan cache across sessions. Oracle caches cursors in the shared SGA library cache for all sessions; PostgreSQL prepared-statement plans are per backend, which is exactly why pooling can lose them.
• **No CURSOR_SHARING / no hints.** There is no FORCE literal-replacement knob and no plan hints — the five-execution heuristic plus plan_cache_mode are the whole toolkit.

Key takeaway

Prepared statements skip repeated parse/plan work; choose_custom_plan() builds value-specific custom plans for the first five executions, then may switch to a reusable generic plan. On skewed parameters the generic plan can be badly wrong — the "fast then slow after warmup" tell. Use plan_cache_mode to force the right behaviour per workload, and coordinate prepared statements with your pooler so they don't vanish between transactions.