PostgreSQL select result size

Question

I have a table in PostgreSQL DB and make a select from this table with some constraints, and than I want to know how much disk space does this select has. I know that there is a postgres function pg_total_relation_size that gives me the size of some table in DB, but how can I find the 'subtable' size?

Any Ideas?

I use PostgreSQL 9.1

A select statement does not have a "disk size", because it's not stored anywhere. — user330315
– user330315, Commented Nov 20, 2012 at 15:39
Ok, so I need to store it first to get the size? Are there some other alternatives? I mean, I just want to know the size of a subtable... — golinko
– golinko, Commented Nov 20, 2012 at 15:47
You could spool it into a text file to get a rough estimate of the raw size of the data — user330315
– user330315, Commented Nov 20, 2012 at 15:49
I've just found the following alternative: select sum(octet_length(temp.*::text)) from (select * from lesson) as temp It works and I get the size of the table in text represantation. — golinko
– golinko, Commented Nov 20, 2012 at 16:29

Craig Ringer · Accepted Answer · 2012-11-20 23:18:52Z

19

To get the data size, allowing for TOAST compression, etc:

regress=> SELECT sum(pg_column_size(devices)) FROM devices WHERE country = 'US';
 sum 
-----
 105
(1 row)

To get the disk storage required including block allocation overhead, headers, etc etc:

regress=> CREATE TEMPORARY TABLE query_out AS SELECT * FROM devices WHERE country = 'US';
SELECT 3
regress=> SELECT pg_total_relation_size('query_out');
 pg_total_relation_size 
------------------------
                  16384
(1 row)

Why are the results so different? Because the latter query is reporting the size of the 8k block for the main table, and the 8k block for the TOAST table. It doesn't care that these blocks are mostly empty.

answered Nov 20, 2012 at 23:18

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

golinko Over a year ago

Thanks, that's exactly what I looked for, the octet_length is also ok, but it gives me the number of bytes in binary string, so I'll get wrong size if the string is just a text...

Craig Ringer Over a year ago

@ann octet_length also won't reflect the space use from TOAST accurately. See stackoverflow.com/questions/13304572/…

Mohd Anas Over a year ago

16384? What does this mean? Is it in kb or bytes ?

Craig Ringer Over a year ago

@MohdAnas Well, the manual for pg_total_relation_size says bytes. So I guess it's bytes. If you want it pretty printed use pg_size_pretty

Mohd Anas Over a year ago

@CraigRinger thanks... Could you please tell me what I need to do if I want to get the size of multiple table in one query?

|

Collectives™ on Stack Overflow

PostgreSQL select result size

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related