I hope someone can help me with this vacuum problem. I can post more
info if needed.
Versions: Postgresql version 8.09 on FreeBSD 6.1
Situation: huge amounts of adds and deletes daily. Running daily vacuums
Problem: Vacuum times jump up from 45 minutes, or 1:30 minutes to 6+
hours overnight, once every 1 to 3 months.
Solutions tried: db truncate - brings vacuum times down. Reindexing
brings vacuum times down.
I know my indexes are getting fragmented and my tables are getting
fragmented. I also know that some of my btree indexes are not being used
in queries. I also know that using "UNIQUE" in a query makes PG ignore
any index.
I am looking for the cause of this. Recently I have been looking at
EXPLAIN and ANALYZE.
1. Running EXPLAIN on a query tells me how my query SHOULD run and
running ANALYZE tells me how it DOES run. Is that correct?
2. If (1) is true, then a difference between the two means my query
plan is messed up and running ANALYZE on a table-level will somehow
rebuild the plan. Is that correct?
3. If (2) is correct, then running ANALYZE on a nightly basis before
running vacuum will keep vacuum times down. Is that correct?
Yudhvir Singh