-----Original Message-----
From: Peter Geoghegan <pg@bowt.ie>
Sent: Wednesday, July 21, 2021 19:34
To: ldh@laurent-hasson.com
Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@postgresql.org
Subject: Re: Big performance slowdown from 11.2 to 13.3
On Wed, Jul 21, 2021 at 4:19 PM ldh@laurent-hasson.com <ldh@laurent-hasson.com> wrote:
> As you can see, the V13.3 execution is about 10x slower.
>
> It may be hard for me to create a whole copy of the database on 11.12 and check that environment by itself. I'd want
todo it on the same machine to control variables, and I don't have much extra disk space at the moment.
I imagine that this has something to do with the fact that the hash aggregate spills to disk in Postgres 13.
You might try increasing hash_mem_multiplier from its default of 1.0, to 2.0 or even 4.0. That way you'd be able to use
2xor 4x more memory for executor nodes that are based on hashing (hash join and hash aggregate), without also affecting
otherkinds of nodes, which are typically much less sensitive to memory availability. This is very similar to increasing
work_mem,except that it is better targeted.
It might even make sense to *decrease* work_mem and increase hash_mem_multiplier even further than 4.0. That approach
ismore aggressive, though, so I wouldn't use it until it actually proved necessary.
--
Peter Geoghegan
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
exactsame VM etc... Why is that behavior so different?
As soon as I can, I'll check if perhaps the hash_mem_multiplier is somehow set differently between the two setups? That
wouldbe my first guess, but absent that, looks like a very different behavior across those 2 versions?
Thank you,
Laurent.
