From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: REINDEX DATABASE
Date: Tue, 26 Jul 2005 17:17:54 +0300 (EEST)
Message-ID: <Pine.LNX.4.44.0507261701340.32187-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <m3vf2xk8md.fsf@mobile.int.cbbrowne.com>
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
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-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, cbbrowne@acm.org
Subject: Re: REINDEX DATABASE
In-Reply-To: <Pine.LNX.4.44.0507261701340.32187-100000@matrix.gatewaynet.com>
* 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