The three kinds of optimization
How do you tell an algebraic rewrite, a data reduction and a cost-based choice apart, and why do they fail differently?
How should the work of an optimizer be grouped? The right test is: does the benefit of the transformation depend on the data, and if so, does it depend on statistics or only on metadata? That gives three categories with very different failure modes.
| Information required | Can it be wrong? | What limits it | |
|---|---|---|---|
| Algebraic rewrites (constant folding, predicate simplification, decorrelation, common-subexpression elimination, view expansion) | None. Only the schema and the laws of algebra | Only through an implementation bug. The benefit is unconditional | Rule coverage, and the cost of checking whether a rule applies |
| Data-reduction (pushdown, pruning, skipping, late materialization) | Metadata only: partition values, min/max values, row-group statistics | Rarely harmful; occasionally so (expensive predicates, orderings destroyed) | What the storage layer is able to accept |
| Cost-based choice (join order, access path, physical operator, data distribution) | Statistics, which are a lossy summary of data you have not read | Without bound, and in the dangerous direction | The information problem above |
The first category is free, correctness-preserving improvement. The second is where the asymptotic wins are: pushdown and pruning change how much data exists for the rest of the plan, not how fast you process it. In a cloud system priced by bytes scanned, they also change the dollar figure directly. The third is the only category that can regress catastrophically, and it is the reason every other mechanism here exists: statistics, feedback, adaptivity, plan caching, hints.
Read that way, the work of the planner is: the free stuff, the big stuff and the risky stuff. A fourth thing hides inside the third and deserves its own name: what the system does when it finds out, in the middle of a run, that the estimate was wrong. Adaptive execution is not just an execution trick. It is the planner admitting the informational limit and moving the decision to a later time.