PostgreSQL prepared statement plan cache: generic vs custom plan pitfalls

When an application repeats the same query with different parameters, prepared statements avoid repeated parsing. But PostgreSQL’s plan cache does not work the way many operators expect. The server does not automatically cache the first plan it generates. It executes the first five invocations with custom plans that see the actual bound values. At the sixth execution, the planner compares the average cost of those custom plans against a generic plan built with placeholders. If the generic plan looks cheaper, the session switches to it permanently. There is no automatic reversion.

This heuristic is session-scoped and hardcoded. For uniform data it works well. For skewed distributions, partitioned tables, or connection-pooled workloads, it causes sudden latency cliffs, memory bloat, or protocol errors. You need to know how the 5-execution switch, the plan_cache_mode setting (PostgreSQL 12+), and client driver behavior interact before these hit production.

What it is and why it matters

A prepared statement is a server-side object created with PREPARE name AS .... The client later executes it with EXECUTE name(parameters). The benefit is avoiding repeated parsing and reusing an execution plan. But PostgreSQL must trade off plan optimality against stability. A plan optimized for one parameter value may be terrible for another. The planner resolves this by testing custom plans first, then choosing a generic plan after five executions.

The generic plan relies on table statistics and averages without knowledge of the actual parameter values. When data is skewed, the generic plan can be off by orders of magnitude. Once the switch happens, the session is stuck with that plan until the prepared statement is deallocated or the connection closes. In long-lived connections or pooled environments, a bad plan persists until the session ends.

Drivers and ORMs often prepare statements transparently. You may never issue an explicit PREPARE yet still be exposed to the plan cache. Python’s psycopg and Go’s pgx both prepare automatically under certain thresholds, and any driver using the extended query protocol can trigger the server-side plan cache.

How it works

The logic lives in choose_custom_plan() in the PostgreSQL planner. When a prepared statement executes:

1. Executions 1 through 5 always generate custom plans. The planner sees the literal values bound to each parameter and optimizes accordingly.
2. At execution 6, the planner computes the average estimated cost of all prior custom plans and compares it to the estimated cost of a generic plan using $n placeholders.
3. If the generic plan cost is less than or equal to the average custom plan cost, PostgreSQL caches the generic plan and uses it for all subsequent executions in that session.
4. If the generic plan is more expensive, the session continues with custom plans. The exact re-evaluation frequency is internal, but the critical invariant is that once a generic plan is chosen, the session never switches back mid-session.

The plan_cache_mode GUC, available from PostgreSQL 12, overrides this heuristic entirely. Valid values are:

• auto: the default, using the 5-execution heuristic.
• force_custom_plan: always generate a custom plan per execution.
• force_generic_plan: always use a generic plan from the first execution.

This setting is consulted at execution time, not at PREPARE time. Changing it affects existing prepared statements immediately. Check the current value with SHOW plan_cache_mode;.

You can confirm a generic plan is active by running EXPLAIN EXECUTE stmt_name(...). If the output shows $1, $2, and so on instead of literal values, the generic plan is in use.

There is no built-in re-evaluation. If the data distribution shifts after the generic plan is adopted, the session continues using the stale plan. The only operator-level escape is DEALLOCATE stmt_name followed by recreation of the prepared statement, or reconnecting to start a fresh session.

flowchart TD
    A[PREPARE statement] --> B[Executions 1-5]
    B --> C[Custom plan per execution
literal values visible]
    C --> D[Execution 6 and later]
    D --> E{Avg custom cost >=
generic plan cost?}
    E -->|Yes| F[Generic plan cached
$n placeholders]
    E -->|No| G[Custom plans continue]
    F --> H[Sticks until DEALLOCATE
or session ends]
    G --> H

Where it shows up in production

The skewed-data trap

The most dangerous manifestation is skewed data. Consider a table with a status column where 99% of rows are shipped and 1% are returned. A prepared statement SELECT * FROM orders WHERE status = $1 might execute its first five invocations with returned. The planner chooses an index scan. At execution six, the generic plan also favors an index scan because it is optimized for the rare value. When the application later binds shipped, the executor performs a random index lookup across nearly the entire table. Latency jumps from milliseconds to minutes.

Partitioned tables and memory bloat

