Message-ID: <9691212b-86f0-4476-940e-2e4ba0dd0cf9@gmail.com>
Date: Sun, 4 May 2025 09:52:13 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18909: Query creates millions of temporary files and stalls
To: Sergey Koposov <Sergey.Koposov@ed.ac.uk>,
 "tgl@sss.pgh.pa.us" <tgl@sss.pgh.pa.us>
Cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
References: <18909-e5e1b702c9441b8a@postgresql.org>
 <2051617.1746223978@sss.pgh.pa.us>
 <beb051161520fc48248c2f90582eb15c6a71d239.camel@ed.ac.uk>
 <5e98fba645682c40bf5f88ef858963ade9681956.camel@ed.ac.uk>
 <2177960.1746289626@sss.pgh.pa.us>
 <80bab58be182e80e73b5d5f71664ede9ce58f957.camel@ed.ac.uk>
 <8fc51cd7-115a-425a-828e-c99defb5afef@gmail.com>
 <ba927eb7126844c1b964e75cadb774488794dfde.camel@ed.ac.uk>
 <0ce957be37434cfca4820d1e89a70cb2b456d3db.camel@ed.ac.uk>
 <2216372.1746309806@sss.pgh.pa.us>
 <ca371295b353c0306ccab1fcd6a06bd6421f4955.camel@ed.ac.uk>
 <2221240.1746312453@sss.pgh.pa.us>
 <f6fb5c3463006c1d4471bc30f9edb419cb777329.camel@ed.ac.uk>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <f6fb5c3463006c1d4471bc30f9edb419cb777329.camel@ed.ac.uk>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/9691212b-86f0-4476-940e-2e4ba0dd0cf9%40gmail.com>
Precedence: bulk

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