How to Find Cause of Long Vacuum Times - NOOB Question
От | Yudhvir Singh Sidhu |
---|---|
Тема | How to Find Cause of Long Vacuum Times - NOOB Question |
Дата | |
Msg-id | 463D0BD5.3010404@gmail.com обсуждение исходный текст |
Ответы |
Re: How to Find Cause of Long Vacuum Times - NOOB Question
Re: How to Find Cause of Long Vacuum Times - NOOB Question Re: How to Find Cause of Long Vacuum Times - NOOB Question |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: