Re: Remove inner joins based on foreign keys

On Thu, Apr 30, 2026 at 4:50 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> The inner join between orders and users is not removed. It appears
> this can be removed in this query. This is my first question.

Right. This is a mis-optimization case I was aware of. The inner
join can be removed in this case. inner_join_is_removable() is
currently overly conservative about ref_rel->joininfo: it bails as
long as the list is non-empty. We can relax this to bail only when a
clause actually references ref_rel via clause_relids. A clause can
appear in ref_rel->joininfo without its expression referencing
ref_rel, which connects to your second question.

> The second question may not be related to this $SUBJECT.
> My question is that the (n.id = o.id) clause seems not related to
> users. Why does the planner add it to the users' RelOptInfo's
> joininfo?
>
> I only know that the thing happens in the
> reconsider_outer_join_clauses(), the restrictinfo->required_relids
> contains {1,2,3}, where the rtindex = 3 is the users relation.

Because the clause "n.id = o.id" is an outer join ON clause that is
non-degenerate (one that references the non-nullable side of the join)
and we need to force it to be evaluated exactly at the level of the
outer join. To handle that, we add the join's minimum input relid set
to required_relids. That's why it appears in users' joininfo without
referencing users.

Attached patch relaxes the check against ref_rel->joininfo. Nothing
else has changed.

- Richard