PostgreSQL relation size does not sum up

Question

I have a very large table, with blob fields, called data. I've tried to figure out why it isn't cached well and repeated SELECTs are quite slow:

=> SELECT pg_size_pretty(pg_total_relation_size('data'));
157 GB

This seemed a bit large, so I tried to sum up the data:

=> SELECT pg_size_pretty(pg_relation_size('data'));
19 GB

With the indices:

SELECT pg_size_pretty(pg_relation_size('data_pkey'));
757 MB 
SELECT pg_size_pretty(pg_relation_size('data_file_end_date_idx'));
766 MB 
SELECT pg_size_pretty(pg_relation_size('data_file_end_date_idx'));
766 MB
SELECT pg_size_pretty(pg_relation_size('data_merged_idx'));
854 MB
SELECT pg_size_pretty(pg_relation_size('data_owner_idx'));
794 MB
SELECT pg_size_pretty(pg_relation_size('data_session_format_idx'));
779 MB

The summation of the data and indices size is around 26 GB, but the total relation size is near 160 GB. The table was restored from dump just a while ago, and no writes were made since.

• What's the explanation for this difference?
• Any way to reduce wasted disk space? Will clustering help?

Answer 1

Does table have any variable-width columns? If the answer is yes, you are probably missed toast tables (pg_toast_xxx). You need to find their names (not sure how to do it, need to google it) and calculate their size as well.

To reduce space (for example after deleting bunch of rows) execlute VACUUM FULL and reindex table after it (to prevent index fragmentation).

• DrColossos: I'm also pretty sure you'll find some of the missing MB in the matching TOAST-tables.

Answer 2

This query includes also toast tables and indexes:

SELECT pg_size_pretty(pg_relation_size(pg_class.oid)), pg_class.relname, pg_namespace.nspname,
CASE pg_class.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' WHEN 't' THEN 'TOAST' ELSE pg_class.relkind::text END
FROM pg_class 
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
ORDER BY pg_relation_size(pg_class.oid) DESC;