Content-Type: text/plain;
	charset=us-ascii
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: <fe4b82fa-9907-43f9-b6ff-e1cbc768158d@pscs.co.uk>
Date: Mon, 22 Jul 2024 07:56:06 -0600
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <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>
To: Paul Smith* <paul@pscs.co.uk>,
 "David G. Johnston" <david.g.johnston@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/6C269096-47A8-4F23-87AC-512656D4A084%40elevated-dev.com>
Precedence: bulk

> ...with each operation generally being allowed to use as much memory =
as this value specifies before it starts to write data into temporary =
files.

So, doesn't explain the 7452-byte files. Unless an operation can use a =
temporary file as an addendum to work_mem, instead of spilling the RAM =
contents to disk as is my understanding.

> So, if it's doing lots of joins, there may be lots of bits of =
temporary data which together add up to more than work_mem.

If it's doing lots of joins, each will get work_mem--there is no "adding =
up" among operations using work_mem.

> You expect the smallest temporary file to be 128MB?  I.e., if the =
memory used exceeds work_mem all of it gets put into the temp file at =
that point?  Versus only the amount of data that exceeds work_mem =
getting pushed out to the temporary file.  The overflow only design =
seems much more reasonable - 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 + =
7KB of data using 128MB of RAM and a 7KB file. Same for many of the =
other operations which use work_mem, so yes, I expected spill over to =
start with 128MB file and grow it as needed. If I'm wrong and there are =
operations which can effectively use temp files as adjunct, then that =
would be the answer to my question. Does anybody know for sure that this =
is the case?=