On Tue, Nov 8, 2011 at 3:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Nov 8, 2011 at 2:26 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>>> I think we need to avoid long pin hold times generally.
>
>> In the case of a suspended sequential scan, which is the case where
>> this has most recently bitten me on a production system, it actually
>> seems rather unnecessary to hold the pin for a long period of time.
>> If we release the buffer pin, then someone could vacuum the buffer.
>
> This seems unlikely to be a productive line of thought. The only way
> you could release buffer pin is if you first copied all the tuples you
> need out of the page, and that seems like an unacceptable performance
> hit. We should not be penalizing foreground query operations for the
> benefit of background maintenance like VACUUM. (The fact that we do
> an equivalent thing in btree index scans isn't an argument for doing
> it here, because the tradeoffs are very different. In the index case,
> the amount of data to be copied is a great deal less; the length of
> time the lock would have to be held is often a great deal more; and
> releasing the lock quickly gives a performance benefit for other
> foreground operations, not only background maintenance.)
>
> It strikes me that the only case where vacuum now has to wait is where
> it needs to freeze an old XID. Couldn't it do that without insisting on
> exclusive access? We only need exclusive access if we're going to move
> data around, but we could have a code path in vacuum that just replaces
> old XIDs with FrozenXID without moving/deleting anything.
Holding buffer pins for a long time is a problem in Hot Standby also,
not just vacuum.
AFAIK seq scans already work page at a time for normal tables. So the
issue is when we *aren't* using a seq scan, e.g. nested loops joins.
Is there a way to solve that?
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services