Date: Wed, 18 Jul 2007 13:52:51 -0500
From: "Jim C. Nasby" <decibel@decibel.org>
To: Steven Flatt <steven.flatt@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: When/if to Reindex
Message-ID: <20070718185251.GD39272@nasby.net>
References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="/YEZ/b5VMxfka8q2"
Content-Disposition: inline
In-Reply-To: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
User-Agent: Mutt/1.5.13 (2006-08-11)


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

On Wed, Jul 18, 2007 at 01:08:30PM -0400, Steven Flatt wrote:
> We're using Postgres 8.2.4.
>=20
> I'm trying to decide whether it's worthwhile to implement a process that
> does periodic reindexing.  In a few ad hoc tests, where I've tried to set=
 up
> data similar to how our application does it, I've noticed decent performa=
nce
> increases after doing a reindex as well as the planner being more likely =
to
> choose an index scan.
>=20
> Some background: we make extensive use of partitioned tables.  In fact, I=
'm
> really only considering reindexing partitions that have "just closed".  In
> our simplest/most general case, we have a table partitioned by a timestamp
> column, each partition 24 hours wide.  The partition will have an index on
> the timestamp column as well as a few other indexes including a primary k=
ey
> index (all b-tree).  Is there a programmatic way I can decide, upon the
> "closing" of a partition, which, if any, of these indexes will benefit fr=
om
> a reindex?  Can I determine things like average node density, node depth,=
 or
> any other indication as to the quality of an index?  Will pg_class.relpag=
es
> be any help here?

Looking at that stuff will help determine if the index is bloated, or if
it's just bigger than optimal. Once you're done writing to an index, it
might be worth reindexing with a fillfactor of 100% to shrink things
down a bit.

> Is it a simple matter of running some queries, reindexing the table, then
> running the queries again to determine overall performance change?  If so,
> what queries would exercise this best?
>=20
> Just trying to determine if the extra cost of reindexing newly closed
> partitions will be worth the performance benefit of querying the data.
> Reindexing a table with a day's worth of data is taking on the order of a
> few hours (10s of millions of rows).
>=20
> The docs say that:
>=20
> "...for B-tree indexes a freshly-constructed index is somewhat faster to
> access than one that has been updated many times, because logically adjac=
ent
> pages are usually also physically adjacent in a newly built index... It
> might be worthwhile to reindex periodically just to improve access speed."

That's the other consideration, though if you're seeing a big difference
I suspect it's an issue of indexes fitting in cache or not.
--=20
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

--/YEZ/b5VMxfka8q2
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFGnmGDdO30qud8SkgRAqC7AJwMJ0Vs9tpAiT4Gz3Orib/dcx7LwACfZbuq
B5lwSCblMAazI+SeeweZ0nI=
=gOhF
-----END PGP SIGNATURE-----

--/YEZ/b5VMxfka8q2--