From: Christopher Browne <cbbrowne@acm.org>
Subject: Re: REINDEX DATABASE
Date: Tue, 26 Jul 2005 09:19:22 -0400
Organization: cbbrowne Computing Inc
Lines: 32
Message-ID: <m3vf2xk8md.fsf@mobile.int.cbbrowne.com>
References: <42E521DF.4000804@rusa.at> <20050725174952.GA23263@wolff.to>
	<42E5E90E.8090604@bankir.ru>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Jumbo Shrimp,
	berkeley-unix)
Cancel-Lock: sha1:RlOMlwTdYGpciizYfkebrXP/wLI=
To: pgsql-sql@postgresql.org

> 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...
-- 
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/rdbms.html
Rules of  the Evil  Overlord #153.  "My Legions of  Terror will  be an
equal-opportunity employer. Conversely, when  it is prophesied that no
man  can defeat  me, I  will  keep in  mind the  increasing number  of
non-traditional gender roles." <http://www.eviloverlord.com/>