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

On Wed, Nov 03, 2004 at 10:31:33AM -0800, patrick ~ wrote:

> Just to clarify, the sliggishness isn't only during the vacuum
> period.  There are more more serious issues during the vacuum,
> but i am not touching on those. The sluggishness is persistant
> throughout daily operations.

Then I suspect you have two problems, not one.  The locking in 7.1
will certainly cause the timeouts during vacuum, but won't explain
the other items.  Someone else suggested REINDEX, also, which is
likely needed.  But again, you'll get more useful remarks from the
-performance list.

> Yes, the plan is to upgrade them with new release of our product.
> I didn't know about the xid exhaustion problem.  I'll need to
> search the mailing list archives.

You can learn a bit about this in the _current_ version of the docs
under regular maintenance.

> Again to clarify, the output I pasted was from my standalone
> PostgreSQL box.  That is, it wasn't being used other than those
> quries being executed.

That's even worse.  I certainly can't explain your results, then. 
You really want to move to the -performance list for that.

> I don't know if you looked at my stored function, but there are
> no locks in it (no explicit ones anyway).

Foreign keys are one well-known area of locking surprises.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well. 
		--Dennis Ritchie