Generic vs Custom Plans in PostgreSQL: When Prepared Statements Lie to You

A team I worked with had an API endpoint that was fast for every customer except their biggest one, and only after the application had been running for a few minutes. Restart the service, fast again. Wait, slow again — for that one customer. The root cause turned out to be a five-line heuristic deep in the planner, and the fix was one setting. That heuristic is the generic-plan switch, and if you run PostgreSQL behind an ORM or a driver that prepares statements, it is already deciding your query plans. This is how it works and how to stop it from hurting you.

The lifecycle of a prepared statement

When a query is prepared — via SQL PREPARE, the extended query protocol, or a driver's prepareThreshold — PostgreSQL parses and rewrites it once, then on each execution decides whether to plan it fresh. A custom plan is built with the actual parameter values for that execution, so the planner can use histograms to estimate selectivity precisely. A generic plan is built once with no knowledge of parameter values, using average selectivity assumptions, and reused across executions.

The motivation is cost. Planning is CPU work on the hot path; for simple OLTP queries the planning time can rival execution time. Reusing a generic plan amortizes that away. The risk is quality: a plan built without knowing the parameter can be optimal on average and catastrophic for specific values.

The 5-execution heuristic

PostgreSQL does not decide once. For the first five executions of a prepared statement it always builds custom plans and records their costs. From the sixth execution on, it compares the average cost of the custom plans seen so far against the estimated cost of a generic plan plus the saved planning overhead, and if the generic plan looks no worse, it sticks with the generic plan from then on. That is the whole rule: five free samples, then a cost comparison with a tilt toward reusing.

You can watch the counters on any recent PostgreSQL, including 16 and 17, through pg_prepared_statements:

SELECT name,
       statement,
       prepare_time,
       generic_plans,
       custom_plans,
       from_sql
FROM pg_prepared_statements
ORDER BY prepare_time DESC;

When generic_plans starts climbing while custom_plans stays at five, the statement has switched. Note the view is per-session, so you only see your own connection's statements — for a fleet-wide picture you infer it from plan changes in pg_stat_statements and from the driver logs.

When the generic plan goes wrong: skewed data

The classic failure is a status or tenant column with wildly uneven distribution. Suppose 99.9 percent of orders belong to status 'shipped' and a handful are 'pending'. A query filtering on status with a custom plan for 'pending' uses the index and returns three rows. The generic plan, built from average selectivity, assumes a status match returns a meaningful fraction of the table and chooses a parallel sequential scan. The moment the prepared statement flips to generic, every lookup for 'pending' scans the table. Average-cost reasoning, terrible tail.

My customer's endpoint was exactly this: the big tenant matched half the table (custom plan: seq scan, correct), everyone else matched a few rows (custom plan: index scan, correct), and the generic plan picked the seq scan for everybody. Five minutes after each restart, the heuristic kicked in and the endpoint fell apart. The tell in production: identical query text, identical pg_stat_statements row, but mean_exec_time jumps by two orders of magnitude with no deploy and no data change that explains it.

Diagnosing with EXPLAIN EXECUTE

You do not have to guess which plan a prepared statement will use. Prepare it yourself in a session and ask:

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

-- Run it a handful of times to walk past the 5-execution threshold:
EXECUTE q('pending');
EXECUTE q('pending');
EXECUTE q('pending');
EXECUTE q('pending');
EXECUTE q('pending');
EXECUTE q('pending');

-- Now compare what the planner chooses:
EXPLAIN (ANALYZE, BUFFERS) EXECUTE q('pending');
EXPLAIN (ANALYZE, BUFFERS) EXECUTE q('shipped');

The reliable tell is in the plan text itself: a generic plan refers to the parameters as $1 in its filter and join clauses, while a custom plan has the literal values substituted in. If both EXECUTEs show $1 and one plan is clearly wrong for its selectivity, you are looking at a generic plan. Repeat with plan_cache_mode forced to custom (below) to confirm the custom plans would have differed. Our EXPLAIN ANALYZE guide covers reading the BUFFERS output, which is where the wasted work shows up.

plan_cache_mode: your three levers

The setting plan_cache_mode controls the decision, and it is settable per session, per role, per database, or globally:

• auto — the default; the 5-execution heuristic described above.
• force_custom_plan — always plan fresh with actual parameters. You pay planning cost on every execution and never get surprised by skew. This is the right answer for analytically-flavored queries with uneven data, and the first thing I reach for when a tail-latency mystery smells like plan caching.
• force_generic_plan — always use the generic plan. Useful when planning genuinely dominates (very fast, uniform queries) or when you want to reproduce the generic-plan behavior on demand for testing.

-- Surgical: only this reporting role always plans fresh
ALTER ROLE reporting SET plan_cache_mode = force_custom_plan;

-- Or per database:
ALTER DATABASE analytics SET plan_cache_mode = force_custom_plan;

Resist setting it globally on a mixed OLTP workload: most prepared statements are uniform and genuinely benefit from generic plans. Aim it at the role or database that owns the skewed queries, or fix the query itself — an explicit predicate, a better index, or splitting the hot path from the cold one beats overriding the planner everywhere.

The driver layer is part of the story

Whether statements get prepared at all is decided below SQL. JDBC only prepares after prepareThreshold executions of the same statement on a connection (default five — yes, five again, and the two heuristics stack). pgx prepares aggressively. Some ORMs prepare everything; some never do. Under PgBouncer in transaction mode, session-level prepared statements historically did not survive pooling at all, which accidentally force-custom-planned the world; with PgBouncer 1.21 and later tracking protocol-level prepared statements across backends, prepared statements work through the pool again and the generic-plan heuristic is back in play. Know which layer is preparing, because that is the layer whose logs will confirm the switch.

Watching plans shift with MonPG

A generic-plan flip is invisible to anything that alerts on query text, because the text never changes — same string, same pg_stat_statements row, wildly different mean time. MonPG monitors PostgreSQL today, and its per-query latency and call-count history is exactly where that regression surfaces: one statement, no deploy, mean time suddenly multiplied. Buffer-read trends beside it separate "plan got worse" from "data grew", and the slow query monitoring workflow walks you from the regressed statement to the numbers that convict the generic plan. There are more diagnostics like this on the MonPG blog. Five executions is a small number; catch the sixth one when it happens, not a week later.