On Mon, Apr 20, 2026 at 6:10 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> This turns out to be because somebody long ago thought that outer join
> removal could be lazy about how much of the planner's data structures
> it needs to update. Specifically, when the lower LEFT OUTER JOIN
> gets removed, we failed to remove the associated relids from the
> left_relids and right_relids of the upper "ON rhs.id = lhs.id" clause,
> and that blocks recognition of the applicability of a hash or merge
> join, because clause_sides_match_join() fails.
I came to the same conclusion.
> The fix seems pretty trivial, as attached. (While I'm only certain
> that we have to fix left_relids and right_relids, this discovery
> makes it seem like it'd be pretty foolish not to fix all the relid
> sets of a RestrictInfo.) I didn't make a regression test case yet,
> but we need one since no existing test results change (!?).
This fix LGTM. I think it'd be better to have a regression test case.
How about this one:
create table t (id int unique);
explain (costs off)
select t1.*
from t t1 full join
(select 1 as x
from t t2 left join t t3 on t2.id = t3.id
) sub on t1.id = sub.x;
ERROR: FULL JOIN is only supported with merge-joinable or
hash-joinable join conditions
> I'm feeling a tad nervous about pushing this into released branches.
> It seems likely that it might enable quite a few join plans that were
> previously not considered, and people tend not to like plan changes in
> stable branches. However, (a) it's hard to argue that this isn't a
> regression from pre-v16, and (b) since this change affects no existing
> test, maybe the blast radius isn't as big as I fear.
Fair points on both sides. I'd lean slightly toward back-patching
this fix, mostly because of your points (a) and (b). Without a
back-patch, users like François would need to adjust affected queries
when upgrading from pre-v16 to v16–v18, which feels a bit unfortunate.
- Richard
