From: Tom Lane <tgl@sss.pgh.pa.us>
To: "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
cc: Justin Pryzby <pryzby@telsasoft.com>,
        "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Big performance slowdown from 11.2 to 13.3
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>
Comments: In-reply-to "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
	message dated "Wed, 21 Jul 2021 23:19:04 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <732366.1626910557.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 21 Jul 2021 19:35:57 -0400
Message-ID: <732367.1626910557@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/732367.1626910557%40sss.pgh.pa.us>
Precedence: bulk

"ldh@laurent-hasson.com" <ldh@laurent-hasson.com> writes:
> My apologies... I thought this is what I had attached in my original ema=
il from PGADMIN. In any case, I reran from the command line and here are t=
he 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