Multi-Aggregate Fusion: One Read, Four Answers

The pattern every SQL engine has wrong

Open any analytical SQL engine — DuckDB, ClickHouse, Spark, even Snowflake — and trace what happens when a user runs SELECT SUM(x), MIN(x), MAX(x), COUNT(x) FROM t. The query planner sees four aggregates. It builds a plan with four aggregate states. It runs the scan. For each row, it updates four states. So far so good.

Where it goes wrong is the implementation. In most engines, those four state updates aren't actually fused in the inner loop. They're run sequentially. SUM is a load-add-store. MIN is a load-cmp-store. MAX is a load-cmp-store. COUNT is an increment. Each one re-reads the value from the column, possibly hits cache, possibly stalls. On a tight inner loop processing 60M rows, that adds up to four passes of the column where there should be one.

The fix is conceptually simple: read the value once into a register, do all four state updates in registers, move on. The performance shift is large enough to be visible from orbit.

The numbers

Three observations to read out of this chart. First, the speedup ratio is highest on the column with the smallest value range. l_quantity has 50 unique integer values across 60M rows; CPU is doing 60M × 4 reads of an 8-byte column for ~2 GB of memory traffic over four passes. The fused Metal kernel does 60M × 1 reads with all four updates in registers, dropping memory traffic by 4×, and then GPU bandwidth carries the rest. Second, the ratio holds (and is still huge) on l_extendedprice, where the unique-cardinality is high. The win there is mostly memory bandwidth, not arithmetic. Third, l_orderkey at 15M unique sees a smaller but still 9.7× win — the constants change because the integer width shifts, but the fused-vs-multipass advantage remains.

Why the technique works on a GPU specifically

The same fusion idea works on a CPU — and to be fair, the most modern CPU engines (DuckDB included) do partially fuse arithmetic aggregates within a single pass when the planner sees them. What the GPU adds is two things:

1. Massive register pressure budget. A modern GPU thread has dozens of registers. Holding four aggregate states in registers is free. On a CPU, register pressure can force spills to L1, which then competes with the column read.
2. Memory bandwidth headroom. The GPU is memory-bandwidth-rich. The CPU on a 16-thread workload is memory-bandwidth-saturated long before it's compute-saturated. Reducing memory traffic by 4× (from four passes to one) directly translates into a near-4× wallclock speedup, before the GPU's higher base bandwidth even enters the picture.

The fused kernel itself is short — under 80 lines of Metal Shading Language — and the only non-obvious detail is using simd_min / simd_max intrinsics to do warp-wide reductions on the MIN/MAX states before atomically merging into threadgroup memory. That's the same trick CUDA reductions use; the names just differ.

When fusion does nothing

Three cases where the fused-kernel approach yields no benefit and shouldn't be used:

• Single-aggregate queries. If the user wrote SELECT SUM(x) FROM t, fusing is free but pointless — there's nothing to fuse. The single-aggregate kernel is fine.
• Aggregates over different columns. SELECT SUM(a), SUM(b) FROM t doesn't benefit from the fusion above because the limiting cost is reading both columns, not the aggregate updates. (You can still fuse this, but the speedup is much smaller.)
• Aggregates with WHERE clauses that drop most rows. If 99% of rows are filtered out, the fused inner loop is mostly idle and the dispatch overhead dominates. The CPU's cache-friendly path can win here.

The general rule is that fusion is a win exactly when memory bandwidth is the bottleneck. For wide-fanout aggregates over hot columnar data, that is almost always the case. For sparse queries with heavy filtering, it isn't.

Why this matters beyond gpudb

Multi-aggregate fusion is the kind of technique that the GPU-database research literature has known about for over a decade and that production engines have only recently started taking seriously. The reason is that fusing aggregates is a planner change as much as a kernel change — the planner has to recognize the multi-aggregate-same-column pattern, decide it's worth fusing, and emit a single fused operator instead of four separate ones. That requires the planner team and the execution team to coordinate, which in most engines is a slow process.

It's also one of the cleanest examples of a workload where GPUs and CPUs aren't fighting over the same primitive — they're solving different versions of the problem. CPU engines are bottlenecked on memory bandwidth and try to amortize multiple aggregates across cache reuse. GPU engines have so much memory bandwidth that the smarter move is to keep the inner loop tiny and saturate the kernel scheduler. Both are correct architectures for their substrate. The difference shows up in the speedup ratio.