Plan Trees

Optimizer Deferred

Plan diagrams show mechanisms and exact counts while cost choice is deferred.

highlighted = computed this step

The optimizer is deferred

The diagrams show exact cardinalities and physical mechanisms. They do not choose a winning plan. Note: choosing by cost needs table statistics, which this book defers.

optimizer deferred\text{optimizer deferred}

Mechanisms shown

The nested-loop root row count is 2 and the hash root row count is 2. Note: both diagrams expose mechanism and exact row counts.

nested loop=2,hash=2\text{nested loop}=2,\quad \text{hash}=2

cardinalities are exact counts on these tiny tables; choosing a plan by cost needs statistics and is deferred - no speed/perf claims; nested-loop vs hash is mechanism, not speed.

Nested-loop mechanismproject namerows 2nested loop joinrows 2scan Studentsrows 3filter course eq DBrows 2scan Enrollrows 3 resultnameAnnBo
Hash mechanismproject namerows 2hash joinrows 2scan Studentsrows 3filter course eq DBrows 2scan Enrollrows 3 resultnameAnnBo

Cost needs statistics

This engine has exact tiny-table counts, but no table statistics model. Real optimizers choose among equivalent mechanisms by estimating cost from table statistics: row counts, selectivity, and histograms of how values are distributed. Those estimates are an empirical register, not an exact fact of this tiny plan tree. This book stays on the exact side: it shows mechanisms and recomputed cardinalities, but it does not model real data distributions or engine assumptions. That boundary is the honest stopping point. Note: cost-based choice belongs outside this book's engine surface.

cost deferred\text{cost deferred}

Summary

Use these plans to read mechanisms and cardinalities, not to rank plans. Note: choosing by cost needs statistics and is deferred.

no ranking\text{no ranking}