Re: SELECT creates millions of temp files in a single directory

On Sat, Apr 23, 2022 at 05:13:41PM -0400, Tom Lane wrote:
! "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
! > I'll add that given the nature of the problem that changing temp_file_limit
! > away from its default of unlimited may be useful.
! > https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-DISK
!
! Maybe ... that limits the total space involved, not the number of
! files it's separated into, so I'm not sure how much it can help.

That's what I understood from the docs, too. What I also read in the
docs is that it will just kill the query when it hits the limit, and
this is not really what I want.

And btw, I probably lost-in-translation the relevant info about the
running version:

Name : postgresql12-server
Version : 12.10
Installed on : Mon Apr 4 04:13:18 2022 CEST
Origin : databases/postgresql12-server
Architecture : FreeBSD:13:amd64

! It might be worth playing around to see how varying values of work_mem
! affect this behavior, too. That should change the planner's initial
! estimate of the number of hash batches needed, which likely plays into
! this somehow.

Agreed. But then, looking at the generated filenames, in the style of
"i44297of524288.p1.0" - this is an algorithm at work, so somebody must
have done this, and obviousely didn't bother to create half a million
of files, after having created another half million already.

So I thought I might just ask what is the idea with this.

| > It would help if you can provide a self-contained demonstration
| > that others can then verify and debug (or explain).
|
| ... and that. As this message stands, it's undocumented whining.
| Please see
|
| https://wiki.postgresql.org/wiki/Slow_Query_Questions
|
| for the sort of information we need to debug performance issues.

It is not a performance issue, it is a design question: You inflict
pain on my beloved ZFS, and as a lover I react. ;)

| (I recall that we have fixed some problems in the past that could
| lead to unreasonable numbers of temp files in hash joins. Whether
| there's more to do, or Peter is running a version that lacks those
| fixes, is impossible to tell with the given info.)

Yes, I was accidentially deleting that info too when I deleted the
more extensive rants from my original posting. See here, above.