Re: Do we need so many hint bits?

On 16 November 2012 19:58, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Fri, 2012-11-16 at 11:58 -0500, Robert Haas wrote:
>> > Also, I am wondering about PD_ALL_VISIBLE. It was originally introduced
>> > in the visibility map patch, apparently as a way to know when to clear
>> > the VM bit when doing an update. It was then also used for scans, which
>> > showed a significant speedup. But I wonder: why not just use the
>> > visibilitymap directly from those places?
>>
>> Well, you'd have to look up, lock and pin the page to do that. I
>> suspect that overhead is pretty significant. The benefit of noticing
>> that the flag is set is that you need not call HeapTupleSatisfiesMVCC
>> for each tuple on the page: checking one bit in the page header is a
>> lot cheaper than calling that function for every tuple. However, if
>> you had to lock and pin a second page in order to check whether the
>> page is all-visible, I suspect it wouldn't be a win; you'd probably be
>> better off just doing the HeapTupleSatisfiesMVCC checks for each
>> tuple.
>
> That's pretty easy to test. Here's what I got on a 10M record table
> (Some runs got some strangely high numbers around 1700ms, which I assume
> is because it's difficult to keep the data in shared buffers, so I took
> the lower numbers.):
>
> PD_ALL_VISIBLE: 661ms
> VM Lookup: 667ms
> Neither: 740ms
>
> Even if pinning the vm buffer were slow, we could keep the pin longer
> during a scan (it seems like the VM API is designed for that kind of a
> use case), so I don't think scans are a problem at all, even if there is
> a lot of concurrency.

The biggest problem with hint bits is SeqScans on a table that ends up
dirtying many pages. Repeated checks against clog and hint bit setting
are massive overheads for the user that hits that, plus it generates
an unexpected surge of database writes. Even without checksums that is
annoying.

ISTM that we should tune that specifically by performing a VM lookup
for next 32 pages (or more), so we reduce the lookups well below 1 per
page. That way the overhead of using the VM will be similar to using
the PD_ALL_VISIBLE. Also, if we pass through a flag to
HeapTupleSateisfies indicating we are not interested in setting hints
on a SeqScan then we can skip individual tuple hints also. If the
whole page becomes visible then we can set the VM.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services