A prepared statement that runs fast the first few times and then gets noticeably slower around the fifth or sixth execution isn't hitting cache pressure or autovacuum — it's hitting Postgres's own planner switching strategy, and the switch is deliberate.
Custom plans, then a generic plan, by design
When you PREPARE a parameterized statement (or your driver does it for you — psycopg2, JDBC, and most ORMs prepare server-side statements by default), Postgres doesn't commit to one plan. For the first five executions, it builds a custom plan: it substitutes the actual parameter values into the query and plans it exactly as if you'd written a one-off query with those literals, using real statistics for those specific values. On the sixth execution, it computes the average cost of those five custom plans and compares it against the cost of a generic plan — one planned without knowing the parameter values at all, using only column-level statistics. If the generic plan's estimated cost is close enough to the average custom-plan cost, Postgres switches to the generic plan and reuses it for every future execution, skipping the replan step entirely.
This is a genuine trade-off, not a bug: replanning on every execution costs CPU, and for most parameter values that cost isn't worth paying. But "close enough" is judged on cost estimates, not on actual runtime, and the generic plan is exactly the plan that ignores the one thing that made your custom plans fast.
Where this bites: skewed columns
SELECT * FROM orders WHERE status = $1;
If status has 100 values nearly all 'completed' and a tiny sliver 'disputed', a custom plan for $1 = 'disputed' correctly picks an index scan returning a handful of rows. A custom plan for $1 = 'completed' correctly picks a sequential scan. Both are fast for their own value. But the generic plan has to pick one strategy for all future values without knowing which — and because the planner has no way to special-case per-value selectivity in a generic plan, it will pick whichever strategy is cheapest on average across the column's distribution. For a heavily skewed column, that average is dragged toward the common value, so the generic plan ends up being a sequential scan baked in for every execution, including the now-common 'disputed' lookups that used to get an index scan.
Seeing it happen
SELECT name, calls, mean_exec_time FROM pg_stat_statements WHERE query LIKE 'SELECT * FROM orders WHERE status%';
mean_exec_time creeping up after exactly five calls to the same statement, with no change in data volume, is the signature. To see the actual switch, run the query manually through EXPLAIN five times with PREPARE/EXECUTE, then a sixth:
PREPARE stmt(text) AS SELECT * FROM orders WHERE status = $1; EXPLAIN ANALYZE EXECUTE stmt('disputed'); -- custom plan, index scan, five times EXPLAIN ANALYZE EXECUTE stmt('disputed'); -- generic plan now, seq scan
The sixth execution's plan won't mention "custom" or "generic" directly in older versions, but PG12+ exposes it: check pg_prepared_statements or, more reliably, look at whether the plan shape itself changed between the fifth and sixth run — a Seq Scan appearing where an Index Scan ran before is the tell.
The fix is not "add an index"
Adding an index doesn't help if the generic plan has already decided not to use it for this parameter. The actual fixes:
plan_cache_mode(PG12+): set toforce_custom_planfor a specific session or function to force replanning on every execution, trading a small CPU cost for always getting the value-appropriate plan. Set it per-connection viaSET plan_cache_mode = force_custom_plan;right before running the problem query, not globally — forcing custom plans everywhere gives up the replanning savings for queries that don't need it.- Avoid server-side prepared statements entirely for this one query, if your driver allows per-query opt-out, so every execution is planned fresh with real literals.
- Don't fight it with statistics alone.
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;improves the custom plans' accuracy but does nothing for the generic plan's single blended estimate — the generic plan is a different kind of estimate, not a less-informed version of the same one.
Rule of thumb: a prepared statement over a skewed column is the one place where more statistics don't fix a plan regression, because the regression isn't about data knowledge — it's about which parameter values the fifth-execution average decided to optimize for. If
mean_exec_timeinpg_stat_statementsjumps after call five with no data change, suspect the generic-plan switch before anything else.