I happened to notice a constant-TRUE clause with is_pushed_down being
true while its required_relids not including the OJ being formed, which
seems abnormal to me. It turns out that this clause comes from
reconsider_outer_join_clauses(), as a dummy replacement if we've
generated a derived clause. The comment explains this as
* If we do generate a derived clause,
* however, the outer-join clause is redundant. We must still put some
* clause into the regular processing, because otherwise the join will be
* seen as a clauseless join and avoided during join order searching.
* We handle this by generating a constant-TRUE clause that is marked with
* required_relids that make it a join between the correct relations.
Should we instead mark the constant-TRUE clause with required_relids
plus the OJ relid?
Besides, I think 'otherwise the join will be seen as a clauseless join'
is not necessarily true, because the join may have other join clauses
that do not have any match. As an example, consider
select * from a left join b on a.i = b.i and a.j = b.j where a.i = 2;
So should we use 'may' rather than 'will' here?
Even if the join does become clauseless, it will end up being an
unqualified nestloop. I think the join ordering algorithm will force
this join to be formed when necessary. So I begin to wonder if it's
really necessary to generate this dummy constant-TRUE clause.
Thanks
Richard