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
