RE: Big performance slowdown from 11.2 to 13.3

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

On Wed, Jul 21, 2021 at 4:19 PM ldh(at)laurent-hasson(dot)com <ldh(at)laurent-hasson(dot)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 to do 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 2x or 4x more memory for executor nodes that are based on hashing (hash join and hash aggregate), without also affecting other kinds 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 is more 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 exact same 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 would be my first guess, but absent that, looks like a very different behavior across those 2 versions?

Thank you,
Laurent.