From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sergey Koposov <Sergey.Koposov@ed.ac.uk>
cc: "lepihov@gmail.com" <lepihov@gmail.com>,
        "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: <ca371295b353c0306ccab1fcd6a06bd6421f4955.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>
 <2177960.1746289626@sss.pgh.pa.us>
 <80bab58be182e80e73b5d5f71664ede9ce58f957.camel@ed.ac.uk>
 <8fc51cd7-115a-425a-828e-c99defb5afef@gmail.com>
 <ba927eb7126844c1b964e75cadb774488794dfde.camel@ed.ac.uk>
 <0ce957be37434cfca4820d1e89a70cb2b456d3db.camel@ed.ac.uk>
 <2216372.1746309806@sss.pgh.pa.us>
 <ca371295b353c0306ccab1fcd6a06bd6421f4955.camel@ed.ac.uk>
Comments: In-reply-to Sergey Koposov <Sergey.Koposov@ed.ac.uk>
	message dated "Sat, 03 May 2025 22:30:19 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2221239.1746312453.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 03 May 2025 18:47:33 -0400
Message-ID: <2221240.1746312453@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2221240.1746312453%40sss.pgh.pa.us>
Precedence: bulk

Sergey Koposov <Sergey.Koposov@ed.ac.uk> writes:
> wsdb=3D> set cursor_tuple_fraction TO 1;
> SET
> wsdb=3D> EXPLAIN DECLARE c CURSOR FOR   select phot_g_mean_mag,phot_bp_m=
ean_mag,phot_rp_mean_mag,
> g.source_id,pmra,pmdec,parallax,parallax_error,d.ra,d.dec,d.ebv,d.gmeanp=
sfmag,rmeanpsfmag,imeanpsfmag,gkronmag,rkronmag from  disk_sample1  as d l=
eft join
> gaia_edr3_aux.panstarrs1bestneighbour as gaps1 on (gaps1.original_ext_so=
urce_id=3Dd.objid) left join gaia_edr3.gaia_source as g  on ( g.source_id =
=3D gaps1.source_id
> ) ;
>                                                   QUERY PLAN
> ------------------------------------------------------------------------=
---------------------------------------
>  Hash Right Join  (cost=3D112529534.06..422118114.74 rows=3D65470868 wid=
th=3D100)
>    Hash Cond: (g.source_id =3D gaps1.source_id)
>    ->  Seq Scan on gaia_source g  (cost=3D0.00..124645680.12 rows=3D1811=
786112 width=3D48)
>    ->  Hash  (cost=3D111007847.21..111007847.21 rows=3D65470868 width=3D=
60)
>          ->  Hash Right Join  (cost=3D3231089.53..111007847.21 rows=3D65=
470868 width=3D60)
>                Hash Cond: (gaps1.original_ext_source_id =3D d.objid)
>                ->  Seq Scan on panstarrs1bestneighbour gaps1  (cost=3D0.=
00..17443258.96 rows=3D951450496 width=3D16)
>                ->  Hash  (cost=3D1709402.68..1709402.68 rows=3D65470868 =
width=3D60)
>                      ->  Seq Scan on disk_sample1 d  (cost=3D0.00..17094=
02.68 rows=3D65470868 width=3D60)

Oh, *that* is interesting: now we have two hash joins, and neither one
has panstarrs1bestneighbour as the table to hash, so it's not too
clear which one is going crazy.  But you showed the stats for
disk_sample1.objid, and that looked pretty well distributed, so I'm
going to guess that that hash is fine.  That leaves the other join
on panstarrs1bestneighbour.source_id as the one under suspicion.
Can we see the stats for that column?

			regards, tom lane