Re: Set visibility map bit after HOT prune

On Sat, Dec 15, 2012 at 4:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.
>
> 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. I think this was discussed
> back when HOT went in, but nobody wanted to make the patch more invasive
> than it had to be.

I think it's wrong to assume that HOT pruning has no value except in
this case. Truncating dead tuples to line pointers and collapsing HOT
chains speeds up future page scans, and if we were able to set the
all-visible bit, that would help even more. The problem is that this
is all somewhat prospective: HOT pruning the page doesn't help the
*current* scan - in fact, it can sometimes slow it down considerably -
but it can be a great help to the next scan that comes through. We
say, oh, don't worry, VACUUM will take care of it, but there are
plenty of cases where a page can be scanned a very large number of
times before VACUUM comes along; and you do can lose a lot of
performance in those cases.

That having been said, I agree with the concerns expressed elsewhere
in this thread that setting the visibility map bit too aggressively
will be a waste. If the page is about to get dirtied again we surely
don't want to go there. Aside from the obvious problem of doing work
that may not be necessary, it figures to create buffer-lock contention
on the visibility map page. One of the strengths of the current
design is that we avoid that pretty effectively.

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