Lesson 18 of 22Advanced19 min

Reading Execution Plans

By the end of this lesson

Interpret a plan to find why a query is slow.

SQL describes what you want, not how to get it. The database's optimiser decides how: which index to use, which table to read first, which join algorithm to apply. That decision is the execution plan.

Guessing why a query is slow is unreliable. The plan tells you what the database actually did, and it is the difference between fixing a query and changing things until the number moves.

Getting a plan and real measurements

SQL

-- SQL Server: the estimated plan, without running the query
SET SHOWPLAN_XML ON;
GO

-- SQL Server: actual plan plus real row counts and timings.
-- In SQL Server Management Studio, use "Include Actual Execution Plan".
SET STATISTICS IO, TIME ON;

SELECT c.company_name, COUNT(*) AS orders
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.company_name;

SET STATISTICS IO, TIME OFF;

-- PostgreSQL equivalent
-- EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

An estimated plan shows what the optimiser intends. An actual plan shows what happened, including real row counts, and is far more useful for diagnosis.
STATISTICS IO reports logical reads per table — how many pages were read. That number is a better comparison between two query versions than elapsed time, because it is not affected by what else the server is doing.
STATISTICS TIME reports CPU and elapsed time. Run a query twice and compare the second runs: the first often pays for loading data into memory, which distorts the comparison.
PostgreSQL's EXPLAIN ANALYZE runs the query and reports actual timings alongside estimates. EXPLAIN without ANALYZE only estimates.

Seek or scan: the first thing to look at

Four operators that cover most of what you will see:

Index Seek: The database used the index's sort order to jump to the rows it needed. Cost grows slowly as the table grows. This is what you want for a selective filter.
Index Scan: It read the whole index. Cheaper than reading the table if the index is narrow, and still proportional to the data size.
Table Scan / Clustered Index Scan: It read every row of the table. On a small table this is the right choice and nothing to fix. On a large table with a selective filter, it is the signal to investigate.
Key Lookup: It found rows via a nonclustered index, then went back to the table for columns the index did not contain — once per row. A few are fine. Thousands usually mean the index should INCLUDE the missing columns.

Estimated versus actual rows: the signal that explains most bad plans

The optimiser chooses a plan by estimating how many rows each step will produce. Those estimates come from statistics: summaries of how values are distributed in each column, maintained by the database.

Every meaningful decision follows from those estimates. Expecting 50 rows, it will happily seek and do a per-row lookup. Expecting two million, it will scan and build a hash table. Both are sensible for the row count assumed.

So when a plan looks strange, compare the estimated row count against the actual on each operator. A large divergence means the optimiser made a reasonable decision from wrong information — and that is a different problem from a missing index.

What a divergence looks like, and what it caused

Text

Nested Loops (Inner Join)
  Estimated rows: 47          Actual rows: 2,140,880

  |-- Index Seek on ix_orders_customer_date
  |     Estimated rows: 47    Actual rows: 2,140,880
  |
  |-- Key Lookup on orders (PK_orders)
        Executions: 2,140,880

Reading: the optimiser expected 47 rows, so a nested loop with a
per-row lookup was a good choice. It got 2.1 million rows, so the
per-row lookup ran 2.1 million times.

The plan is not wrong for 47 rows. A nested loop join with a lookup per row is close to optimal at that size.
At 2.1 million rows it is a disaster, and the fix is not to force a different join. It is to find out why the estimate was 47.
Common causes: statistics not updated after a large data change, a filter the optimiser cannot estimate well (such as a comparison between two columns, or a value hidden inside a local variable), or a query where multiple filters interact in ways the statistics do not capture.

A repeatable way to work through a slow query:

Capture an actual plan, not an estimated one
You need real row counts to compare against estimates. An estimated plan cannot show you a divergence, which is the most informative signal available.
Find the expensive operator
Look for the highest actual row counts and the highest execution counts, not just the highest cost percentage. Cost percentages are derived from the same estimates that may be wrong.
Compare estimated with actual on that operator
Within an order of magnitude, the estimate is fine and the plan is probably reasonable. Off by 1,000 times or more, treat the estimate as the problem to solve first.
If estimates are wrong, refresh the statistics
In SQL Server: UPDATE STATISTICS orders WITH FULLSCAN. In PostgreSQL: ANALYZE orders. Then capture the plan again. This alone fixes a meaningful share of sudden slowdowns after a bulk load.
If estimates are right, look at access and lookups
A scan where few rows were wanted points at a missing or unusable index. A Key Lookup with a high execution count points at an index that needs INCLUDE columns.
Change one thing, then measure again
Record logical reads and elapsed time before and after. Two changes at once tell you nothing about which one helped, and one of them may have hurt.

