| 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
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | Daniel Gustafsson | 2026-11-19 19:47:51 | Re: Assert "target_wal_endrec == divergerec" in pg_rewind |