Warehouse performance: the scan you did not need to run

Transactional database tuning is mostly about finding the right index for a lookup. Warehouse tuning is about not reading data. The engine is designed to scan enormous volumes quickly, so the win is never a faster scan — it is arranging for the scan to touch a tenth of the table.

Partition pruning, and the filters that silently defeat it

Partitioning divides a table into physically separate chunks, usually by date. A query filtering on the partition column reads only the relevant chunks. A query whose filter the engine cannot evaluate before reading reads all of them, which is the difference between scanning a day and scanning four years.

-- Prunes: the filter is a literal range on the partition column.
SELECT count(*) FROM events
WHERE event_date >= '2026-09-01' AND event_date < '2026-09-20';

-- Does not prune: the column is wrapped in a function, so the engine
-- cannot decide which partitions match without reading them first.
SELECT count(*) FROM events
WHERE date_trunc('month', event_date) = '2026-09-01';

-- Does not prune: a cast changes the column, defeating the same check.
SELECT count(*) FROM events
WHERE cast(event_date AS string) LIKE '2026-09%';

-- Does not prune: the filter is on a joined table, so this table is
-- fully scanned before the join can eliminate anything.
SELECT e.* FROM events e
JOIN calendar c ON e.event_date = c.date
WHERE c.is_month_end;

-- Rule: keep the partition column bare on one side of a literal
-- comparison. Transform the constant, never the column.

Clustering, for the filters partitioning cannot serve

Partitioning handles one dimension well, usually time. Clustering sorts data within each partition by additional columns, so a filter on a clustered column also skips blocks.

• Cluster by columns you filter on repeatedly, in order of how often — the first clustering column does most of the work
• High cardinality helps here, unlike partitioning: clustering on tenant identifier or customer identifier is effective
• Two or three columns, not eight. Each additional column weakens the sort available to the earlier ones
• Partition on time, cluster on identity is the pattern that fits most analytical workloads

Conversely, partitioning on a high-cardinality column is a common and expensive mistake. Partitioning by customer identifier across ten thousand customers produces ten thousand small partitions, and the per-partition overhead exceeds any benefit — the engine spends its time on metadata rather than data.

Select the columns you need, which is not a style preference

In a columnar store, each column is stored separately, so a query reads only the columns it names. SELECT * on a wide table reads every column including the large text and nested ones that nobody wanted.

Materialise what is recomputed

A dashboard aggregating four years of events on every load recomputes the same answer repeatedly for data that cannot change. That is the clearest possible case for precomputation.

Incremental models are where the savings concentrate for large event tables. Processing only the last day's partition and appending it, rather than rebuilding four years, turns a query whose cost grows forever into one whose cost is roughly constant — and is only safe if the write is idempotent, so the re-run after a failure replaces the partition rather than duplicating it.

Joins, and the two things that go wrong

• Filter before joining, not after. Reducing both sides first means less data moves between nodes, and data movement is what makes distributed joins expensive
• Watch for the accidental fan-out. Joining to a dimension that is not unique on the join key multiplies rows, and the resulting sum is silently too large
• Join on the clustered column where you have the choice, so both sides can skip blocks
• Small dimensions broadcast well — most engines will send a small table to every node rather than shuffling the large one, and will do it automatically if the statistics are current

Read the plan, or you are guessing

Every warehouse reports what a query actually did. Four numbers tell you most of what you need, and checking them takes less time than speculating about the cause.

1. 01Bytes scanned. Compare against the table size — anything close to the full table means pruning did not happen.
2. 02Partitions read. If a one-day query touched every partition, the filter is not prunable; look for a function or cast on the partition column.
3. 03Rows in versus rows out, per stage. A stage where output greatly exceeds input is a fan-out.
4. 04Time in data movement. High shuffle time means the join keys or the distribution are wrong, not that the engine is slow.

The first number resolves most investigations immediately. A query scanning 400GB to return 200 rows is not a query needing more compute; it is a query reading data it then discards, and adding capacity makes it faster and more expensive without addressing anything.

The order to try things

1. 01Check bytes scanned. Everything else is speculation until you have that number.
2. 02Fix the filters so pruning works — bare partition column, literal comparison.
3. 03Name the columns instead of selecting everything.
4. 04Add clustering on the columns you filter by most after the partition key.
5. 05Materialise what is recomputed, incrementally where the table is large.
6. 06Only then consider more compute, which is the expensive answer and the one that hides the cause.

Steps two and three cost minutes and frequently resolve the problem entirely. Reaching for a larger warehouse before checking whether a filter prunes is how a query that should cost pennies becomes a permanently expensive one that nobody revisits.