Re: Freebsd vs linux and hardware question

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.

--
Josh Berkus
Aglio Database Solutions
San Francisco