Row Estimate Errors Multiply Across Joins, So Deep Plans Go Wrong Fastest
Each join's row estimate is built from its inputs' estimates, so an error at the bottom of the plan is not carried along, it is multiplied. A filter that is off by ten feeding a join that is off by ten produces an estimate off by a hundred, and by the fourth join the planner may be a million rows adrift with no way to notice. This is why you fix the deepest wrong estimate first rather than the step that looks slowest, and why plans over many tables are so fragile.
Questions this Concept answers
- Why do estimate errors get worse as a plan gets deeper?
Why Cardinality Estimation Errors Break the Cost-Based Query Optimizer in Databases
A cost-based query optimizer has three sequential components — cardinality estimation (CE), predicting the row count of every intermediate result; a cost model (CM), mapping those estimates to expect…