Re: 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