Re: slow growing table

Jone C wrote:

>>On second thought... Does a VACUUM FULL help? If so, you might want to
>>increase your FSM settings.
>>
>>
>
>Thank you for the reply, sorry for delay I was on holiday.
>
>I tried that it had no effect. I benchmarked 2x before, peformed
>VACUUM FULL on the table in question post inserts, then benchmarked 2x
>after. Same results...
>
>Should I try your suggestion on deleting the indexes? This table needs
>to be accessible for reads at all times however though...
>
>thank you kindly
>
>

I believe dropping an index inside a transaction is only visible to that
transaction. (Can someone back me up on this?)
Which means if you did:

BEGIN;
DROP INDEX <index in question>;
CREATE INDEX <same index> ON <same stuff>;
COMMIT;

The only problem is that if you are using a unique or primary key index,
a foreign key which is referencing that index would have to be dropped
and re-created as well. So you could have a pretty major cascade effect.

A better thing to do if your table only has one (or at least only a few)
indexes, would be to CLUSTER, which is effectively a VACUUM FULL + a
REINDEX (plus sorting the rows so that they are in index order). It
holds a full lock on the table, and takes a while, but when you are
done, things are cleaned up quite a bit.

You might also try just a REINDEX on the indexes in question, but this
also holds a full lock on the table. (My DROP + CREATE might also as
well, I'm not really sure, I just think of it as a way to recreate
without losing it for other transactions)

John
=:->