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

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.

--
regards, Andrei Lepikhov