Re: Is FOR UPDATE an optimization fence?

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sun, Oct 11, 2009 at 12:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Of course the downside of changing it is that queries that worked fine
>> before might work differently (and much slower) now; first because not
>> flattening the sub-select might lead to a worse plan, and second because
>> locking more rows takes more time.
>>
>> The alternative would be to let it continue to flatten such sub-selects
>> when possible, and to tell anyone who doesn't want that to stick in
>> OFFSET 0 as an optimization fence.
>>
>> It's an entirely trivial code change either way. I'm inclined to think
>> that we should prevent flattening, on the grounds of least astonishment.

> The other comment I have is that I *expect* subqueries to be pulled
> up. So my own personal POLA would not be violated by locking only the
> rows with a join partner; in fact it would be more likely to be
> violated by the reverse behavior. I might not be typical, though. My
> experience is that not pulling up subqueries tends to have disastrous
> effects on performance, so I'm somewhat biased against creating more
> situations where that will happen.

On further reflection I've decided to stick with the old behavior on
this point, at least for the time being. I'm concerned about subtly
altering the behavior of existing queries, and I've also realized that
changing it isn't as much of a one-liner as I thought. The current
behavior of the parser and rewriter really depends on the assumption
that there's not much of a semantic difference between FOR UPDATE
markings at different syntactic levels, because they will happily push
down a FOR UPDATE *into* a sub-select. That is,

select * from a join (select * from b) ss on a.x = ss.y for update;

gets transformed into

select * from a join (select * from b for update of b) ss
on a.x = ss.y
for update of a;

There isn't any simple way to avoid that with the current RowMarkClause
representation, because it only applies to the current query level.
Maybe we should think about changing that sometime, but it seems like
material for a different patch.

regards, tom lane