From: Decibel! <decibel@decibel.org>
To: Bill Moran <wmoran@collaborativefusion.com>
Cc: Steven Flatt <steven.flatt@gmail.com>
Cc: Vivek Khera <vivek@khera.org>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: When/if to Reindex
Date: Wed, 8 Aug 2007 15:35:54 -0500
Message-ID: <20070808203554.GP20424@nasby.net> (raw)
In-Reply-To: <20070808152757.9eb9bbcc.wmoran@collaborativefusion.com>
References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
<38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org>
<357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com>
<20070808152757.9eb9bbcc.wmoran@collaborativefusion.com>
On Wed, Aug 08, 2007 at 03:27:57PM -0400, Bill Moran wrote:
> I've had similar experience. One thing you didn't mention that I've noticed
> is that VACUUM FULL often bloats indexes. I've made it SOP that
> after application upgrades (which usually includes lots of ALTER TABLES and
> other massive schema and data changes) I VACUUM FULL and REINDEX (in that
> order).
You'd be better off with a CLUSTER in that case. It'll be faster, and
you'll ensure that the table has optimal ordering.
> Lots of ALTER TABLEs seem to bloat the database size considerably, beyond
> what normal VACUUM seems to fix. A FULL seems to fix that, but it appears
> to bloat the indexes, thus a REINDEX helps.
Hrm, are you sure that's still true? I just did an ALTER TABLE ... TYPE
and it created a new file, meaning no bloating.
> I would expect that setting fillfactor to 100 will encourage indexs to bloat
> faster, and would only be recommended if you didn't expect the index contents
> to change?
Yes.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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: decibel@decibel.org, wmoran@collaborativefusion.com, steven.flatt@gmail.com, vivek@khera.org
Subject: Re: When/if to Reindex
In-Reply-To: <20070808203554.GP20424@nasby.net>
* 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