Re: Slow query - lots of temporary files.

The problematic piece of the explain plan is this:

 ->  Merge Join  (cost=4384310.92..21202716.78 rows=6664163593
                  width=390)"
       Output: a.ut, c.gt, b.go, b.gn, d.au"
       Merge Cond: ((c.ut)::text = (d.rart_id)::text)"

That is, the planner expects ~6.7 billion rows, each ~390B wide. That's ~2.5TB of data that needs to be stored to disk (so that the sort can process it).

The way the schema is designed might be one of the issues - ISTM the 'ut' column is somehow universal, mixing values referencing different columns in multiple tables. Not only that's utterly misleading for the planner (and may easily cause issues with huge intermediate results), but it also makes formulating the queries very difficult. And of course, the casting between text and int is not very good either.

Fix the schema to follow relational best practices - separate the values into multiple columns, and most of this will go away.