Optimizer Choices

Hash Join Work Units

Hash join work is build rows plus probe rows in this toy model.

highlighted = computed this step

Hash join work units

In this toy model, hash join builds one side and probes with the other.

build rows+probe rows\text{build rows}+\text{probe rows}

Add build and probe

The hash join estimate is 11 work units.

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.

Hash join work units: 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 build side

The plan row shows which side is counted as build rows and which side is counted as probe rows.

build and probe\text{build and probe}

Summary

The formula is intentionally small so the estimate is inspectable.

inspectable model\text{inspectable model}