pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Andrei Lepikhov <lepihov@gmail.com>
To: Sergey Koposov <Sergey.Koposov@ed.ac.uk>
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 09:52:13 +0200
Message-ID: <9691212b-86f0-4476-940e-2e4ba0dd0cf9@gmail.com> (raw)
In-Reply-To: <f6fb5c3463006c1d4471bc30f9edb419cb777329.camel@ed.ac.uk>
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>

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





view thread (17+ messages)  latest in thread

Message-ID: <9691212b-86f0-4476-940e-2e4ba0dd0cf9@gmail.com>
Permalink:  ../9691212b-86f0-4476-940e-2e4ba0dd0cf9@gmail.com/
Also on:    postgresql.org/message-id/9691212b-86f0-4476-940e-2e4ba0dd0cf9@gmail.com

 · 

reply
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: lepihov@gmail.com, Sergey.Koposov@ed.ac.uk, 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: <9691212b-86f0-4476-940e-2e4ba0dd0cf9@gmail.com>

* 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