Postgres Professional
en

Re: Question on REINDEX

От: Tom Lane
Тема: Re: Question on REINDEX
Дата: ,
Msg-id: 25363.1113855234@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Question on REINDEX  (Josh Berkus)
Список: pgsql-performance

Скрыть дерево обсуждения

Question on REINDEX  (Bill Chandler, )
 Re: Question on REINDEX  (Josh Berkus, )
  Re: Question on REINDEX  (Tom Lane, )
 Re: Question on REINDEX  (Bruno Wolff III, )
 Re: Question on REINDEX  ("Tambet Matiisen", )
  Re: Question on REINDEX  (Tom Lane, )
   Re: Question on REINDEX  (Alvaro Herrera, )
    Re: Question on REINDEX  (Tom Lane, )
  Re: Question on REINDEX  (Josh Berkus, )
   Re: Question on REINDEX  (Tom Lane, )
    Re: Question on REINDEX  (Josh Berkus, )
     Re: Question on REINDEX  (Tom Lane, ) 
Josh Berkus <> writes:
>> 1) When is it necessary to run REINDEX or drop/create
>> an index?  All I could really find in the docs is:

> If you need to VACUUM FULL, you need to REINDEX as well.  For example, if you
> drop millions of rows from a table.

That's probably a pretty good rule of thumb.  It's worth noting that
VACUUM FULL tends to actively bloat indexes, not reduce them in size,
because it has to create new index entries for the rows it moves before
it can delete the old ones.  So if a VACUUM FULL moves many rows you
are likely to see the indexes get bigger not smaller.

> Better to up your max_fsm_pages and do regular VACUUMs regularly and
> frequently so that you don't have to REINDEX at all.

Yes, definitely.  Also consider using CLUSTER rather than VACUUM FULL
when you need to clean up after massive deletions from a table.  It's
not any less intrusive in terms of locking, but it's often faster and it
avoids the index bloat problem (since it effectively does a REINDEX).

            regards, tom lane

В списке pgsql-performance по дате сообщения:

Предыдущее:
От: Bruce Momjian
Дата:
Сообщение: Re: How to improve db performance with $7K?
Следующее:
От: "Joshua D. Drake"
Дата:
Сообщение: Re: Foreign key slows down copy/insert