Course contents
On this page
Lesson plan 16: Cost-based planning
Derive cardinality estimates and identify errors caused by skew and correlated predicates.
Placement: Part 3: Turning requests into execution.
Prerequisites: Lesson 15: Physical operators, plus the earlier dependency chain.
Learning outcomes
- Derive cardinality estimates and identify errors caused by skew and correlated predicates.
- Explain how estimation, operator costs, parameter values, and plan reuse interact.
- Compare reoptimization and adaptive execution against their overhead and correctness constraints.
- Separate a general mechanism, a documented implementation choice, and an illustrative numerical assumption.
Teaching sequence
- Establish the motivating situation: An optimizer assumes independence between country and currency and chooses an expensive join order.
- Introduce the vocabulary and the correctness obligation before an optimization. Define statistics, cardinality, skew.
- Develop the mechanism: Derive estimated cardinalities, expose correlation error, and describe plan alternatives without equating cost units to milliseconds.
- Follow the complete worked example: Independent 1% predicates predict 100 matches in 1 million rows; perfect correlation yields 10000. Explain the assumptions, intermediate units, and limiting interpretation.
- Extend the scenario to changed load, skew, or failure, explaining correlation, plan cache, parameter sensitivity, adaptive execution.
- Connect the mechanism to a documented implementation and an alternative with a different workload fit. Close by stating the remaining cost and the next lesson dependency.
Required chapter material
Use a complete execution trace or worked calculation, supported by an annotated static diagram or table. Include language-neutral pseudocode or labeled SQL when it clarifies the mechanism. State preconditions, ownership, visibility, and failure boundaries for algorithms. Describe operational costs as well as the normal path. All questions raised in the narrative must be resolved in the text.
Visual explanation
A competing-plan cost table uses both estimated and actual row counts; a correlation example shows why multiplying independent selectivities fails. The accompanying prose explains the arrows, state changes, or table entries so the lesson remains understandable in a plain Markdown reader.
Editorial checks
Confirm that the numerical example agrees with its units and assumptions. Check that a faster path preserves the stated contract, or explicitly identify the changed guarantee. Keep hypothetical figures separate from product measurements. Explain both an appropriate use and a limitation of the case design. Check terminology against the glossary and links against the course index.