Why do my prepared statements get slow after the fifth execution?
Quick answer: PostgreSQL uses parameter-aware custom plans for a prepared statement’s first several executions, then switches to a single generic plan, built without knowing the parameter values, if that plan does not look sufficiently more expensive to justify replanning every time. On skewed data, the generic plan can be badly wrong for a common value. Inspect it with
EXPLAIN (GENERIC_PLAN); force per-execution planning withplan_cache_mode = force_custom_plan.
What is a generic plan?
Section titled “What is a generic plan?”When you prepare a statement, PostgreSQL keeps the parsed query in a plan cache so it does not have to replan it on every execution. It has two ways to plan it:
- A custom plan is built for the specific parameter values of this
execution. The planner can use the value (
'pending',42,'2026-01-01') to estimate selectivity from the column’s statistics. The estimate is usually more specific, but you pay the planning cost every time. - A generic plan is built once with the parameters left unknown. The planner uses average selectivity for the column. The plan is reused until it is invalidated, with no planning cost after the initial build.
PostgreSQL tries to balance the two approaches: it uses custom plans for the statement’s first several executions and records their costs. On a later execution, it builds the generic plan once and compares its estimated cost with the average custom-plan cost. If the generic plan’s cost is not so much higher that repeated replanning seems worthwhile, PostgreSQL keeps it and stops replanning. That is why the symptom appears after a handful of executions. The documented rule is specific: the first five executions use custom plans, and the comparison happens from the sixth onward. PostgreSQL calls this “the current rule,” so treat it as a heuristic that could change between major versions rather than a contract.
In practice, the switch usually sticks: once the generic plan wins the cost
comparison, PostgreSQL stops accumulating custom-plan costs, so the comparison
does not naturally swing back. It is not immutable, though. plan_cache_mode is
consulted each time a cached plan is executed, which is why a mid-session
SET works. DDL or a fresh ANALYZE also invalidates the cached plan and forces
a rebuild.
Why is this so hard to spot?
Section titled “Why is this so hard to spot?”Because nothing visible changed:
- The SQL text is identical.
- The client, the connection string, and the deployed code are identical.
- Running the same query by hand in
psqlis fast because typing it out literally gives you a custom plan every time. pg_stat_statementsshows the same normalized query text with a mean time that quietly got worse, mixing fast custom-plan executions and slow generic-plan executions into one average.
It also does not correlate with a deploy, a schema change, or a traffic spike. It correlates with a connection having executed a statement a few times. The problem can therefore appear minutes after a rolling restart, on some pods and not others, and disappear when you reconnect. That intermittency is the tell.
Why does skewed data make it dangerous?
Section titled “Why does skewed data make it dangerous?”A generic plan assumes that the average parameter value is representative. On uniformly distributed data, that assumption is usually fine and the generic plan avoids repeated planning. On skewed data, it can be badly wrong.
The classic case is a status column:
SELECT * FROM orders WHERE status = $1;Say orders has 50 million rows: 99.8% 'shipped' and 0.1% (50,000 rows)
'pending'. The two plans then look like this:
- Custom plan for
'pending': the planner sees the value, checks the most-common-values statistics, estimates ~50,000 rows, and picks an index scan. Milliseconds. - Generic plan: the planner has no value. It estimates the row count by
dividing the table by the column’s number of distinct values, capped at the
most common value’s frequency, regardless of which value arrives, and may pick
a sequential scan. That is correct for
'shipped'but can mean a full 50-million-row scan for'pending'.
Once the generic plan wins the cost comparison, every value uses a plan that is
only right for the common one. The same mechanism affects any skewed predicate:
a tenant_id where one tenant owns most of the rows, a soft-delete flag, an
event_type enum, or a partition key that routes almost everything to one
partition.
This is related to a plan flip, but the trigger is different. A plan flip happens because your data changed. A generic-plan switch happens because your execution count changed; the data can be completely static.
How do I confirm it?
Section titled “How do I confirm it?”1. Look at the generic plan directly. EXPLAIN (GENERIC_PLAN) (added in
PostgreSQL 16) plans a parameterized query without executing it and without
knowing the values:
EXPLAIN (GENERIC_PLAN)SELECT * FROM orders WHERE status = $1;2. Compare it to the custom plan for a value you care about:
EXPLAIN (ANALYZE, BUFFERS)SELECT * FROM orders WHERE status = 'pending';If the generic plan shows a Seq Scan where the value-specific plan shows an
Index Scan, you have found the difference. For help reading either one, see
how to read an EXPLAIN ANALYZE plan.
3. Or reproduce it end to end in one session, using SQL-level PREPARE:
PREPARE p (text) AS SELECT * FROM orders WHERE status = $1;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE p('pending'); -- run this repeatedlyRun the EXPLAIN ANALYZE EXECUTE several times in the same session and watch the plan. When the node types change on a later execution with the same argument, you are watching the switch happen live.
| Signal | What it means |
|---|---|
Fast in psql, slow from the app | The app uses prepared statements; your literal SQL does not |
| Slow only after a connection has been up a while | Generic plan latched on that backend |
| Slow on some app instances, fast on others | Per-backend plan caches at different execution counts |
EXPLAIN (GENERIC_PLAN) differs from the value-specific plan | The switch will change performance |
| Fixed by a restart or reconnect, then returns | Plan cache cleared, then re-latched |
4. Check the setting itself:
SHOW plan_cache_mode;How do I fix it?
Section titled “How do I fix it?”Force custom plans where skew is real. plan_cache_mode (available since
PostgreSQL 12) takes three values:
| Value | Behavior |
|---|---|
auto (default) | Custom plans first, then the cost comparison decides |
force_custom_plan | Always replan with the actual parameter values |
force_generic_plan | Always reuse one parameter-blind plan |
force_custom_plan is appropriate when the query filters on a skewed column and
the plans genuinely differ by value. You pay the planning cost on every
execution. That cost is real, but usually a fraction of a millisecond compared
with a query that would otherwise scan 50 million rows.
Set it as narrowly as you can. Prefer a session or transaction scope on the specific workload:
SET plan_cache_mode = force_custom_plan; -- this sessionor pin it to the role that runs the affected queries:
ALTER ROLE reporting SET plan_cache_mode = force_custom_plan;Changing it globally in postgresql.conf gives up generic plans for every
prepared statement on the server, including the high-frequency point lookups
where generic planning is useful. Only do that if you have measured the effect.
Other options, in rough order of preference:
- Improve the statistics. If the planner’s generic estimate is bad because
the sample missed the distribution, raise the target:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;thenANALYZE. This will not stop the generic plan from being parameter-blind, but it can make the cost comparison come out right. - Make the query less skew-sensitive. A partial index
(
CREATE INDEX CONCURRENTLY ON orders (created_at) WHERE status = 'pending') or a separate query for the rare case removes the ambiguity. - Turn off server-side prepares for that query in your driver if it is a single query and you want a narrow fix.
force_generic_planis rarely what you want here. It is for cases where planning cost dominates and one plan fits all values.
Do I have prepared statements if I never wrote PREPARE?
Section titled “Do I have prepared statements if I never wrote PREPARE?”Almost certainly yes. Very little of this appears as the word PREPARE in a
codebase. It happens at the protocol level, inside the driver:
- JDBC (PgJDBC) switches a statement to a server-side prepared statement
after it has been executed a few times; the threshold is configurable via
prepareThreshold(0disables server-side prepares). - psycopg 3 does the same thing with a
prepare_thresholdon the connection. - Go’s
pgxprepares and caches statements by default in its default query execution mode. - ORMs (Django, SQLAlchemy, Hibernate, ActiveRecord, Prisma) sit on top of a driver and inherit its behavior. Whether you get a prepared statement is a driver-and-pool question, not something visible in the ORM call.
So the “after the fifth execution” symptom usually arrives in an application where nobody chose to use prepared statements. Check your driver’s documentation for its prepare threshold and whether it caches statements per connection. That determines whether you reach the generic-plan comparison at all. For the broader “my ORM generated something the database hates” problem, see how to optimize ORM-generated queries.
How does connection pooling change this?
Section titled “How does connection pooling change this?”It changes the behavior substantially.
The plan cache lives in the server backend process, so it belongs to a physical server connection, not to your application’s logical connection. What your pooler does with that mapping determines the result:
- Session pooling / a plain app-side pool (HikariCP,
pgxpool, SQLAlchemy): a client holds one backend for a long time, executes the same statements over and over, and reliably crosses the generic-plan threshold. This setup makes the problem easier to reproduce, and long-lived connections mean a bad generic plan can persist for hours. - PgBouncer transaction pooling: a client gets a different backend from one
transaction to the next. SQL-level
PREPARE/EXECUTEdoes not survive that handoff. Protocol-level prepared statements do work under transaction pooling since PgBouncer 1.21, gated onmax_prepared_statements, which defaults to 200 since PgBouncer 1.24. PgBouncer tracks the named statements and reprepares them on whichever server connection it assigns. - Under transaction pooling, executions are spread across many backends, so any individual backend’s plan cache heats up slowly. You may reach the generic plan much later, on some backends and not others. That appears as some requests are slow rather than the endpoint got slow. Recycled server connections reset the cache.
This does not make the problem go away. It makes it less predictable. If you are
chasing intermittent latency on a pooled setup, check max_prepared_statements
(default 200 since 1.24, 0 before) and your pooling mode before blaming the
query. See PostgreSQL connection pooling and “too many connections”
for how the modes differ.
Why does AI-generated code walk into this?
Section titled “Why does AI-generated code walk into this?”An AI coding agent can write a query that is correct, uses a parameter placeholder
as it should, and is fast on every test it can run. It cannot see that status
is 99.8% one value, that the driver promotes the statement to a server-side
prepare after a few executions, or that the generic plan the planner eventually
builds turns a 3 ms index scan into a full table scan. Parameterization is a
security and correctness best practice. The interaction with data skew and the
plan cache is a runtime property of production data that does not appear in the
source.
How do I stop it coming back?
Section titled “How do I stop it coming back?”- For each query on a known-skewed column, run
EXPLAIN (GENERIC_PLAN)in review and compare it to the plan for the most common and least common values. - Keep a short list of columns you know are skewed (status flags, tenant ids,
soft-delete booleans) and default those queries to
force_custom_planat the role level. - Set a high statistics target on those columns up front.
- Treat “fast in psql, slow in the app” as a specific diagnosis, not a mystery. It is nearly always prepared statements or a search-path/role difference.
- Watch for latency that is bimodal rather than shifted: a mix of fast and slow executions of the same statement is the fingerprint.
Why does PostgreSQL switch from a custom plan to a generic plan?
PostgreSQL plans a prepared statement with parameter-aware custom plans for its first several executions and records their cost. After that it plans the statement once generically, with no knowledge of the parameter values, and compares that generic cost to the average custom cost. If the generic plan's cost is not so much higher than the average custom-plan cost as to make repeated replanning preferable, PostgreSQL keeps it (it can be estimated as more expensive and still win) and stops re-planning, saving planning time on every later execution.
How do I see the generic plan for a prepared statement?
Use EXPLAIN (GENERIC_PLAN) on the parameterized SQL with $1-style placeholders, which shows the plan PostgreSQL would build without knowing the parameter values. Compare it to a plain EXPLAIN of the same query with a real, common value substituted. If the two plans differ, the generic-plan switch can change your performance.
What does plan_cache_mode do?
plan_cache_mode controls the custom-vs-generic decision for prepared statements. The default, auto, uses the cost comparison. force_custom_plan makes PostgreSQL re-plan on every execution using the actual parameter values, which is the right choice for queries over skewed columns. force_generic_plan always reuses one plan.
Do I hit generic plans if I never write PREPARE?
Yes. Most drivers and ORMs use protocol-level prepared statements implicitly, PgJDBC, psycopg 3 and pgx among them. Several of them switch to a server-side prepared statement after a query has been executed a handful of times, so an application that only ever calls an ORM method can still land on a generic plan without any PREPARE statement in the codebase.