Query Plans and Optimization
Query plans and optimisation are the database engine’s chosen physical strategy for running SQL, and the process of improving that strategy. A plan shows table access, filters, joins, sorting, aggregation, data movement, and estimated work, so engineers can see why a query is slow instead of guessing from the SQL text.
SQL describes the result you want, not the exact work required to produce it. The same query can be executed by scanning different tables first, applying filters earlier or later, choosing different join algorithms, and moving data around a cluster. Slow analytical queries are often slow because they read too much data before narrowing it, or because a join multiplies rows unexpectedly. The plan is the bridge between declarative SQL and actual database work.
A query plan is usually read from the data access operations upward. Start with which tables or partitions are scanned, which columns are read, and which predicates are applied at the source. Then follow joins, aggregations, sorts, repartitions, and final projections. The optimiser estimates row counts and costs, then chooses a physical plan such as scanning partitions, broadcasting a small table, repartitioning large inputs, or sorting before aggregation.
Optimisation trades simplicity and generality for more deliberate query shape. Adding partition filters, selecting fewer columns, pre-aggregating inputs, or rewriting joins can make the engine do far less work, but may make SQL less compact. Estimates can also be wrong, especially with stale statistics, skewed keys, correlated predicates, or many-to-many joins. A common misunderstanding is that the optimiser always fixes bad SQL. It helps, but it cannot infer every intent.
Engineers meet query plans through tools such as EXPLAIN, visual plan viewers, warehouse job profiles, and database logs. In practice, you inspect a slow training query, dashboard query, or feature pipeline and ask where the work explodes: full scans, missing partition pruning, late filters, large shuffles, expensive sorts, or mismatched join keys. The fix is usually to reduce inputs before joins and make filters recognisable to the engine.
Common questions
- What should I look at first in a query plan?
- Start at the table scan or data read steps, not the final output. Check whether the engine is reading only the needed partitions and columns, whether filters are applied early, and whether estimated rows are plausible. If the plan begins by reading a huge amount of irrelevant data, later join or aggregation tuning will have limited effect.
- Why does applying a function to a filter column hurt performance?
- Many engines can prune partitions or use indexes only when the predicate directly matches the stored key. If a table is partitioned by a date column, filtering that date column plainly is easier to optimise than wrapping another timestamp in a function. The latter may force the engine to inspect much more data before it can evaluate the condition.
- Are joins always the cause of slow SQL?
- No. Joins are a common source of cost, especially when they require repartitioning or create many-to-many row growth, but scans, sorts, aggregations, and poor partition pruning can dominate too. The honest answer is that it depends on the plan: look for where rows, bytes, or data movement become large relative to the final result.
- What is the difference between logical SQL order and a query plan?
- Logical SQL order describes the meaning of clauses such as filtering, grouping, and projecting. A query plan describes the physical operations the engine actually chose. The optimiser may reorder joins, push filters down, combine operations, or choose different algorithms, as long as the result remains equivalent under SQL semantics.