MediumExperience level: 3-5 years · 8-15 years1 min read

How do you read an execution plan to find why a query is slow?

Reviewed by Gurusankar M.

Asked inAmazonMicrosoftDeloitte
#explain#execution plan#full scan#index seek#optimization
Report issue

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.

On this page

Concept Made Simple

Coffee Chat Question

“How do you read an execution plan to find why a query is slow?”

Remember It Faster

Mind Map Answer

Seq/Table Scan
reading whole table — index?
Index Seek/Scan
using an index (good)
Estimated vs actual rows
mismatch = stale stats
Join type
nested loop / hash / merge

Learn by Doing

Hands-on Keyboard

sql
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?

Think Beyond the Expected

What If?

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:

  • EXPLAIN ANALYZE
  • scan vs seek
  • join types
  • estimated vs actual rows
  • update stats / fix costliest node

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

  1. Nested loop vs hash vs merge join — when each?
  2. How do stale statistics cause bad plans?
  3. What's the difference between EXPLAIN and EXPLAIN ANALYZE?

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.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.