Message-ID: <42E60939.9010905@oli.tudelft.nl>
Date: Tue, 26 Jul 2005 11:58:17 +0200
From: Jochem van Dieten <jochemd@oli.tudelft.nl>
Organization: OnLine Internet
User-Agent: Mozilla Thunderbird 1.0.5 (Windows/20050711)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: "Vacuum Full Analyze" taking so long
References: 
 <C45835824D00A844BBD0F032D5CDED920116DC12@pisgana01sxch01.ana.firstamdata.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Tomeh, Husam wrote:
> The other question I have. What would be the proper approach to rebuild
> indexes. I re-indexes and then run vacuum/analyze. Should I not use the
> re-index approach, and instead, drop the indexes, vacuum the tables, and
> then create the indexes, then run analyze on tables and indexes?? 

If you just want to rebuild indexes, just drop and recreate.

However, you are also running a VACUUM FULL, so I presume you 
have deleted a significant number of rows and want to recover the 
space that was in use by them. In that scenario, it is often 
better to CLUSTER the table to force a rebuild. While VACUUM FULL 
moves the tuples around inside the existing file(s), CLUSTER 
simply creates new file(s), moves all the non-deleted tuples 
there and then swaps the old and the new files. There can be a 
significant performance increase in doing so (but you obviously 
need to have some free diskspace).
If you CLUSTER your table it will be ordered by the index you 
specify. There can be a performance increase in doing so, but if 
you don't want to you can also do a no-op ALTER TABLE and change 
a column to a datatype that is the same as it already has. This 
too will force a rewrite of the table but without ordering the 
tuples.

So in short my recommendations:
- to rebuild indexes, just drop and recreate the indexes
- to rebuild everything because there is space that can 
bepermanently reclaimed, drop indexes, cluster or alter the 
table, recreate the indexes and anlyze the table

Jochem