Primary Key Index Bloat?

Hi,

I have a table with the following usage characteristic:

INSERT bulk data using INSERT INTO ... SELECT ... FROM
-- this table uses a varchar(50) for the PK
-- the PK is rarely (effectively never unless a mistake was made) altered
-- always appending to the existing table; some bulk deletions in the past
-- no toasting of values

UPDATE these records many, many times (but never the PK)

At the moment the table itself (and thus all columns) is showing as size of
just under 5GB
The corresponding index for the PK (a single column, also present on the
table...) is between 8-10GB [1]I actually inadvertently have two indexes, one unique and one not, over the same field. The sizes of these two indexes are the rough endpoints of the 8-10GB range I provided. It appears that both indexes are being chosen for use by the planner. I have never REINDEXed this table that I
can recall.

I am guessing that it is the need for the index to point to new versions of
the physical record that the index is churning so much and causing this kind
of bloat?

[1]: I actually inadvertently have two indexes, one unique and one not, over the same field. The sizes of these two indexes are the rough endpoints of the 8-10GB range I provided. It appears that both indexes are being chosen for use by the planner.
the same field. The sizes of these two indexes are the rough endpoints of
the 8-10GB range I provided. It appears that both indexes are being chosen
for use by the planner.

I am preparing to REINDEX the unique index and DROP the non-unique one over
the same field - probably Tuesday evening. Does everything I am saying here
sound kosher or would someone like me to provide additional information?

The deletions were fairly voluminous when they occurred and so I would have
expected that enough empty pages would have been freed to avoid any
significant index bloat.

I have mostly left VACUUMing to auto-vacuum though the occasional manual one
has been performed. Never performed VACUUM FULL.

Been running 9.0 on Ubuntu 10.04 since day one with a pg_restore load. A
while ago and no comparative metrics available.

I greatly appreciate any sanity checks you can provide.

Thanks!

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Primary-Key-Index-Bloat-tp5778840.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general