Re: heap vacuum & cleanup locks

On Mon, Jun 6, 2011 at 8:02 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> On 06.06.2011 09:35, Jim Nasby wrote:
>>
>> I've had a related idea that I haven't looked into... if you're scanning a
>> relation (ie: index scan, seq scan) I've wondered if it would be more
>> efficient to deal with the entire page at once, possibly be making a copy of
>> it. This would reduce the number of times you pin the page (often quite
>> dramatically). I realize that means copying the entire page, but I suspect
>> that would occur entirely in the L1 cache, which would be fast.
>
> We already do that. When an index scan moves to an index page, the heap tid
> pointers of all the matching index tuples are copied to backend-private
> memory in one go, and the lock is released. And for a seqscan, the
> visibility of all the tuples on the page is checked in one go while holding
> the lock, then the lock is released but the pin is kept. The pin is only
> released after all the tuples have been read. There's no repeated pin-unpin
> for each tuple.

But I think you've hit the important point here. The problem is not
whether VACUUM waits for the pin, its that the pins can be held for
extended periods.

It makes more sense to try to limit pin hold times than it does to
come up with pin avoidance techniques.

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