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

On Sun, 2025-05-04 at 09:52 +0200, Andrei Lepikhov wrote:
> On 4/5/2025 01:05, Sergey Koposov wrote:
> > The only thing I can add is that panstarrs1bestneighbour is ordered on disk by source_id. I don't know if that can be relevant.
> Seems promising. May you show us how much NULLs generates underlying
> JOIN operator. I guess, in your example the query should be close to the
> following:
>
> SELECT count(*) FROM disk_sample1 d
> LEFT JOIN gaia_edr3_aux.panstarrs1bestneighbour AS gaps1
> ON (gaps1.original_ext_source_id = d.objid)
> WHERE gaps1.original_ext_source_id IS NULL;
>
> And just to be sure, show us the number of NULLs that may be generated
> by alternative LEFT JOIN:
>
> SELECT count(*) FROM gaia_edr3_aux.panstarrs1bestneighbour AS gaps1
> LEFT JOIN gaia_edr3.gaia_source AS g
> ON (g.source_id = gaps1.source_id)
> WHERE g.source_id IS NULL;
>
> Also, show please the current value of the GUC hash_mem_multiplier.
>

Here:

wsdb=>
SELECT count(*) FROM disk_sample1 d
LEFT JOIN gaia_edr3_aux.panstarrs1bestneighbour AS gaps1
ON (gaps1.original_ext_source_id = d.objid)
WHERE gaps1.original_ext_source_id IS NULL;
count
----------
37635427
(1 row)

wsdb=>
SELECT count(*) FROM gaia_edr3_aux.panstarrs1bestneighbour AS gaps1
LEFT JOIN gaia_edr3.gaia_source AS g
ON (g.source_id = gaps1.source_id)
WHERE g.source_id IS NULL;
count
-------
0
(1 row)

wsdb=> show hash_mem_multiplier ;
hash_mem_multiplier
---------------------
2
(1 row)

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