Monitoring pg_prepared_statements #

A statement is slow in production and fast everywhere else, and the difference is not data, load or configuration — it is that production’s connections are old enough for the plan cache to have switched to a generic plan. The counters that prove it are one query away, and almost nobody looks at them.

The mechanism is described in prepared statement plan pinning. This page is about observing it in a live system.

The Condition #

pg_prepared_statements is a per-session view listing the prepared statements that session holds. Two of its columns describe the plan cache directly:

PostgreSQL builds custom plans for the first five executions, then compares their average cost with the generic plan’s cost. If the generic plan is not more expensive, it is adopted for the remainder of the session; otherwise custom planning continues. So custom_plans = 5 with generic_plans climbing means the transition happened; custom_plans climbing with generic_plans at 1 means the planner kept re-planning deliberately.

The view has two limitations that shape how it is used. It is session-local: it shows the statements of the session querying it and nothing about other backends. And it covers statements prepared through the protocol or SQL PREPARE, not the implicit ones inside PL/pgSQL functions, which have their own cache described in PL/pgSQL function plan caching.

That makes it a diagnostic tool rather than a monitoring one: you reproduce the workload on a connection you control, and read the counters there.

Reading the two counters A table of patterns. Five custom plans with generic plans climbing means the transition happened and the generic plan is in use. Custom plans climbing with one generic plan means the planner rejected the generic plan as more expensive. Both low means the statement has not been executed enough times to decide. meaning action custom 5, generic rising generic plan in use check it is right custom rising, generic 1 generic judged worse nothing: working custom < 5, generic 0 not yet decided execute more generic in use, slow wrong plan adopted force_custom_plan the counters are per session and reset when it ends

Annotated Evidence #

Reading the counters:

SELECT name, generic_plans, custom_plans, parameter_types, left(statement, 50) AS stmt
FROM pg_prepared_statements ORDER BY generic_plans + custom_plans DESC;
 name | generic_plans | custom_plans |    parameter_types     |                stmt
------+---------------+--------------+------------------------+------------------------------------
 s_4  |           412 |            5 | {integer,timestamptz}  | SELECT o.id, o.total FROM orders o…
 s_7  |             1 |          208 | {text}                 | SELECT * FROM products WHERE sku =…
-- s_4: transitioned to a generic plan after five custom ones
-- s_7: the generic plan was more expensive, so custom planning continues

Comparing the two plans for a statement that transitioned:

SET plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE s_4(4821, '2026-01-01');
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE s_4(4821, '2026-01-01');
-- custom
Index Scan using orders_customer_placed_idx on orders  (actual time=0.031..0.412 rows=12 loops=1)

-- generic
Bitmap Heap Scan on orders  (actual time=182.204..1840.118 rows=12 loops=1)
  Rows Removed by Filter: 4102008
-- the generic plan is 4,000× slower for this parameter, and was adopted anyway
-- because its estimated cost was not higher on average

The GENERIC_PLAN option answers the same question without executing:

EXPLAIN (GENERIC_PLAN)
SELECT o.id, o.total FROM orders o WHERE o.customer_id = $1 AND o.placed_at >= $2;
Three observations that settle it Three items are annotated. A custom plan count of exactly five with a rising generic count shows the transition occurred. Forcing each mode and comparing execution times shows whether the adopted plan is suitable. Rows removed by filter in the millions in the generic plan identifies average selectivity as the reason it was chosen. custom_plans = 5, generic_plans = 412 transition occurred forced comparison: 0.4 ms against 1840 ms the adopted plan is wrong generic plan: Rows Removed by Filter: 4.1M average selectivity misled it the planner compared estimates, not these timings

Step-by-Step Resolution #

  1. Reproduce the statement on a session you control, using the same parameter types the application sends. Protocol-level statements appear here too when the driver prepares them.

  2. Execute it at least six times so the transition can occur, then read the counters.

  3. Compare the two plans explicitly with plan_cache_mode set to each value in turn, measuring both. This is the step that answers whether the transition was harmful — the counters alone only say it happened.

  4. Use EXPLAIN (GENERIC_PLAN) where executing is undesirable; it shows the generic plan for a parameterised statement without running it, and needs no prepared statement at all.

  5. If the generic plan is wrong, ask why. Almost always a skewed parameter whose average selectivity does not describe the values actually used. Extended statistics or a higher statistics target may fix the estimate, which is better than overriding the decision.

  6. Override narrowly if the estimate cannot be fixed: SET LOCAL plan_cache_mode = force_custom_plan in the transaction, or a role default for a workload that consistently needs it.

  7. Record the finding. A statement known to need custom plans should have that documented next to the query, because the next person to see it will see only a slow query with a normal-looking plan.

