| 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: | Whole Thread | Raw Message | 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 |