Date: Tue, 26 Jul 2005 17:17:54 +0300 (EEST)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: REINDEX DATABASE
In-Reply-To: <m3vf2xk8md.fsf@mobile.int.cbbrowne.com>
Message-ID: <Pine.LNX.4.44.0507261701340.32187-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-7
Content-Transfer-Encoding: 8BIT

O Christopher Browne έγραψε στις Jul 26, 2005 :

> > Hello
> >
> > Would you like to advice to use REINDEX DATABASE on regular basis ?
> >
> > if (yes)
> > how it should be connected with VACUUM FULL ANALYZE which is run
> > regularly ?  (reindex before vacuum or vacuum before reindex?)
> >
> > else
> > haw to determine _when_ to run REINDEX ?
> 
> If you are doing ordinary VACUUM ANALYZE frequently enough, it
> shouldn't be necessary to either VACUUM FULL or REINDEX.
> 
> Back in the 7.2 days, there were sorts of update patterns that would
> mandate reindexing every so often, as you could get cases where index
> pages would be very sparsely populated.  That was alleviated in
> version 7.3, I believe, and was clearly evident in 7.4.
> 
> You know you need to REINDEX if analysis of an index shows that it is
> sparsely populated.  This generally shows up if you do an analyze on
> the table and find an index has more pages than tuples.
> 
> But if you run VACUUM reasonably frequently, this shouldn't be
> necessary...

I vacuum analyze every hour, however after reindexdb 
my (currently) 2.47 GB db is reduced to 2.37 GB,
thus helping both db-wise and freebsd_cache-wise boost performance.

All the above in 7.4.6.

I am not arguing that i have done detailed analysis of the 
situation, i'm just saying that i have witnessed a performance gain after
running contrib/reindexdb every month (or so).

> 

-- 
-Achilleus