Improve hash aggregate spilling by writing only the needed columns

Started by Mario Karuza2 months ago5 messageshackers
Jump to latest
#1Mario Karuza
mkaruza.pg@icloud.com

Hi hackers,

While exploring TPC-H and its spilling behaviour, I noticed that
spilling can be quite inefficient for queries where only a small subset
of the input columns is actually used by the aggregate.

In particular, when the input is a seq scan, it can pass the complete
table tuple to the aggregate even when we need only a few columns. When
we spill, the tuple is written using the full input tuple layout, with
the unused columns set to NULL. This means that for wide tables we can
end up writing and reading much more data than the aggregate actually
needs.

Reducing what a hash aggregate spills was discussed before, both as a
planner-side and an executor-side change [1]/messages/by-id/20200519151202.u2p2gpiawoaznsv2@development[2]/messages/by-id/20200606041146.slqfg7cuptx27tuy@alap3.anarazel.de. The result is the
current behaviour where unneeded columns are set to NULL when a tuple
is spilled.

The attached patch changes the spill format locally in the aggregate
node, so that only the columns needed by the aggregate are written to
the spill file.

A tuple descriptor for that layout and a map from spill columns back to
input columns are built once in ExecInitAgg. The write path gathers the
needed values from the input slot into the spill slot, and the read path
scatters them back into the original input tuple layout.

A tuple that is read back from a spill file and has to be spilled again
is now written out as it was read, instead of being deformed and formed
again.

hashagg_spill_tuple() is marked now as pg_noinline. Patch left the
function with a single caller, so the compiler started inlining it into
lookup_hash_entries(), which runs for every input tuple whether the
aggregate spills or not

When all columns are needed there is no behavoiur change.

TPC-H benchmark
---

TPC-H was constructed with scale factor 1, 5 and 10.

The configuration was:
- work_mem = 4MB
- max_parallel_workers_per_gather = 0.

Nothing else was changed, so the plans are the ones the planner picks by
default.

With that setting only Q18 shows significant spilling. The whole query
was run unmodified.

All benchmarks were run locally on a laptop so benchmark may contain
noise.

Measured with EXPLAIN (ANALYZE, TIMING OFF), 3 runs, median:

* execution time (ms) * | * spill disk size *
HEAD patched change | HEAD patched change
SF1 1113 1075 -3.4% | - - -
SF5 16944 12751 -24.7% | 1.06 GiB 857.4 MiB -21.0%
SF10 34231 26079 -23.8% | 2.19 GiB 1.68 GiB -23.2%

* At SF1 the query does not spill, so this shows that in-memory path is
not affected. Minimal diff -3.4% is could be explained by benchmark
noise.

Edge cases
---

The patch was tested with two edge cases to verify that there is no
regression.

Case 1 -- a two column table, one column needed:

CREATE TABLE t AS
SELECT (random() * 5e6)::int AS a,
(random() * 100)::int AS b
FROM generate_series(1, 20000000);
VACUUM ANALYZE t;

EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM t GROUP BY a;

HashAggregate
Output: a
Group Key: t.a
-> Seq Scan on public.t
Output: a, b

Case 2 -- a ten column table, nine columns needed:

CREATE TABLE m AS
SELECT (random() * 5e6)::int AS a,
i % 1000 AS c1, i % 1000 AS c2, 
i % 1000 AS c3, i % 1000 AS c4,
i % 1000 AS c5, i % 1000 AS c6, 
i % 1000 AS c7, i % 1000 AS c8,
0::int AS unused
FROM generate_series(1, 10000000) i;
VACUUM ANALYZE m;

EXPLAIN (VERBOSE, COSTS OFF)
SELECT a, sum(c1+c2+c3+c4+c5+c6+c7+c8) FROM m GROUP BY a;

HashAggregate
Output: a, sum((((((((c1 + c2) + c3) + c4) + c5) + c6) + c7) + c8))
Group Key: m.a
-> Seq Scan on public.m
Output: a, c1, c2, c3, c4, c5, c6, c7, c8, unused

Measured with EXPLAIN (ANALYZE, TIMING OFF), work_mem = 4MB,
max_parallel_workers_per_gather = 0, 3 runs, median:

HEAD (ms) PATCH (ms) CHANGE SPILL DISK SIZE
case 1 4059 3805 -6.3% 445.8 -> 445.8 MiB (0.0%)
case 2 3521 3244 -7.9% 635.3 -> 571.5 MiB (-10.1%)