Two more things worth recognising in a plan. A warning icon on an operator is worth reading — it commonly reports an implicit conversion that disabled an index seek, or a sort that spilled to disk because the memory grant was too small.

And a missing index suggestion is a hint, not an instruction. The optimiser proposes an index that would help this query, with no knowledge of your other queries or your write volume. Treat it as one input: check whether an existing index could be widened instead.

Common mistake

Common mistakes

Reading the estimated plan and drawing conclusions about actual behaviour. If the estimates are wrong, the estimated plan shows you a reasonable plan for data you do not have.

Trusting the cost percentages in a plan as measurements. They are the optimiser's own estimates, so a badly estimated operator also has a badly estimated cost. Use actual row counts and logical reads.

Applying every missing index recommendation. It is common to end up with several overlapping indexes this way, each suggested for a different query, and a much slower write path.

Adding a join hint or an index hint to force a plan. It can work today and locks the optimiser out of adapting as data grows. Treat hints as a last resort with a comment explaining why, not as a tuning technique.

Comparing a cold first run against a warm second run. The first paid to read data from disk into memory. Run both versions twice and compare the second runs.

Summary

The execution plan shows what the database actually did, which is the only reliable starting point for tuning
A seek uses index order to jump to rows; a scan reads everything, and is correct on small tables or wide result sets
Estimated versus actual row counts diverging by orders of magnitude points at stale or unusable statistics
A Key Lookup with a high execution count means the index is missing columns the query needs
Measure logical reads and timings before and after, and change one thing at a time

Practice

Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.

Think about it

A report ran in 400ms for months. After a bulk import of 4 million order rows on Monday, it takes 90 seconds. The plan now shows a nested loop where it previously showed a hash join, and the estimated rows on the orders seek is 200 while the actual is 3.9 million.

What is the most likely cause, what would you try first, and what would you check afterwards?

Show solution

The statistics on orders describe the table as it was before the import. The optimiser is estimating from a distribution that no longer matches reality, so it expects 200 rows and picks a nested loop — which is a good plan for 200 rows and a terrible one for 3.9 million.

First action: update the statistics on orders, then capture the plan again. If the estimate comes back close to the actual and the plan switches to a hash join, that was the cause and the fix is complete.

Afterwards, check why the statistics were stale. Automatic updates are triggered by a proportion of rows changing, and on a very large table a 4-million-row import can be below that threshold. Adding an explicit statistics update to the end of the import job prevents a repeat.

What not to do first: add an index, or force a hash join with a hint. Both might improve the number, and neither addresses the cause, so the next import creates the same problem somewhere else.

SQL

-- 1. Refresh what the optimiser knows about the table
UPDATE STATISTICS orders WITH FULLSCAN;

-- 2. Re-run with measurements and compare the plan
SET STATISTICS IO, TIME ON;
-- ... the report query ...
SET STATISTICS IO, TIME OFF;

-- 3. Add this to the end of the import job so it does not recur
-- UPDATE STATISTICS orders;

Try it yourself

Take any query from this course that joins orders and order_items. Capture its actual plan and its logical reads. Then add a covering index on order_items (order_id) INCLUDE (product_id, quantity, unit_price) and capture both again.

Write down the before and after numbers, and describe what changed in the plan.

Show solution

What to expect: a Key Lookup disappearing, because the index now contains every column the query needed from order_items. Logical reads on order_items should drop, often substantially.

The specific figures depend entirely on your data volume, so the number itself is not the lesson. The practice being built is recording a baseline before the change, because without one you cannot tell improvement from noise.

Also worth observing: on a small test table the difference may be negligible or even slightly worse. That is real information, not a failed exercise — it is why index decisions are made against production-scale data rather than a development sample.

Finally, consider the cost side. That index must now be maintained on every insert into order_items, which is one of the most written-to tables in this schema. A read improvement on one report is not automatically worth it.

Knowledge check

Nothing is recorded and there is no score. The explanation appears either way.

An operator shows estimated rows 47 and actual rows 2,140,880. What does this most likely indicate?

The query is missing an indexThe optimiser's statistics are out of date or unusable, so it chose a plan suited to a much smaller resultThe plan is fine and the divergence can be ignoredThe table needs to be rebuilt

When is a table scan the correct choice?

Never — a seek is always betterOn a small table, or when the query genuinely needs a large proportion of the rowsOnly when no indexes exist on the tableOnly on tables that are never written to

Saved in this browser only.