DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:subject:content-type:content-transfer-encoding;
	b=b+YKn+x/04uef0qOU2CZPSfKx1wz8E0scrsRfLUV/zVwkq1AdPgPcou8oLBcnQowXJnNG6gdIPTg6seljx1rKFRi6O0dcBoW3TAKfClN3dJ9kgdxAxdDvv5MlCuQ9EoMW1WC4at7OshDdYTQIBRr5x+HxS4ncaF0dDStMLaHiFU=
Message-ID: <463D0BD5.3010404@gmail.com>
Date: Sat, 05 May 2007 15:57:25 -0700
From: Yudhvir Singh Sidhu <ysidhu@gmail.com>
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: How to Find Cause of Long Vacuum Times - NOOB Question
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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