Re: Index size

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