DEV Community

Cover image for The same query went from 0.07 ms to 151 ms on its 6th run, because Postgres switched to a generic plan
Muhammad Adnan
Muhammad Adnan

Posted on

The same query went from 0.07 ms to 151 ms on its 6th run, because Postgres switched to a generic plan

Here's a query that was fast in every test, and fast the first five times it ran in production:

SELECT id, customer_id, total, created_at
FROM orders
WHERE status = $1
ORDER BY created_at DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

On the sixth run it took 151 ms, and it kept taking 151 ms after that. Same query, same parameter, same data. Nothing changed except how many times the connection had run it.

The setup

PostgreSQL 16, 2,000,000 orders, with the skew most order tables have:

status rows
delivered 1,939,883
pending 59,909
refunded 208

There's an index on status and one on created_at. The query runs as a prepared statement, which is what you get from node-postgres when you pass a query name, from postgres.js (it prepares by default), and from Prisma's statement cache. In psql that's PREPARE and EXECUTE. The scripts and full output are in the measurement folder for this post.

Five fast runs, then a slow one

Run as a plain query, status = 'refunded' takes 0.4 to 0.7 ms. Postgres sees the value 'refunded', knows from its statistics that it matches about 200 rows, reads them through the status index and sorts them:

Limit
  ->  Sort  (top-N heapsort)
        ->  Index Scan using orders_status_idx on orders  (rows=208)
Enter fullscreen mode Exit fullscreen mode

Now the prepared version. The app's busiest call is the "recent deliveries" list, so the connection runs EXECUTE recent('delivered') five times, then someone opens the refunds page:

Execution Parameter Time
1 to 5 delivered 0.01 to 0.03 ms
6 refunded 11.1 ms
7 to 10 refunded 9.4 to 10.2 ms

And for a rare status whose rows are all old (50 disputed orders, all among the oldest 10% of the table, like disputes from a past incident):

Query Time Rows read to return 20
Plain query, 'disputed' 0.07 to 0.12 ms 50
Prepared, after 5 runs with 'delivered' 151 ms 1,880,649

pg_prepared_statements shows what happened: generic_plans = 5, custom_plans = 5.

Why the sixth run is different

For the first five executions of a prepared statement, Postgres plans each one with the actual parameter value, a custom plan. From the sixth on, it builds a generic plan, planned without knowing $1, and keeps using it if its estimated cost isn't higher than the average of the custom plans so far. That saves planning time on every later call.

Without the value, the planner can't use the statistics for 'refunded'. It assumes status = $1 matches an average share of rows: one third of the table (rows=666667 in the plan), since there are three distinct values. If a third of all orders match, the cheapest plan is to walk the created_at index from the newest end and stop after 20 matches:

Limit
  ->  Index Scan Backward using orders_created_at_idx on orders
        Filter: (status = $1)
        Rows Removed by Filter: 1880629
Enter fullscreen mode Exit fullscreen mode

For 'delivered' that's perfect: the newest 20 rows are almost all delivered. For 'disputed', Postgres walks back through 1.88 million rows before it finds 20. The plan is cached for the connection, so every later call pays the same price.

The five fast calls also made the switch look safe: their custom plans were this very index walk, cheap for 'delivered', so the generic plan's estimate compared well.

How to spot it

  • The query is fast in psql or a SQL console, and slow from the app. Consoles send plain queries; the app sends prepared statements.
  • It's slow only for some parameter values, and only after a connection has warmed up. A fresh deploy looks fine for a while.
  • EXPLAIN EXECUTE shows $1 in the plan instead of a value. That's a generic plan.
  • SELECT name, generic_plans, custom_plans FROM pg_prepared_statements; shows generic_plans above 0 (PostgreSQL 14+).

The fix

1. Give the query an index that's right for every value. The query filters on status and sorts by created_at, so index both, equality column first:

CREATE INDEX CONCURRENTLY orders_status_created_idx
  ON orders (status, created_at);
Enter fullscreen mode Exit fullscreen mode

With it, the generic plan jumps straight to the rows for whatever $1 is and reads 20 index entries. Measured with the same five-then-one sequence: under 0.08 ms for 'refunded', generic plan and all. Fixing the index removes the bad choice instead of hiding it, and it's the same rule as for any "latest N" query: equality columns first, then the ORDER BY columns.

2. Or tell Postgres to always plan with the real value, for queries where no single plan fits every parameter:

SET plan_cache_mode = force_custom_plan;  -- PostgreSQL 12+
Enter fullscreen mode Exit fullscreen mode

With that, the same sequence stayed at 0.07 to 0.54 ms (custom_plans = 10, generic_plans = 0). You can set it per session, per role (ALTER ROLE app SET plan_cache_mode = force_custom_plan) or per database. The cost is planning on every execution, which is small for a query like this one but adds up for complex ones, so prefer fixing the index where you can.

3. Don't prepare that query. With node-postgres, leave out name for this query; it then goes out as an unnamed statement and gets planned with its values each time.

Checklist

  • Columns with skewed values (status, type, tenant_id) used with $1 in a prepared statement get an index that serves every value, not just the common one.
  • Test rare parameter values after five or more executions on the same connection, not on a fresh one.
  • When a query is fast in psql and slow from the app, check pg_prepared_statements and EXPLAIN EXECUTE before anything else.
  • Reach for plan_cache_mode = force_custom_plan only where no index can serve every value.

Making Claude Code check this

Reading a plan, spotting a filter that throws away almost every row, and choosing the composite index is what the slow-query profiling skill in my Claude Code skills pack for TypeScript + GraphQL + Postgres backends is for. The generic-plan twist is one more reason to test with your rarest values, not your most common ones.

Top comments (0)