Horton
Menu

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sergey Koposov <Sergey.Koposov@ed.ac.uk>
Cc: "lepihov@gmail.com" <lepihov@gmail.com>, "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Date: 2025-05-03T22:03:26Z
Lists: pgsql-bugs 
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 ...

			regards, tom lane