Execution Plans in SQL
Introduction
An execution plan is the database engine's description of how it intends to produce the result of a SQL statement. It exposes the physical work hidden behind declarative SQL: which objects are accessed, how rows are located, where predicates are applied, how tables are joined, whether data is sorted or aggregated, and how rows move from one operation to another.
Plans are indispensable for performance work, but they are not verdicts. A scan is not automatically bad, a seek is not automatically fast, and the operator with the largest displayed percentage is not always the real bottleneck. Good analysis connects a plan to runtime measurements, representative parameter values, data distribution, concurrency, and the business purpose of the query.
What Is an Execution Plan in SQL?
A query optimizer evaluates multiple legal strategies and selects one according to its cost model. For a simple filter, it might scan a table, scan an index, or navigate directly through an index. For a join, it must also choose table order and an algorithm such as nested loops, hash join, or merge join. The resulting plan is a tree of physical operations that collectively produce the requested rows.
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
The statement does not command the engine to use an index. The plan records the engine's chosen access path based on indexes, statistics, estimated selectivity, available memory, and engine capabilities.
Why Execution Plans Matter
Two statements can return identical results while consuming very different resources. One may inspect millions of rows and discard nearly all of them; another may navigate directly to a small range. A slow query's plan can reveal excessive row access, repeated lookups, unnecessary sorting, poor join order, inaccurate estimates, memory spills, or parallel coordination overhead.
Plans also validate changes. Adding an index or rewriting a predicate is only a hypothesis until the new plan and runtime measurements show what changed. The useful question is not whether the drawing looks simpler, but whether elapsed time, CPU, reads, memory, waits, and workload throughput improved without changing results or harming writes.
How SQL Execution Plans Are Generated
Query processing commonly moves through parsing, binding, logical rewriting, optimization, plan selection, and execution. Parsing checks syntax. Binding resolves objects, columns, types, and permissions. Rewriting may simplify predicates or transform subqueries while preserving meaning. Optimization estimates row counts and costs for possible plans, then selects a candidate for execution.
The exact search is limited because every possible join order and physical strategy would be too expensive to enumerate for a large query. Optimizers use transformations, pruning, time limits, heuristics, and cost estimates. A chosen plan is therefore the optimizer's best practical choice under its model and available information, not mathematical proof that no faster plan exists.
Estimated and Actual Execution Plans
An estimated plan is produced without executing the statement. It is useful when a query is expensive, modifies data, or cannot safely run in the current environment. It shows predicted operators, row counts, and costs. An actual plan executes the statement and augments the plan with runtime details such as actual rows, loops, and timing, depending on the engine.
Actual plans provide stronger evidence but can have overhead. More importantly, runtime analysis of an INSERT, UPDATE, or DELETE may perform the modification. Use a safe test environment or a carefully controlled transaction, and understand the selected database's behavior before requesting execution statistics.
Sample Database for the Examples
The following simplified schema supports the examples. The foreign-key columns make relationships clear, while candidate indexes will be introduced only when a workload justifies them.
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
department_name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
department_id INTEGER NOT NULL,
employee_name VARCHAR(120) NOT NULL,
salary DECIMAL(12, 2) NOT NULL,
hire_date DATE NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
Viewing Plans in MySQL
MySQL uses EXPLAIN before a statement to display its planned execution. Traditional tabular output includes table order, access type, candidate keys, selected key, estimated rows, filtering percentage, and extra details. JSON format exposes a richer hierarchy and cost information that is easier for tools to process.
EXPLAIN
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
EXPLAIN FORMAT=JSON
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
Understanding MySQL Access Types
The type column describes the access strategy. Values such as const, eq_ref, ref, and range often indicate increasingly broad indexed access, while index means a full index scan and ALL means a full table scan. This is contextual rather than a universal ranking. A scan can be reasonable for a small table or a query returning most rows.
Read key, rows, filtered, and Extra together. Messages about temporary tables, filesort, index conditions, or attached filters explain work that the access-type label alone cannot describe.
EXPLAIN ANALYZE in MySQL
EXPLAIN ANALYZE runs the statement and reports iterator timing, rows, and loop counts. It is valuable for comparing estimates with actual behavior and finding operations repeated many times. Because it executes the statement, treat it as runtime work rather than harmless metadata.
EXPLAIN ANALYZE
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
Viewing Plans in PostgreSQL
PostgreSQL's EXPLAIN shows a plan tree containing nodes such as sequential scans, index scans, bitmap scans, joins, sorts, and aggregates. Each node reports an estimated startup cost, total cost, row count, and average row width. Costs are internal planning units, not milliseconds.
EXPLAIN
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
EXPLAIN ANALYZE in PostgreSQL
Adding ANALYZE executes the query and includes actual timing, rows, and loops. Options such as BUFFERS expose cache and I/O activity, while machine-readable formats support deeper tooling. Planning time and execution time appear separately.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
For data-changing statements, PostgreSQL documentation recommends wrapping analysis in a transaction and rolling it back when appropriate. That technique still requires caution because locks, sequences, triggers, and external side effects may not behave like ordinary table changes.
Reading PostgreSQL Plan Output
Indentation identifies parent-child relationships. A parent consumes rows produced by its children. actual time=a..b generally represents time until the first row and time until all rows for one loop; multiply rows and timing considerations by loops when an inner node repeats. Lines such as Rows Removed by Filter, heap fetches, sort method, memory, and buffer counts often explain the real cost.
Viewing Plans in SQL Server
SQL Server Management Studio can display graphical estimated and actual plans. Transact-SQL options can also return textual or XML plans. SET SHOWPLAN_XML ON obtains an estimated plan without running the statement, whereas SET STATISTICS XML ON executes it and returns runtime plan data.
SET SHOWPLAN_XML ON;
GO
SELECT employee_id, employee_name
FROM dbo.employees
WHERE department_id = 10;
GO
SET SHOWPLAN_XML OFF;
SQL Server Estimated and Actual Plans
An estimated plan is appropriate when execution is unsafe or prohibitively expensive. An actual plan helps compare estimated and actual row counts, operator executions, memory behavior, and runtime warnings. SQL Server plans may also expose missing-index suggestions, implicit conversions, spills, parallel exchanges, and parameter values used during compilation and execution.
Operator percentages are estimates relative to the displayed batch. They are useful orientation, not measured elapsed time. A low-percentage node executed thousands of times can matter more than a visually prominent node, and blocking or storage latency may dominate beyond the plan's cost model.
Viewing Plans in Oracle
Oracle can explain a statement into a plan table and format it with DBMS_XPLAN. An explain plan contains optimizer estimates. Runtime information can be obtained from cursor statistics when the statement is executed with suitable instrumentation and displayed through the relevant DBMS_XPLAN function.
EXPLAIN PLAN FOR
SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);
Table Scans
A table scan reads table pages or rows to find qualifying data. It may be the correct operation when a table is small, a predicate matches a large portion of the data, no useful index exists, or sequential access costs less than many random lookups. Treating every scan as a defect produces unnecessary indexes and can make writes slower.
A scan deserves investigation when it repeatedly processes a large object to return a tiny result, especially for a high-frequency request. Check predicate form, statistics, data type alignment, selectivity, available indexes, and whether the query requests too many columns.
Index Scans
An index scan traverses a substantial part of an index. Because an index can be narrower than its table and ordered by key, scanning it may be efficient for broad filters, ordered output, or covering queries. It can also be expensive when the index is large and only a few rows are ultimately needed.
Index Seeks and Targeted Access
An index seek navigates to a key or range rather than reading the entire structure. Selective equality and bounded range predicates commonly support seeks when the indexed key order aligns with the predicate. A seek may still read many rows, and a seek followed by repeated lookups can cost more than a scan, so the label alone does not establish efficiency.
Seek, Index Scan, and Table Scan Compared
| Access pattern | Typical strength | Possible concern |
|---|---|---|
| Targeted index access | Selective keys or ranges | Many matches or repeated row lookups |
| Index scan | Ordered or covered broad access | Large index read for a small result |
| Table scan | Small tables or broad results | Large object repeatedly filtered to few rows |
Key Lookups and Covering Indexes
In SQL Server, a key lookup uses a clustered key to retrieve columns missing from a nonclustered index. This can be efficient for a few rows but expensive when repeated thousands of times. A carefully designed covering index may remove the lookup by storing all columns needed by an important query.
CREATE INDEX ix_employees_department
ON employees (department_id)
INCLUDE (employee_name, salary);
INCLUDE is not portable syntax. Covering also increases index size and write maintenance, so validate the benefit rather than adding every selected column.
RID Lookups
SQL Server uses a RID lookup when a nonclustered index points to rows in a heap, which is a table without a clustered index. Repeated RID lookups have a similar amplification risk to key lookups. Investigate the number of executions, selected columns, and whether the heap and index design match the workload.
Filter Operators
A filter evaluates a predicate on incoming rows. Filtering late is expensive when an earlier operator produces far more rows than necessary. Determine whether the predicate can participate in the access path, be pushed closer to the data source, or be expressed in a SARGable form. Some residual filters are normal because not every condition can define an index boundary.
Sort Operators
Sorting supports ORDER BY, merge operations, duplicate removal, window functions, and some aggregations. It is a blocking operation because it may need substantial input before returning output. Large sorts require memory and can spill to temporary storage when the grant or working memory is insufficient.
Possible remedies include reducing rows or columns before sorting, aligning an index with a frequent ordering requirement, removing unnecessary ordering, and improving estimates. An index created only to avoid one sort still carries storage and write costs.
Nested Loops Joins
A nested loops join takes rows from an outer input and searches the inner input for each one. It is effective when the outer input is small and the inner side has efficient indexed access. It becomes costly when estimation errors cause the outer side to be much larger than expected or when each iteration scans substantial data.
Hash Joins
A hash join builds an in-memory hash structure from one input and probes it with the other. It often suits large, unsorted equality joins. It requires memory, and inaccurate estimates can cause batching or spills. A hash join is not inherently a problem; it may be the most efficient strategy for broad data combinations.
Merge Joins
A merge join processes inputs ordered by the join keys and advances through both sets. It can be efficient for large ordered inputs and many-to-many matching, but required sorts can erase the advantage. Existing index order or upstream operations may provide order without an explicit sort.
Choosing Among Join Algorithms
| Join | Often effective when | Common risk |
|---|---|---|
| Nested loops | Small outer input and indexed inner access | Large actual outer input or costly repeated access |
| Hash join | Large equality joins without useful order | Memory pressure and spills |
| Merge join | Both inputs are already ordered | Expensive preparatory sorts |
The optimizer chooses based on estimated cardinality and cost. If the algorithm looks inappropriate, first investigate why the estimates or available access paths led to it.
Aggregation Operators
Aggregates implement calculations such as COUNT, SUM, and grouped results. A stream aggregate consumes ordered input and can process groups incrementally. A hash aggregate builds groups in a hash table and works without preordered input, but it needs memory and can spill. Reduce input early and select only necessary grouping keys.
Estimated Rows
Estimated rows are the optimizer's prediction of how many rows an operation will produce. They influence join order, join algorithm, access paths, memory, and parallelism. Estimates depend on statistics, predicate assumptions, parameter values, correlations, and engine-specific cardinality models.
Actual Rows
Actual rows are observed during execution. Compare them with estimates at each important node, not only at the root. A large mismatch near the beginning can propagate through the entire tree and explain poor memory allocation or join selection. Multiple loops also matter: a small per-loop row count can become large when repeated many times.
Cardinality Estimation
Cardinality estimation predicts row counts after filters, joins, and grouping. Problems arise with stale statistics, skewed values, correlated columns, expressions on indexed columns, implicit conversions, temporary data, or parameter-sensitive predicates. Improve the information available to the optimizer before forcing a plan.
Understanding Plan Costs
Estimated costs combine model-specific assumptions about CPU, I/O, row counts, and operation types. They compare alternatives within the optimizer; they are not universal units and should not be compared casually across engines or servers. Runtime evidence can differ because of cache state, waits, concurrency, hardware, and estimation error.
Logical and Physical Reads
A logical read obtains a data page through the database buffer manager, whether the page was already cached or had to be loaded. A physical read requires storage access. Reducing unnecessary logical reads is often a durable optimization because it lowers work even when data is cached. Physical reads vary more with cache warmth and concurrent workload.
Memory Grants, Sorts, and Hash Spills
Some engines reserve execution memory for sorts and hashes. Too little can spill intermediate data to temporary storage; too much can reduce concurrency by making other requests wait. SQL Server exposes memory grants directly, while PostgreSQL and MySQL report memory and temporary work through different mechanisms. Correct row estimates and smaller inputs often help more than simply increasing a global memory setting.
Parallel Execution Plans
Parallel plans divide work among workers and use exchange or coordination operators to distribute and combine rows. They can reduce elapsed time for large operations but consume more total CPU and incur coordination overhead. Skew can leave one worker doing most of the work. Parallelism is therefore a workload decision, not an automatic sign of either quality or failure.
Predicate Pushdown
Predicate pushdown applies filters as close as possible to the data source, reducing rows passed to joins, sorts, and aggregates. Remote databases, column stores, partitions, and storage engines may expose whether filtering happened internally. Pushdown can be blocked by expressions, type conversions, outer-join semantics, or transformations that would change results.
Index Conditions and Residual Filters
An index condition defines what portion of an index is navigated or scanned. A residual filter checks additional conditions after candidate rows are obtained. Distinguishing them explains why an index is technically used but still processes many rows. Align useful leading keys with selective equality or range predicates while respecting the broader workload.
Plan Caching
Compiling plans costs CPU, so database systems commonly reuse prepared or cached plans. Reuse improves efficiency, but a plan compiled for one parameter distribution may not fit another. Schema changes, statistics updates, memory pressure, configuration, and engine rules can invalidate or replace plans.
Parameter Sensitivity
Suppose one customer has ten orders while another has millions. The same parameterized statement may need different plans for those values. SQL Server discussions often call the issue parameter sniffing, while other engines have their own generic-versus-custom or bind-sensitive behavior. Diagnose the actual value distribution and compilation context before applying recompilation, hints, or plan controls.
Missing Index Recommendations
Plan tools may recommend indexes based on the statement being optimized. Treat these as candidates, not instructions. A suggestion may overlap an existing index, ignore write overhead, omit useful key order, or optimize one query at the expense of the workload. Consolidate proposals and validate them with representative reads and writes.
Execution Plan Warnings
Warnings can identify spills, implicit conversions, missing statistics, unmatched partitions, excessive memory, or other engine-specific conditions. Read the warning details and affected node. A warning is evidence to investigate, while its urgency depends on frequency, runtime impact, and business importance.
Common Plan Performance Problems
Recurring patterns include a large scan returning few rows, a seek followed by excessive lookups, a nested loop with a huge actual outer input, sorts or hashes spilling, estimates differing dramatically from reality, conversions preventing useful access, and repeated application queries creating an N-plus-one pattern. Blocking and network transfer can also dominate even when the plan itself is reasonable.
Before-and-After Filter Example
A function applied to an indexed date column may prevent direct range navigation:
-- Harder to use as an index range
SELECT employee_id, employee_name
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) = 2025;
-- Explicit half-open range
SELECT employee_id, employee_name
FROM employees
WHERE hire_date >= DATE '2025-01-01'
AND hire_date < DATE '2026-01-01';
The range version preserves the same year boundary while offering a direct index condition in many systems. Confirm the new plan, result equivalence, reads, and elapsed time rather than assuming the rewrite wins.
Before-and-After Join Example
A join plan may scan employees and repeatedly search departments, or it may reorder the inputs based on filtering. An index on the employee foreign key can support both filtering and joining when department queries are common:
CREATE INDEX ix_employees_department
ON employees (department_id);
SELECT d.department_name, e.employee_name
FROM departments AS d
JOIN employees AS e
ON e.department_id = d.department_id
WHERE d.department_id = 10;
The plan should show how many department and employee rows were estimated and produced. If one department contains most employees, the optimizer may legitimately prefer a broad access strategy.
Real-World E-Commerce Order History
An order-history endpoint commonly filters by customer, orders by descending creation time, and returns a page. A useful plan should avoid sorting an entire order table and avoid scanning unrelated customers. A composite index beginning with customer and continuing with order time can support filtering and ordering, but keyset pagination usually scales better than deep offsets.
Real-World Banking Transaction History
A transaction-history query must preserve account authorization and stable ordering while meeting strict latency goals. Analyze account selectivity, date ranges, partition pruning, and lookup amplification. Never remove security predicates for speed. Test hot accounts separately because their distributions can produce parameter-sensitive plans.
Real-World Software Testing Dashboard
A dashboard may join runs, suites, cases, and failures before grouping by status. Its plan can expose duplicate-producing joins, broad scans, and hash aggregates that spill. Pre-aggregate at the correct grain, filter the reporting period early, and consider a maintained summary only when freshness and operational ownership are explicit.
Real-World Employee Management Query
Employee searches often combine optional department, status, salary, and hire-date filters. One catch-all statement may produce unstable plans across combinations. Compare representative parameter sets, avoid expressions that hide indexed values, and resist creating an index for every possible filter combination.
Real-World Article Listing
An article listing may filter publication status, order by publication time, and return a small page. A plan that reads all drafts and published articles before sorting indicates an access-path mismatch. An engine-supported filtered or partial index can focus on published rows; a composite index can also support status and ordering where appropriate.
Common Mistakes When Reading Plans
- Assuming every table scan is inefficient or every seek is efficient.
- Treating estimated cost percentages as measured elapsed time.
- Reading only the root or the visually largest operator.
- Ignoring loops, rows read, rows returned, and removed rows.
- Adding every suggested index without checking overlap and write cost.
- Forcing join types before investigating estimates and statistics.
- Comparing plans produced with different parameters or cache conditions.
- Running runtime analysis on modifying statements without safeguards.
- Optimizing a rare query while ignoring frequency and business priority.
- Changing several factors at once and losing causal evidence.
Best Practices for Plan Analysis
Begin with a reproducible performance problem and record a baseline. Capture query text, parameter values, plan, execution frequency, elapsed and CPU time, logical and physical reads, waits, memory, and result size. Read the plan from its data-producing leaves toward the consumer while following each engine's display convention.
Focus on row flow. Compare rows read, estimated rows, actual rows, and loops. Find where the first major divergence or expansion occurs. Then form one targeted hypothesis: rewrite a predicate, improve statistics, alter an index, reduce selected columns, or remove unnecessary work. Retest correctness and performance across representative values and concurrency.
Interview Questions and Answers
What is an execution plan?
It is a tree of physical operations selected or used by a database engine to execute a SQL statement, including data access, joins, filtering, sorting, and aggregation.
What is the difference between an estimated and actual plan?
An estimated plan is generated without running the statement. An actual plan executes it and adds runtime observations, with the exact metrics depending on the database.
Is an index seek always better than a scan?
No. A seek can touch many rows or trigger expensive lookups, while a scan can be cheapest for small tables or broad results.
Why compare estimated and actual rows?
Large differences reveal cardinality problems that can distort join selection, memory allocation, access paths, and parallelism.
What causes a spill?
A sort or hash operation exceeds its available execution memory and writes intermediate work to temporary storage, often because inputs are large or estimates are inaccurate.
How should missing-index recommendations be used?
As workload candidates. Check existing indexes, key order, write cost, storage, and measured benefit before implementation.
Practical Exercises
- Create the sample tables and load skewed department data.
- Capture an estimated plan for a selective department query.
- Capture runtime statistics in a safe test environment.
- Compare estimated and actual rows at each important node.
- Add a department index and compare reads and elapsed time.
- Select extra columns and observe whether lookup work appears.
- Rewrite a date function as a half-open range.
- Join employees and departments and identify the join algorithm.
- Increase result size and observe whether the algorithm changes.
- Add ordering and inspect sort memory or temporary work.
- Test two highly different parameter values for sensitivity.
- Document whether each change helped reads, CPU, duration, and writes.
Quick Reference
| Signal | Meaning | Question to ask |
|---|---|---|
| Estimated rows | Optimizer prediction | Does it match representative reality? |
| Actual rows and loops | Observed row flow | Where does work multiply? |
| Logical reads | Pages accessed through buffers | Can unnecessary access be reduced? |
| Physical reads | Pages fetched from storage | Is cache state affecting the comparison? |
| Spill | Temporary storage used for work | Are estimates, memory, or input size responsible? |
| Parallelism | Work divided among workers | Does coordination or skew outweigh the benefit? |
| Residual filter | Condition checked after access | Can a useful condition define the access range? |
| Plan cache | Reusable compiled strategy | Do different parameters need different plans? |
Step-by-Step Execution Plan Workflow
- Identify a query with meaningful latency, resource use, or frequency.
- Save exact SQL, parameters, schema context, and baseline metrics.
- Capture an estimated plan when execution is unsafe.
- Capture an actual plan or runtime profile in a controlled environment.
- Trace the plan tree and identify major access, join, sort, and aggregate nodes.
- Compare estimated rows, actual rows, rows read, and loop counts.
- Find the first important estimation error or row-flow expansion.
- Check reads, CPU, waits, memory, spills, blocking, and network output.
- Review predicates, selected columns, joins, statistics, and indexes.
- Apply one targeted and reversible change.
- Capture the new plan under comparable conditions.
- Verify result correctness and compare all baseline metrics.
- Test representative parameters, data volumes, and concurrency.
- Evaluate write cost, storage, maintenance, and effects on other queries.
- Deploy safely, monitor production behavior, and document the evidence.
Key Takeaway
Execution plans translate declarative SQL into observable physical work. They reveal access paths, join algorithms, row estimates, sorting, aggregation, memory behavior, and runtime warnings, but their symbols must be interpreted in context. The same operator can be appropriate in one workload and expensive in another.
Reliable analysis starts with a measured problem, follows row flow through the plan, compares predictions with reality, and changes one justified factor at a time. Evaluate plans alongside reads, CPU, waits, elapsed time, concurrency, and correctness. The goal is not to manufacture a particular plan shape; it is to achieve predictable, efficient behavior for the real workload.