Plan Trees

Same Result, Different Plan

Nested-loop and hash plans can produce identical row sets.

highlighted = computed this step

Same result, different mechanism

Two physical plans can produce the same query result. This lesson renders both mechanisms and compares only recomputed outputs. Note: plan choice changes mechanism, not result rows.

equivalent plans\text{equivalent plans}

Nested-loop plan

The nested-loop plan recomputes row count 2. Note: the result rows are shown only in the rendered table.

nested loop rows=2\text{nested loop rows}=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 planproject namerows 2nested loop joinrows 2scan Studentsrows 3filter course eq DBrows 2scan Enrollrows 3 resultnameAnnBo

Hash plan

The hash plan recomputes row count 2. Note: the render has the same result rows by recompute, not by prose assertion.

hash rows=2\text{hash rows}=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.

Hash planproject namerows 2hash joinrows 2scan Studentsrows 3filter course eq DBrows 2scan Enrollrows 3 resultnameAnnBo

Side by side

Both root counts are 2. Note: equality means identical row sets for this tiny query.

both rows=2\text{both rows}=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 planproject namerows 2nested loop joinrows 2scan Studentsrows 3filter course eq DBrows 2scan Enrollrows 3 resultnameAnnBo
Hash planproject namerows 2hash joinrows 2scan Studentsrows 3filter course eq DBrows 2scan Enrollrows 3 resultnameAnnBo

Summary

Nested-loop and hash plans can be equivalent mechanisms for one query result. Note: choosing by cost needs statistics and is deferred.

equivalence\text{equivalence}