Re: Hash aggregate collisions cause excessive spilling

From: Andres Freund <andres(at)anarazel(dot)de>
To: 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>, Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Hash aggregate collisions cause excessive spilling
Date: 2026-11-19 20:01:34
Message-ID: gryjtm5dwgywtee4qqq7hloaw3bexjngloh2q5i2uld4momsvn@o6afndrhbr67
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

On 2026-11-19 12:43:58 -0500, Andres Freund wrote:
> On 2026-03-01 19:19:38 +0200, Ants Aasma wrote:
> > On Sat, 28 Feb 2026 at 22:03, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > > I think right now that limits it to hash aggregates with parallel
> > > > query. ExecBuildHash32FromAttrs comment says that we should be using
> > > > non-zero init_value only when needed for a marginal performance gain. So I
> > > > limited the plan node id inclusion to use_variable_hash_iv, but made it
> > > > unconditional for aggregates.
> > >
> > > That's an argument... I guess we could mostly avoid that though, by just
> > > always combining in EEOP_HASHDATUM_FIRST.
> >
> > I think that makes it exactly the same as EEOP_HASHDATUM_NEXT32,
> > making the special op code pointless. I assume David observed some
> > speedup there to go through the effort to include it.
>
>
> > > > diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
> > > > index 7d487a165fa..5c947fd151f 100644
> > > > --- a/src/backend/executor/nodeAgg.c
> > > > +++ b/src/backend/executor/nodeAgg.c
> > > > @@ -1535,7 +1535,7 @@ build_hash_table(AggState *aggstate, int setno, double nbuckets)
> > > > metacxt,
> > > > tuplescxt,
> > > > tmpcxt,
> > > > - DO_AGGSPLIT_SKIPFINAL(aggstate->aggsplit));
> > > > + true);
> > > > }
> > >
> > > Hm, won't that mean we'll now always use the slower path? I think there are
> > > non-partial cases that could be problematic, but if we do this we probaly
> > > should do the optimization of the hash expression stuff I mentioned above.
> >
> > If you mean EEOP_HASHDATUM_FIRST, then I think that would just be
> > removing an optimization. I think getting rid of the quicker growth
> > and correlated table filling in the 1:1 case might be more significant
> > than the slightly slower hash calculation. However the downside of the
> > bad case is pretty nasty. Do you think it's worth the effort of trying
> > to figure out if we are in a dangerous looking structure?
>
> David, any opinion?

One way around potentially regressing EEOP_HASHDATUM_FIRST would be to move
the IV handling into TupleHashTableHash_internal(). Of course at the price of
having to store the IV into TupleHashTableData, but I think we can do that
safely even in the backbranches.

Thinking about that made me wonder about this comment:

/*
* The hashing done above, even with an initial value, doesn't tend to
* result in good hash perturbation. Running the value produced above
* through murmurhash32 leads to near perfect hash perturbation.
*/
return murmurhash32(hashkey);

I wonder if it's more than high time to instead make EEOP_HASHDATUM_NEXT32
combine hashes in a smarter way (including the IV)?

pg_rotate_left(hash1, 1) ^ hash2

to me seems extremely bad. If you e.g. hash two columns with the same value,
and the hash value
a) does not have the highest bit set
b) has a few zero low bits

the range of the resulting hash value used for buckets etc will not
change. For execGrouping.c that's not *that* bad, because of the above final
murmurhash32() perturbing everything. But e.g. hashjoins don't have that.

hash_combine() is pretty terrible too, if used repeatedly. Apparently boost
has since fixed their implementation, but of course we haven't picked that up.

Perhaps we should use the fasthash_* stuff from hashfn_unstable.h?

Greetings,

Andres Freund

In response to

Browse pgsql-hackers by date

  From Date Subject
Previous Message Daniel Gustafsson 2026-11-19 19:47:51 Re: Assert "target_wal_endrec == divergerec" in pg_rewind