MIME-Version: 1.0
References: <CANwKhkPOZupu3PYQVdkMmYjquYVqG2v8XmCAuuVM9Eu13-Zw3g@mail.gmail.com>
 <a5f46e2e-3b11-44bd-9a24-fa7d5b3948cd@vondra.me> <CANwKhkM853zqt4xkpG6UvS0CTJ1_nqL-PDju+QmF_-Dv5TjFtw@mail.gmail.com>
 <vhafwgkrzqlc5cm7vuz6df5ohmf4rjfgrfnd6stqag5i7edi46@wcdtbcib4nnd>
 <CANwKhkNQjUwSR_2KPE0my1cYiMyFij_50Bmfdy0eWsy-5_p5WQ@mail.gmail.com> <vx4azu62rgrnkt4oauviepbydxj5q7wbtzycwmqnmby2sfpvwc@xfvp3pcjnv2w>
In-Reply-To: <vx4azu62rgrnkt4oauviepbydxj5q7wbtzycwmqnmby2sfpvwc@xfvp3pcjnv2w>
From: Ants Aasma <ants.aasma@cybertec.at>
Date: Thu, 19 Feb 2026 20:07:42 +0200
Message-ID: <CANwKhkMoXaTEe8yCd0hJSXxTH6S9awF8g=ES-xYt8E0FoVpfzQ@mail.gmail.com>
Subject: Re: Hash aggregate collisions cause excessive spilling
To: Andres Freund <andres@anarazel.de>
Cc: Tomas Vondra <tomas@vondra.me>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CANwKhkMoXaTEe8yCd0hJSXxTH6S9awF8g%3DES-xYt8E0FoVpfzQ%40mail.gmail.com>
Precedence: bulk

On Thu, 19 Feb 2026 at 19:30, Andres Freund <andres@anarazel.de> wrote:
> On 2026-02-19 19:06:04 +0200, Ants Aasma wrote:
> > 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?
>
> You mean just use the numerical value of the pointer? I think that'd be pretty
> likely to be the same between parallel workers. And I think it's not great for
> benchmarking / debugging if every run ends up with a different IV.
>
> But we certainly should do something about the IV for the leader in these
> cases.

I was thinking more along the lines of hashing together the pointer
value and worker number. But something more deterministic would indeed
be better. How about this?

--- a/src/backend/executor/execGrouping.c
+++ b/src/backend/executor/execGrouping.c
@@ -201,3 +201,3 @@ BuildTupleHashTable(PlanState *parent,
        MemoryContext oldcontext;
-       uint32          hash_iv = 0;
+       uint32          hash_iv = parent->plan->plan_node_id;

I also figured out why this is not a more common issue.

The iv randomization is predicated on the aggregate node not having a
final function. Normally the partial hash aggregate will use
randomized iv and the finalize will use 0. But in this case because
it's implementing distinct, there is no finalize function on the upper
hash aggregate so on the leader the upper hash aggregate gets the same
iv as the one below gather.

Are there any other cases where a hash aggregate would be fed from
another hash aggregate using the same group key? Those could run into
the same problem.

Regards,
Ants Aasma