What should I look at first in a query plan?

Start at the data access steps and work upward. Check which tables are scanned, whether partition pruning happened, which filters were applied early, and how much data flows into later operations. If a query reads far more data than the result needs, join tuning will not fix the main problem.

Why does applying a function to a date column hurt performance?

Partition pruning usually depends on the optimiser recognising a direct constraint on the partition column. If the query wraps a timestamp or date in a function, the engine may not infer which partitions can be skipped. Filtering directly on the partition field gives the planner a clearer path to scan less data.

How do I make a large SQL join cheaper?

Reduce both sides before the join. Apply filters early, select only required columns, pre-aggregate when the result allows it, and join on compatible key types without expressions. Also check whether a small table can be broadcast instead of repartitioning large inputs across the cluster.

SQL for ML Engineers

02.05 · Walkthrough

Query Plans and Optimization

Read a query execution plan and improve a slow query by reducing scanned data, optimizing joins, and using partition filters.

A query plan shows where the database will spend work: scans, filters, joins, sorts, shuffles and aggregations. For slow analytical SQL, the fastest wins usually come from reading fewer columns and partitions, filtering before joins, and shaping joins so large inputs are reduced before data is moved or combined.

What this lesson answers

how to read a SQL query execution plan
why is my analytical SQL query slow
how to optimise joins and partition filters

Notes

A query execution plan is the database’s explanation of how it intends to run a SQL query. For a working engineer, the most important habit is to read the plan from the data access steps upward: which tables are scanned, how many rows or bytes are expected, which filters are applied early, how joins are performed, and where expensive operations like sorts, shuffles, aggregations, or repartitions occur. In analytical systems, a slow query is often slow because it reads too much data before doing useful work.

Common questions

What should I look at first in a query plan?: Start at the data access steps and work upward. Check which tables are scanned, whether partition pruning happened, which filters were applied early, and how much data flows into later operations. If a query reads far more data than the result needs, join tuning will not fix the main problem.
Why does applying a function to a date column hurt performance?: Partition pruning usually depends on the optimiser recognising a direct constraint on the partition column. If the query wraps a timestamp or date in a function, the engine may not infer which partitions can be skipped. Filtering directly on the partition field gives the planner a clearer path to scan less data.
How do I make a large SQL join cheaper?: Reduce both sides before the join. Apply filters early, select only required columns, pre-aggregate when the result allows it, and join on compatible key types without expressions. Also check whether a small table can be broadcast instead of repartitioning large inputs across the cluster.

Short definition: what is Query Plans and Optimization?

02.04 · SQL for ML EngineersPoint-in-Time Correct Joins 02.06 · SQL for ML Engineersdbt Models for ML Transformations