DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=CzgyTvb1E6mUfSWS208t/3/gC1yoDWehQpKFcST3NJLznebSGsryqGuQ88lBw+0myiI7t3y8/JMriLaWZxh/RWFMF4BxhKWeeuoIHC3N7xGzi+PYFm1l68IQMSL4cQ2saG5ZWRHRXIiSpi74NfG+bqMKqci9LDVeWKUkYlK8ilE=
Message-ID: <463DA162.8050605@gmail.com>
Date: Sun, 06 May 2007 02:35:30 -0700
From: Yudhvir Singh Sidhu <ysidhu@gmail.com>
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
To: Yudhvir Singh Sidhu <ysidhu@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: How to Find Cause of Long Vacuum Times - NOOB Question
References: <463D0BD5.3010404@gmail.com> <20070505234014.GA15522@uio.no>
	<463D5F28.5060006@gmail.com> <20070506091707.GB22451@uio.no>
In-Reply-To: <20070506091707.GB22451@uio.no>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Steinar H. Gunderson wrote:
> On Sat, May 05, 2007 at 09:52:56PM -0700, Yudhvir Singh Sidhu wrote:
>   
>> Here is what I think the story is:
>> a.  Large amounts of rows are added to and deleted from a table - daily. 
>> With this much activity, the statistics get out of whack easily. That's 
>> where ANALYZE or VACUUM ANALYZE would help with query speed.
>>     
>
> You are still confusing ANALYZE and VACUUM. Those are distinct operations,
> and help for different reasons.
>
> Deleting rows leaves "dead rows" -- for various reasons, Postgres can't
> actually remove them from disk at the DELETE point. VACUUM scans through the
> disk, searching for dead rows, and actually marks them as removed. This
> results in faster query times since there will be less data overall to search
> for.
>
> ANALYZE updates the statistics, as mentioned. Yes, by adding or deleting a
> lot of data, the estimates can get out of whack, leading to bad query plans.
>
>   
>> b.  If ANALYZE does not have a direct impact on vacuum times, what does? 
>> Meaning what in this EXPLAIN/ANALYZE and Indexing world would have a 
>> direct impact?
>>     
>
> Improving your vacuum speed is overall not that easy (although there are
> options you can tweak, and you can of course improve your hardware).  The
> simplest thing to do is simply to vacuum more often, as there will be less
> work to do each time. It's a bit like cleaning your house -- it might be
> less work to clean it once a year, but it sure is a better idea in the long
> run to clean a bit every now and then. :-)
>
> /* Steinar */
>   

Thanks for the clarification Steingar,

I'll try some of the things we discussed out on Monday and will let you 
guys know what happens. I know I am confusing some concepts but I am new 
to this db and to tuning in general. I am excited about this new 
adventure and really appreciate the level of support I have seen.

Yudhvir