I do not have more disks lying around, and I fear that if it does not complete with 1.3TB of disk space it might not be that likely that adding 750GB would work...
Postgres version: the original (prd) issue was on 10.x. I also tested it on 14.x with the same issue. I then upgraded my machine to 15.1 to make sure to report on the latest version, and all information mentioned in this thread is from that version.
btw, this query generates quite different plans when tweaking things like nested_loop=false/true, and the "fast" plan requires nested_loops=true and join_collapse_limit=1 (5 seconds response). An odd thing is that both plans contain only one nested loop (a cross join, I think it cannot do that without one) but the general plan changes a lot.. I am trying to get output from that second plan because this one just loops using CPU, not disk... Perhaps that one will finish with some statistics...
