What is a query execution plan?

WHAT THIS TESTS The interviewer wants to know if you can diagnose a slow query in production rather than just guessing. They are checking that you know the tool, can read its output, and connect symptoms to fixes like indexes or rewrites.

A GOOD ANSWER COVERS A query execution plan is the tree of physical operators the optimizer selected to run a query: scans, joins, sorts, aggregates, with estimated cost and row counts at each node. You obtain it by prefixing the query with EXPLAIN to see estimates without running it, or EXPLAIN ANALYZE to actually execute and capture real timings and row counts. You read it bottom-up and inside-out. Key things to look for: a sequential or full table scan on a large table that has a selective filter, which usually means a missing or unusable index; a large divergence between estimated and actual rows, which signals stale statistics; expensive sort or hash operations that spill to disk; and nested loop joins driving many iterations over a large inner relation.

COMMON WRONG ANSWERS Reading cost as a time unit in milliseconds; it is an abstract optimizer unit. Assuming any scan is bad when a full scan can be optimal for small tables or low-selectivity filters. Reading only EXPLAIN and never EXPLAIN ANALYZE, so you never see actual versus estimated rows.

LIKELY FOLLOW-UPS How would you fix a sequential scan; how do stale statistics mislead the optimizer; what does a row-estimate error of 1000x imply; when is a scan actually the right choice.

ONE CONCRETE EXAMPLE A report query on orders runs for thirty seconds. EXPLAIN ANALYZE shows a sequential scan on orders estimating 100 rows but returning 2 million, then a nested loop join. The estimate is stale, so the optimizer picked a nested loop suited to few rows. Running ANALYZE to refresh statistics and adding an index on the filtered column lets it choose a hash join and an index scan, dropping runtime to under a second.