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 SELECT
s 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?