This trace follows the actual state transitions behind the companion Reading a Query Plan as an Execution Hypothesis. It describes a common execution path; implementation details can vary, so keep the contract separate from the mechanism.
Step 1: Parse and rewrite the query
A query planner estimates alternative ways to produce a result and chooses one using statistics and cost assumptions. A plan is an executable tree of operators such as scans, joins, sorts, and aggregates. Reading it helps explain where rows are found and how intermediate results are combined.
Step 2: Estimate rows and costs
In a nested-loop join, the inner plan may run once for each outer row; that is attractive when the outer side is small and the inner side has an index. A hash join builds a hash table from one input and probes it with the other, often fitting larger unsorted equijoins.
Step 3: Select an operator tree
The planner estimates candidate scans and joins from statistics, then the executor runs the chosen tree; compare estimated with actual row counts to find a mistaken assumption.
At this point, record the state that changed and check the invariant before advancing. If the operation repeats, make clear which values persist and which are recomputed.
Step 4: Produce and combine rows
Estimated row counts can diverge from actual counts when statistics are stale or data is correlated. A plan cost is not a direct elapsed-time promise. Compare estimated and actual rows, buffers, and timing, then identify the earliest point where the plan’s assumptions stop matching reality.
Step 5: Compare estimates with observations
An index exists but a query still uses a sequential scan. List three plausible reasons and the evidence in an execution plan that would help distinguish them.
The trace is complete when the result satisfies the stated contract. Compare this model with the concrete runtime or system you are studying before making a performance claim.