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