public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ants Aasma <[email protected]>
To: Andres Freund <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Hash aggregate collisions cause excessive spilling
Date: Thu, 19 Feb 2026 19:06:04 +0200
Message-ID: <CANwKhkNQjUwSR_2KPE0my1cYiMyFij_50Bmfdy0eWsy-5_p5WQ@mail.gmail.com> (raw)
In-Reply-To: <vhafwgkrzqlc5cm7vuz6df5ohmf4rjfgrfnd6stqag5i7edi46@wcdtbcib4nnd>
References: <CANwKhkPOZupu3PYQVdkMmYjquYVqG2v8XmCAuuVM9Eu13-Zw3g@mail.gmail.com>
<[email protected]>
<CANwKhkM853zqt4xkpG6UvS0CTJ1_nqL-PDju+QmF_-Dv5TjFtw@mail.gmail.com>
<vhafwgkrzqlc5cm7vuz6df5ohmf4rjfgrfnd6stqag5i7edi46@wcdtbcib4nnd>
On Thu, 19 Feb 2026 at 18:32, Andres Freund <[email protected]> wrote:
> > Interestingly the plan doesn't have partial and final on those hash agg nodes:
> > ...
> > There are timescale tables involved in the plan, so I think timescale
> > might be behind that.
>
> Hm, so timescale creates a plan that we would not?
No, after poking around in the query, it's actually just that the
aggregate is for implementing DISTINCT, which means there is no
aggregate state.
> > I'm wondering if some way to decorrelate the hashtables would help.
> > For example a hashtable specific (pseudo)random salt.
>
> We do try to add a hash-IV that's different for each worker:
>
> /*
> * If parallelism is in use, even if the leader backend is performing the
> * scan itself, we don't want to create the hashtable exactly the same way
> * in all workers. As hashtables are iterated over in keyspace-order,
> * doing so in all processes in the same way is likely to lead to
> * "unbalanced" hashtables when the table size initially is
> * underestimated.
> */
> if (use_variable_hash_iv)
> hash_iv = murmurhash32(ParallelWorkerNumber);
>
>
> I don't remember enough of how the parallel aggregate stuff works. Perhaps the
> issue is that the leader is also building a hashtable and it's being inserted
> into the post-gather hashtable, using the same IV?
>
> In which case parallel_leader_participation=off should make a difference.
After turning leader participation off the problem no longer
reproduced even after 10 iterations, turning it back on it reproduced
on the 4th iteration. Is there any reason why the hash table couldn't
have an unconditional iv that includes the plan node?
Regards,
Ants Aasma
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Hash aggregate collisions cause excessive spilling
In-Reply-To: <CANwKhkNQjUwSR_2KPE0my1cYiMyFij_50Bmfdy0eWsy-5_p5WQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox