Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1uBFht-008VPh-VA
	for pgsql-bugs@arkaria.postgresql.org; Sat, 03 May 2025 16:27:18 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1uBFhr-00FRm8-V9
	for pgsql-bugs@arkaria.postgresql.org; Sat, 03 May 2025 16:27:17 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1uBFhr-00FRm0-N8
	for pgsql-bugs@lists.postgresql.org; Sat, 03 May 2025 16:27:16 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by makus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1uBFhq-000saE-2b
	for pgsql-bugs@lists.postgresql.org;
	Sat, 03 May 2025 16:27:15 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 543GR6Ab2177961;
	Sat, 3 May 2025 12:27:07 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sergey Koposov <Sergey.Koposov@ed.ac.uk>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18909: Query creates millions of temporary files and stalls
In-reply-to: <5e98fba645682c40bf5f88ef858963ade9681956.camel@ed.ac.uk>
References: <18909-e5e1b702c9441b8a@postgresql.org>
 <2051617.1746223978@sss.pgh.pa.us>
 <beb051161520fc48248c2f90582eb15c6a71d239.camel@ed.ac.uk>
 <5e98fba645682c40bf5f88ef858963ade9681956.camel@ed.ac.uk>
Comments: In-reply-to Sergey Koposov <Sergey.Koposov@ed.ac.uk>
	message dated "Sat, 03 May 2025 16:11:35 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2177959.1746289626.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 03 May 2025 12:27:06 -0400
Message-ID: <2177960.1746289626@sss.pgh.pa.us>
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/2177960.1746289626%40sss.pgh.pa.us>
Precedence: bulk

Sergey Koposov <Sergey.Koposov@ed.ac.uk> writes:
> #8  0x00005615d84f6a59 in ExecHashTableInsert (hashtable=3D0x5615da85e5c=
0, slot=3D0x5615da823378, hashvalue=3D2415356794)
>     at nodeHash.c:1714
>         shouldFree =3D true
>         tuple =3D 0x5615da85f5e8
>         bucketno =3D 32992122
>         batchno =3D 3521863

Yeah, this confirms the idea that the hashtable has exploded into an
unreasonable number of buckets and batches.  I don't know why a
parallel hash join would be more prone to do that than a non-parallel
one, though.  I'm hoping some of the folks who worked on PHJ will
look at this.

What have you got work_mem set to?  I hope it's fairly large, if
you need to join such large tables.

			regards, tom lane