Re: More Praise for 7.4RC2

Are there any guidelines on how often one should do a reindex?
----- Original Message -----
From: Reece Hart
To: scott.marlowe
Cc: pgsql-general
Sent: Thursday, November 13, 2003 12:50 PM
Subject: Re: [GENERAL] More Praise for 7.4RC2

On Thu, 2003-11-13 at 10:09, scott.marlowe wrote:
Do you vacuum full every so often? If not, and if you've been overflowing
your fsm, then your tables will just grow without shrinking.
Also, index growth could be a problem.
Hmm. I didn't realize that I needed to vacuum full as well -- I thought vacuum was sufficient for performance gains, and that full reclaimed space but didn't result in significant performance gains. I have reindexed infrequently, but since that locks the table I didn't do that (or vacuum full) often. I guess I should try out pg_autovacuum, but I think that full vacuums only to prevent XID wraparound (if age>1.5B transactions), but not for compaction (is this correct?).

The real test is to dump the database and reload it to give 7.3.4 a fair
shake.It turns out that I have two copies of this database around at the moment running on 7.3.4. One was a fresh restore, and that's what I used to generate the explain. However, the query was run on the older database which was vacuumed and analyzed (but not vacuum full or reindexed), and on that instance the query took a long time. On the fresh install, it takes 72s. In summary:

7.3.4, long-running db: eons
7.3.4, freshly restored: 72s
7.4RC2, freshly restored: 0.3s

Thanks everyone for feedback and setting me straight. Although the gain isn't as great as I thought, it's still very significant.

-Reece

--
Reece Hart, http://www.in-machina.com/~reece/, GPG:0x25EC91A0