VocalHyre
Sign up

SQL · Queries that survive scale

How do you read a query execution plan?

Hardquery plansexplainoptimizerUpdated Oct 2026
Quiz this track

Short answer

Use EXPLAIN ANALYZE to see access methods, join types, estimated versus actual rows, and time. Look for misestimates, sequential scans on large tables, and expensive sorts or loops.

A plan is a tree of operations. Each node has a cost estimate and, with ANALYZE, actual row counts and timing. The most useful signal is often the gap between estimated and actual rows, because a bad estimate propagates into a bad join strategy.

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.email = 'a@example.com'
  AND o.created_at >= now() - interval '30 days';
ANALYZE executes the query, so avoid running it casually on expensive writes or production queries without care.

What to look for

  • Seq Scan on a large table when a selective index exists
  • Nested Loop with a high actual row count on the inner side
  • Hash Join with a large hash build or spill to disk
  • Sort or HashAggregate using significant memory or temporary files
  • Estimated rows far from actual rows, which often means stale statistics
  • Rows Removed by Filter, indicating wasted work

Join algorithms have different strengths: nested loops suit small indexed lookups, hash joins suit large unsorted inputs, and merge joins suit inputs already sorted on the join key. Seeing one does not automatically mean it is wrong; compare it with the row counts and cost.

Use EXPLAIN without ANALYZE when you only need the plan and cannot afford to execute the statement. Timing from a cold cache also differs from steady-state performance, so compare plans under realistic conditions.

Follow-ups you should expect

Interviewers use these to check whether the first answer was understanding or memorisation.

  1. 1What is the difference between EXPLAIN and EXPLAIN ANALYZE?
  2. 2Why does the planner sometimes choose a sequential scan?
  3. 3What does a large gap between estimated and actual rows indicate?
  4. 4How do nested loop, hash join, and merge join differ?

Say it in the interview

I read a plan from the inside out, looking at access methods, join types, estimated versus actual rows, and time. The biggest signal is usually a row estimate that is far off, because that leads to a poor join or memory choice. I check for sequential scans on large tables, expensive sorts, and nested loops with many inner iterations. Then I verify the fix with the same realistic query and data.

Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.

Related questions

MediumWhat is an index and what does it cost?HardHow do you approach SQL performance tuning?MediumHow do composite indexes and the leftmost prefix rule work?

Reading is step one

Now say it out loud.

Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.

Start a free SQL interviewBrowse all questions