What does a database execution plan tell you, and what is the difference between a plan the optimizer only predicts and a plan collected while the query actually ran?
answer
- operator tree, not SQL text
- estimate-only vs run-and-measure
- cost = unitless internal units, never ms
- actual plan really executes (writes too)
- rows returned vs rows touched
basics
~20 sA plan is the tree of physical operators the engine will run - scans, joins, sorts - with estimated rows and cost per node. A predicted plan shows estimates only; an execution-time plan really runs the query and adds actual rows, loop counts and timings.
solid answer
~50 sA plan is the optimizer's chosen physical strategy: how each table is accessed (full scan vs index access), the order tables are joined in, which join algorithm is used, and where sorts, aggregations and materializations sit. Each node carries estimates - rows it expects to emit, row width, and a cost number in the optimizer's own internal units. There are two flavours. A **predicted, compile-only plan** is produced without executing anything: cheap and safe, but every number is a guess derived from statistics. An **execution-time plan** actually runs the query and annotates each node with what really happened - actual rows, number of executions, elapsed time, memory used, whether an operator spilled to temporary storage. Diagnosis needs the execution-time plan, because the interesting defect is nearly always the gap between what the optimizer expected and what the data really did. Fall back to the predicted plan when running the query would be too slow or too destructive.
go deeper
Be able to say a plan is the engine's step-by-step strategy with per-operator row estimates and cost, and that an execution-time plan additionally reports what really happened.
Name the common operator families, explain that cost is unitless, and say why you prefer an execution-time plan for diagnosis and what it costs you to get one.
Frame the plan as evidence: rows first, cost second, and the estimate-versus-actual gap as the primary diagnostic signal. Mention safe capture on production (replica, rollback, plan capture from monitoring).
Talk about plans as artefacts of a moment - statistics, volume and configuration - and about the practices that make them trustworthy: production-like data for testing, plan capture in monitoring, and validating fixes by measurement rather than by cost.
## A plan is a procedure, not your SQL SQL is declarative: you describe the result you want, not the steps. The optimizer converts that description into a procedure - a tree of physical operators - and the execution plan is that tree made visible. The plan is not a rewrite of your query text; two very different-looking queries can compile to the same plan, and the same query can compile to different plans on different days as data and statistics change. Typical operators you will see, whatever the engine calls them: - **Access methods**: full table scan, index range scan, index unique lookup, index-only access (all needed columns live in the index), bitmap/rowid access. - **Join methods**: nested loop (for each outer row, probe the inner side), hash join (build a hash table on the smaller input, probe with the larger), merge join (both inputs sorted, walk them together). - **Blocking/materializing operators**: sort, hash build, aggregate, spool/temp table. These must consume their whole input before emitting a row. - **Row-shaping operators**: filter, projection, limit, distinct, window computation. ## The numbers attached to each node Every node in a predicted plan carries the optimizer's guesses: - **estimated rows** - how many rows the optimizer believes this node emits, derived from table statistics (row counts, distinct values, histograms) and the predicates applied. - **row width** - average bytes per row, which drives memory and sort estimates. - **cost** - a *unitless internal number*, roughly a model of page reads plus CPU work, calibrated to the optimizer's own constants. It is not milliseconds and is only meaningful for comparing candidate plans for the same query on the same server configuration. An execution-time plan adds measured facts: **actual rows**, **executions/loops** (how many times the node was started), **actual elapsed time**, **memory granted/used**, and warnings such as spilling to disk. ## Why the two flavours matter The predicted plan answers 'what does the optimizer intend to do?'. That is enough to spot a structurally wrong shape - a full scan of a huge table where you expected an index lookup, a join order that starts from the biggest table, a sort you did not ask for. The execution-time plan answers 'what actually happened?'. Only it can show you that a node the optimizer expected to emit 50 rows emitted 2 million, that a nested loop ran 40,000 times, or that a sort spilled. Almost every hard performance bug is that mismatch, so the execution-time plan is the real diagnostic tool. The price is that the engine must genuinely run the query. That means: the full runtime is paid (a 40-minute query takes 40 minutes to explain this way), the load hits production, and for INSERT/UPDATE/DELETE the data really changes. The standard safety measures are to run it inside a transaction that you roll back, to run against a replica or a restored copy with comparable data volume and statistics, or to capture the plan from whatever plan-capture/monitoring facility the engine offers for queries already running. ## Reading discipline Three habits separate people who read plans from people who stare at them: 1. Look at **rows before cost**. Cost is a model; row counts are the thing the model gets wrong. 2. Look for the **cheapest ratio question**: how many rows did the query return versus how many rows did the plan touch? A query returning 10 rows after touching 10 million is an access-path problem regardless of what the cost says. 3. Never compare cost numbers **across** queries or servers. A cost of 12,000 for one query says nothing about a cost of 400 for another; it is only a currency inside one optimization decision. Finally, a plan is a snapshot of one compilation. It reflects the statistics, data volume and configuration at that moment. A plan captured on a small development database is evidence about shape, not about production behaviour.
- Why can requesting an execution-time plan be risky on a production system?Because the engine really executes the statement: you pay the full runtime and full resource cost, and a data-modifying statement genuinely modifies data. Mitigations are to run it inside a transaction you roll back, to use a replica or a restored copy with production-like volume, or to capture the plan of the already-running statement from the engine's monitoring facilities.
- Two plans for the same query show costs of 1,200 and 3,400. Does the cheaper one always run faster?No. Cost is the optimizer's model output based on estimated cardinalities, and if those estimates are wrong the cost ranking is wrong too. Cost also assumes a particular cache state and hardware calibration. The only authoritative comparison is measured execution - actual rows, time and I/O - which is why you validate a fix by running it, not by admiring a lower cost.
A predicted plan is the route your navigation app draws before you leave; an execution-time plan is the trip recorder afterwards, showing where you actually sat in traffic.
saying these in an interview costs you the question
- Reading the plan as the order of SQL clauses (FROM, WHERE, SELECT) rather than an operator tree
- Calling the cost number milliseconds or seconds
- Comparing cost values between different queries or different servers
- Claiming a predicted plan proves the query is fast, without ever running it
- Not realising an execution-time plan on an UPDATE or DELETE actually changes data