Received-SPF: Pass (protection.outlook.com: domain of cern.ch designates
 51.103.219.121 as permitted sender) receiver=protection.outlook.com;
 client-ip=51.103.219.121; helo=mx1.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=UkKZhx9r
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:49:57 +0200
From: =?iso-8859-1?Q?Adrian_M=F6nnich?= <adrian.moennich@cern.ch>
Message-ID: <94712944.20260402164957@cern.ch>
To: Andres Freund <andres@anarazel.de>
CC: pgsql-bugs@lists.postgresql.org, Tomas Vondra <tv@fuzzy.cz>, 
 Thomas Munro <thomas.munro@gmail.com>
Subject: Re: BUG #19449: Massive performance degradation for complex query on Postgres 
  16+ (few seconds -> multiple hours)
In-Reply-To: <jivwllcuyvd7m4ceydwwpjptmadfe3cfbw47hqnej7yjfkleej@2q33rbrfybm4>
References: <19449-4fac687c06cc7def@postgresql.org> 
  <dihw6lynx3p75sv5fbgqjlsu3kfeagcnm4px2r7mgsvf4w2sf5@53udqm4e5wid>
  <43225458.20260402160627@cern.ch>
  <jivwllcuyvd7m4ceydwwpjptmadfe3cfbw47hqnej7yjfkleej@2q33rbrfybm4>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-Microsoft-Antispam-Message-Info-Original:
 WYFCR358Y6+pjo8T2SPN1lZXjl1Ayy25V4k7dXCGRyVEGXQvfzy7+kC5rFd+p7fNBydeZ3XtwdLIiZ2alqp4WiwgbKIml4iav4SAd76G46lH6XeipCjPb1qjOBbsxikl7G+ov7RQTjzYmUn5t7G1NHIQP88X5V7oSqMI3Mrj+M+9MWfvr8lQvQELwz1BGdUXIXEbSpJI+eCF+6x/gKeIufFGBC/v6pQ+rZGVi5sdZq0URjMGszOI73lv8DJ7PwFghLIlCCYCxHtJRGWjLACuuAiglLNOwvHyW6Z+Q3Atf4WoZfsqWdrHQLC2XJXXekEUjEZRWn39NF/s4Mhe/dQGmGLswSeZ7muuy9xcaw3AZ3oMpGV602jbQtPBr7CjSiO0YjCw2MTJVlfF4dRIp5Xe7n1O3LQnaG3+jTvikXSgxerfZkk+S9NzKDjDf90Alzxk4aWW187RfHKhgLwNYod9kuVSLz2V2dqAXsj7BlE2nxp/lHWNadMEOjgpTZsFLUxOs25dbQkQxrRBjk6Z4F/8oxbGrK49FsZ3didJUWwHNXcE7IxKKjKyoDTOKTQdj62qGqAxwb3Hcucw7/WT3f/ewt/LSP/fkDym/mZjnXCVccKJWvXkltMS0zVLZA8dWCGUwBik6YGomp8AAnTl0Pu82sFyxK+/hzksMyUWAuYT8tor7naTBw4bIB3c+q6f9cvL
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)(1800799024)(19092799006)(786006)(376014)(13003099007)(56012099003)(22082099003)(18002099003);DIR:OUT;SFP:1102;
X-Exchange-RoutingPolicyChecked:
 jEyw5XvML/SUrxFZFHUVjoSaRcQNa1jywaXZKck+E5a07ORDVymB9xqomS26272ncYWKdLYsmgp/p6Q1PKMzVzdLu5UUHoYfw3NPXaof53y962BkpkcItqCvQBtp8HBOGOtv+FbEIxAcjt8URVcQ3oGbCPMdCZqnzOwT/WzoPgf59FU7Zckgt1Bj0iuDQ9oUmVmDWnBK8xqRztm5VYRCNsM3bZP7OG417xRTbiqlvid8MDb3mFC5ggMo8zLHR7IiCZnmK506C8E0oz0OhtHIw0zHvlvsggiy9ZpLe8WdAVLyg7IvntwQSLOZl4S+XmmU6hX5zEACuXwRhJYd0B1tYg==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: ZR4P278MB2080
X-EOPAttributedMessage: 0
X-MS-Exchange-Transport-CrossTenantHeadersStripped:
 AM3PEPF0000A795.eurprd04.prod.outlook.com
X-MS-PublicTrafficType: Email
X-MS-Office365-Filtering-Correlation-Id-Prvs:
	c92c669c-7e12-47c8-c537-08de90c71ce0
X-Microsoft-Antispam:
	BCL:0;ARA:13230040|1800799024|19092799006|14060799003|786006|35042699022|36860700016|82310400026|376014|13003099007|56012099003|22082099003|18002099003;
