From: Tom Lane <tgl@sss.pgh.pa.us>
To: ldh@laurent-hasson.com <ldh@laurent-hasson.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Big performance slowdown from 11.2 to 13.3
Date: Wed, 21 Jul 2021 19:35:57 -0400
Message-ID: <732367.1626910557@sss.pgh.pa.us> (raw)
In-Reply-To: <MN2PR15MB256016E33F00DE60B96F02D385E39@MN2PR15MB2560.namprd15.prod.outlook.com>
References: <MN2PR15MB25601E80A9B6D1BA6F592B1985E39@MN2PR15MB2560.namprd15.prod.outlook.com>
<20210721191528.GC19620@telsasoft.com>
<MN2PR15MB256016E33F00DE60B96F02D385E39@MN2PR15MB2560.namprd15.prod.outlook.com>
"ldh@laurent-hasson.com" <ldh@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ldh@laurent-hasson.com, pryzby@telsasoft.com
Subject: Re: Big performance slowdown from 11.2 to 13.3
In-Reply-To: <732367.1626910557@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox