What is a Query Execution Plan?
Learn what a query execution plan is, how the optimizer builds it from table statistics, and how to read one in interviews.
Expected Interview Answer
A query execution plan is the step-by-step, low-level operation tree the database engine builds to actually run a SQL statement — which access paths, join orders, and algorithms it will use — chosen by the query optimizer before execution begins.
The optimizer parses the SQL, considers multiple candidate plans (different index choices, join orders, join algorithms), and picks the one with the lowest estimated cost based on table statistics like row counts and value distributions. The chosen plan is represented as a tree of operators — scans, joins, sorts, aggregates — where each node feeds rows to its parent, and the engine executes that tree bottom-up. Reading a plan lets an engineer see exactly why a query is slow, such as an unexpected full table scan where an index scan was expected.
- Reveals the real access path chosen, not the one assumed
- Exposes expensive operators like sorts or nested loops early
- Guides index and query rewrite decisions with evidence
- Turns guesswork about slowness into a measurable diagnosis
AI Mentor Explanation
Think of a captain planning a bowling attack before the innings starts: given the pitch report and batting lineup, the captain decides bowler order, field placements, and when to take the new ball, all mapped out as a sequence before a single delivery is bowled. A query execution plan is this same pre-committed sequence for a database — the optimizer studies the data's shape and decides the order of scans and joins before running the query, so execution simply follows the plan already drawn up.
Step-by-Step Explanation
Step 1
Parse and validate the query
The engine checks SQL syntax and resolves table and column references.
Step 2
Generate candidate plans
The optimizer enumerates alternative access paths, join orders, and algorithms.
Step 3
Estimate cost per candidate
Using table statistics (row counts, cardinality), each candidate plan gets an estimated cost.
Step 4
Select and execute the cheapest plan
The lowest-cost plan is chosen and its operator tree is executed to produce results.
What Interviewer Expects
- Understanding that the plan is chosen before execution, based on statistics
- Awareness that a plan is a tree of operators, not just an SQL rewrite
- Ability to name common operators like scan, join, and sort
- Knowledge that EXPLAIN reveals the plan without necessarily running the query
Common Mistakes
- Assuming the plan always matches the order clauses are written in SQL
- Confusing the logical query with the physical execution plan
- Not mentioning that statistics drive the optimizer's cost estimates
- Believing the plan never changes once written, ignoring stale statistics
Best Answer (HR Friendly)
“A query execution plan is the database's internal roadmap for how it will actually run a query — which indexes it will use, what order it will join tables in, and which algorithms it will apply. The optimizer builds this plan by estimating costs from table statistics before the query runs, and I read these plans to figure out exactly why a slow query is slow.”
Code Example
-- Ask the optimizer what plan it would use, without running the query
EXPLAIN
SELECT o.order_id, c.name
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
WHERE o.status = 'shipped';
-- Typical output shows an operator tree, for example:
-- Hash Join
-- -> Seq Scan on Orders (filter: status = 'shipped')
-- -> Hash
-- -> Index Scan on Customers using customers_pkeyFollow-up Questions
- What table statistics does the optimizer rely on to estimate cost?
- Why might the optimizer choose a full scan over an available index?
- How does a stale statistics table lead to a bad execution plan?
- What is the difference between a logical query plan and a physical execution plan?
MCQ Practice
1. A query execution plan is best described as?
The execution plan is the optimizer's chosen sequence of physical operations for running the query.
2. What does the query optimizer primarily use to choose between candidate plans?
The optimizer estimates the cost of each candidate plan using statistics like row counts and cardinality, then picks the cheapest.
3. Which SQL command typically reveals a query's execution plan without necessarily executing it?
EXPLAIN asks the database to show the plan it would use for a query, in most engines without running it.
Flash Cards
What is a query execution plan? — The operator tree of scans, joins, and other steps the engine will actually run to execute a query.
Who chooses the execution plan? — The query optimizer, based on estimated costs from table statistics.
How do you view a plan? — Using EXPLAIN (or the engine's equivalent) before or instead of running the query.
Why do plans matter for performance? — They reveal the real access path used, showing exactly why a query is fast or slow.