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;
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)
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
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
psqlor 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 EXECUTEshows$1in the plan instead of a value. That's a generic plan. -
SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;showsgeneric_plansabove 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);
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+
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$1in 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_statementsandEXPLAIN EXECUTEbefore anything else. - Reach for
plan_cache_mode = force_custom_planonly 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.
- Two skills are free (GraphQL N+1 fixes and safe Postgres migrations): github.com/adnan909/free-sample
- The full pack, with the slow-query profiling skill, 6 more skills and a starter backend tested in CI: Backend Skills Pack on Gumroad
Top comments (0)