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 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
