Re: Self-join elimination discards differing row lock wait policies

Tender Wang <tndrwang(at)gmail(dot)com> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 于2026年10月25日周日 08:56写道：
>> I experimented with writing logic that would allow merging locks of
>> different strengths, but I'm not really convinced that it's worth the
>> trouble.

> I haven't included the other improvement you suggested—performing SJE
> when lock strengths differ and retaining the stronger lock type, along
> with an appropriate rule for choosing the waitPolicy.
> I think it would be better to consider that separately from this cleanup patch.

Agreed that there's separable issues there. Since you seem to be
interested in pursuing this, I attach the patch I had as far as
I'd taken it (I stopped upon realizing that preprocess_rowmarks
prevents a lot of the new code from being reached). You could
compare this to yours, which I've not looked at, and take the better
bits from each.

> During testing, I also noticed another possible missed optimization.
> For example, adding a restriction such as:
> postgres=# EXPLAIN (VERBOSE, COSTS OFF)
> SELECT p.a, q.a
> FROM t1 p
> JOIN t1 q ON p.a = q.a
> WHERE p.a = 18;
> QUERY PLAN
> -------------------------------
> Nested Loop
> Output: p.a, q.a
> -> Seq Scan on public.t1 p
> Output: p.a, p.b
> Filter: (p.a = 18)
> -> Seq Scan on public.t1 q
> Output: q.a, q.b
> Filter: (q.a = 18)
> (8 rows)

> to the self-join query prevents SJE, whereas removing that restriction
> allows SJE to occur.
> It appears that the equivalence class containing the constant absorbs
> the equality between p.a and q.a, so
> generate_join_implied_equalities() returns NIL and SJE gives up.

Yeah, I believe that explanation.

> Do you think this case is worth improving, perhaps as a separate patch?

Hm. Certainly the plan looks a little stupid. Now we know, in a
case like this, that each scan will produce just one row --- but
if they're implemented as seqscans not indexscans then they could
still be expensive. (On the third hand, there must be a unique index
on t1.a, or there would be no opportunity for SJE. So I guess your
example here is with toy-size tables that the planner doesn't think
justify an indexscan?)

Right offhand I'm thinking there is no meaningful performance win to
be had, so fixing this seems mostly cosmetic. If it's trivial to
do, or if you can refute my feeling that it's not a performance win,
then let's look closer.

regards, tom lane