Re: Question on REINDEX

On Mon, Apr 18, 2005 at 12:21:42 -0700,
Bill Chandler <billybobc1210(at)yahoo(dot)com> wrote:
>
> Running PostgreSQL 7.4.2 on Solaris.
>
> 1) When is it necessary to run REINDEX or drop/create
> an index? All I could really find in the docs is:
>
> "In some situations it is worthwhile to rebuild
> indexes periodically with the REINDEX command. (There
> is also contrib/reindexdb which can reindex an entire
> database.) However, PostgreSQL 7.4 has substantially
> reduced the need for this activity compared to earlier
> releases."

In pathologic cases it is possible to have a lot of empty space on a lot
of your index pages. Reindexing would change that to a smaller number.
In earlier versions, I think it was possible to have completely empty
pages and this happened for patterns of use (new values monotonically
increasing, oldest values deleted first) that were actually seen in
practice.