Re: [PERFORM] encouraging index-only scans

On Fri, Jan 31, 2014 at 10:22 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Thu, Sep 19, 2013 at 02:39:43PM -0400, Robert Haas 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.
>>
>> Thoughts?  Let the hurling of rotten tomatoes begin.
>
> Robert, where are we on this?  Should I post a patch?

I started working on this at one point but didn't finish the
implementation, let alone the no-doubt-onerous performance testing
that will be needed to validate whatever we come up with.  It would be
really easy to cause serious regressions with ill-considered changes
in this area, and I don't think many people here have the bandwidth
for a detailed study of all the different workloads that might be
affected here right this very minute.  More generally, you're sending
all these pings three weeks after the deadline for CF4.  I don't think
that's a good time to encourage people to *start* revising old
patches, or writing new ones.

I've also had some further thoughts about the right way to drive
vacuum scheduling.  I think what we need to do is tightly couple the
rate at which we're willing to do vacuuming to the rate at which we're
incurring "vacuum debt".  That is, if we're creating 100kB/s of pages
needing vacuum, we vacuum at 2-3MB/s (with default settings).  If
we're creating 10MB/s of pages needing vacuum, we *still* vacuum at
2-3MB/s.  Not shockingly, vacuum gets behind, the database bloats, and
everything goes to heck.  The rate of vacuuming needs to be tied
somehow to the rate at which we're creating stuff that needs to be
vacuumed.  Right now we don't even have a way to measure that, let
alone auto-regulate the aggressiveness of autovacuum on that basis.

Similarly, for marking of pages as all-visible, we currently make the
same decision whether the relation is getting index-scanned (in which
case the failure to mark those pages all-visible may be suppressing
the use of index scans or making them less effective) or whether it's
not being accessed at all (in which case vacuuming it won't help
anything, and might hurt by pushing other pages out of cache).  Again,
if we had better statistics, we could measure this - counting heap
fetches for actual index-only scans plus heap fetches for index scans
that might have been planned index-only scans but for the relation
having too few all-visible pages doesn't sound like an impossible
metric to gather.  And if we had that, we could use it to trigger
vacuuming, instead of guessing.

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