Hi,
compared to Sybase, wich compresses indices, the index size in
PostgrSQL seems huge. To determine the Index size I used
"pg_relation_size" and "pg_total_relation_size". In fact I used a
query like:
SELECT pg_size_pretty( pg_total_relation_size('tablename') -
pg_relation_size('tablename') );
This will give me the index size *plus* the toast size. Is there a way
to retrieve /only/ the index size?
As a more practical example:
mydb=# SELECT pg_size_pretty( pg_total_relation_size('mytable') );
pg_size_pretty
----------------
5032 MB
(1 row)
mydb=# SELECT pg_size_pretty( pg_relation_size('mytable') );
pg_size_pretty
----------------
2382 MB
(1 row)
Note that these are not the real table-/db-names. I'd shoot myself in
the foot if I had names like these in production ;) As you can see,
this result would mean that indexes with toast tables combined are
larger than the data itself. This comes from a database which has an
awful schema design, and that could very well be the cause.But I
really would like to examine this case further and see where the disk-
space is allocated.
To re-iterate the question: How can I see the (on-disk) size of one
specific index?
