1

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:

https://pastebin.com/wug86Wrr

http://www.sharecsv.com/s/a6333a7f610e049dd81ebcfc19a4c02f/temp_file_limit_exceeded.csv

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;

I see:

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.

1

You are joining around 40 tables to retrieve around 200 columns. I did not dig in but, typically, such a query is a misunderstanding. Either way, no wonder you amass a lot of temporary files.

Also note that all temp files of the same session count against the limit. Bound cursors can become a problem that way. Or multiple large queries in the same transaction.

By default the parameter temp_file_limit is set to -1, which means "no limit". So either sanitize your query or remove or extend the limit. (Only superusers can do that.)

If you cannot change either, setting more work_mem might help to keep more operations in RAM, so less temp space is needed.

Don't confuse this setting with temp_buffers, which is mostly unrelated and sets the amount of RAM that can be used for temporary objects (like in CREATE TEMP TABLE ...)

And you did not bother to mention your version of Postgres. Maybe outdated?

  • You are joining around 40 tables to retrieve around 200 columns. I did not dig in but, typically, such a query is a misunderstanding. Yes, I am! The use case is a detailed user profile, where most fields many-to-many to lookup tables. I am not sure how to optimize that query, so I will create a separate question for that if that's the issue. But increasing my temp_file_limit helped (on Google Cloud SQL it was capped). work_mem was at 4GB and I increased to 64GB, but didn't notice much difference here. Google Cloud SQL is on Postgres 9.6. – atkayla Mar 24 at 14:00
  • Setting work_mem too high can have adverse effects. It very much depends on available RAM, number of concurrent sessions and the complete workload, but 4 GB is already an exceptionally high setting and 64 GB seems way out of proportion except for specialized warehouse systems. – Erwin Brandstetter Mar 24 at 23:04
  • Apologies! Meant 4MB -> 16MB -> 64MB work_mem on a 3.75GB RAM database. :P – atkayla Mar 24 at 23:23
  • OK; that makes more sense. 4MB is almost always to low. But OTOH, 3.75GB RAM is a very smallish setup. My humble smartphone has more RAM ... – Erwin Brandstetter Mar 24 at 23:56
0

Maybe statistics are out of date, now causing the same query plan to run with a much worse query plan?

https://stackoverflow.com/questions/6903938/how-do-i-know-if-the-statistics-of-a-postgres-table-are-up-to-date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.