Database and SQL Developer interview question
How do you read a query execution plan?
Read the plan from actual row flow: compare estimated and actual cardinalities, identify scans, join algorithms, sorts, spills and repeated loops, then find the first bad estimate or expensive operator rather than only the highest cost.
Direct answer
What a strong answer covers
How it works
A plan is a tree of operators; reading it means following the rows from the leaves upward and comparing what the planner expected with what happened. EXPLAIN ANALYZE shows both. The first thing to find is the largest gap between estimated and actual rows, because a wrong estimate upstream leads to a wrong join strategy downstream. Then look for the expensive shapes: a sequential scan on a large table where a filter is selective, a nested loop with a large outer side, a sort or hash that spilled to disk, and an operator executed thousands of times in a loop. The fix is usually an index, updated statistics, or a rewrite that changes the join order, and the plan is re-read after each change.
Example
A report query took 40 seconds; the plan showed a hash join whose inner side was estimated at 200 rows but actually produced 2 million because statistics on a recently loaded table were stale. Running ANALYZE fixed the estimate, the planner switched to a merge join with an index, and the query dropped to 900 ms. A second look found a sort spilling to disk that a covering index removed.
Common mistakes
- Reading only the total cost number and ignoring the row estimates.
- Adding an index for the operator with the highest cost when the real problem is a bad estimate upstream.
- Tuning against EXPLAIN without ANALYZE and never seeing the actual rows.
A useful answer structure
Answer from your own work.
Do not memorise a script: use only examples you can explain honestly in follow-up questions.
Open the desktop workspace and rehearse a clear answer in your own words.