This is PostgreSQL 11 on AWS, there is a mview query in this OLAP database, the tables involved are huge - 50-100m records on average records hundreds of columns in most cases. The query runs for a while and then errors out saying "No space left on device". I could see it generating around 500gb of temp file data. At times it goes thru and at times it fails - probably due to other queries running at the same time and causing failure.
The tables are partitioned and indexed on the PKs and FKs. Using parallelism (4) with increased work_mem (4gb).
The joins are happening on around 10 tables and all are joining on the PK and FK columns. I see partition pruning happening but the hash joins are killing the query.
Is there any way to avoid hash joins? If we create hash indexes on the joining columns, would PostgreSQL avoid hashing operation and instead use hash indexes on the tables and join them. That way I feel resource intensive hashing would be avoided and there wont be any need of temp files. I tried but does not seem to work, when I query the table with specific values then it uses the hash index but when I am joining the tables it seems to do its own hash join.
My question is how to optimize massive table joins in PostgreSQL to resolve - avoid space failures and make it run fast - takes a couple of hours to complete now. Any best practices or suggestions.
