Re: Re: Need for rebuilding index after many deletions?

>>> With Postgres 6.5.2, if a table has undergone several row deletions,
>>> does it make sense/ is it needed to rebuild the index?

If you've deleted a large fraction of the rows in the table, dropping
and recreating the indexes would be worth doing, because VACUUM by
itself won't reclaim unused space in an index.

BTW, many people have found that
drop indexes;
VACUUM;
rebuild indexes;
is actually faster than letting VACUUM try to vacuum the indexes.
One of the things on the to-do list is to redesign VACUUM so that it
handles indexes this way internally.

> I could be wrong, but you also need the -a (analyze) option to vacuum to
> have it "re-think" it's lookup strategies, right?

Doesn't have anything to do with index efficiency. If the table stats
(column min/max values, etc) have changed a lot then it's worth doing
"analyze" again to update pg_statistic. But in many cases you don't
need to do "analyze" nearly as often as basic "vacuum".

regards, tom lane