The model
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.
A concrete walk-through
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.
Costs and failure cases
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.
Check your understanding
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.