Re: Hash aggregate collisions cause excessive spilling

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Andres Freund <andres(at)anarazel(dot)de>, Ants Aasma <ants(dot)aasma(at)cybertec(dot)at>, David Rowley <dgrowleyml(at)gmail(dot)com>, Tomas Vondra <tomas(at)vondra(dot)me>, Richard Guo <guofenglinux(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Hash aggregate collisions cause excessive spilling
Date: 2026-11-20 00:47:26
Message-ID: a5c3f0e5df3fcb2e735dde4508e3461be3db61ae.camel@j-davis.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, 2026-11-19 at 12:43 -0500, Andres Freund wrote:
> There is already the logic to have at least one group, I wonder if
> that ought
> to be changed to some fillfactor based logic.  Resetting the table's
> size ends
> up performing very poorly, so I don't think that's a great option
> either...

When possible, we should avoid getting in that situation.

One major cause of crowding is that adding a single tuple can cause the
hash table to double in size, which then causes spilling. Doubling the
hash table right before spilling wastes the effort to grow the hash
table, and it also crowds out space for tuples in every future batch.
As discussed at the top of the thread, this can happen at 20% in
practical cases, and the hash improvements will hopefully help.

But that's not enough: even if the table is 90% full, why double it
right before spilling? It still wastes effort, and still crowds out
future batches. We need some way to decide that the hash table is big
enough, and to allow new groups to be inserted only if they don't cause
the table to grow. IIUC this is what Ants is suggesting in the first
email.

Another cause of crowding is grouping sets. The initial run builds hash
tables for all grouping sets and they never get destroyed. Each
subsequent batch only represents a single grouping set, but all the
other grouping sets' hash tables are crowding the memory for the
current batch. We can solve this by choosing one grouping set to
process first (e.g. the one with the largest hash table), keeping that
set's hash table around and destroying the rest. Then process the
remaining batches by grouping set, and when all batches from a set are
finished, destroy its hash table.

My hope is that if we solve those problems, then we only need to
destroy and recreate the hash table in extreme cases.

Regards,
Jeff Davis

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro Horiguchi 2026-11-20 00:48:16 Re: A latent pointer increment bug in pgtypes_defmt_scan()
Previous Message Peter Geoghegan 2026-11-20 00:46:01 Parallel GIN, BRIN builds fail to maintain ii_BrokenHotChain