Re: Set visibility map bit after HOT prune

On 19 December 2012 16:21, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sat, Dec 15, 2012 at 4:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> 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.
>
> Well, no. The problem with the way we do it now is that doing it every
> time a query scan arrives at a page is too often, resulting in a lot of
> wasted work. That wasted work is somewhat tolerable as long as it only
> involves looking at the current page and ending up not actually changing
> it. If we start generating a lot of useless WAL activity and I/O as
> a result of thrashing the all-visible bit, it won't be so tolerable
> anymore. But the problem is not so much the desire to set the bit as
> that we're doing this whole activity at the wrong place and time.
>
> Perhaps doing it every time an UPDATE arrives at the page is too far
> in the other direction, and we need to look for some other mechanism
> entirely.

The benefit of saying that only UPDATEs clean the block is that this
penalises only the workload making the mess, rather than everybody
cleaning up repeatedly over one messy guy.

Having a random SELECT clean the block causes both delay in
non-UPDATEing process, contention and additional writes.

We definitely know we write too often; this has been measured and
discussed over a period of years.

It would be useful to have a table-level option of hot_cleanup= SELECT
| UPDATE | NONE to allow people to minimise cleanup and test the
difference this makes.

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