From: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Ribe <scott_ribe@elevated-dev.com>
cc: Paul Smith* <paul@pscs.co.uk>,
        "David G. Johnston" <david.g.johnston@gmail.com>,
        pgsql-admin@lists.postgresql.org
Subject: Re: small temp files
In-reply-to: <6C269096-47A8-4F23-87AC-512656D4A084@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>
Comments: In-reply-to Scott Ribe <scott_ribe@elevated-dev.com>
	message dated "Mon, 22 Jul 2024 07:56:06 -0600"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <870799.1721660046.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 22 Jul 2024 10:54:06 -0400
Message-ID: <870800.1721660046@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/870800.1721660046%40sss.pgh.pa.us>
Precedence: bulk

Scott Ribe <scott_ribe@elevated-dev.com> writes:
>> You expect the smallest temporary file to be 128MB?  I.e., if the memor=
y used exceeds work_mem all of it gets put into the temp file at that poin=
t?  Versus only the amount of data that exceeds work_mem getting pushed ou=
t to the temporary file.  The overflow only design seems much more reasona=
ble - why write to disk that which fits, and already exists, in memory.

> Well, I don't know of an algorithm which can effectively sort 128MB + 7K=
B of data using 128MB of RAM and a 7KB file. Same for many of the other op=
erations which use work_mem, so yes, I expected spill over to start with 1=
28MB file and grow it as needed. If I'm wrong and there are operations whi=
ch can effectively use temp files as adjunct, then that would be the answe=
r to my question. Does anybody know for sure that this is the case?

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.

			regards, tom lane