How do you read an execution plan to find why a query is slow?
Reviewed by Gurusankar M.
⚡ Short Answer
Run EXPLAIN/EXPLAIN ANALYZE. Look for full table scans on big tables (missing/unused index), the join order and join type (nested loop vs hash), the rows estimated vs actual (bad stats), and the most expensive node. Fix the costliest operation first.
☕Coffee Chat Question
Concept Made Simple
“How do you read an execution plan to find why a query is slow?”
🧠Mind Map Answer
Remember It Faster
⌨️Hands-on Keyboard
Learn by Doing
EXPLAIN ANALYZE
SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'OPEN';
-- look for: Seq Scan on orders? rows est vs actual? join method?🔥What If?
Think Beyond the Expected
Estimated rows = 10 but actual = 2,000,000 — what does that tell you?
The optimizer is working off stale/missing statistics, so it picked a bad plan (e.g. nested loop instead of hash join). Update statistics / ANALYZE the table so the planner estimates correctly and chooses an efficient plan.
😂Real World
Reading EXPLAIN is the core skill for query tuning; the estimated-vs-actual row gap is the single most useful signal for 'why did it pick this terrible plan?'.
🎯Interviewer's Expectation
Keywords they're listening for:
⚠️Common Mistakes
- ✗Optimizing without looking at the plan
- ✗Ignoring the estimated-vs-actual row gap
- ✗Adding indexes blindly instead of reading the plan
✅Best Practices
- ✓Always EXPLAIN ANALYZE slow queries
- ✓Keep statistics fresh (auto-analyze/ANALYZE)
- ✓Target the most expensive plan node first
🔁Follow-up Questions
- 1Nested loop vs hash vs merge join — when each?
- 2How do stale statistics cause bad plans?
- 3What's the difference between EXPLAIN and EXPLAIN ANALYZE?
🧩Related Technologies
Continue Learning with AI
Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.
Plain-language foundations
I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner. Topic: Optimization (SQL) Interview question: "How do you read an execution plan to find why a query is slow?" Please: 1. Explain the core idea in simple, plain language, using an everyday analogy. 2. Define any technical terms you use. 3. Walk through one small, concrete example. 4. Finish with a single sentence I can easily remember. Keep the tone friendly and assume I'm new to this topic.
Was this answer helpful?
⭐ Featured Products
Support our platform by exploring our recommended products.
As an Amazon affiliate, purchases through these links may earn us a small commission — at no extra cost to you. It helps keep Full Stack Interview Guru free.