Re: Set visibility map bit after HOT prune

On Sun, Dec 16, 2012 at 3:18 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> Doing that only makes sense when we're running a SELECT. Setting the
>> all visible bit immediately prior to an UPDATE that clears it again is
>> pointless effort, generating extra work for no reason.
>
> On the other hand, the HOT prune operation itself is worthless when
> we're running a SELECT. The only reason we do it that way is that we
> have to prune before the query starts to use the page, else pruning
> might invalidate pointers-to-tuples that are being held within the
> query plan tree.
>

Right. HOT prune may and often would be called in the SELECT path or
heap/index scan leading to UPDATE/DELETE. But whenever its called, it
looks like a good idea to set the visibility map bit. There is some
additional overhad to check if a LIVE tuple is all-visible or not,
but that doesn't look too much. I did run some pgbench tests for fully
cached tables and did not see any difference in tps.

Another idea could have been to NOT clear the visibility bit when a
HOT update happens. Such tuple can get pruned by HOT prune, so we
don't need vacuum per se, and the index-only scans are not affected
because the update was a HOT update, so the index keys did not change
either. So index-only scans would continue to return the same result.
Don't know if this would work with hot standby, probably not.

> Maybe it's time to look at what it'd take for the low-level scan
> operations to know whether they're scanning the target relation of
> an UPDATE query, so that we could skip pruning altogether except
> when a HOT update could conceivably ensue.

Yeah, we could do that. We may not be able to predict whether the
coming update is HOT or not, but I don't think that matters. OTOH its
probably not too bad to prune in any scan (like we do today) because
there is fairly high chance that the page will be dirtied for hint bit
updates. So may be its better to just prune as well. We have already
put in several optimisations to do so only when required and without
any unnecessary contention. Of course, benchmarks can prove me wrong.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee