Spotting and Fixing Slow Queries
A diagnostic checklist for the 'this query is slow, fix it' interview prompt.
The 'This Query Is Slow, Fix It' Prompt
This is the capstone interview prompt: the interviewer hands you a slow query and an EXPLAIN ANALYZE plan and asks you to diagnose it. They are testing a method, not memorized tricks.
A strong answer follows a checklist out loud: measure, read the plan, find the dominant cost, form a hypothesis, propose a fix, and verify. This lesson builds that checklist step by step.
Stay systematic and narrate your reasoning, that is what earns the senior rating.
Step 1: Measure With EXPLAIN ANALYZE
Never guess from the SQL alone. Get the real plan with EXPLAIN (ANALYZE, BUFFERS).
ANALYZE gives actual times and row counts; BUFFERS shows whether you are hitting cache or reading from disk. Together they tell you if the query is CPU-bound, I/O-bound, or just doing too much work.
Run it a couple of times; the first run may pay a cold-cache penalty that distorts timing.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01';All lessons in this course
- Reading an EXPLAIN Plan
- Seq Scan vs Index Scan vs Index-Only
- Join Algorithms: Nested Loop, Hash, Merge
- Spotting and Fixing Slow Queries