CODEWITHSUNDEEP
databasepostgresql
cdalxndr
cdalxndr

Reputation: 1551

Postgres: how to calculate temp file space requirement

During execution of query I get error: temporary file size exceeds temp_file_limit (<some num>kB).

How can I calculate the required temp file storage of a query (using EXPLAIN ANALYZE)?

I require method of calculating temp file disk usage, so that I can tune my query to lower the requirement.

Upvotes: 1

Views: 1851

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247270

The way to go would be to disable temp_file_limit and set log_temp_files = 0. Then you would see the size of the temporary files in the log (and in the EXPLAIN (ANALYZE) output).

But if I were you, I'd look at the EXPLAIN output and find the Sort that promises to be very big and see what I can to to avoid it. The optimizer will avoid plans with very large hashes, and bitmaps won't ever exceed work_mem, so it must be a large sort that is creating temporary files.

Upvotes: 2

Related Questions