Where the transition shows up A timeline across executions of one statement. The first five executions are custom plans and are fast. At the sixth the generic plan is adopted. From then on every execution uses it, and for skewed parameters the timing jumps sharply and stays there for the life of the session. exec 1–5 custom plans, 0.4 ms exec 6 generic adopted exec 7+ 1,840 ms each session ends counters reset new session fast again briefly the last two steps are why it is hard to reproduce

Before and After #

-- BEFORE: generic plan adopted, wrong for the parameters in use
name | generic_plans | custom_plans      execution: 1,840 ms
s_4  |           412 |            5

-- AFTER: SET LOCAL plan_cache_mode = force_custom_plan for this statement
name | generic_plans | custom_plans      execution: 0.4 ms
s_4  |             0 |          417

Monitoring at workload scale #

Because the view is session-local it cannot answer “which statements across the system have adopted a bad generic plan”. Three indirect approaches cover that gap.

pg_stat_statements with track_planning shows the ratio of planning to execution time per statement: a statement with negligible planning time and rising execution time is a candidate, since that combination is exactly what a generic plan produces. Conversely, high planning time relative to execution means custom planning is continuing, which is informative in its own right.

auto_explain captures the plan that actually ran, so a statement whose logged plans change shape partway through a session’s life is showing the transition directly. This is the most conclusive evidence and the most expensive to collect.

And a synthetic check works well in staging: run each important parameterised statement six times through a harness, read the counters, compare forced plans, and record any statement whose generic plan is materially worse. That list is small, changes slowly, and is worth keeping alongside the schema.

What resets the counters #

The counters are not a permanent record, and knowing what clears them prevents a good deal of confused debugging.

Ending the session clears everything, since the view is session state. Deallocating the statement — DEALLOCATE, or a driver dropping it from its cache — removes the row. A connection returned to a pool that issues DISCARD ALL loses all of them, which is the reason a pooled application can appear never to reach the generic plan while a plain psql session reaches it in seconds.

Plan invalidation is different and more subtle: a schema change or a fresh ANALYZE on a referenced table invalidates the cached plan, and the next execution builds a new one. The counters continue rather than resetting, but the plan they refer to has changed, so a statement can go from a good generic plan to a bad one — or back — without the numbers signalling anything. Where a plan is suspected of changing mid-session, auto_explain is the only source that shows it.

Common Pitfalls #

Expecting the view to cover other sessions. It is session-local. Diagnostic signal: an empty result on a monitoring connection. Fix: reproduce on the session you are querying from.

Concluding from the counters alone. They show the transition, not whether it was harmful. Diagnostic signal: force_custom_plan applied without measurement. Fix: compare both plans.

Looking for PL/pgSQL statements here. They use a separate cache. Diagnostic signal: a function’s slow statement absent from the view. Fix: auto_explain.log_nested_statements.

Fixing the symptom. A wrong generic plan usually means a wrong estimate. Diagnostic signal: force_custom_plan spreading across the codebase. Fix: improve statistics first.

Frequently Asked Questions #

What do generic_plans and custom_plans mean? #

They count how many times a prepared statement used the cached parameter-independent plan and how many times a plan was built from actual parameter values. Five custom plans followed by a rising generic count indicates the planner adopted the generic plan.

Why is pg_prepared_statements empty? #

It shows only the current session’s prepared statements. A monitoring connection that has prepared nothing sees nothing, and statements inside PL/pgSQL functions never appear there.

How do I see a generic plan without executing the statement? #

Use EXPLAIN (GENERIC_PLAN) on the parameterised statement text. It plans with parameter placeholders and returns the plan without running anything.

Up: Prepared Statement Plan Pinning