On Sat, Apr 23, 2022 at 05:13:41PM -0400, Tom Lane wrote: ! "David G. Johnston" <david.g.johnston@xxxxxxxxx> 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.
