By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,362 Members | 3,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,362 IT Pros & Developers. It's quick & easy.

vacuum

P: n/a
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 ?

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

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)

Thank you for your help.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.