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
