Originally posted by KenEwald
Periodic reindexing will recover space (especially if you update the base table frequently). Updates force the index to create new leaf blocks, and the old ones just don't get recycled or deleted.
I've realized significant performance increases by rebuilding indexes. However, it's not something you want to do weekly. We do it once a year or as needed .. that seems to be enough.
Analyzing your table will provide more accurate statistics for the cost based optimizer.
What exactly is the difference between analyzing and rebuilding indexes.
As KenEwald pointed out, rebuilding your indexes reshuffles your leaf nodes and removes any leaf nodes that are empty. I usually rebuild indexes after a large number of rows has been deleted or the height gets really large.
Analyze will compute statistics on your data to help the optimizer choose the best execution method. You should analyze your tables whenever the data distribution changes drastically.