Improve hash aggregate spilling by writing only the needed columns
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253806psql -h localhost -U postgresBuilt from patchset v1 (message #1), September 16, 2026 at 10:34 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253806_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253806_1 && git checkout t253806_1Patchset v1 (message #1) is on t253806_1
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