About the constant-TRUE clause in reconsider_outer_join_clauses - Mailing list pgsql-hackers

From Richard Guo
Subject About the constant-TRUE clause in reconsider_outer_join_clauses
Date
Msg-id CAMbWs48=wUECRpFb7NNUBY10miChk-JWS5FAj5N7-Ee_m7WW7w@mail.gmail.com
Whole thread Raw
Responses Re: About the constant-TRUE clause in reconsider_outer_join_clauses
List pgsql-hackers
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

pgsql-hackers by date:

Previous
From: "houzj.fnst@fujitsu.com"
Date:
Subject: RE: Initial Schema Sync for Logical Replication
Next
From: Jelte Fennema
Date:
Subject: Re: running logical replication as the subscription owner