Re: Freebsd vs linux and hardware question

On Mon, Sep 22, 2003 at 11:08:56AM -0700, Josh Berkus wrote:
> Dror,
>
> > > > Finally, what about REINDEX?
> >
> > I've run REINDEX once. My understanding is that you don't need to run it
> > on a continous basis (after all shouldn't the tree balance itself on
> > it's one?). Am I wrong with that?
>
> Very.
>
> Indexes, like tables, accumulate dead tuples every time you run a DELETE or
> UPDATE. As a result, they must get re-built periodically, and the best way
> is with the transaction-safe REINDEX.
>
> If you have run REINDEX only once, then chances are your older tables are
> suffering 75% or more index bloat, and the indexes are all but useless.
>
> In 7.4, this will be alleviated because index space will be governed by the
> Free Space Map, and thus with proper tuning should not accumulate more than
> 10-20% dead tuples.

VACUUM seems to remove dead tuples in all the indices too. Does REINDEX
do something else?

Dror

>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco

--
Dror Matalon, President
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com