From: Sergey Koposov <Sergey.Koposov@ed.ac.uk>
To: tgl@sss.pgh.pa.us <tgl@sss.pgh.pa.us>
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, 3 May 2025 22:30:19 +0000
Message-ID: <ca371295b353c0306ccab1fcd6a06bd6421f4955.camel@ed.ac.uk> (raw)
In-Reply-To: <2216372.1746309806@sss.pgh.pa.us>
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>
On Sat, 2025-05-03 at 18:03 -0400, Tom Lane wrote:
> Sergey Koposov <Sergey.Koposov@ed.ac.uk> writes:
> > -> Parallel Hash Left Join (cost=14442102.04..22124798.60 rows=16367717 width=60) (actual time=209805.943..273008.489 rows=13102859 loops=5)
> > Hash Cond: (d.objid = gaps1.original_ext_source_id)
> > -> Parallel Seq Scan on disk_sample1 d (cost=0.00..1218371.17 rows=16367717 width=60) (actual time=37.353..25185.340 rows=13095751 loops=5)
> > -> Parallel Hash (cost=10307380.24..10307380.24 rows=237862624 width=16) (actual time=169633.067..169633.068 rows=190290095 loops=5)
> > Buckets: 67108864 Batches: 32 Memory Usage: 1919904kB
> > -> Parallel Seq Scan on panstarrs1bestneighbour gaps1 (cost=0.00..10307380.24 rows=237862624 width=16) (actual
> > time=132.295..117548.803
> > rows=190290095 loops=5)
>
> Hm, interesting. The number of batches stayed sane here (32), whereas
> it went crazy in the other run. I wonder if there's something
> nondeterministic about that choice in a parallel hash join.
>
> > I don't think I know how to see the plan of the declare cursor query.
>
> EXPLAIN DECLARE c CURSOR FOR SELECT ...
Thanks! I tried something like that before, but without the cursor name.
Here's the plan for the case that's originally triggered it:
Note I did not disable the parallelism here by hand.
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)
JIT:
Functions: 18
Options: Inlining true, Optimization true, Expressions true, Deforming true
(12 rows)
S
The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. Is e buidheann carthannais a th’ ann an Oilthigh Dhùn Èideann, clàraichte an Alba, àireamh clàraidh SC005336.
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: Sergey.Koposov@ed.ac.uk, tgl@sss.pgh.pa.us, lepihov@gmail.com, 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>
* 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