Partitioned tables create a different problem. Because a generic plan is built without parameter values, PostgreSQL cannot prune partitions at plan time. Before PostgreSQL 15, a generic plan referenced all partitions and could consume gigabytes of memory per backend. With a connection pooler maintaining dozens or hundreds of backend connections, this multiplies into severe memory pressure. PostgreSQL 15 reduces this overhead by allowing partition pruning at execution time for generic plans, but the inability to prune at plan time remains.

Connection poolers and driver mismatches

Connection poolers add protocol-level breakage. Prepared statements are session-scoped. PgBouncer in pool_mode=transaction returns the backend to the pool after each commit or rollback. The next transaction on that connection starts fresh, and the prepared statement no longer exists. If the client driver tries to reuse the prepared statement name, the server responds with prepared statement "X" already exists or does not exist.

The Java PostgreSQL JDBC driver defaults to prepareThreshold=5, meaning it does not create a server-side prepared statement until the fifth execution on a single connection. In a transaction-pooled environment, the driver and the server can get out of sync. Disable server-side prepares with prepareThreshold=0. Some driver versions also issue server-side prepares for metadata queries when PgBouncer is present; in that case set preparedStatementCacheQueries=0 as well.

Tradeoffs and when to use it

When generic plans win

Generic plans eliminate planning overhead and are predictable. They work well for simple lookup queries on uniform data where the same plan is optimal regardless of parameter value. Short point queries on primary keys or uniformly distributed foreign keys are good candidates. If planning time dominates execution time, force_generic_plan can improve throughput.

When custom plans are safer

Custom plans are safer when data is skewed or when partition pruning matters, but they cost CPU. Every execution re-runs the planner. For complex queries on skewed columns, that overhead is usually smaller than the cost of a bad generic plan. Set plan_cache_mode = force_custom_plan at the database, user, or session level when you observe bimodal latency or when queries touch heavily skewed columns like tenant IDs, status fields, or rare event types.

Warning: force_custom_plan increases planning CPU overhead. Apply it selectively, not globally, unless you have proven the planner cost exceeds the execution penalty of bad generic plans.

Working around bad plan adoption

If a generic plan has been adopted and is performing badly, you cannot force a mid-session reversion. DEALLOCATE stmt_name followed by re-preparation resets the counter, but this requires application cooperation or session cycling.

For PgBouncer, the cleanest fix is to use pool_mode=session for databases that rely on prepared statements, though this reduces pooling efficiency. Alternatively, disable server-side prepares in the driver and let the client handle parameter binding without PREPARE.

For partitioned workloads, prefer PostgreSQL 15 or later to avoid generic-plan memory bloat. If you are on an older release and cannot upgrade, monitor backend memory closely and consider force_custom_plan to reduce the number of generic plans held in memory.

Signals to watch in production

pg_stat_statements requires the extension to be loaded. It aggregates across sessions, so it will not show a clean 5-execution cutoff; pair it with application logs or connection-level tracing.

How Netdata helps

• Compare pg_stat_statements latency percentiles to spot bimodal distributions from bad generic plans.
• Monitor backend process memory with query throughput to catch partition-plan bloat before OOM.
• Track PgBouncer connection state and error rates to detect prepared-statement mismatches.
• Compare planning time versus execution time per query to justify force_custom_plan or force_generic_plan.
• Overlay query latency charts with deployment markers to correlate driver-level prepare threshold changes.

Related guides

• How PostgreSQL actually works in production: a mental model for operators
• PostgreSQL ALTER TABLE blocked: zero-downtime DDL patterns
• PostgreSQL autovacuum blocked by long-running transaction: detection and fix
• PostgreSQL autovacuum not running: detection, causes, and fixes
• PostgreSQL autovacuum tuning: per-table thresholds for high-churn workloads
• PostgreSQL blocking queries: finding the root blocker in a lock cascade
• PostgreSQL checkpoint storms: detection, causes, and tuning
• PostgreSQL: checkpoints are occurring too frequently – what to tune
• PostgreSQL connection exhaustion: detection, diagnosis, and prevention
• PostgreSQL connection refused: pg_hba, listen_addresses, and TCP diagnosis
• PostgreSQL: database is not accepting commands to avoid wraparound data loss
• PostgreSQL dead tuples piling up: why autovacuum can’t keep up