Postgres prepared statement turns slow after the fifth run: the generic plan trap

Our activity feed API in Go started showing a strange latency profile. Most requests took 3 to 6 ms, but a stable minority took 3 to 5 seconds, always for small tenants, and never right after a deploy. Restarting the service made the problem disappear for a few minutes, then it came back. When we pasted the exact SQL into psql with the same tenant id, it ran in 2 ms every time.

The slow query log on the primary showed the statement as an extended protocol execution:

LOG:  duration: 4187.332 ms  execute stmtcache_9b1f2c...: SELECT id, kind, actor_id, created_at
      FROM activity WHERE account_id = $1 ORDER BY created_at DESC LIMIT 20
DETAIL:  Parameters: $1 = '88412'

Reproducing it

The key was to run the query the way the driver does: as a named prepared statement, several times on the same session. In psql:

PREPARE feed(bigint) AS
  SELECT id, kind, actor_id, created_at
  FROM activity
  WHERE account_id = $1
  ORDER BY created_at DESC
  LIMIT 20;

-- five executions with large accounts, as the busy pods see them
EXECUTE feed(101); EXECUTE feed(102); EXECUTE feed(103);
EXECUTE feed(104); EXECUTE feed(105);

-- sixth execution, small account
EXPLAIN (ANALYZE, BUFFERS) EXECUTE feed(88412);

Limit  (cost=0.57..112.94 rows=20 width=40) (actual time=4102.871..4102.880 rows=3 loops=1)
  Buffers: shared hit=310227 read=1188904
  ->  Index Scan Backward using activity_created_at_idx on activity
        (cost=0.57..2809734.11 rows=500084 width=40) (actual time=4102.869..4102.876 rows=3 loops=1)
        Filter: (account_id = $1)
        Rows Removed by Filter: 61204415
Execution Time: 4102.951 ms

The $1 in the filter is the signature of a generic plan: it was built without knowing the parameter value.

What we ruled out

• Lock waits. pg_stat_activity.wait_event was empty during slow executions; the backend was on CPU and I/O.
• Pool exhaustion. pgx pool metrics showed no acquire wait. The time was spent inside the query.
• Bad statistics. After ANALYZE activity, the custom plan for the small tenant was still fast and the generic plan was still slow. Statistics were fine; the plan type was the issue.

Root cause

PostgreSQL plans a prepared statement with the actual parameter values (a custom plan) for the first five executions. From the sixth execution on, it also builds a generic plan and compares its estimated cost with the average cost of the custom plans so far. If the generic plan does not look more expensive, it is cached and reused for every later execution on that session, whatever the parameter values.

Our pods mostly served large accounts, so the first five custom plans on each connection were relatively expensive (bitmap scan on account_id plus a sort of thousands of rows). The generic plan assumes an average account with about 500,000 rows evenly spread over time. Under that assumption, walking the created_at index backwards and stopping after 20 matches looks very cheap, so the generic plan won the comparison. For a small account with three events, that "stop after 20" never happens, and the scan reads 61 million rows to find three.

What is happening under the hood

The decision lives in the plan cache. Each prepared statement keeps counters of how many custom and generic plans it has used. The logic, simplified:

1. Executions 1 to 5: always custom plans, costs recorded.
2. Execution 6 and later: build the generic plan once. If its cost, plus a small planning cost credit, is lower than the average custom plan cost, use it from now on.
3. Once switched, the session keeps reusing the generic plan unless the statement is invalidated (for example by DDL or a new ANALYZE changing statistics).

The behaviour can be overridden with plan_cache_mode, available since PostgreSQL 12:

PostgreSQL 14 added the generic_plans and custom_plans counters to pg_prepared_statements, which turns this from guesswork into something you can observe. PostgreSQL 16 added EXPLAIN (GENERIC_PLAN), which shows the generic plan for a parameterized query without preparing it.

The Go side matters too. pgx v5 uses QueryExecModeCacheStatement by default: each connection prepares a statement the first time it sees a SQL string and reuses it afterwards. Long-lived pooled connections therefore cross the five-execution threshold quickly, which is why restarts "fixed" the problem for a few minutes.

Environment

Minimal reproduction

A self-contained version you can run on any PostgreSQL 16 or 17:

CREATE TABLE activity (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id bigint NOT NULL,
  kind       text NOT NULL,
  actor_id   bigint NOT NULL,
  created_at timestamptz NOT NULL
);

-- 20 big accounts with 200k rows each, 20,000 small accounts with 5 rows each
INSERT INTO activity (account_id, kind, actor_id, created_at)
SELECT 1 + (g % 20), 'view', g, now() - g * interval '1 second'
FROM generate_series(1, 4000000) AS g;

INSERT INTO activity (account_id, kind, actor_id, created_at)
SELECT 1000 + (g % 20000), 'view', g, now() - interval '400 days' + g * interval '1 second'
FROM generate_series(1, 100000) AS g;

CREATE INDEX activity_account_idx ON activity (account_id);
CREATE INDEX activity_created_at_idx ON activity (created_at);
ANALYZE activity;

PREPARE feed(bigint) AS
  SELECT id FROM activity WHERE account_id = $1
  ORDER BY created_at DESC LIMIT 20;

EXECUTE feed(1); EXECUTE feed(2); EXECUTE feed(3); EXECUTE feed(4); EXECUTE feed(5);
EXPLAIN ANALYZE EXECUTE feed(1500);

SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;

On our test machine the sixth execution flips to the generic plan and the counters show generic_plans = 1. Exact thresholds depend on your cost settings, so if you do not see the flip, increase the number of big-account rows.

Who is affected

Any client that uses server-side prepared statements on long-lived connections: pgx (default mode), pgjdbc after prepareThreshold executions, psycopg 3 after prepare_threshold executions, Npgsql with automatic preparation, and PgBouncer 1.21 or later with max_prepared_statements enabled. Queries are at risk when the best plan depends heavily on the parameter value: skewed tenant columns, rare status values, and LIMIT queries where a sort index competes with a filter index.