Query Optimizers
The same question has many correct executions with wildly different costs. An optimiser turns what you asked into how it will run — using rules it can always apply and statistics it can only sometimes trust.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
Two queries returning the same answer differ by orders of magnitude in cost. What decided that, and how much of it did I control?
Every analyst and every model that writes declarative SQL and expects it to run sensibly. The optimiser is what makes declarative querying viable; understanding where its vision ends is what makes a query tunable.
The unit is the plan: a tree of operators that produces the requested rows. Optimisation is a sequence of transformations on that tree, each of which must preserve the result exactly while changing what it costs (DAG (Directed Acyclic Graph)).
Write the query that expresses the question, and trust the engine. This is right, it is what declarative languages are for, and it works until the query is expensive enough that the plan matters — at which point the trust needs to become knowledge.
A predicate wrapped in a function — WHERE date(event_ts) = ... — cannot be matched to the partition values, so nothing is pruned and the query scans the entire table while looking identical to one that prunes perfectly (Partition Pruning).
- A predicate wrapped in a function —
WHERE date(event_ts) = ...— cannot be matched to the partition values, so nothing is pruned and the query scans the entire table while looking identical to one that prunes perfectly (Partition Pruning). - A user-defined function in the filter makes the predicate opaque. It cannot be pushed into the scan, cannot inform the size estimate, and cannot be reordered — three optimisations lost to one call (Predicate Pushdown).
- Statistics are stale after a load, so a cost-based decision — which side to broadcast, which join order to use — is made from a picture of the data that is months old (Broadcast Joins).
SELECT *in a view that a dozen models read defeats column pruning at every one of them, and the cost is paid on every query rather than once (Projection Pushdown).- An engine upgrade changes a default, a plan flips, and a job that ran in twenty minutes for a year takes two hours with no code change at all.
What is actually happening
- The query is parsed into a tree, then resolved against the catalog so that names become real tables and columns with real types. Errors of schema are found here, before any data is touched (The Planner: Enumerating Ways to Answer).
- Rule-based rewrites are then applied — transformations that are always at least as good: pushing filters toward the scan, pruning unused columns, folding constants, simplifying boolean expressions, flattening subqueries. They need no statistics because they cannot make things worse (How a Query Executes: Planner and Executor).
- Cost-based decisions come next, and these need statistics: row counts, distinct counts, null fractions, min and max, sometimes histograms. Join order, join strategy and aggregation strategy are chosen by estimating the cost of alternatives (Cost-Based Optimization).
- A physical plan is then produced — actual operators with actual algorithms: a hash join or a sort-merge join, a hash aggregate or a sort aggregate, a broadcast exchange or a hash exchange (Join Algorithms: Nested Loop, Hash, Merge).
- Estimation error compounds through the tree. The estimate for a join's output feeds the decision for the join above it, so a wrong estimate at the leaves produces a plan that is wrong in a coordinated way — which is why deep join trees are where optimisers are least reliable.
- Some engines re-plan at runtime, replacing an estimate with a measurement once a stage has produced its statistics. That converts a guess made before execution into a decision made during it, for the stages that follow (Stages and Tasks).
From what you asked to how it runs
An optimiser is a sequence of transformations on a tree, and its phases divide neatly into two kinds: those that are always safe and need no knowledge of the data, and those that require statistics and are therefore only as good as the statistics are.
The rule-based phase is where most of the value is, and it is remarkably reliable. Pushing a filter toward the scan, dropping unused columns, folding constants — none of these can make a query worse, so they are applied unconditionally. This is the phase that a wrapped predicate or a user-defined function defeats, and it is the phase whose loss costs the most.
The cost-based phase is where the sophistication is, and where the fragility is. Choosing a join order or a join strategy requires estimating how many rows each operator will produce, and those estimates come from statistics of unknown age about a distribution the engine has not looked at (Cost-Based Optimization).
- 1Parse
Turns text into an abstract tree of operations.
guarantees Syntax errors are found. Nothing about tables or columns is checked yet.
fails by Nothing interesting — this phase is not where the engineering happens.
- 2Resolve
Binds names to catalog objects, checks types, expands views and stars.
guarantees Schema errors are caught before execution, which is the earliest real feedback a query gets (The Planner: Enumerating Ways to Answer).
fails by Expanding a
SELECT *in a view into every column, which then has to be pruned back by a later phase that may not manage it. - 3Rule-based rewrite
Pushes filters down, prunes columns, folds constants, flattens subqueries, simplifies predicates.
guarantees Result-preserving and never worse. Applied without any knowledge of the data.
fails by Stopping at anything opaque — a user-defined function, a call into another runtime, a predicate on the result of one (Predicate Pushdown).
- 4Cost-based choice
Estimates cardinalities and chooses join order, join strategy and aggregation strategy.
guarantees Only that the cheapest plan *according to its estimates* was chosen.
fails by Stale or missing statistics, and estimation error that compounds up a deep join tree (Cost-Based Optimization).
- 5Physical planning
Selects concrete operators: hash or sort-merge join, hash or sort aggregate, broadcast or hash exchange.
guarantees An executable plan whose result matches the query.
fails by Choosing a broadcast from a size estimate that was wrong, which fails at runtime rather than at planning (Broadcast Joins).
- 6Runtime re-planning
Replaces estimates with measured statistics from completed stages and adjusts what follows.
guarantees Later decisions are made from measurement rather than estimate — for the stages that have not started.
fails by Making the plan less predictable between runs, which complicates a tight schedule (Pipeline SLOs).
Read the guarantee column: the middle phase is the one that is always safe, and it is exactly the phase that opaque expressions disable. Most practical query tuning is about keeping that phase working.
| Rewrite | What it needs to work | What defeats it | What it saves |
|---|---|---|---|
| Predicate pushdown | A predicate the engine can interpret against the source | A user-defined function, or a predicate on its result | Rows never read, and file or row-group skipping (Predicate Pushdown) |
| Partition pruning | The partition column compared directly to a value | Wrapping the column in a function, or filtering on a different column | Entire directories never opened (Partition Pruning) |
| Projection pruning | An explicit column list somewhere in the chain | SELECT *, especially inside a view | Column chunks never decoded (Projection Pushdown) |
| Constant folding / simplification | Expressions over literals | Values only known at runtime | Per-row CPU on every row of the scan |
| Join reordering | Cardinality estimates for each relation | Missing or stale statistics; correlated predicates | Enormous intermediate results that never get materialised (Join Algorithms: Nested Loop, Hash, Merge) |
| Join strategy choice | A size estimate for the build side | Stale statistics, or compressed size mistaken for memory size | A whole shuffle, when a broadcast applies (Broadcast Joins) |
| Partial aggregation | An aggregate that combines associatively | Exact distinct counts, medians, percentiles | Most of the bytes that would have been shuffled (Parallel Reduce) |
Where the optimiser goes blind
The single most valuable thing to know about an optimiser is not what it does but what it cannot see. Everything it does depends on being able to reason about an expression, and there are a handful of ordinary constructs that make an expression unreasonable-about.
The first is the wrapped predicate. WHERE dt = DATE '2026-08-25' prunes to one partition. WHERE date(event_ts) = DATE '2026-08-25' prunes nothing, because the planner cannot invert an arbitrary function to work out which partitions could contain matching rows. The two queries return the same rows, look equally reasonable in review, and differ by the entire table (Partition Pruning).
The second is the user-defined function. To the optimiser it is a black box with unknown cost, unknown selectivity and unknown determinism, so it cannot be pushed below anything, cannot inform an estimate, and cannot be reordered. A filter expressed in one is a filter that runs after all the data has been read and moved.
The third is correlated data. Optimisers generally assume predicates are independent, so WHERE country = 'DE' AND city = 'Berlin' is estimated by multiplying two selectivities — which understates the result badly, because the two columns are anything but independent. This is a known, structural limitation rather than a bug, and it is why deep plans over correlated dimensions are where estimates are worst.
| Check | Expresses | Catches | Still misses |
|---|---|---|---|
| Bytes scanned per query compared with the partitions the predicate names | Pruning actually happened. | Wrapped predicates, filters on a non-partition column, and views that hide a SELECT *. | A query that prunes perfectly and still reads far more than it needs because the partitioning was chosen for a different predicate (The Partitioning Decision). |
| Physical plan diff between the current run and the last known-good one | The engine is executing this query the way it did before. | Plan flips from statistics refreshes, data growth, configuration changes and upgrades. | A plan that has been consistently bad since the day it was written — there is nothing to diff against. |
| Estimated versus actual row counts per operator, where the engine reports both | The statistics describe the data. | Stale statistics and correlated-predicate underestimates, which are the two dominant causes of bad join plans. | Estimates that are accurate and a cost model that still picks badly, which happens with unusual data shapes. |
| Duration variance for unchanged code over time | Plan stability. | Intermittent plan flips, which are otherwise nearly impossible to catch in the act. | A steadily worsening plan, which looks like ordinary data growth in this signal (Regression or Tuesday? Telling a Real Change from Noise). |
SELECT country, SUM(amount) FROM orders WHERE date(event_ts) = DATE '2026-08-25' GROUP BY country -- the partition column `dt` is never mentioned; the filter is on a function of a different column, so every partition is opened and read before the filter is applied.
SELECT country, SUM(amount) FROM orders WHERE dt = DATE '2026-08-25' AND event_ts >= TIMESTAMP '2026-08-25 00:00' GROUP BY country -- the partition column is compared directly, so the planner reads one directory; the finer timestamp condition then filters within it.
Pruning works by comparing a predicate against values recorded in the path or in file metadata. A predicate expressed as a function of a column cannot be matched against those values, because inverting an arbitrary function is not something a planner can do. The queries are semantically identical and physically incomparable, which is exactly why this mistake survives code review (Partition Pruning).
Which statistics an engine collects, whether it refreshes them automatically, whether it re-plans at runtime, and what its default join-strategy thresholds are all vary by product and by version — and they change between releases more often than any other part of this lesson. Treat every specific threshold or automatic-statistics behaviour as something to verify in the documentation for the version you actually run.
How to build it
Most important first.
- Write predicates the planner can see: compare a column to a constant rather than wrapping the column in a function, and keep partition columns un-transformed in the
WHEREclause (Partition Pruning). - Project the columns you need. It is the cheapest optimisation in the domain and the one most often given away by habit (Projection Pushdown).
- Keep statistics current as part of the load rather than as a maintenance task. A cost-based optimiser with stale statistics is a rule-based optimiser with confidence (Cost-Based Optimization).
- Express logic in the engine's own operations wherever possible and reserve user-defined functions for what genuinely cannot be expressed otherwise. Every opaque expression is a blind spot in three phases at once (SQL Transformations).
- Read the physical plan for anything expensive, and treat a plan change as a reviewable event. Most "it was fine yesterday" incidents are plan changes (Reading EXPLAIN ANALYZE).
- Use hints last and with an expiry date. A hint is a hard-coded belief about the data, and the data will change (Broadcast Joins).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Every rewrite the optimiser applies preserves the result exactly. If a rewrite would change the answer — pushing a filter below an outer join, for instance — it is not applied, however much faster it would be (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
- It guarantees no particular plan. The same query on the same data can be planned differently after an upgrade, a statistics refresh or a configuration change.
- It guarantees nothing about opaque expressions. A user-defined function is a black box with unknown cost and unknown selectivity, and the optimiser treats it accordingly (The Planner: Enumerating Ways to Answer).
- Cost estimates are estimates. A cost-based decision is the engine's best guess from statistics of unknown freshness, not a measurement (Benchmark Fallacies: Confident Numbers That Are Wrong).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Record the physical plan with each run and diff it. A plan diff explains more slow-job incidents than any other single artefact and costs almost nothing to keep (Pipeline Observability).
- Assert on the outcome rather than the plan: row counts, reconciliation and duration. Asserting on a specific plan makes every upgrade a false alarm (Reconciliation).
- Neither notices a plan that is optimal for a query that asks the wrong question. The optimiser optimises what you wrote, faithfully, including when what you wrote is at the wrong grain (Grain: What Does One Row Represent?).
- A good plan is often the difference between a job landing inside its window and not, so optimisation is a freshness lever as much as a cost one (The Freshness SLO).
- Plan instability is itself a freshness risk: a job whose duration depends on a decision the engine makes from statistics has a duration that can change without warning (Pipeline SLOs).
- Runtime re-planning improves the expected case and widens the variance of the plan, which is a trade worth knowing about when a schedule is tight.
- Statistics drift as data changes, so plans drift with them. This is the intended behaviour and it makes plans a moving target.
- Adding a column changes projection pruning; adding a predicate changes selectivity estimates; adding a join changes the search space. Small query edits can produce large plan changes (Schema Evolution).
- Engine upgrades are plan events. Treat them as such: run the important jobs, diff the plans, and compare durations before adopting broadly.
- A bad plan is not a data problem. Recovery means fixing the input to the decision — refresh statistics, rewrite the opaque predicate, narrow the build side — and re-running.
- A hint is the emergency override, and it should be recorded as technical debt with the condition that made it necessary, so it can be removed when that condition changes (dbt Concepts).
- If a plan change produced wrong-looking numbers, the plan is not the cause. A rewrite that preserves results cannot change the answer, so look for a data change that happened at the same time (Debugging a Data Incident).
What can go wrong
- An opaque predicate defeating partition pruning, so a query scans everything and looks correct doing it.
- Stale statistics producing a broadcast of something large, or a join order that materialises an enormous intermediate.
- A plan flip after an upgrade or a data change, turning a reliable job unreliable with no diff to point at.
- A hint that was right when written and is now forcing a plan the data no longer justifies.
- The mitigation failing: adding more hints to steer around a bad estimate, which produces a query that is pinned to one shape of data and breaks when that shape changes (Query Optimization: Finding the Actual Bottleneck).
- "The optimiser will fix my query." It will restructure what it can prove is equivalent. It cannot fix a wrong join key, a wrong grain, or a predicate it cannot read (Grain: What Does One Row Represent?).
- "Rewriting the SQL changed the answer, so the optimiser was wrong." A rewrite that preserves semantics cannot change the answer. If the answer moved, the two queries were not equivalent.
- "Cost-based is always better than rule-based." Cost-based is better when the statistics are current. With stale statistics it makes confident, expensive mistakes that a rule-based plan would not have made (Cost-Based Optimization).
- "The plan is stable because the code is." The plan is a function of code, statistics, configuration and engine version. Only one of those is in your repository (Regression or Tuesday? Telling a Real Change from Noise).
Operating it
- The physical plan per run, stored and diffable. This is the artefact (Reading EXPLAIN ANALYZE).
- Bytes scanned versus bytes the predicate should have allowed. A large gap means pruning did not happen (Scan Cost).
- Join strategy per join, and estimated versus actual row counts where the engine reports both — the gap between them is the health of your statistics (Cost-Based Optimization).
- Duration variance for a job with unchanged code, which is the signature of plan instability (Regression or Tuesday? Telling a Real Change from Noise).
- At 10x, the same plan usually remains reasonable and the cost of a bad one grows proportionally.
- At 100x, plan quality dominates every other factor, and the difference between a pruned scan and a full one is the difference between a query and an outage (Partition Pruning).
- Search space grows combinatorially with join count, so optimisers apply heuristics beyond a certain number of joins. Very wide join trees are where estimates are worst and hand-checking pays most (Join Algorithms: Nested Loop, Hash, Merge).
- The largest term the optimiser controls is bytes read, through partition pruning, file skipping and column projection — and all three are defeated by expressions it cannot see through (Scan Cost).
- The second is bytes shuffled, through join strategy, join order and pre-aggregation (The Shuffle).
- Optimisation itself costs planning time on the driver, which is negligible for a batch job and can be significant for very short interactive queries against enormous catalogs.
- Cost-based optimisation buys much better plans and costs a dependency on statistics that must be maintained and can be wrong.
- Runtime re-planning buys robustness against bad estimates and costs plan predictability, which matters when a schedule is tight.
- Hints buy determinism and give up adaptation. Every hint is a promise about data that will eventually be broken (Query Optimization: Finding the Actual Bottleneck).
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALParse, resolve, rewrite by rule, choose by cost, then produce physical operators is the structure of every serious query engine, from an embedded single-node one to a distributed warehouse. The rewrites themselves — pushdown, pruning, folding, reordering — are the same ideas everywhere.
- ENGINE-SPECIFICWhich rewrites exist, which statistics are collected, when they are refreshed, and whether the engine re-plans at runtime all differ by engine and version. A tuning technique learned on one engine frequently does nothing on the next, while the reasoning about visibility transfers completely.
- SIMPLIFIEDPresenting optimisation as an ordered pipeline of phases leaves out cascading rule application, cost models that consider physical properties like sort order and partitioning, and adaptive re-planning that folds runtime measurements back into later phases. The phase model is enough to reason about what the optimiser can and cannot see.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns treating an engine upgrade as a change that needs a rollout plan, because a plan flip is a production behaviour change delivered without a code diff.