My database is suddenly hanging on some queries that join many tables and I see:
temporary file size exceeds temp_file_limit (1025563kB)
A sample query and query plan can be seen here:
This query normally takes less than 100ms but hangs when hit temporary file size limit.
When I run:
SELECT temp_files AS "Temporary files", temp_bytes AS "Size of temporary files" FROM pg_stat_database db;
Temporary files Size of temporary files --- 22550 10651900248
How do I resolve this?
I was able to enable more of the problem. Say a user lists they can speak 2 languages - the query runs ok. Then they edit their profile to say they know 20 languages. The query ends up exceeding the
temp_file_limit and hanging.