Обсуждение: Index size

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?

On Aug 3, 12:52 pm, Michel Albert <exh...@gmail.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... ;)

On Mon, Aug 3, 2009 at 12:06 PM, Michel Albert<exhuma@gmail.com> wrote:
> On Aug 3, 12:52 pm, Michel Albert <exh...@gmail.com> wrote:
>> This will give me the index size *plus* the toast size. Is there a way
>> to retrieve /only/ the index size?

You can just use pg_relation_size() on the index name itself.


> 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 :(

Indeed. That may be useful (marginally) for enforcing a unique
constraint. But it's going to be seriously impaired at being useful
for the usual index things like satisfying where clauses and order by
clauses quickly.

And it'll slow down updates and inserts noticably to have to maintain
an index like that.

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

Indexes in Postgres look a lot like tables. The per-row overhead is
lower but you have such large index rows here that the overhead will
be irrelevant anyways. The actual data takes the same amount of space
in the index that it does in the table. Individual columns can be
compressed if they're big but if so then they'll already be compressed
in the table anyways. Usually a big part of the performance advantage
of an index is precisely that it has a lot fewer columns than the
table.

What i think you're really looking for is either a kind of index
organized table or prefix compression. There have been attempts at
both but nothing that's been accepted for various reasons.

--
greg
http://mit.edu/~gsstark/resume.pdf