Thank you all for your great input. It sure helped.
--
Husam
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jochem van
Dieten
Sent: Tuesday, July 26, 2005 2:58 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] "Vacuum Full Analyze" taking so long
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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
**********************************************************************
This message contains confidential information intended only for the
use of the addressee(s) named above and may contain information that
is legally privileged. If you are not the addressee, or the person
responsible for delivering it to the addressee, you are hereby
notified that reading, disseminating, distributing or copying this
message is strictly prohibited. If you have received this message by
mistake, please immediately notify us by replying to the message and
delete the original message immediately thereafter.
Thank you. FADLD Tag
**********************************************************************