Re: Index size

On Aug 3, 12:52 pm, Michel Albert <exh(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> 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?

Nevermind.... by looking at http://www.postgresql.org/docs/8.2/interactive/disk-usage.html
I tracked down the culprit. It's got nothing to do with the TOAST
tables. The index itself is that large. The PK of the table spans over
13 columns. With the table having only 17 colums ;)

It still surprises me that the index is larger than the table
though... Anyways. It seems it's a design related problem and there's
noting I can do about that :(

I'd still be interested to know if the indexes are compressed in
Postgres... ;)