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?
Date: Fri, 17 Oct 2008 12:35:32 -0500
Message-ID: <48F88694.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <8a547c840810170947p52075b0cmadc8c2060b38cfa4@mail.gmail.com>
References: <8a547c840810170905m7323dbd8x90de7dd004abc7d4@mail.gmail.com>
<48F8773F.EE98.0025.0@wicourts.gov>
<8a547c840810170947p52075b0cmadc8c2060b38cfa4@mail.gmail.com>
>>> "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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, tmblue@gmail.com
Subject: Re: Index bloat, reindex weekly, suggestions etc?
In-Reply-To: <48F88694.EE98.0025.0@wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox