Message-ID: <fb86d600-09f9-469a-a712-6ab26ff38641@gmail.com>
Date: Sun, 4 May 2025 14:13:08 +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>,
 "a.rybakina" <a.rybakina@postgrespro.ru>, Tomas Vondra <tomas@vondra.me>,
 melanieplageman@gmail.com
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>
 <9691212b-86f0-4476-940e-2e4ba0dd0cf9@gmail.com>
 <0a2ccb925ec37c93a143359de6381392779fdde4.camel@ed.ac.uk>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <0a2ccb925ec37c93a143359de6381392779fdde4.camel@ed.ac.uk>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/fb86d600-09f9-469a-a712-6ab26ff38641%40gmail.com>
Precedence: bulk

On 4/5/2025 13:45, Sergey Koposov wrote:
> 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.
Hmm. That means the bucket size to store tuples with NULL values should 
be at least 2.103GB (I think it will borrow more).

So, my main conjecture is the extreme data skew issue - when we have 
said Postgres should use less memory than a single non-divisible batch 
may fit in. To solve it, we have at least two ongoing projects in this area.
Project [1] is dedicated to resolving this issue using 'slices' by 
matching the too-massive batch to the corresponding batch on the other 
side of the join in small portions. Project [2] should allow the 
optimiser to take into account NULLs generated by the nullable side of 
the join, which will give more chances to avoid skewed HashJoin.

Both these projects are not guaranteed to be committed even in PG 19. 
What's more, there is little chance they will be back-patched into v.15.

So, if your problem is exactly with too many nulls, I would say you need 
a workaround: increment hash_mem_multiplier in case you are sure you may 
calculate the top boundary for the number of such nulls in this query. 
Or turn off hash join in this query at all.

As for fixing the bug quickly (I have at least one report on this issue 
right now, too), I think we may invent two or three steps of (local) 
hash_mem_multiplier incrementation in case a massive not-breaking-down 
batch is detected.
And definitely, it make sense for me to add an error message which will 
signal people what to do if they stuck into this problem.

[1] 
https://www.postgresql.org/message-id/flat/7bed6c08-72a0-4ab9-a79c-e01fcdd0940f%40vondra.me
[2] 
https://www.postgresql.org/message-id/flat/52b55b53-420a-722c-adbd-706922fc059b%40enterprisedb.com

-- 
regards, Andrei Lepikhov