Query optimization in a warehouse is mostly about one thing: reading and moving less data. Analytical queries are bound by how many bytes they scan and how much they shuffle across nodes, so the most effective tuning reduces both - before any clever rewrite of the SQL itself. The optimizer does a lot automatically, but its choices depend on how the data is laid out and how the query is written.

Scan less: pruning and projection

The first wins come from not reading data. Partition pruning skips irrelevant partitions; predicate pushdown applies filters as early as possible, often down to the storage layer so zone maps can skip blocks; column projection reads only the columns the query references, which columnar storage makes cheap. The practical implications: filter on the partition/sort key, push selective filters before joins, and never SELECT * when you need three columns. Each of these shrinks the bytes scanned, which is usually the dominant cost.

Shuffle less: joins and aggregation

The other big cost is data movement between nodes during joins and group-bys. Join order matters - filter and reduce the large fact table before joining to dimensions, and let the optimizer broadcast small dimension tables rather than shuffling both sides. A join on the table's sort/clustering key can avoid a reshuffle entirely. Pre-aggregating with an materialized view removes repeated heavy group-bys from the hot path altogether.

Read the plan

The tool that turns guessing into engineering is EXPLAIN. The query plan shows what the engine actually does - which partitions it prunes, how it orders joins, where it shuffles, and where the row-count estimates blow up. A plan that scans far more rows than the result needs, or shuffles a billion rows for a small join, points straight at the fix: a missing filter, a bad join order, stale statistics, or data that should be partitioned or sorted differently. Optimize against the plan, not against intuition.

EXPLAIN
SELECT d.region, sum(f.amount)
FROM fact_sales f
JOIN dim_store d ON f.store_key = d.store_key
WHERE f.date_key >= 20260601      -- prunes partitions, pushes down
GROUP BY d.region;                 -- broadcast small dim, no big shuffle

Frequently asked questions

What's the biggest factor in warehouse query speed? Bytes scanned and data shuffled. Pruning partitions, pushing down filters, and projecting only needed columns reduce scanning; good join order and clustering reduce shuffling.

Why avoid SELECT *? Columnar storage reads only referenced columns, so selecting every column forces the engine to read data the query doesn't use - wasted IO on a wide table.

How do I find a slow query's problem? Run EXPLAIN and look for full scans that should prune, large shuffles for small results, and row estimates that diverge from reality (often stale statistics). Fix what the plan reveals.

Does rewriting SQL help more than layout? Usually layout wins - partitioning, sort order, and pre-aggregation cut data read and moved. Rewrites help when the query forces unnecessary scans or shuffles the optimizer can't avoid.