Custom plan versus generic plan in prepared statements
After five executions PostgreSQL may freeze a prepared statement onto a generic plan. On a 999,000 to 1,000 skew that turned a 7.466 ms nested loop into a 153.215 ms hash join that spilled to disk, with no change to the SQL.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
A prepared statement is planned with the actual parameter values for its first five executions. After that the planner compares the average custom cost against a generic plan built with no parameter knowledge, and may switch. On evenly distributed data nobody notices. On skewed data the generic plan is built for the average row count, which matches neither of the values you actually pass.
session_tokens_pc is 99.90% active and 0.10% revoked. A revocation sweep that runs as a prepared statement was quick in testing and slow in production, and the query text is byte-for-byte identical.
Simple terms
A prepared statement is planned with your real values the first few times it runs. After five executions PostgreSQL may lock in a single generic plan built without knowing which value you will pass, an average-case plan. On evenly spread data that is harmless. On lopsided data, where one value matches a thousand rows and another matches a million, the average-case plan fits neither, so the exact same SQL is quick in testing and slow in production.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Query performance runbooks
Neighbouring incidents that share the same diagnostic surface.
Connected
How this connects to the rest of the library
A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.
Fixes these errors
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.