Skip site navigation (1) Skip section navigation (2)

Re: Freebsd vs linux and hardware question

From: Dror Matalon <dror(at)zapatec(dot)com>
To: sfpug(at)postgresql(dot)org
Subject: Re: Freebsd vs linux and hardware question
Date: 2003-09-22 18:32:21
Message-ID: 20030922183221.GX97268@rlx11.zapatec.com (view raw or flat)
Thread:
Lists: sfpug
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

In response to

Responses

sfpug by date

Next:From: Josh BerkusDate: 2003-09-22 18:45:04
Subject: Re: Freebsd vs linux and hardware question
Previous:From: Josh BerkusDate: 2003-09-22 18:08:56
Subject: Re: Freebsd vs linux and hardware question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group