> What version of postgres?
8.0.2 ... but I think I've seen this before on 7.3 ...
> There are a few possibilities. If you are having a lot of updates to the
> table, you can get index bloat. And vacuum doesn't fix indexes. You have
> to "REINDEX" to do that. Though REINDEX has the same lock that VACUUM
> FULL has, so you need to be a little careful with it.
> Probably better is to do CLUSTER, as it does a REINDEX and a sort, so
> your table ends up nicer when you are done.
Thanks, will try those next time this problem crops up (i just deleted /
recreated the database to speed things for its users in the office ...
probably should have held off to see if I could find a solution first!).
Yes, the database / table-in-question does have a lot of updates, deletes,
and new rows (relatively speaking for a small business).
Would CLUSTER / REINDEX still have an effect if our queries were done via
sequential scan? This is a old database (as in built by me when i was just
starting to learn unix / postgres) so the database design is pretty horrible
(little normalisation, no indexes).
Have taken Chris's advice onboard too and setup cron to do a vacuumdb hourly
instead of my weekly vacuum.
Cheers,
Dave.
