Date: Wed, 8 Aug 2007 15:35:54 -0500
From: Decibel! <decibel@decibel.org>
To: Bill Moran <wmoran@collaborativefusion.com>
Cc: Steven Flatt <steven.flatt@gmail.com>, Vivek Khera <vivek@khera.org>,
	Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: When/if to Reindex
Message-ID: <20070808203554.GP20424@nasby.net>
References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
	<38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org>
	<357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com>
	<20070808152757.9eb9bbcc.wmoran@collaborativefusion.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="1EKig6ypoSyM7jaD"
Content-Disposition: inline
In-Reply-To: <20070808152757.9eb9bbcc.wmoran@collaborativefusion.com>
User-Agent: Mutt/1.5.13 (2006-08-11)


--1EKig6ypoSyM7jaD
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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 noti=
ced
> is that VACUUM FULL often bloats indexes.  I've made it SOP that
> after application upgrades (which usually includes lots of ALTER TABLES a=
nd
> 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 bl=
oat
> faster, and would only be recommended if you didn't expect the index cont=
ents
> to change?

Yes.
--=20
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

--1EKig6ypoSyM7jaD
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFGuikqdO30qud8SkgRAr+/AKCOHrHC0eS4RF5b2qQsk80tVF8xYQCeJn8e
TcZ54MMc+MW5hAkSZgWIRDo=
=OKZp
-----END PGP SIGNATURE-----

--1EKig6ypoSyM7jaD--