Re: Vacuum, Freeze and Analyze: the big picture

On 2013-05-30 20:01:01 +0100, Thom Brown wrote:
> "Problem: As of 9.3, there's a significant benefit to vacuum freezing
> tables early so that index-only scan is enabled, since freezing also
> updates the visibility map. However, with default settings, such
> freezing only happens for data which is very old. This means that
> index-only scan is less effective than it could be for tables which
> have relatively infrequent updates and deletes."

> Why specifically VACUUM FREEZE rather than regular VACUUM? I thought
> regular VACUUM updated the visibility map too?

It does. It's after all what it uses to decide which parts of the table
to scan if not doing a full table vacuum.

> And why as of 9.3 instead of 9.2?

Mabe because 9.3 updates the vm quicker than earlier version by checking
whether all tuples are visible after we've actually removed the dead
tuples.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services