Re: [PERFORM] encouraging index-only scans

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> 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.

+1  I've been thinking of suggesting something along the same lines,
for the same reasons.

 
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company