Re: Set visibility map bit after HOT prune

On 16 December 2012 07:53, Pavan Deolasee <pavan.deolasee@gmail.com> wrote:
> On Sun, Dec 16, 2012 at 3:18 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Simon Riggs <simon@2ndQuadrant.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.

As explained above, I disagree that it looks like a good idea, and
you've shown no evidence it would be or is true.

I agree with Tom that cleaning on SELECT can be worthless and I'd
definitely like to be able to turn it off conditionally or
permanently.

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

We can predict that an update is HOT in advance, if none of the index
columns are touched in the UPDATE. Yes, there are some cases where it
might not be, but we could probably store that in the statement cache.

Making that checkat run time must cost some block contention, so it
would be good to remove it from every update.

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

I think we could use some measurement/stats there so we can check.

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