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

Reply via email to