Received-SPF: Pass (protection.outlook.com: domain of cern.ch designates
 51.107.2.244 as permitted sender) receiver=protection.outlook.com;
 client-ip=51.107.2.244; helo=mx2.crn.activeguard.cloud; pr=C
Authentication-Results-Original: auth.opendkim.xorlab.com;	dkim=pass (1024-bit
 key; unprotected) header.d=cern.ch header.i=@cern.ch header.a=rsa-sha256
 header.s=selector1 header.b=UhyHwZ7g
Authentication-Results-Original: dkim=none (message not signed)
 header.d=none;dmarc=none action=none header.from=cern.ch;
Date: Thu, 2 Apr 2026 16:06:27 +0200
From: =?windows-1250?Q?Adrian_M=F6nnich?= <adrian.moennich@cern.ch>
Message-ID: <43225458.20260402160627@cern.ch>
To: Andres Freund <andres@anarazel.de>, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19449: Massive performance degradation for complex query on Postgres 
  16+ (few seconds -> multiple hours)
In-Reply-To: <dihw6lynx3p75sv5fbgqjlsu3kfeagcnm4px2r7mgsvf4w2sf5@53udqm4e5wid>
References: <19449-4fac687c06cc7def@postgresql.org> 
  <dihw6lynx3p75sv5fbgqjlsu3kfeagcnm4px2r7mgsvf4w2sf5@53udqm4e5wid>
Content-Type: text/plain; charset=windows-1250
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-Microsoft-Antispam-Message-Info-Original:
 GL7j9JJSPzgmXpqsu0H/539I6S6oQeEZrO7CjaD1T/4xHHJ0Oe19mCU7GMJWyUiIEWsvWw+KYfTZE0KKIRBKhph40MUljGXiEPJtygB0YDq3BH5n0E8NR7zHoZ7khpmwyJLOlS+fmlAdAoJkLTG9M0Sb+mAwJjn1tE1avkaNl6o7HH2DJuWfhNpgg+QjD/IZJR/lHbLFquSBu9Xt061bRmGotYnUi/dhrnQhoAD3E53UjOaGlq1gsGIkk/WVzuf+7UlMr3rti6fccLUw8JNz51S3a3y3RszQ4MGfv/QN3wdhSfS8m5+o7CLoTFbtpKeGVmVEtIm5tyh7D3sKX3YACYq228B0jbHeI3RF08fKsDXwUJv/UQyHw97gaJcai8I9c5+FzRkNWuXTL3siaVYs2D5oSE2jKJGNeHcSJ7KJ5yGEQZWMEBMlY4PffisSsmMlAx/0CCEeFEWjZuRxF5B3sb+8rBY+dcIA77P5/GKWriyUf5l581K4KZbwafvzliEOD+H/KtDDklmKmUXO+8s0D1LPFevQDpkoJPpB0+14Eb5Fp5Sik/OGX4K6opM08h2K60o5t7hnOjQjWv/z8ic1va0LYctZhVvz5LCHS/CF8QRjU9bP2u4xZN5R+ZJiaH9hgA7UUHspz1GSgIl6drdEJA9SEpmUXzkRkfxi3qOIg4RpfRXdqfQYEHMJLlaXwZWr
X-Forefront-Antispam-Report-Untrusted:
 CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:GV0P278MB1846.CHEP278.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(366016)(376014)(1800799024)(786006)(19092799006)(22082099003)(18002099003)(56012099003);DIR:OUT;SFP:1102;
X-Exchange-RoutingPolicyChecked:
 sZubZZTPWZOmXaqu9ygHQtuM4he2knZGqrB31UTTCif0tcq/av31WqEw5XOYWyG0+dO80AlCg6vPhUa4b3gumoRzjYl4oAnr99/zgF626Wi5sYcQOkZqUhRz67Fqy6u/eTWA+xYxAHVguLJb3WprmDJGzQsi7GriNMnsORM2gURUuFL20AVJizR9nTVhoF2uISMHR0hE9gh8CcLioSldz0jlEL66aRzX3SdJPbjeXbPt/urrRH3my9LID+99CZN7ne0MkDTzXcALN6d2fsqWrl/WPjjY9RHE8Ss5iqb3Fa8QoFGbC7mHOwWFEUvl4+oaxCX4hr/tTCHLR5FMoZcMuQ==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: GVAP278MB0761
X-EOPAttributedMessage: 0
X-MS-Exchange-Transport-CrossTenantHeadersStripped:
 AMS1EPF00000094.eurprd05.prod.outlook.com
X-MS-PublicTrafficType: Email
X-MS-Office365-Filtering-Correlation-Id-Prvs:
	6c5249c4-8a02-4466-8f53-08de90c10996
X-Microsoft-Antispam:
	BCL:0;ARA:13230040|36860700016|1800799024|376014|82310400026|14060799003|19092799006|35042699022|786006|18002099003|13003099007|22082099003|56012099003;
