Execution plan

What is an execution plan?

An execution plan is the step-by-step route a database takes to answer a query. It is a tree of physical operators, the scans, joins, sorts, and aggregates the engine will run, arranged in the order they feed into each other.

You need it because SQL is declarative. You write the result you want, not the procedure to produce it. The query optimizer reads your query, weighs the possible routes, and picks one. The execution plan is that decision written down, so reading it is how you find out what the database is really doing when a report drags.

Every major engine exposes it. PostgreSQL and MySQL print it with EXPLAIN, and SQL Server shows an estimated or actual execution plan in Management Studio. The vocabulary differs a little, but the shape is the same everywhere.

The plan is a tree of operators

Read a plan from the bottom up. The leaves are the operators that touch raw data, a table scan or an index read. Their output flows upward into joins, then sorts, then aggregates, until the top operator returns your rows.

Each node carries an estimate. In PostgreSQL a node prints a cost range, an estimated row count, and an estimated row width:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

                       QUERY PLAN
-----------------------------------------------------------
 Seq Scan on orders  (cost=0.00..2145.00 rows=98 width=64)
   Filter: (customer_id = 42)

Here the engine chose a sequential scan: it reads the whole orders table and keeps the rows where customer_id = 42. The rows=98 is a guess at how many will match. A fitting database index on customer_id would turn that leaf into an index scan and let the engine jump straight to the matching rows instead of reading all of them.

Estimated plans versus actual plans

There are two versions of a plan, and telling them apart is the first thing to get right. An estimated plan is what the optimizer expects to do before the query runs. PostgreSQL gives it with EXPLAIN; SQL Server calls it the estimated execution plan.

An actual plan is what really happened during a run. EXPLAIN ANALYZE in PostgreSQL and MySQL executes the query and records the real timing and the real row counts next to the estimates. SQL Server calls this the actual execution plan.

The estimates come from statistics. The optimizer does not count your rows live; it reads samples that ANALYZE or auto-analyze collected earlier: table sizes, common values, histograms. As the PostgreSQL manual puts it, "the query planner needs to estimate the number of rows retrieved by a query in order to make good choices of query plans." When those statistics are stale, the estimates are wrong, and a wrong estimate is how a cheap-looking plan turns into a slow one.

Reading the operators that matter

A handful of operator choices explain most slow queries. Two comparisons come up again and again.

Sequential scan versus index scan

A sequential scan (MySQL labels it type: ALL) reads every row in the table. An index scan uses an index to jump to a small range. A scan is not automatically wrong: for a small table, or a query that returns most of the rows anyway, reading everything beats bouncing through an index. It turns bad when the table is large and you wanted only a few rows. Whether a filter reaches the storage layer in the first place is what predicate pushdown decides.

Nested loop versus hash join

A nested loop join walks the outer input and looks up matches in the inner input once per outer row. It is quick when the outer side is small. A hash join builds a hash table from one input and probes it with the other, which wins when both sides are large. The optimizer picks between them from its row estimates, so the join you get is only as sound as those estimates.

The estimated-versus-actual row gap

If you read one number in a plan, read this one: the difference between estimated rows and actual rows on each operator. It is the most reliable sign of a plan gone wrong.

Say the optimizer estimates that a filter returns 10 rows and picks a nested loop join on that basis. At run time the filter returns 1,000,000 rows, because the statistics were built before a large load into the data warehouse. The engine now runs the inner lookup a million times instead of ten. The estimated plan looked cheap; the actual plan is a wreck, and EXPLAIN ANALYZE shows it plainly, with rows=10 sitting next to actual rows=1000000.

A gap that wide almost always means one of two things: the statistics are out of date and need a fresh ANALYZE, or the data is skewed in a way a single-column estimate cannot capture. It is also how you confirm query folding in Power Query: the plan on the source database shows whether your filter ran as a real predicate, or whether every row was pulled out first and filtered afterwards.