Message-Id: <48F88694.EE98.0025.0@wicourts.gov>
Date: Fri, 17 Oct 2008 12:35:32 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Tory M Blue" <tmblue@gmail.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Index bloat, reindex weekly, suggestions etc?
References: <8a547c840810170905m7323dbd8x90de7dd004abc7d4@mail.gmail.com>
	<48F8773F.EE98.0025.0@wicourts.gov>
	<8a547c840810170947p52075b0cmadc8c2060b38cfa4@mail.gmail.com>
In-Reply-To: <8a547c840810170947p52075b0cmadc8c2060b38cfa4@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

>>> "Tory M Blue" <tmblue@gmail.com> wrote: 
> DETAIL:  A total of 501440 page slots are in use (including
overhead).
> 501440 page slots are required to track all free space.
> Current limits are:  1087500 page slots, 430 relations, using 6401
kB.
 
As already pointed out, that's a lot of free space.  You don't use
VACUUM FULL on this database, do you?  That would keep the data
relatively tight but seriously bloat indexes, which is consistent with
your symptoms.  VACUUM FULL should not be used routinely, it is
basically for recovery from serious heap bloat when you don't have
space for another copy of the data, and it should usually be followed
by a REINDEX to clean up the index bloat it causes.
 
> And effective_cache, ya I didn't change that as I wanted to see how
> the results were with just adding memory and in all honesty it was
> night and day without changing that param, what will modifying that
> param accomplish?
 
It may allow PostgreSQL to pick more efficient plans for some of your
queries.  Be honest with it about the available resources and give it
the chance.  In particular, you may see fewer queries resorting to
sequential scans of entire tables.
 
-Kevin