Index size

From: Michel Albert <exhuma(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Index size
Date: 2009-08-03 10:52:59
Message-ID: 347c9927-da1e-48e8-955d-7bc3853d36ab@o15g2000yqm.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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?

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Michel Albert 2009-08-03 11:06:13 Re: Index size
Previous Message Jasen Betts 2009-08-03 10:46:15 Re: Weekends between