Prepared Statements in Postgres: When the Generic Plan Bites

Prepared statements are sold as a performance feature. They skip the parse-plan step on every execution, which can be a meaningful win for queries that run thousands of times per second. They are also one of the more subtle Postgres performance traps, because Postgres switches to a "generic plan" after a few executions and that plan can be much worse than the per-execution plan.

This is one of those features that works great until you hit the case where it doesn't. Here is how to recognize the case.

How prepared statements actually plan

The first 5 executions of a prepared statement use a custom plan — Postgres replans for the specific parameters each time. After 5 executions, Postgres compares the average custom plan cost to the cost of a generic plan (one that does not consider parameter values).

If the generic plan is not significantly worse, Postgres switches to it. From then on, every execution uses the generic plan, skipping the planning cost.

This is fine when the parameter values do not affect plan choice. It is a problem when they do.

A worked example of when it goes wrong

A query like:

SELECT * FROM orders WHERE status = $1;

If status is one of ('pending', 'paid', 'shipped', 'delivered') with very different cardinalities — say 0.1% pending, 5% paid, 70% shipped, 25% delivered — the right plan depends on the value:

• For status = 'pending': use the index on status, return 0.1% of rows fast.
• For status = 'shipped': seq scan would be faster than going through the index for 70% of rows.

A custom plan picks the right one each time. A generic plan picks one and uses it forever. If the generic plan is index scan, queries for 'shipped' are slow. If it is seq scan, queries for 'pending' are slow.

This is the classic "generic plan is wrong for some parameter values" failure.

Recognizing it

The symptom: a query that was fine for months suddenly gets slow after some workload shift, but only for some inputs. The shift in question is usually that an application now queries a wider variety of values than it used to.

In pg_stat_statements, the same query (same SQL, parameterized) appears once. Its mean_exec_time looks reasonable on average. But individual executions vary 10-100x.

In auto_explain output (if logged with auto_explain.log_analyze = on), you see plans that are clearly wrong for the parameters they were called with.

Postgres 12+: plan_cache_mode

Postgres 12 added plan_cache_mode:

SET plan_cache_mode = 'force_custom_plan';

Values:

• auto (default): the 5-execution comparison logic above.
• force_custom_plan: always replan. No generic plan ever.
• force_generic_plan: always use generic plan from the start.

For queries with parameter-dependent plans, force_custom_plan eliminates the generic-plan trap. The cost is a small per-execution planning overhead.

For queries where parameters do not matter for the plan, force_generic_plan skips planning entirely.

For most workloads, the default auto is fine. The override is for specific queries where you have observed the issue.

Setting it per session or per query

The parameter can be set globally (rare), per session, or per call:

-- For just this connection
SET plan_cache_mode = 'force_custom_plan';

-- For just this transaction
SET LOCAL plan_cache_mode = 'force_custom_plan';

-- Global default (for the whole server) — usually leave at auto
ALTER SYSTEM SET plan_cache_mode = 'auto';

For problematic queries, the cleanest approach is to set it in the session before issuing the query, then reset:

SET LOCAL plan_cache_mode = 'force_custom_plan';
SELECT * FROM orders WHERE status = $1;

When prepared statements are the wrong tool

For most application workloads, the framework or driver decides whether to prepare. Some libraries prepare every query (potentially exposing the generic-plan issue); others do not.

If you are running into the generic-plan issue and plan_cache_mode does not help, the alternative is to not prepare at all — use direct query execution. The performance loss is the parse-plan overhead, which is usually under a millisecond.

The library-specific way to opt out varies. In libpq, do not call PQprepare; pass parameters via PQexecParams instead. In Python's psycopg, use cursor.execute() with prepare=False (or use the per-query syntax).

On read replicas and replication

Prepared statements are session-local; they do not propagate via replication. Each session has its own prepared statement cache. This is usually a non-issue but matters in two cases:

• Connection poolers in transaction mode (PgBouncer): the application's prepared statements may not survive across connection borrows. Each new "connection" the application sees may be a different backend with a different cache.
• Failover: after the primary fails over, application connections re-establish to a different backend. Prepared statements have to be re-prepared.

Most ORMs handle these cases. If you are writing low-level code, be aware of the behavior.

A diagnostic walkthrough

Real scenario: a query that was 5ms and started taking 500ms intermittently. The SQL was:

SELECT * FROM events WHERE event_type = $1 ORDER BY created_at DESC LIMIT 10;

The event_type column had skewed distribution: most events were 'pageview' (95%), a few were 'signup' (1%), the rest various.

For 'signup', the right plan was index scan on event_type, created_at — fast.

For 'pageview', the right plan was index scan on created_at alone (the event_type filter is non-selective; better to scan recent events and filter).

Generic plan picked a single strategy. Worked for one case, failed for the other.

Fix: SET plan_cache_mode = 'force_custom_plan' in the session that ran this query. Each execution replanned. Latency stabilized at 5-15ms regardless of input.

What I check first

When a prepared statement query has variable performance:

1. Look at auto_explain output (if enabled). Do plans differ between executions?
2. Check the parameter cardinality — is the value space skewed?
3. Try SET plan_cache_mode = 'force_custom_plan' for that session and observe.
4. If that fixes it, decide whether to set it permanently for that workload or live with replanning.

The issue is rare in absolute terms — most workloads do not hit it. But when they do, the diagnosis is non-obvious unless you already know the failure mode.