From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sergey Koposov <Sergey.Koposov@ed.ac.uk>
Cc: lepihov@gmail.com <lepihov@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18909: Query creates millions of temporary files and stalls
Date: Sat, 03 May 2025 18:47:33 -0400
Message-ID: <2221240.1746312453@sss.pgh.pa.us> (raw)
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>
Sergey Koposov <Sergey.Koposov@ed.ac.uk> writes:
> wsdb=> set cursor_tuple_fraction TO 1;
> SET
> wsdb=> EXPLAIN DECLARE c CURSOR FOR select phot_g_mean_mag,phot_bp_mean_mag,phot_rp_mean_mag,
> g.source_id,pmra,pmdec,parallax,parallax_error,d.ra,d.dec,d.ebv,d.gmeanpsfmag,rmeanpsfmag,imeanpsfmag,gkronmag,rkronmag from disk_sample1 as d left join
> gaia_edr3_aux.panstarrs1bestneighbour as gaps1 on (gaps1.original_ext_source_id=d.objid) left join gaia_edr3.gaia_source as g on ( g.source_id = gaps1.source_id
> ) ;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------
> Hash Right Join (cost=112529534.06..422118114.74 rows=65470868 width=100)
> Hash Cond: (g.source_id = gaps1.source_id)
> -> Seq Scan on gaia_source g (cost=0.00..124645680.12 rows=1811786112 width=48)
> -> Hash (cost=111007847.21..111007847.21 rows=65470868 width=60)
> -> Hash Right Join (cost=3231089.53..111007847.21 rows=65470868 width=60)
> Hash Cond: (gaps1.original_ext_source_id = d.objid)
> -> Seq Scan on panstarrs1bestneighbour gaps1 (cost=0.00..17443258.96 rows=951450496 width=16)
> -> Hash (cost=1709402.68..1709402.68 rows=65470868 width=60)
> -> Seq Scan on disk_sample1 d (cost=0.00..1709402.68 rows=65470868 width=60)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, Sergey.Koposov@ed.ac.uk, lepihov@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18909: Query creates millions of temporary files and stalls
In-Reply-To: <2221240.1746312453@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox