How to Find Cause of Long Vacuum Times - NOOB Question

From: Yudhvir Singh Sidhu <ysidhu(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: How to Find Cause of Long Vacuum Times - NOOB Question
Date: 2007-05-05 22:57:25
Message-ID: 463D0BD5.3010404@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Steinar H. Gunderson 2007-05-05 23:40:14 Re: How to Find Cause of Long Vacuum Times - NOOB Question
Previous Message Sebastian Hennebrueder 2007-05-05 15:54:33 Re: Feature Request --- was: PostgreSQL Performance Tuning