From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: Robert Treat <xzilla@users.sourceforge.net>
Cc: Clarence Gardner <clarence@silcom.com>
Cc: Oleg Lebedev <oleg.lebedev@waterford.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: slow query
Date: Mon, 24 Feb 2003 15:50:35 -0500
Message-ID: <5421.1046119835@sss.pgh.pa.us> (raw)
In-Reply-To: <200302241045.20856.josh@agliodbs.com>
References: <Pine.LNX.4.44.0302240925360.25665-100000@liberty.sba2.netlojix.net>
<1046111434.1014.165.camel@camel>
<200302241045.20856.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
> ... However, I agree that good FSM tuning and frequent
> regular VACUUMs can greatly extend the period required for running FULL.
> I have not found, though, that this does anything to prevent the need for
> REINDEX on frequently-updated tables. How about you, Robert?
As of 7.3, FSM doesn't have anything to do with indexes. If you have
index bloat, it's because of the inherent inability of btree indexes to
reuse space when the data distribution changes over time. (Portions of
the btree may become empty, but they aren't recycled.) You'll
particularly get burnt by indexes that are on OIDs or sequentially
assigned ID numbers, since the set of IDs in use just naturally tends to
migrate higher over time. I don't think that the update rate per se has
much to do with this, it's the insertion of new IDs and deletion of old
ones that causes the statistical shift. The tree grows at the right
edge, but doesn't shrink at the left.
As of CVS tip, however, the situation is different ;-). Btree indexes
will recycle space using FSM in 7.4.
regards, tom lane
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: tgl@sss.pgh.pa.us, josh@agliodbs.com, xzilla@users.sourceforge.net, clarence@silcom.com, oleg.lebedev@waterford.org
Subject: Re: slow query
In-Reply-To: <5421.1046119835@sss.pgh.pa.us>
* 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