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 mechanism project name rows 2 nested loop join rows 2 scan Students rows 3 filter course eq DB rows 2 scan Enroll rows 3 result name Ann Bo
Hash mechanism project name rows 2 hash join rows 2 scan Students rows 3 filter course eq DB rows 2 scan Enroll rows 3 result name Ann Bo

Cost needs statistics

This engine has exact tiny-table counts, but no table statistics model. 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}