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

"ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)com> writes:
> My apologies... I thought this is what I had attached in my original email from PGADMIN. In any case, I reran from the command line and here are the two plans.

So the pain seems to be coming in with the upper hash aggregation, which is spilling to disk because work_mem of '384MB' is nowhere near enough.
The v11 explain doesn't show any batching there, which makes me suspect that it was using a larger value of work_mem. (There could also be some edge effect that is making v13 use a bit more memory for the same number of tuples, which could lead it to spill when v11 had managed to scrape by without doing so.)

So the first thing I'd try is seeing if setting work_mem to 1GB or so improves matters.

The other thing that's notable is that v13 has collapsed out the CTE that used to sit between the two levels of hashagg. Now I don't know of any reason that that wouldn't be a strict improvement, but if the work_mem theory doesn't pan out then that's something that'd deserve a closer look. Does marking the WITH as WITH MATERIALIZED change anything about v13's performance?

regards, tom lane

Hello Tom (and Peter)! Thanks for all this info.

I created 3 versions of this query: CTE MATERIALIZED, CTE NOT MATERIALIZED, and no CTE (select directly in a sub join). Only very minor change in the final execution time (seconds).

I'll try the following later this evening:
- set work_mem to 1GB
- play with hash_mem_multiplier as per Peter's suggestions although he did suggest to try being more aggressive with it and lower work_mem... so I'll play with those 2 variables.

Thank you,
Laurent.