Re: BUG #18909: Query creates millions of temporary files and stalls

On Sat, 2025-05-03 at 18:03 -0400, Tom Lane wrote:
> Sergey Koposov <Sergey(dot)Koposov(at)ed(dot)ac(dot)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.