Re: BUG #18909: Query creates millions of temporary files and stalls - Mailing list pgsql-bugs

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.489rows=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.340rows=13095751 loops=5)
 
> >                ->  Parallel Hash  (cost=10307380.24..10307380.24 rows=237862624 width=16) (actual
time=169633.067..169633.068rows=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_sourceas 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
buidheanncarthannais a th’ ann an Oilthigh Dhùn Èideann, clàraichte an Alba, àireamh clàraidh SC005336.