Date: Wed, 3 Nov 2004 07:01:00 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: vacuum analyze slows sql query
Message-ID: <20041103120059.GA14880@phlogiston.dyndns.org>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <20041103025031.43795.qmail@web52103.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20041103025031.43795.qmail@web52103.mail.yahoo.com>
User-Agent: Mutt/1.5.6+20040722i

On Tue, Nov 02, 2004 at 06:50:31PM -0800, patrick ~ wrote:
> We have a nightly "garbage collection" process that runs and purges
> any old data.  After this process a 'vacuum analyze' is kicked off
> (regardless of whether or not any data was actually purged).
> 
> At this point I should mention that our customer sites are running
> PostgreSQL 7.1.3; however, I am able to reproduce the issue on 7.4.2.

A 7.1 system takes an exclusive lock on any VACUUM.  It's the same as
VACUUM FULL in 7.4.  Nothing you can do to make that not be sluggish. 
You want to get those sites off 7.1 anyway.  At the very least, you
should be aware of xid exhaustion which can be prevented in 7.1 only
with an initdb and complete restore.  Failure to accommodate that
will mean that one day your databases will just disappear.

Current VACUUM certainly does impose a serious I/O load; this is the
reason for the vacuum setting tweaks in 8.0.  See the -hackers
archives (from more than a year ago now) for (for instance) Jan
Wieck's discussion of his feature and the subsequent debates.

> I noticed that a freshly created db with freshly inserted data (from
> a previous pg_dump) would result in quite fast results.  However,
> after running 'vacuum analyze' the very same query slowed down about
> 1250x (Time: 1080688.921 ms vs Time: 864.522 ms).
> 

My best guess is that there's something going on inside your
function.  I'd be looking for locks here, though.  That makes no
sense, given that you've only 78 rows being returned.  BTW, this
topic should probably be better pursued on -performance.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.
		--J.D. Baldwin