Regards,
Mario

[1]: /messages/by-id/20200519151202.u2p2gpiawoaznsv2@development
/messages/by-id/20200519151202.u2p2gpiawoaznsv2@development

[2]: /messages/by-id/20200606041146.slqfg7cuptx27tuy@alap3.anarazel.de

Attachments:

t253806_1
v1-0001-Write-only-the-needed-columns-to-hash-aggregate-s.patchtext/x-patch; charset=UTF-8; name=v1-0001-Write-only-the-needed-columns-to-hash-aggregate-s.patchDownload+95-33
#2Ethan Tran
ethantran@quwin.dev
In reply to: Mario Karuza (#1)
Re: Improve hash aggregate spilling by writing only the needed columns

Hi Mario,

I reviewed CF 7310 v1
Improve hash aggregate spilling by writing only the needed columns [1]CF 7310 v1, 2026-09-17, Improve hash aggregate spilling by writing only the needed columns /messages/by-id/7247cfafe659f49340ebd5d9e31a5ced45381879.camel@icloud.com

The approach looks useful. The compact spill layout and
direct re-spilling fit the existing executor design.

Initial Run
===========

The patch applies cleanly. Automated testing used macOS arm64 and
Apple Clang 21. No compiler warnings or correctness failures were
observed.

Performance
===========

My local tests supported the spill-performance improvements.

* At SF1 the query does not spill, so this shows that in-memory path is
not affected.

I found a small startup cost in a different non-spilling case:
repeated aggregation over an empty, wide table. Across 10,000
executions, this measured 509 ms on master versus 535 ms with v1.

The attached follow-up initializes the compact layout on the first
actual spill. It allocates in es_query_cxt to survive resets and
rescans. This restored the startup result to 510 ms.

Nitpicking & Conclusion
=======================

Nit: A brief comment explaining that the reconstructed input slot
borrows Datums from the compact spill slot would help document
ownership.

The patch adds no regression tests. Coverage for sparse-column
reconstruction, NULL/by-reference values, recursive spilling, mixed
grouping sets and rescans would be helpful.

The attached lazy implementation is one tested option. Please feel
free to incorporate or adapt it.

Attachments:

- CF-7310-lazy-initialization.patch
Applies on top of v1; for discussion.

Regards,
Ethan

[1]: CF 7310 v1, 2026-09-17, Improve hash aggregate spilling by writing only the needed columns /messages/by-id/7247cfafe659f49340ebd5d9e31a5ced45381879.camel@icloud.com
/messages/by-id/7247cfafe659f49340ebd5d9e31a5ced45381879.camel@icloud.com



Attachments:

CF-7310-lazy-initialization.patchapplication/octet-stream; name=CF-7310-lazy-initialization.patch; x-unix-mode=0644Download+40-23
#3Xiaoxuan Li
xiaoxuan.li.dev@gmail.com
In reply to: Ethan Tran (#2)
Re: Improve hash aggregate spilling by writing only the needed columns

Hi Mario,

Applies to edbe7ffdc8a, builds clean and passes the regression tests
on macOS/arm64 (clang, no LLVM) and Linux/aarch64 (gcc 11, LLVM 15),
both with cassert.

There is one problem, found only on the LLVM build:

JIT crash with grouping sets
============================

The new hash_spill_colmap field is inserted before all_pergroups, but
FIELDNO_AGGSTATE_ALL_PERGROUPS is still 54, so JIT-compiled transition
code loads hash_pergroup instead. That happens to be the same pointer
for AGG_HASHED, but not for AGG_MIXED, and once a spill triggers the
nullcheck recompile the generated code indexes the wrong array.

CREATE TABLE w AS
SELECT i, i % 1000 AS g,
CASE WHEN i % 7 = 0 THEN NULL ELSE 'v' || (i % 97) END AS txt,
(i % 13)::numeric / 3 AS num
FROM generate_series(1, 200000) i;
ANALYZE w;

SET work_mem = '64kB';
SET hash_mem_multiplier = 1;
SET enable_sort = off;
SET jit_above_cost = 0;
SELECT g, txt, count(*), sum(num)
FROM w GROUP BY GROUPING SETS ((g), (txt));

server closed the connection unexpectedly

#0 evalexpr_0_10 ()
#1 ExecEvalExprNoReturn (executor.h:431)
#2 advance_aggregates (nodeAgg.c:825)
#3 agg_retrieve_direct (nodeAgg.c:2558)

Master with the same settings is fine, so is jit=off. count(*) alone
does not crash; any aggregate with a transition argument does. The
regression tests do not reach it because nothing there lowers
jit_above_cost.

Bumping the define to 55 fixes it here. See src/backend/jit/README,
"Type Synchronization", for the rule.

Other than that
===============

With the define fixed, forced-spill hash aggregation matches
enable_hashagg=off on ~30 query shapes without JIT and on 16 of
them with JIT (sparse columns, NULLs, by-ref and toasted values,
FILTER/HAVING-only columns, grouping sets, rescans, multi-level
respill).

One minor thing: hashagg_spill_tuple() and hashagg_spill_minimal_tuple()
both return total_written and both callers discard it; Disk Usage
comes from LogicalTapeSetBlocks(). Make them void?

I'll set the CF entry to Waiting on Author.

--
Xiaoxuan Li
Amazon Web Services

#4Mario Karuza
mkaruza.pg@icloud.com
In reply to: Xiaoxuan Li (#3)
Re: Improve hash aggregate spilling by writing only the needed columns

Hi Ethan, Xiaoxuan,

Thanks for the reviews.

Attached is v2, rebased on current master (4a934833fb0):

1. Fixed the JIT crash reported by Xiaoxuan.

FIELDNO_AGGSTATE_ALL_PERGROUPS is bumped to 55. I kept
hash_spill_colmap next to the other spill fields rather
than moving it after all_pergroups.

2. Used Ethan lazy initialization.

The compact spill slot and column map are now built on the first
spill, so executions that never spill don't pay any cost.
Thanks for providing patch.

Make them void?

hashagg_spill_tuple() already returns Size on master, and both
callers discard it there too, so I left it unchanged to keep
patch with minimal changes.

Attachments:

t253806_4
v2-0001-Write-only-the-needed-columns-to-hash-aggregate-s.patchtext/plain; charset=UTF-8; name=v2-0001-Write-only-the-needed-columns-to-hash-aggregate-s.patchDownload+113-34
#5Alain LESAGE
alain.lesage@dalibo.com
In reply to: Mario Karuza (#4)
Re: Improve hash aggregate spilling by writing only the needed columns

Hi Mario,

Thanks for working on this, it is a welcome improvement for analytics
workloads.

On Fri, Nov 6, 2026 at 2:41 PM Mario Karuza <mkaruza.pg@icloud.com> wrote:

Attached is v2, rebased on current master (4a934833fb0):

I tested v2 on top of 4a934833fb0 (Linux x86_64, gcc 14, LLVM 19).

1. Fixed the JIT crash reported by Xiaoxuan.

The crash reproduces with v1 and is fixed in v2. The regression tests pass, also
with JIT forced for every query, and the cases Ethan listed (NULLs, by-ref
types, recursive respill, rescans, grouping sets) return the same results as a
reference run, with and without JIT.

2. Used Ethan lazy initialization.

I see no measurable overhead when nothing spills, neither on an empty table nor
with a work_mem large enough to avoid spilling.

I reproduced your TPC-H Q18 and edge-case numbers, then ran ClickBench [1]https://github.com/ClickHouse/ClickBench
(official 105-column hits table, first 5M rows, the 43 official queries) with
the default configuration (work_mem = 4MB, no parallel workers), 6 runs per
query, median:

master v2
q13 GROUP BY SearchPhrase 1964 ms 1318 ms -32.9%
q16 GROUP BY UserID 4712 ms 1716 ms -63.6%
q17 GROUP BY UserID, SearchPhrase 5551 ms 2317 ms -58.3%
q31 GROUP BY SearchEngineID, ClientIP 1838 ms 1296 ms -29.5%
q33 GROUP BY WatchID, ClientIP 6228 ms 3651 ms -41.4%
q34 GROUP BY URL 8764 ms 3104 ms -64.6%

Geometric mean: -38% on the 14 queries whose HashAggregate spills, -1.7% (within
noise) on the 29 others, -15.6% overall.

The 4 spilling queries that group on expressions (q19, q29, q36, q40) do not
change: the scan already projects, so there is nothing to remove. That might be
worth a sentence in the commit message.

[1]: https://github.com/ClickHouse/ClickBench

Regards,
Alain