X-Microsoft-Antispam-Message-Info:
	ntbzHQMLDHmeDKKP6e8IKNGfxblUQVf7I0C8NMf+ZJGUoq0rQ+7/6sd6DEjB1dKE+pn3WsfpFOC/4Dxk+b3PZnbV2ZY7Qti039kMKv32+U9WynKpf896mUXIW4nGjjrPothhEzjzBWjqspuaKCLxDGTV8RQXrKkWAmz1HJ5R8gicA1aB870bLDmkksZDsNvmzxiztLEEAtpIulMyyFtBtKe/eiS4BCCGfmXIL4cFekxWrWTg3e4yzsMo44tdRw/7cDZeUCx+44tSXiTPj9VsTieDGLhggdwzwP58OBWX4SaV2ubMaE6nS6bfXTWq2bTzKwTgCAlX3yvOSxjEqRUoqUs21FFZkFGj6eev4ofRI6xEAxRdsKJRsZB8LKJa/bcgjeMm2CW2REYCF6Hb+N+GakPnLKZ36U9jpQmADkukQCuzdvme2D4SERdLq+8rp4gpjsrFmjVjDkLbtpgxaEi9eyi6/jbg0E+HHhA4izDc6HyVvnH9InK8mNLfYwn9PuvU+3moNRWPikS1YqPhCchCgorxtzj8HhuAFT2od9HWcn9eIjJLef3ki9MMoKPrI4gotRsvngYLv8yVIQfMjy7tdmdFvjfv2szCF3rw2kb+MiF4hhr/UUUP7yvRvyzG3N95q1P9F9vzIYzH/g2LPIUTDVXx646mG84OA3RcMmX5LhrGfiE9kR9OahDLLyOK7jG3O9hNXu6JIYJN0UuwVM3lYXZ8M1yQ+60Neuz2U1VD2Zs=
X-Forefront-Antispam-Report:
	CIP:51.103.219.121;CTRY:CH;LANG:en;SCL:1;SRV:;IPV:CAL;SFV:NSPM;H:mx1.crn.activeguard.cloud;PTR:InfoDomainNonexistent;CAT:NONE;SFS:(13230040)(1800799024)(19092799006)(14060799003)(786006)(35042699022)(36860700016)(82310400026)(376014)(13003099007)(56012099003)(22082099003)(18002099003);DIR:OUT;SFP:1102;
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	9nLvmL/8g/gRw5bLgRKogf2xUj+7jKZa82en6dKDQ4wkd91bZ6UHuhzEOaRTKOScP/yj2JbeBojn7+gqOlidmthLLz4ctneOyN+Sj9Eogef3WtW1mesdC9n6YLzM6oQbtXpyyzYdM2oQIYGGyaT0OP2nE4J7Ly8QPqsCF/BsRKMfbkYSYwg5UHY6r3hkcJfeblbCRE5aWF5JTG/Mgmr6PD2WVTnxg4D40wU6WRTl144wMmID16zLjdzDVhitmghBTjIBRE3cMB1fkQXrP0uDoeIi0rHClTFvqfuqoBXWHQ/glUi/s43pGXoXzGrh+F0eTEjTdt1J+eKXxq9qNB3f55j0Phkt78fdVBxTbtb1E/zPkPOi1Kt79Wwpr6PtP9EajBcD58AcslBn2pH4c0A4uwg+uG91oFGacMgcE543WLlX+x7fXIB2Ji9J8YEFHScz
X-OriginatorOrg: cern.ch
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 02 Apr 2026 14:50:02.1449
 (UTC)
X-MS-Exchange-CrossTenant-Network-Message-Id: da471490-461d-4c58-7f2d-08de90c71ec4
X-MS-Exchange-CrossTenant-Id: c80d3499-4a40-4a8c-986e-abce017d6b19
X-MS-Exchange-CrossTenant-OriginalAttributedTenantConnectingIp: TenantId=c80d3499-4a40-4a8c-986e-abce017d6b19;Ip=[51.103.219.121];Helo=[mx1.crn.activeguard.cloud]
X-MS-Exchange-CrossTenant-AuthSource:
	AM3PEPF0000A795.eurprd04.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Anonymous
X-MS-Exchange-CrossTenant-FromEntityHeader: HybridOnPrem
X-MS-Exchange-Transport-CrossTenantHeadersStamped: ZRZP278MB1937
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/94712944.20260402164957%40cern.ch>
Precedence: bulk

Indeed, good catch. I was generating the test data from an older prod data =
copy
and not a more recent one. In any case, the performance was fine on that sa=
me
copy on 14/15 and got bad on 16.

I just re-ran it with a larger database (and also replaced the gzipped SQL =
file
from my initial message with the latest one).

PG14: https://explain.depesz.com/s/ysdJ
PG16, 4M: massive cpu + disk usage and thus aborted after a few seconds
PG16, 32M: https://explain.depesz.com/s/mYiY

Cheers,
Adrian

> Hi,

> On 2026-04-02 16:06:27 +0200, Adrian M=F6nnich wrote:
>> thanks a lot, I just tried with work_mem set to 128MB on PG16 and it wor=
ked fine:
>> https://explain.depesz.com/s/7Zan
>>=20
>> Likewise on PG18:
>> https://explain.depesz.com/s/H15B
>>=20
>> And with enable_memoize=3D0 (PG18, 128MB):
>> https://explain.depesz.com/s/SaVI

> That's good.


>> 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 result=
s to the
>> wrong estimate?

> I don't even know if it's a misestimate that didn't happen in the earlier
> versions - the join order is different in 14 than it's in the later ones.=
  I
> don't know why that is at this point.

> This means that we don't know if 14 would have had the same misestimation=
 if
> the same join order had been chosen.


> There also seem to be some data differences:

> 14: https://explain.depesz.com/s/17Fp#source
>   ->  Parallel Seq Scan on contributions contributions_1=20
> (cost=3D0.00..164891.13 rows=3D2687413 width=3D5) (actual time=3D0.013..4=
54.721 rows=3D2143186 loops=3D3)

> 16: https://explain.depesz.com/s/7Zan
>   ->  Parallel Seq Scan on contributions contributions_1=20
> (cost=3D0.00..37776.28 rows=3D1643228 width=3D5) (actual time=3D0.081..78=
.499 rows=3D1314582.00 loops=3D3)

> That's a pretty substantial difference in the number of rows.


> Greetings,

> Andres Freund