"David G. Johnston" <david.g.johnston@gmail.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.
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.
regards, tom lane