Identical SQL is fast in dev but slow in prod — is it stale stats, data skew, or parameter sniffing?
The same query is instant in dev and slow in prod. You captured the prod plan:
Nested Loop (cost=.. rows=5) (actual time=41200 rows=3841002 ..)
-> Index Scan using idx_events_type on events (actual rows=3841002 ..)
-> Index Scan using users_pkey on users (actual rows=1 ..)
Planning Time: 0.3 ms
Execution Time: 41833 msThe prod tables are far larger than dev. Diagnose which cause fits and why.
The plan estimated 5 rows but the Index Scan returned 3.8M, and the planner chose a Nested Loop that only pays off for a few rows — a stale-statistics symptom. Run ANALYZE so the estimate matches reality and it switches to a hash join. Data skew fits when stats are fresh but one value dominates.
- ✗Calling it parameter sniffing when Postgres re-plans per session
- ✗Attributing the whole slowdown to data skew despite the estimate gap
- ✗Forcing a Seq Scan instead of refreshing statistics
- →How would you confirm data skew if ANALYZE did not help?
- →When does a PL/pgSQL generic plan cause a sniffing-like effect in Postgres?
The key is the line (cost=.. rows=5) (actual .. rows=3841002): the planner expected 5 rows but got 3.8M. A Nested Loop only pays off when the outer side yields a handful of rows — for each one it does a fast indexed lookup into the second table. At 3.8M iterations it is a disaster. The estimate, low by 750,000x, is a direct stale-statistics signature on a large prod table (in dev the table is tiny, so even a bad plan is fast).
ANALYZE events;Once statistics are refreshed the estimate lands near 3.8M and the planner switches from Nested Loop to Hash Join on its own. How to tell the causes apart: stale statistics — a large estimate-vs-actual gap (this case); data skew — statistics are fresh but one type value is disproportionately frequent; parameter sniffing — a cached generic plan for a prepared statement (mainly a SQL Server term; in Postgres the near-analogue is a generic plan after a few executions).
