Re: Set visibility map bit after HOT prune

On Wed, Dec 19, 2012 at 11:12 PM, Pavan Deolasee
<pavan.deolasee@gmail.com> wrote:
> Yeah, VM buffer contention can become prominent if we break the
> invariant that page level bit status implies the vm bit status, at
> least when its clear.OTOH IMHO we need some mechanism to address the
> issue of aggressive clearing of the VM bits, but a very lame
> corresponding set operation.

I agree.

> Today we don't have much contention on
> the VM page, but we must be sacrificing its usability in return. IOS
> as well as vacuum optimizations using VMs will turn out not so useful
> for many workloads.

I have that fear too, but the evidence isn't really in yet.  The
testing that people have reported on this list has had mostly positive
outcomes.  Of course that doesn't mean that it will work as well in
the field as it did in the lab, but it doesn't mean that it won't,
either.

> I'm very reluctant to suggest that we can solve
> this my setting aside another page-level bit to track visibility of
> tuples for heapscans. Or even have a bit in the tuple header itself to
> track this information at that level to avoid repeated visibility
> check for a tuple which is known to be visible to all current and
> future transactions.

This has been suggested before, as an alternative to freezing tuples.
It seems to have some potential although I think more thought and work
is needed to figure out exactly where to go with it.

> And we expect vacuums to be very less or none. AFAIR in pgbench, it
> now takes hours for accounts table to get chosen for vacuum and we
> should be happy about it. But IOS are almost impossible for pgbench
> kind of workloads today because of our aggressive strategy to clear
> the VM bits.

IMHO, it's probably fairly hopeless to make a pure pgbench workload
show a benefit from index-only scans.  A large table under a very
heavy, completely random write workload is just about the worst
possible case for index-only scans.  Index-only scans are a way of
avoiding unnecessary visibility checks when the target data hasn't
changed recently, not a magic bullet to escape all heap access.  If
the target data has changed, you're going to have to touch the heap.
And while I agree that we aren't aggressive enough in setting the VM
bits right now, I also think it wouldn't be too hard to go too far in
the opposite direction: we could easily spend more effort trying to
make index-only scans effective than we could ever hope to recoup from
the scans themselves.

Now, if you set up N threads of which 10% are doing regular pgbench
and the other 90% are doing pgbench -S, or something like that, then
you might start to hope for some benefit from index-only scans.  But I
think you might also GET some benefit in that case, even at steady
state.

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