To: PostgreSQL Performance <pgsql-performance@postgresql.org>
From: MichaelDBA <MichaelDBA@sqlexec.com>
Subject: temporary file log lines
Message-ID: <d658f01b-7a1d-da5e-e996-3403e6b7cf49@sqlexec.com>
Date: Thu, 8 Jul 2021 17:22:56 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 PostboxApp/7.0.48
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/d658f01b-7a1d-da5e-e996-3403e6b7cf49%40sqlexec.com>
Precedence: bulk

Hi all,

I got a question about PG log lines with temporary file info like this:

case 1: log line with no contextual info
2021-07-07 20:28:15 UTC:10.100.11.95(50274):myapp@mydb:[35200]:LOG: 
temporary file: path "base/pgsql_tmp/pgsql_tmp35200.0", size 389390336

case 2: log line with contextual info
2021-07-07 20:56:18 UTC:172.16.193.118(56080):myapp@mydb:[22418]:LOG: 
temporary file: path "base/pgsql_tmp/pgsql_tmp22418.0", size 1048576000
2021-07-07 20:56:18 
UTC:172.16.193.118(56080):myapp@mydb:[22418]:CONTEXT:  PL/pgSQL function 
memory.f_memory_usage(boolean) line 13 at RETURN QUERY

There are at least 2 cases where stuff can spill over to disk:
* queries that don't fit in work_mem, and
* temporary tables that don't fit in temp_buffers

Question, if log_temp_files is turned on (=0), then how can you tell 
from where the temporary log line comes from?
I see a pattern where work_mem spill overs have a CONTEXT line that 
immediately follows the LOG LINE with keyword, temporary. See case 2 above.

For other LOG lines with keyword, temporary, there is no such pattern. 
Could those be the ones caused by temp_buffer spill overs to disk?  case 
1 above.

I really want to tune temp_buffers, but I would like to be able to 
detect when temporary tables are spilling over to disk, so that I can 
increase temp_buffers.

Any help would be appreciated.

Regards,
Michael Vitale