Diagnosing a bad plan, and what planning forbids
In what order should you look for the cause of a bad plan, and what can planning never do?
What planning forbids, and the escapes
What the planner cannot do
- You cannot know an intermediate cardinality without producing it. So no amount of planning effort makes a plan reliably optimal.
- You cannot store a joint distribution over columns within any affordable budget. So correlation is invisible by construction, not by omission.
- You cannot decide query containment in general. So materialized-view and rewrite coverage will always be incomplete, and the incompleteness will always be silent.
- You cannot have both complete search and bounded planning time beyond roughly fifteen relations.
- You cannot convert optimizer cost into time, ever.
The escapes and their prices
| Escape | Price |
|---|---|
| More statistics | Collection scans, and it still does not address join-level error, where the damage is |
| Runtime sampling | Per-query latency; provably poor for NDV and for tails |
| Hints and plan baselines | You lose adaptivity: you froze a decision made under one distribution and accepted the organizational duty to revisit it forever |
| Better rewrites | Correctness corners; each one is a real bug that shipped somewhere |
| Deferring the decision entirely (adaptive execution) | A materialization point, nearly free in a shuffle-based engine and expensive in a pipelined one (Execution decisions) |
One escape that is rarely named: reduce the number of decisions
Wide denormalized tables, pre-joined data marts and "one big table" modelling are usually justified by scan efficiency, and that justification is weak, because columnar projection pushdown (reading only the needed columns) mostly removes the width penalty. The real benefit is that a query over one table has no join order to get wrong, no correlation across join boundaries to mis-estimate, and no containment assumption to violate. Denormalization is largely optimizer risk reduction. It is chosen because variance governs production: a 20% faster average with a 100× worse tail is not a trade a team will accept, and removing the decision removes the tail. That is also the honest reason why hints exist and will not go away.