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).
Well, my first recommendation is to put in some indexes. :) They are relatively easy to setup and can drastically improve select performance. What version of postgres are you using? What does it say at the end of "VACUUM FULL ANALYZE VERBOSE", that should tell you how many free pages were reclaimed and how big your free space map should be. If you only did 1 VACUUM FULL, you might try another, as it sounds like your tables aren't properly filled. I'm pretty sure vacuum only removes empty pages/marks locations for the free space map so they can be re-used, while vacuum full will move entries around to create free pages. It sounds like it didn't do it properly. But even so, CLUSTER is still your friend, as it allows you to "presort" the rows in your tables.
Have taken Chris's advice onboard too and setup cron to do a vacuumdb hourly instead of my weekly vacuum. Cheers, Dave.
Description: OpenPGP digital signature