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 plan 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 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 plan 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

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 plan 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 plan 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

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}