public inbox for [email protected]
help / color / mirror / Atom feedCTEs and temp_buffers?
6+ messages / 4 participants
[nested] [flat]
* CTEs and temp_buffers?
@ 2022-04-26 17:08 Wells Oliver <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Wells Oliver @ 2022-04-26 17:08 UTC (permalink / raw)
To: pgsql-admin
Out of curiosity, does the value of temp_buffers apply to how CTEs are
generated under the hood?
--
Wells Oliver
[email protected] <[email protected]>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CTEs and temp_buffers?
@ 2022-04-26 17:48 Bruce Momjian <[email protected]>
parent: Wells Oliver <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2022-04-26 17:48 UTC (permalink / raw)
To: Wells Oliver <[email protected]>; +Cc: pgsql-admin
On Tue, Apr 26, 2022 at 10:08:31AM -0700, Wells Oliver wrote:
> Out of curiosity, does the value of temp_buffers apply to how CTEs are
> generated under the hood?
To see temp file usage, you can enable log_temp_files, and check the
server logs or enable client_min_messages.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CTEs and temp_buffers?
@ 2022-04-26 17:59 MichaelDBA <[email protected]>
parent: Bruce Momjian <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: MichaelDBA @ 2022-04-26 17:59 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Wells Oliver <[email protected]>; pgsql-admin
Hmmm, I think you may be wrong about that one, Bruce. I tried that
before and log_temp_files seems to only log work_mem errors, not
temp_buffers associated with temporary table allocations. The only way
to see temp files used by temporary tables is to monitor the
*<datadir>/base/pgsql_tmp*directory, and that can be quite tricky since
it happens so fast there.
Regards,
Michael Vitale
Bruce Momjian wrote on 4/26/2022 1:48 PM:
> On Tue, Apr 26, 2022 at 10:08:31AM -0700, Wells Oliver wrote:
>> Out of curiosity, does the value of temp_buffers apply to how CTEs are
>> generated under the hood?
> To see temp file usage, you can enable log_temp_files, and check the
> server logs or enable client_min_messages.
>
Regards,
Michael Vitale, Sr. PostgreSQL DBA
[email protected] <mailto:[email protected]>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, 3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CTEs and temp_buffers?
@ 2022-04-26 18:03 Tom Lane <[email protected]>
parent: Wells Oliver <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2022-04-26 18:03 UTC (permalink / raw)
To: Wells Oliver <[email protected]>; +Cc: pgsql-admin
Wells Oliver <[email protected]> writes:
> Out of curiosity, does the value of temp_buffers apply to how CTEs are
> generated under the hood?
No. Intermediate results within a query (whether CTE or not) might
get spilled to disk in a "temporary file", but that's a distinct
mechanism from temp tables, which is what temp_buffers applies to.
Bruce's nearby answer explains how you can control/monitor temp
files, but he didn't actually answer your question ;-)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CTEs and temp_buffers?
@ 2022-04-26 18:55 MichaelDBA <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: MichaelDBA @ 2022-04-26 18:55 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Wells Oliver <[email protected]>; pgsql-admin
Hi Tom,
Of course, you're right! I got sidetracked thinking about temp_buffers
with respect to temporary tables and not CTEs where work_mem stuff would
apply. But back to the temp_buffers thing. Can you acknowledge that my
thinking is right about that? That temporary table buffers if exceeding
temp_buffers gets logged to the *<datadir>/base/pgsql_tmp* area and is
not logged in the logs at all? My testing seems to confirm that.
Tom Lane wrote on 4/26/2022 2:03 PM:
> Wells Oliver <[email protected]> writes:
>> Out of curiosity, does the value of temp_buffers apply to how CTEs are
>> generated under the hood?
> No. Intermediate results within a query (whether CTE or not) might
> get spilled to disk in a "temporary file", but that's a distinct
> mechanism from temp tables, which is what temp_buffers applies to.
>
> Bruce's nearby answer explains how you can control/monitor temp
> files, but he didn't actually answer your question ;-)
>
> regards, tom lane
>
>
Regards,
Michael Vitale, Sr. PostgreSQL DBA
[email protected] <mailto:[email protected]>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, 3-pgadvanced3.jpg)
download | view image
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CTEs and temp_buffers?
@ 2022-04-26 19:03 Tom Lane <[email protected]>
parent: MichaelDBA <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2022-04-26 19:03 UTC (permalink / raw)
To: MichaelDBA <[email protected]>; +Cc: Wells Oliver <[email protected]>; pgsql-admin
MichaelDBA <[email protected]> writes:
> Hi Tom,
> Of course, you're right! I got sidetracked thinking about temp_buffers
> with respect to temporary tables and not CTEs where work_mem stuff would
> apply. But back to the temp_buffers thing. Can you acknowledge that my
> thinking is right about that? That temporary table buffers if exceeding
> temp_buffers gets logged to the *<datadir>/base/pgsql_tmp* area and is
> not logged in the logs at all? My testing seems to confirm that.
No. Temp tables go into the same directories as regular tables.
The files have funny names though, following the format "tN_OID"
rather than just "OID".
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-04-26 19:03 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-26 17:08 CTEs and temp_buffers? Wells Oliver <[email protected]>
2022-04-26 17:48 ` Bruce Momjian <[email protected]>
2022-04-26 17:59 ` MichaelDBA <[email protected]>
2022-04-26 18:03 ` Tom Lane <[email protected]>
2022-04-26 18:55 ` MichaelDBA <[email protected]>
2022-04-26 19:03 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox