OptimizationIntermediate
A PostgreSQL query is slow. What do you do?
“Walk me through diagnosing a slow query.”
What this tests
- Evidence-first debugging
- Reading a plan
Answers by level
Read the beginner answer first and notice what is missing.
Get the real query with real parameters, then EXPLAIN (ANALYZE, BUFFERS). Find the node that dominates time or rows: a Seq Scan with huge Rows Removed by Filter, a Sort over many rows, a Nested Loop with a high loops count. Fix that one node, then re-run EXPLAIN to confirm the plan changed.
I change one thing at a time and never skip the second EXPLAIN.
Green flags · Red flags
Strong green flag · Starts with EXPLAIN ANALYZE and investigates before changing anything.
Green flags
- EXPLAIN ANALYZE first
- Identifies the dominant node
- Considers query rewrites, not just indexes
Red flags
- "Upgrade the server" first
- Adds indexes blindly
Follow-up questions
F1
What in the plan tells you an index is missing?
Scenario
A query is at 8 s. Before you touch anything, what is the first command you run and what do you look for?