Re: vacuum

On Thu, 8 Jan 2004, Guillaume Houssay wrote:

> I am using a 7.3.4.
>
> just simple questions ;=)
>
> 1.when I launch a vacuum verbose analyze on my database. Does it
> re-cretae all the indexes of each table ?

No, vacuuming (harvesting dead tuples from tables) and analyzing
(determining the statistical layout of your data) do not cause any form of
reindexing.

> 2.In addition to the vacuum, do I have to re-index my database using
> REINDEX

Not usually. Generally reindexing is used to fix broken / corrupted
indexes, like the 7.3 docs say. However...

> 3.Is REINDEX only used for created corrupt indexes (as I understood
> from 7.3 documentation) or can it be used to re-index a database or a
> table (as I understood from documentation for 7.4)

somewhere before 7.3 came out an issue with index growth in monotonically
increasing indexes was noted and the two standard ways to fix it were to
either drop / recreate the index or reindex the index. the problem was
that with an index that increased in only one direction, the old pages
that held the older entries were never freed or collapsed down. I had a
1.5 Meg table that was updated every day, and it had an 85 meg index
hanging on the side due to this problem.

The short term solution for postgresql up to and including version 7.3.5
is to reindex indexes that experience this problem. As of 7.4, empty
pages are now collapsed and reclaimed. Note that sparsely populated pages
are NOT collapsed, and therefore there is still the possiblity that you
could be hit by sparsely populated indexes that are larger than optimal,
but that chance is greatly reduced for most, if not nearly all,
situations now in 7.4.