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 =:->
signature.asc
Description: OpenPGP digital signature