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

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 ...

            regards, tom lane