Index relation size

Discussion:

Index relation size

(too old to reply)

Rigmor Ukuhe

2004-10-25 11:02:46 UTC

Hi,

I have table with size ~35 MB, it has several indexes and couple of them are
well over 500 MB in size (they are indexes with 3-4 columns involved, with
datatypes like varchar, int4 , timestamp, boolean).
Database is VACUUMed nightly, version is 7.2.4 (cant upgrade to newest
version at the moment). Are these
index sizes indicating some problems with our Postgres server?

Rigmor Ukuhe
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 19.10.2004

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Gaetano Mendola

2004-10-25 17:50:59 UTC

Permalink

Post by Rigmor Ukuhe
Hi,
I have table with size ~35 MB, it has several indexes and couple of them are
well over 500 MB in size (they are indexes with 3-4 columns involved, with
datatypes like varchar, int4 , timestamp, boolean).
Database is VACUUMed nightly, version is 7.2.4 (cant upgrade to newest
version at the moment). Are these
index sizes indicating some problems with our Postgres server?

If you do not vacuum full, this is normal.
Using a version > 7.4 then a normal vacuum "shall" be enough.

Regards
Gaetano Mendola

Scott Marlowe

2004-10-26 00:44:33 UTC

Permalink

Assuming you aren't suffering from index bloat, which was a problem in
7.2, this may be normal. To see if you are, try "reindex indexname" and
see if the index shrinks.

Multicolumn indexes being bigger than their table because of the way
they're put together, so it's likely that when you get done reindexing
the index will still be bigger than the table.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org