From: Sergey Koposov <Sergey.Koposov@ed.ac.uk>
To: lepihov@gmail.com <lepihov@gmail.com>
To: tgl@sss.pgh.pa.us <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #18909: Query creates millions of temporary files and stalls
Date: Sun, 4 May 2025 11:45:05 +0000
Message-ID: <0a2ccb925ec37c93a143359de6381392779fdde4.camel@ed.ac.uk> (raw)
In-Reply-To: <9691212b-86f0-4476-940e-2e4ba0dd0cf9@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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: Sergey.Koposov@ed.ac.uk, lepihov@gmail.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18909: Query creates millions of temporary files and stalls
In-Reply-To: <0a2ccb925ec37c93a143359de6381392779fdde4.camel@ed.ac.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox