Course contents
On this page
Lesson plan 14: Logical optimization
Apply predicate pushdown, projection pruning, and join transformation only when semantics allow.
Placement: Part 3: Turning requests into execution.
Prerequisites: Lesson 13: Query front end, plus the earlier dependency chain.
Learning outcomes
- Apply predicate pushdown, projection pruning, and join transformation only when semantics allow.
- Explain why outer joins, duplicates, NULLs, volatile expressions, and correlated queries restrict rewrites.
- Follow a concrete equivalence proof or counterexample instead of assuming fewer operators always means correctness.
- Separate a general mechanism, a documented implementation choice, and an illustrative numerical assumption.
Teaching sequence
- Establish the motivating situation: Moving a filter across an outer join silently removes customers with no orders.
- Introduce the vocabulary and the correctness obligation before an optimization. Define predicate pushdown, projection, join transformations.
- Develop the mechanism: Prove a safe inner-join rewrite using bag and null semantics, then construct the outer-join counterexample.
- Follow the complete worked example: Filtering 1 million rows to 1% leaves 10000 rows before a join. Explain the assumptions, intermediate units, and limiting interpretation.
- Extend the scenario to changed load, skew, or failure, explaining decorrelation, equivalence, semantic constraints.
- 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
Two relational-plan trees show a legal filter pushdown, followed by an outer-join counterexample where the same-looking rewrite removes rows. 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.