Обсуждение: Re: Need for rebuilding index after many deletions?

I could be wrong, but you also need the -a (analyze) option to vacuum to
have it "re-think" it's lookup strategies, right?
In article <am.pgsql.general.965411449.47605@illiad.adhesivemedia.com>,
Jeffrey A. Rhines <jrhines@email.com> wrote:
>If you're worried about performance, a vacuum should do the trick,
>right?
>
>Jeff
>
>Patrick Robin wrote:
>>
>> Hi all,
>>
>>    With Postgres 6.5.2,  if a table has undergone several row deletions,
>> does it make sense/ is it needed  to rebuild the index?  Would this be
>> accomplished by
>> dropping it and then recreating it?

>>> 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