Optimizer Choices

Choose the Lower Estimate

The lower estimated work row is chosen.

highlighted = computed this step

Choose the lower estimate

The toy optimizer chooses the plan with fewer estimated work units.

lower estimate wins\text{lower estimate wins}

Compare two rows

Nested loop has 24 units and hash join has 11 units, so the chosen plan is hash_join.

hash work=11\text{hash work}=11

Optimizer-choice examples are named toy estimate-model outputs; they are not benchmark results, database-product claims, hardware claims, or real speed predictions.

Choose the lower estimate: table statstablerolerowCountdistinctKeysOrdersleft_after_filter_input126Customersright_join_input88 filter estimatelabelinputRowsselectivityestimatedRowsstatus filter121/43 plan estimate rowsplanformulaleft/buildright/probeestimatedWorkchosennested_loop_joinfiltered_left_rows * right_rows3824nohash_joinbuild_rows + probe_rows8311yes estimate factsfactvaluemodelNametoy_optimizer_estimate_v1leftRows12rightRows8selectivity1/4filteredRows3nestedLoopWork24hashJoinWork11chosenPlanhash_joinrealSpeedClaimnoproductClaimnorequiresBenchmarkForSpeedyes

Read the chosen flag

Only the lower estimate row is marked chosen.

chosen by estimate\text{chosen by estimate}

Summary

This answers the Book Four question inside a named model boundary.

model boundary\text{model boundary}