X-Microsoft-Antispam-Message-Info:
	qyzgMH0r1tzO1BvWg31EAPCsAEELDtq7X2S5dgcnZl9pGVFsO9jh24LDZKc+PabXcviUd+WpfBIoHyuZT7SwhXppZmKPyqeGvz30yMJZGLkEIyTvcIz2GIhJEz6J2AqETioNwRfRF1duW3ioXFs7pfYYhU9rQoI6hNbjERTC0h0q1ajvKwW6pET9Lu9QsO9cLEXg0bBAf+VLGHZOYum3BN0+/zENoYKnaF6kFIYuUCAyPWGLEdxRi9qSfUBTnhZN/ucDwLJo5qO3XZ7Fc47AKQR19Ln3qlsdL5kQVWanuhe+/31TCoWv29dJc69+SuCrMBay71H/nAllpD8UAmUvfw3K5EyP2FHj1yL4jVfNUgMwDTYmFgCmk2uQqAGXWg8vzqv8X2reB6Sfg5FOlIbnownWGl0mNPuGxq43IrnxFASEw8sPAfGU7YtOwd7Y67Idd+H2yrT8bL1IDkcWIFdCiN6mtUFbLF/VQnlUunfmtqryQN4WYkBhH6gzsul8SA7d5jptTq7tVkfX00m8Zc8ucpvjM/oxEeRdeCe0Po0k6IhL0afeNXE3ppAR/pncezNP3PpdgVk0wwV7pXsvzN5hD8KX9CyC47IL4VnW02OCDHGo1OYbxlpmqDlAjQNs/5IifS9Xk+X5cTqRrAlWZctUPUOZ0R/KKlkZ3S/ePTlTOZD6DVCfU5xpOTetmU0Fb8wcF9M5zA16klCTZmK6OYTzLJ3OhQIFhibs+aEiPaIpD08=
X-Forefront-Antispam-Report:
	CIP:51.107.2.244;CTRY:CH;LANG:en;SCL:1;SRV:;IPV:CAL;SFV:NSPM;H:mx2.crn.activeguard.cloud;PTR:InfoDomainNonexistent;CAT:NONE;SFS:(13230040)(36860700016)(1800799024)(376014)(82310400026)(14060799003)(19092799006)(35042699022)(786006)(18002099003)(13003099007)(22082099003)(56012099003);DIR:OUT;SFP:1102;
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	eF/GsRMI4/FgDH+NQD27hF0F5faRDDw9y5GTFbitMZITGaHZcarLyE6BWVc4TDi0K5fu3qMWSf4K94ymEGF1cLUEqRBiJtJtIALxPLd7aTHOU9XqB6ANoFPU903ZCs1YKvVLBPlsKdK8y+cCrdbwnRUSO42rLDQWXZtQ+2uw3UO2EYqn9suaCWigfvfwti++dQNIKF7sVGsyfJ0i6k5l4oqgnEBvWGvWEkqZfhHs9kjGheOfjrB78jr2vSO51aiQNb7Q1ocJlJcsW6XBmhQeicnZeYceJd5aTvd98tYpNqPVyMsGeOADOagnx0cKKUJVJUN20FMEICGaPDNBGTKR1LremihPXgWZfBvr0TERu2OAlz2bKF+9jK6xBBrCtLiYmQ2nzL9fVwukKCCs9yE+M4ZYZQZrsk7ChziPlZebN2jqr0rLkSYd9bocA41sKile
X-OriginatorOrg: cern.ch
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 02 Apr 2026 14:06:32.3207
 (UTC)
X-MS-Exchange-CrossTenant-Network-Message-Id: 0083b67e-e2fc-4be8-4d5f-08de90c10b35
X-MS-Exchange-CrossTenant-Id: c80d3499-4a40-4a8c-986e-abce017d6b19
X-MS-Exchange-CrossTenant-OriginalAttributedTenantConnectingIp: TenantId=c80d3499-4a40-4a8c-986e-abce017d6b19;Ip=[51.107.2.244];Helo=[mx2.crn.activeguard.cloud]
X-MS-Exchange-CrossTenant-AuthSource:
	AMS1EPF00000094.eurprd05.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Anonymous
X-MS-Exchange-CrossTenant-FromEntityHeader: HybridOnPrem
X-MS-Exchange-Transport-CrossTenantHeadersStamped: GV0P278MB0993
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: <https://www.postgresql.org/message-id/43225458.20260402160627%40cern.ch>
Precedence: bulk

Hi,

thanks a lot, I just tried with work_mem set to 128MB on PG16 and it worked=
 fine:
https://explain.depesz.com/s/7Zan

Likewise on PG18:
https://explain.depesz.com/s/H15B

And with enable_memoize=3D0 (PG18, 128MB):
https://explain.depesz.com/s/SaVI

So increasing work_mem seems like a good workaround for when we upgrade
our production DB. But I guess there's still a but somewhere that results t=
o the
wrong estimate?

Cheers,
Adrian

> Hi,

> On 2026-04-02 13:04:46 +0000, PG Bug reporting form wrote:
>> This is extreme both in general and compared to the performance we got o=
n
>> 14/15, where the same
>> query took just a few seconds.
>>=20
>> Here are EXPLAIN ANALYZE outputs from when I tested this a few weeks ago=
 on
>> 14 and 16
>> using our real production database.
>> https://explain.depesz.com/s/17Fp
>> https://explain.depesz.com/s/0dHI

> A lot of time is wasted due to batching in the hash join in 16, seemingly=
 due
> to a mis-estimate in how much batching we would need:

>                                  ->  Parallel Hash=20
> (cost=3D323037.00..323037.00 rows=3D1075136 width=3D10) (actual
> time=3D3267572.432..3267575.016 rows=3D1023098 loops=3D3)
>                                        Buckets: 262144 (originally 262144=
)=20
> Batches: 262144 (originally 32)  Memory Usage: 18912kB
> (note the 262144 batches, when 32 were originally assumed)

> I'd suggest trying to run the query with a larger work mem.  Not because
> that should be necessary to avoid regressions, but because it will be use=
ful
> to narrow down whether that's related to the issue...

> However, even on 14, you do look to be loosing a fair bit of performance =
due
> to batching, so it might be also worth running the query on 14 with a lar=
ger
> work mem, to see what performance you get there.


> It also looks like that the choice of using memoize might not be working =
out
> entirely here. Although I don't think it's determinative for performance,=
 it
> might still be worth checking what plan you get with
>   SET enable_memoize =3D 0;

> Greetings,

> Andres Freund