Given a query q to execute. Is there a way to determine how much disk-space postgresql will (or used) as temporary_space in order to execute it (during its execution) ?

I'm under Ubuntu 12.0.4 with postgresql 9.1


Knowing how much will be used is hard. How much has been used, you there are logging options for that.

set log_temp_files=0;
set client_min_messages TO log;
select count(distinct x) from generate_series(1,10000000) x;

LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp25126.2", size 120127488.

LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp25126.1", size 140000000


You can use EXPLAIN (ANALYZE, BUFFERS) SELECT ... to see how much space your current query with current params take. But it may need different amounth of space for different input parameters or different data set.

Some examples where the temp files are used:

Sort Method: external merge  Disk: 6848kB
Buffers: shared hit=4428, temp read=2305 written=2305

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.