Re: Vacuum, Freeze and Analyze: the big picture

> "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? And why as of 9.3
> instead of 9.2?

As of 9.2, that was a typo.

Allvisible only gets set if there was some reason for VACUUM to visit
the page anyway, no? Which means that an insert-only or insert-mostly
table doesn't get set allvisible until FREEZE. And insert-only tables
are usually very large, and thus really *need* index-only scan.

Hmmm. I should rewrite that item entirely. It has nothing to do with
FREEZE, really.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com