Skip to main content

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 with plan_cache_mode = force_custom_plan.

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 re-plan 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. Accurate, but you pay planning cost every time.
  • A generic plan is built once with the parameters left unknown. The planner falls back to average selectivity for the column. One plan, reused forever, zero planning cost after the first build.

PostgreSQL tries to get both: it uses custom plans for the statement's first several executions and records what they cost. On a later execution it builds the generic plan once and compares its estimated cost against the average custom plan cost. If the generic plan's cost is not so much higher that repeated replanning looks worth it, PostgreSQL keeps it and stops re-planning. That is why the symptom shows up a handful of executions in. 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 sticks: once the generic plan wins, PostgreSQL stops accumulating custom-plan costs, so the comparison does not naturally swing back. It is not literally immutable though.plan_cache_mode is consulted each time a cached plan is executed (which is why a mid-session SET works), and DDL or a fresh ANALYZE invalidates the cached plan and forces a rebuild.

Why is this so hard to spot?​

Because nothing you can see changed:

  • The SQL text is identical.
  • The client, the connection string, and the deployed code are identical.
  • Running the same query by hand in psql is fast, because typing it out literally gives you a custom plan every time.
  • pg_stat_statements shows the same normalized query text with a mean time that quietly got worse, mixing fast custom-plan executions and slow generic-plan ones 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, which means it can 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?​

A generic plan is a bet that the average parameter value is representative. On uniformly distributed data that bet is fine and the generic plan is a free win. On skewed data it is catastrophic.

The classic case is a status column:

SELECT * FROM orders WHERE status = $1;

Say orders has 50 million rows: 99.8% 'shipped', and 'pending' is 0.1%. 50,000 rows. Now:

  • 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 using the column's average selectivity across all values, which the dominant 'shipped' value drags toward "this matches most of the table", and picks a sequential scan. Correct for 'shipped'; a full 50-million-row scan for 'pending'.

Once the generic plan wins the cost comparison, every value pays the plan that is only right for the common one. The same mechanism bites any skewed predicate: a tenant_id where one tenant owns most of the rows, a soft-delete flag, an event_type enum, a partition key that routes almost everything to one partition.

This is a cousin of 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?​

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 it. (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 repeatedly

Run 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.

SignalWhat it means
Fast in psql, slow from the appThe app uses prepared statements; your literal SQL does not
Slow only after a connection has been up a whileGeneric plan latched on that backend
Slow on some app instances, fast on othersPer-backend plan caches at different execution counts
EXPLAIN (GENERIC_PLAN) differs from the value-specific planThe switch will change performance
Fixed by a restart or reconnect, then returnsPlan cache cleared, then re-latched

4. Check the setting itself:

SHOW plan_cache_mode;

How do I fix it?​

Force custom plans where skew is real. plan_cache_mode (available since PostgreSQL 12) takes three values:

ValueBehavior
auto (default)Custom plans first, then the cost comparison decides
force_custom_planAlways re-plan with the actual parameter values
force_generic_planAlways reuse one parameter-blind plan

force_custom_plan is the right call when the query filters on a skewed column and the plans genuinely differ per value. You pay planning cost on every execution, real, but usually a fraction of a millisecond against 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 session

or 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 a genuine win. Only reach for that if you have measured it.

Other angles, 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; then ANALYZE. 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 splitting the rare case into its own query removes the ambiguity entirely.
  • Turn off server-side prepares for that query in your driver, if it is one query and you want a surgical fix.
  • force_generic_plan is rarely what you want here. It is for when planning cost dominates and you are confident one plan fits all values.

Do I have prepared statements if I never wrote PREPARE?​

Almost certainly yes. Very little of this shows up 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 (0 disables server-side prepares).
  • psycopg 3 does the same thing with a prepare_threshold on the connection.
  • Go's pgx prepares and caches statements by default in its default query execution mode.
  • ORMs. Django, SQLAlchemy, Hibernate, ActiveRecord, Prisma, sit on top of those drivers and inherit the 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 ever chose to use prepared statements. Check your driver's docs for its prepare threshold and whether it caches statements per connection, that, not your ORM code, decides 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?​

A lot, and in a direction that surprises people.

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 everything:

  • 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 is the setup where the problem is most reproducible, and where long-lived connections mean a bad generic plan can persist for hours.
  • PgBouncer transaction pooling: a client gets a different backend transaction to transaction. SQL-level PREPARE/EXECUTE does not survive that handoff at all. Protocol-level prepared statements do work under transaction pooling since PgBouncer 1.21, gated on max_prepared_statements, which defaults to 200 since PgBouncer 1.24, so on current versions it is on by default rather than something you opt into. PgBouncer tracks the named statements and re-prepares them on whichever server connection it hands you.
  • The practical consequence: under transaction pooling your 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, which shows up as some requests are slow rather than the endpoint got slow. Recycled server connections reset it again.

None of this makes the problem go away, it makes it non-deterministic, which is worse to debug. 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 you blame the query. See PostgreSQL connection pooling and "too many connections" for how the modes differ.

Why does aI-generated code walk into this?​

Your AI coding agent (Claude Code, Cursor) writes a query that is correct, uses a parameter placeholder exactly as it should, and is fast on every test it can run. It cannot see that status is 99.8% one value, that your driver promotes the statement to a server-side prepare after a few executions, or that the generic plan the planner will eventually build turns a 3 ms index scan into a full table scan. Parameterization is a security and correctness best practice the model correctly applies, the interaction with data skew and the plan cache is a runtime property of your production data that never appears in the source.

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_plan at 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.

How DBGorilla helps​

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the real database facts behind this: the plan for the query, the column statistics and value distribution that make the generic plan a bad bet, the pg_stat_statements entry showing the same normalized SQL with a worsening mean, and the current plan_cache_mode. Your agent can then explain that the plan cache flipped and why, and propose the change in your code or your role settings. It surfaces and explains through your agent. It does not change server configuration or run anything against production. Get started free →

FAQ​

Why does PostgreSQL switch from a custom plan to a generic plan? It uses parameter-aware custom plans for a statement's first several executions and records their cost, then builds one generic plan with the parameters unknown and compares. If the generic plan's cost is not so much higher as to make replanning worthwhile, it keeps it and stops re-planning, saving planning time on every later execution.

How do I see the generic plan for a prepared statement? Run EXPLAIN (GENERIC_PLAN) on the parameterized SQL with $1 placeholders, then compare it to a plain EXPLAIN of the same query with a real common value. Differing plans mean the switch can change your performance.

What does plan_cache_mode do? It controls the custom-vs-generic decision. auto (the default) uses the cost comparison; force_custom_plan re-plans with real parameter values every time, which is the right choice on skewed columns; force_generic_plan always reuses one plan.

Do I hit generic plans if I never write PREPARE? Yes. Drivers like PgJDBC, psycopg 3, and pgx use protocol-level prepared statements implicitly, several of them after a query has run a handful of times. Your ORM inherits that behavior, so you can land on a generic plan with no PREPARE anywhere in your code.