From: Tom Lane <tgl@sss.pgh.pa.us>
To: ldh@laurent-hasson.com <ldh@laurent-hasson.com>
Cc: Peter Geoghegan <pg@bowt.ie>
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:43:27 -0400
Message-ID: <732758.1626911007@sss.pgh.pa.us> (raw)
In-Reply-To: <MN2PR15MB2560C1A890DC07F9359FB56285E39@MN2PR15MB2560.namprd15.prod.outlook.com>
References: <MN2PR15MB25601E80A9B6D1BA6F592B1985E39@MN2PR15MB2560.namprd15.prod.outlook.com>
<20210721191528.GC19620@telsasoft.com>
<MN2PR15MB256016E33F00DE60B96F02D385E39@MN2PR15MB2560.namprd15.prod.outlook.com>
<CAH2-Wzm3rSAWHyENb3T0X8M_qdYjF1t7rntkMJ2kDiuhVOHx3g@mail.gmail.com>
<MN2PR15MB2560C1A890DC07F9359FB56285E39@MN2PR15MB2560.namprd15.prod.outlook.com>
"ldh@laurent-hasson.com" <ldh@laurent-hasson.com> writes:
> From: Peter Geoghegan <pg@bowt.ie>
>> I imagine that this has something to do with the fact that the hash aggregate spills to disk in Postgres 13.
> So how is this happening? I mean, it's the exact same query, looks like the same plan to me, it's the same data on the exact same VM etc... Why is that behavior so different?
What Peter's pointing out is that v11 never spilled hashagg hash tables to
disk period, no matter how big they got (possibly leading to out-of-memory
situations or swapping, but evidently you have enough RAM to have avoided
that sort of trouble). I'd momentarily forgotten that, but I think he's
dead on about that explaining the difference. As he says, messing with
hash_mem_multiplier would be a more targeted fix than increasing work_mem
across the board.
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, pg@bowt.ie, pryzby@telsasoft.com
Subject: Re: Big performance slowdown from 11.2 to 13.3
In-Reply-To: <732758.1626911007@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