agora inbox for pgsql-admin@postgresql.org  

help / color / mirror / Atom feed

From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Paul Smith* <paul@pscs.co.uk>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: small temp files
Date: Mon, 22 Jul 2024 09:46:29 -0600
Message-ID: <16C7116A-2CB2-4909-8830-2FA16470EE27@elevated-dev.com> (raw)
In-Reply-To: <870800.1721660046@sss.pgh.pa.us>
References: <7A0C9A69-5632-4CFC-B156-53FAAAB33E26@elevated-dev.com>
	<a9dee6e8-6197-d9bb-028e-6d020167bf57@jakobs.com>
	<8E03B792-C389-456D-913D-4F2B0EBB903E@elevated-dev.com>
	<fe4b82fa-9907-43f9-b6ff-e1cbc768158d@pscs.co.uk>
	<6C269096-47A8-4F23-87AC-512656D4A084@elevated-dev.com>
	<870800.1721660046@sss.pgh.pa.us>

> On Jul 22, 2024, at 8:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> You would get more specific answers if you provided an example of the
> queries that cause this, with EXPLAIN ANALYZE output.  But I think a
> likely bet is that it's doing a hash join that overruns work_mem.
> What will happen is that the join gets divided into batches based on
> hash codes, and each batch gets dumped into its own temp files (one
> per batch for each side of the join).  It would not be too surprising
> if some of the batches are small, thanks to the vagaries of hash
> values.  Certainly they could be less than work_mem, since the only
> thing we can say for sure is that the sum of the temp file sizes for
> the inner side of the join should exceed work_mem.

OK, that makes total sense, and fits our usage patterns. (Lots of complex queries, lots of hash joins.)

thanks

view thread (8+ messages)

Message-ID: <16C7116A-2CB2-4909-8830-2FA16470EE27@elevated-dev.com>
Permalink:  ../16C7116A-2CB2-4909-8830-2FA16470EE27@elevated-dev.com/
Also on:    postgresql.org/message-id/16C7116A-2CB2-4909-8830-2FA16470EE27@elevated-dev.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-admin@postgresql.org
  Cc: scott_ribe@elevated-dev.com, tgl@sss.pgh.pa.us, paul@pscs.co.uk, david.g.johnston@gmail.com, pgsql-admin@lists.postgresql.org
  Subject: Re: small temp files
  In-Reply-To: <16C7116A-2CB2-4909-8830-2FA16470EE27@elevated-dev.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox