RE: Big performance slowdown from 11.2 to 13.3

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Wednesday, July 21, 2021 19:43
To: ldh(at)laurent-hasson(dot)com
Cc: Peter Geoghegan <pg(at)bowt(dot)ie>; Justin Pryzby <pryzby(at)telsasoft(dot)com>; pgsql-performance(at)postgresql(dot)org
Subject: Re: Big performance slowdown from 11.2 to 13.3

"ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)com> writes:
> From: Peter Geoghegan <pg(at)bowt(dot)ie>
>> I imagine that this has something to do with the fact that the hash aggregate spills to disk in Postgres 13.

> So how is this happening? I mean, it's the exact same query, looks like the same plan to me, it's the same data on the exact same VM etc... Why is that behavior so different?

What Peter's pointing out is that v11 never spilled hashagg hash tables to
disk period, no matter how big they got (possibly leading to out-of-memory
situations or swapping, but evidently you have enough RAM to have avoided
that sort of trouble). I'd momentarily forgotten that, but I think he's
dead on about that explaining the difference. As he says, messing with
hash_mem_multiplier would be a more targeted fix than increasing work_mem
across the board.

regards, tom lane

OK, got it! That sounds and smells good. Will try later tonight or tomorrow and report back.

Thank you!
Laurent.