Subject: Re: slow query
From: Robert Treat <xzilla@users.sourceforge.net>
To: Josh Berkus <josh@agliodbs.com>
Cc: Clarence Gardner <clarence@silcom.com>,
	Oleg Lebedev <oleg.lebedev@waterford.org>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <200302241045.20856.josh@agliodbs.com>
References: 
 <Pine.LNX.4.44.0302240925360.25665-100000@liberty.sba2.netlojix.net>
	<1046111434.1014.165.camel@camel>
	<200302241045.20856.josh@agliodbs.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 24 Feb 2003 14:58:57 -0500
Message-Id: <1046116737.1014.284.camel@camel>
Mime-Version: 1.0

On Mon, 2003-02-24 at 13:45, Josh Berkus wrote:
> Robert,
> 
> > Actually if you are vacuuming frequently enough, it can (and should*)
> > obviate a vacuum full. Be aware that frequently enough might mean really
> > frequent, for instance I have several tables in my database that update
> > every row within a 15 minute timeframe, so I run a "lazy" vacuum on
> > these tables every 10 minutes. This allows postgresql to reuse the space
> > for these tables almost continuously so I never have to vacuum full
> > them.
> 
> This would assume absolutely perfect FSM settings, and that the DB never gets 
> thrown off by unexpected loads.  I have never been so fortunate as to work 
> with such a database.   However, I agree that good FSM tuning and frequent 
> regular VACUUMs can greatly extend the period required for running FULL.
> 

It's somewhat relative. On one of my tables, it has about 600 rows, each
row gets updated within 15 minutes. I vacuum it every 10 minutes, which
should leave me with around 1000 tuples (dead and alive) for that table,
Even if something overload the updates on that table, chances are that I
wouldn't see enough of a performance drop to warrant a vacuum full. Of
course, its a small table, so YMMV.  I think the point is though that if
your running nightly vacuum fulls just to stay ahead of the game, your
not maintaining the database optimally.

> I have not found, though, that this does anything to prevent the need for 
> REINDEX on frequently-updated tables.  How about you, Robert?
> 

Well, this touches on a different topic. On those tables where I get
"index bloat", I do have to do REINDEX's. But that's not currently
solvable with vacuum (remember indexes dont even use FSM) though IIRC
Tom & Co. have done some work toward this for 7.4

Robert Treat