Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: small temp files
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <870800.1721660046@sss.pgh.pa.us>
Date: Mon, 22 Jul 2024 09:46:29 -0600
Cc: Paul Smith* <paul@pscs.co.uk>,
 "David G. Johnston" <david.g.johnston@gmail.com>,
 pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <16C7116A-2CB2-4909-8830-2FA16470EE27@elevated-dev.com>
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>
To: Tom Lane <tgl@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/16C7116A-2CB2-4909-8830-2FA16470EE27%40elevated-dev.com>
Precedence: bulk

> On Jul 22, 2024, at 8:54=E2=80=AFAM, Tom Lane <tgl@sss.pgh.pa.us> =
wrote:
>=20
> 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