From: Tom Lane <tgl@sss.pgh.pa.us>
To: "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
cc: David Rowley <dgrowleyml@gmail.com>, Peter Geoghegan <pg@bowt.ie>,
        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: <784576.1626968702@sss.pgh.pa.us>
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>
 <732758.1626911007@sss.pgh.pa.us>
 <MN2PR15MB2560778595BB7AF6E691B26785E39@MN2PR15MB2560.namprd15.prod.outlook.com>
 <MN2PR15MB2560D429866BD65A70574A6C85E49@MN2PR15MB2560.namprd15.prod.outlook.com>
 <CAApHDvqoF7y7SQjGT=940njnAd_CUcUG6oRYChcP_+eJyuNiug@mail.gmail.com>
 <MN2PR15MB25603D3CE06C8F9C312EA58985E49@MN2PR15MB2560.namprd15.prod.outlook.com>
 <MN2PR15MB2560E4574A6E2B5AED2C03CB85E49@MN2PR15MB2560.namprd15.prod.outlook.com>
 <784576.1626968702@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 22 Jul 2021 11:45:02 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <785217.1626969409.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 22 Jul 2021 11:56:49 -0400
Message-ID: <785218.1626969409@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/785218.1626969409%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> "ldh@laurent-hasson.com" <ldh@laurent-hasson.com> writes:
>> It's still spilling to disk and seems to cap at 2.5GB of memory usage i=
n spite of configuration.

> That is ... weird.

Oh: see get_hash_mem:

	hash_mem =3D (double) work_mem * hash_mem_multiplier;

	/*
	 * guc.c enforces a MAX_KILOBYTES limitation on work_mem in order to
	 * support the assumption that raw derived byte values can be stored in
	 * 'long' variables.  The returned hash_mem value must also meet this
	 * assumption.
	 *
	 * We clamp the final value rather than throw an error because it should
	 * be possible to set work_mem and hash_mem_multiplier independently.
	 */
	if (hash_mem < MAX_KILOBYTES)
		return (int) hash_mem;

	return MAX_KILOBYTES;

So basically, we now have a hard restriction that hashaggs can't use
more than INT_MAX kilobytes, or approximately 2.5GB, and this use case
is getting eaten alive by that restriction.  Seems like we need to
do something about that.

			regards, tom lane