Re: [PERFORM] encouraging index-only scans

On Tue, Sep 17, 2013 at 7:10 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> I generally think the current logic for triggering VACUUMs via
> autovacuum doesn't really make all that much sense in the days where we
> have the visibility map.

Right now, whether or not to autovacuum is the rest of a two-pronged
test. The first prong is based on number of updates and deletes
relative to table size; that triggers a regular autovacuum. The
second prong is based on age(relfrozenxid) and triggers a
non-page-skipping vacuum (colloquially, an anti-wraparound vacuum).

The typical case in which this doesn't work out well is when the table
has a lot of inserts but few or no updates and deletes. So I propose
that we change the first prong to count inserts as well as updates and
deletes when deciding whether it needs to vacuum the table. We
already use that calculation to decide whether to auto-analyze, so it
wouldn't be very novel. We know that the work of marking pages
all-visible will need to be done at some point, and doing it sooner
will result in doing it in smaller batches, which seems generally
good.

However, I do have one concern: it might lead to excessive
index-vacuuming. Right now, we skip the index vac step only if there
ZERO dead tuples are found during the heap scan. Even one dead tuple
(or line pointer) will cause an index vac cycle, which may easily be
excessive. So I further propose that we introduce a threshold for
index-vac; so that we only do index vac cycle if the number of dead
tuples exceeds, say 0.1% of the table size.

Thoughts? Let the hurling of rotten tomatoes begin.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company