DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=SXkgNUYsxevYxR4khun/R7OpYiFnMPicIVVV02Fj2TlGmjdjoS/ilN52JqLcczMxti472KXGcJeTMDZPU8YaGV/bq5aZ0OVYkNwgVOlR89pNn3GIe76/VK8uSaViruSF+0BvsDVcUROge+YPelTNjs9HEGfUKuTUUvY2THdAIug=
Message-ID: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
Date: Wed, 18 Jul 2007 13:08:30 -0400
From: "Steven Flatt" <steven.flatt@gmail.com>
To: pgsql-performance@postgresql.org
Subject: When/if to Reindex
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_90251_6347561.1184778510657"

------=_Part_90251_6347561.1184778510657
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

We're using Postgres 8.2.4.

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 performance
increases after doing a reindex as well as the planner being more likely to
choose an index scan.

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 key
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 from
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.relpages
be any help here?

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?

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).

The docs say that:

"...for B-tree indexes a freshly-constructed index is somewhat faster to
access than one that has been updated many times, because logically adjacent
pages are usually also physically adjacent in a newly built index... It
might be worthwhile to reindex periodically just to improve access speed."

Thanks,
Steve

------=_Part_90251_6347561.1184778510657
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div>We&#39;re using Postgres 8.2.4.</div>
<div>&nbsp;</div>
<div>I&#39;m trying to decide whether it&#39;s worthwhile to implement a process that does periodic reindexing.&nbsp; In a few ad hoc tests, where I&#39;ve tried to set up data similar to how our application does it, I&#39;ve noticed decent performance increases after doing a reindex as well as the planner being more likely to choose an index scan.
</div>
<div>&nbsp;</div>
<div>Some background: we make extensive use of partitioned tables.&nbsp; In fact, I&#39;m really only considering reindexing partitions that have &quot;just closed&quot;.&nbsp; In our simplest/most general&nbsp;case, we have a table partitioned by a timestamp column, each partition 24 hours wide.&nbsp; The partition will have an index on the timestamp column as well as a few other indexes including a primary key index (all b-tree).&nbsp; Is there a programmatic way I can decide, upon the &quot;closing&quot; of a partition, which, if any, of these indexes will benefit from a reindex?&nbsp; Can I determine things like average node density, node depth, or any other indication as to the quality of an index?&nbsp; Will pg_class.relpages be any help here?
</div>
<div>&nbsp;</div>
<div>Is it a simple matter of running some queries, reindexing the table, then running the queries again to determine overall performance change?&nbsp; If so, what queries would exercise this best?</div>
<div>&nbsp;</div>
<div>Just trying to determine if the&nbsp;extra cost of reindexing newly closed partitions will be worth the performance benefit of querying the data.&nbsp; Reindexing a table with a day&#39;s worth of data is taking on the order of a few hours (10s of millions of rows).
</div>
<div>&nbsp;</div>
<div>The docs say that:</div>
<div>&nbsp;</div>
<div>&quot;...for B-tree indexes a freshly-constructed index is somewhat faster to access than one that has been updated many times, because logically adjacent pages are usually also physically adjacent in a newly built index... It might be worthwhile to reindex periodically just to improve access speed.&quot;
</div>
<div>&nbsp;</div>
<div>Thanks,</div>
<div>Steve</div>
<div>&nbsp;</div>

------=_Part_90251_6347561.1184778510657--