-----Original Message-----
From: Tom Lane <t...@sss.pgh.pa.us>
Sent: Wednesday, July 21, 2021 19:36
To: l...@laurent-hasson.com
Cc: Justin Pryzby <pry...@telsasoft.com>; pgsql-performa...@postgresql.org
Subject: Re: Big performance slowdown from 11.2 to 13.3
"l...@laurent-hasson.com" <l...@laurent-hasson.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.
