How Postgres Switched to a Generic Plan on the Sixth Execution and Timed Out Our Biggest Tenant's Reports
13:52 UTC. Alert: reports-api p99 at 31 seconds, which is suspicious because the
statement timeout is 30. Support has two tickets from the same customer, our largest, saying
the monthly revenue report spins and then errors. The service was migrated to a new database
driver at 13:05. The migration PR was reviewed by three people and changed no SQL.
the scramble
First theory: the driver migration broke something. On-call rolls reports-api back
to the old driver at 14:01. Latency drops to normal within a minute. That looks like proof, so
the team starts reading the migration diff for a bug. There isn't one. The queries are
byte-for-byte identical, the pool sizes match, the timeouts match.
At 14:20 the rollback is re-applied, because a rollback can't stay in place while the diff is clean. The first twenty minutes are fine. Then the same customer's report times out again. Smaller customers never complain. Nobody has a theory for why only one tenant is affected, and the dashboard for the endpoint shows no difference between tenants because it aggregates across all of them.
Second theory: the table is bloated or the stats are stale. Someone runs
ANALYZE orders by hand at 14:41. It takes 9 seconds, changes nothing, and the next
report for the big tenant still times out. Third theory: lock contention from the nightly
billing job. pg_locks is empty of anything interesting, and the billing job doesn't
run until 02:00 anyway.
the hunt
The detail that fits no theory is the delay. The report works right after a deploy, breaks later, and breaks for one tenant only. When a query gets slower with time instead of with input, there is usually a cache involved. So the next step is to look at what the slow backend is running right now:
SELECT pid, now() - query_start AS running, state, left(query, 90)
FROM pg_stat_activity
WHERE application_name = 'reports-api'
AND now() - query_start > interval '5 seconds';
pid | running | state | left
-------+---------------+--------+--------------------------------------------------------------
28117 | 00:00:22.41 | active | SELECT o.id, o.total, i.sku, i.qty FROM orders o JOIN order_...
Nothing unusual in the text. The old driver sent the same text with the parameters interpolated into it on the client. The new driver, asyncpg, prepares each statement on the server and sends only the parameter values. That is the real difference between the two builds, and the diff never showed it because it lives inside the driver, not in the repo. Reproducing in psql with a prepared statement and the big tenant's id:
PREPARE tenant_report (int, timestamptz) AS
SELECT o.id, o.total, i.sku, i.qty
FROM orders o JOIN order_items i ON i.order_id = o.id
WHERE o.tenant_id = $1 AND o.created_at >= $2;
-- executions 1 to 5, big tenant (id 4417)
EXPLAIN (ANALYZE, COSTS OFF) EXECUTE tenant_report(4417, '2026-10-01');
-- Hash Join ... Seq Scan on order_items ... Execution Time: 612 ms
-- execution 6
EXPLAIN (ANALYZE, COSTS OFF) EXECUTE tenant_report(4417, '2026-10-01');
-- Nested Loop
-- -> Index Scan using orders_tenant_created_idx on orders o
-- Index Cond: (tenant_id = $1) AND (created_at >= $2)
-- (rows=710 estimated, rows=712044 actual)
-- -> Index Scan using order_items_order_id_idx on order_items i
-- (loops=712044)
-- Execution Time: 41207 ms
Executions one through five used the big tenant's real value, so the planner saw the real
skew and picked a hash join. On the sixth, Postgres switched to a cached generic plan. That
plan is built without knowing the parameter, so for tenant_id = $1 it uses the
average rows per tenant: 4.4 million orders over 6,200 tenants is about 710. A nested loop with
710 probes is the right plan for 710 rows. It is the wrong plan for the one tenant that holds
1.7 million of those 4.4 million rows, 712,044 of them inside the report window.
This also explains every earlier observation. The rollback "worked" because the old driver
never used server-side prepared statements, so every query was planned with its real
parameters. The re-apply worked for twenty minutes because each pooled connection needs five
executions before the switch. Small tenants never noticed because the generic plan is correct
for them. ANALYZE couldn't help because the statistics were accurate. The plan
was built to ignore them.
the find
Postgres chooses between custom and generic plans under plan_cache_mode = auto.
For the first five executions of a prepared statement it plans with the actual parameters and
records the average cost. From the sixth it builds a generic plan, and if the generic plan's
estimated cost isn't worse than that average, it keeps using it. The comparison is on
estimates. Nothing checks whether the estimate was right for the value being passed now.
exec 1-5 : custom plan, real $1 = 4417 -> hash join, ~600 ms
exec 6 : generic plan, $1 unknown -> est. 710 rows
generic cost (est) <= avg custom cost?
yes -> cache generic plan
exec 7+ : generic plan, $1 = 4417 -> nested loop, 712,044 probes, 41 s
the fix
Two options existed. Turning off the driver's statement cache would restore the old behavior but costs a parse and plan round trip on every call for the other 300 queries the service runs. The narrower fix is to tell Postgres to stop switching, and to do it for the reporting role only, since the OLTP paths benefit from cached plans:
ALTER ROLE reports_api SET plan_cache_mode = 'force_custom_plan';
-- confirm on a fresh connection
SHOW plan_cache_mode;
-- force_custom_plan
Setting it on the role instead of in the driver's server_settings matters here,
because the connections go through PgBouncer in transaction mode, which doesn't forward
arbitrary startup parameters. The role-level default applies on every new server connection
no matter how it was opened. Existing pooled connections kept the old setting until they were
recycled, so the fix also needed a RECONNECT on the pooler. The big tenant's
report ran in 604 ms on the next attempt, and on the 6th, 60th and 600th after that.
the aftermath
- Swapping drivers can change query plans while the diff shows zero SQL. Whether parameters travel inside the query text or beside it decides what the planner gets to see.
- Any query where one parameter value covers a large share of a table needs a test with that value, run at least seven times. Five clean runs prove nothing about the sixth.
- The slow-query alert now groups by tenant id. The aggregate dashboard averaged 41-second reports for one customer into a p50 that looked fine.
- A rollback that fixes things for exactly one connection lifetime is a strong hint that state is accumulating per connection. Plan caches are per backend, so this pattern points straight at them.
The planner did what the docs say it does. The generic plan was right for 6,199 of 6,200 tenants, and its estimate was honest. What failed was our assumption that a query which got through review, tests and its first five production runs would keep the plan it started with.