Thread: About the constant-TRUE clause in reconsider_outer_join_clauses
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
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
Richard Guo <guofenglinux@gmail.com> writes:
> Should we instead mark the constant-TRUE clause with required_relids
> plus the OJ relid?
I do not think it matters.
> 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.
We would find *some* valid plan, but not necessarily a *good* plan.
The point of the dummy clause is to ensure that the join is considered
as soon as possible. That might not be the ideal join order of course,
but we'll consider it among other join orders and arrive at a cost-based
decision. With no dummy clause, the join order heuristics would always
delay this join as long as possible; so even if another ordering is
better, we'd not find it.
regards, tom lane
On Sat, Mar 25, 2023 at 11:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Richard Guo <guofenglinux@gmail.com> writes:
> Should we instead mark the constant-TRUE clause with required_relids
> plus the OJ relid?
I do not think it matters.
Yeah, I agree that it makes no difference currently. One day if we want
to replace the is_pushed_down flag with checking to see if a clause's
required_relids includes the OJ being formed in order to tell whether
it's a filter or join clause, I think we'd need to make this change.
to replace the is_pushed_down flag with checking to see if a clause's
required_relids includes the OJ being formed in order to tell whether
it's a filter or join clause, I think we'd need to make this change.
> 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.
We would find *some* valid plan, but not necessarily a *good* plan.
The point of the dummy clause is to ensure that the join is considered
as soon as possible. That might not be the ideal join order of course,
but we'll consider it among other join orders and arrive at a cost-based
decision. With no dummy clause, the join order heuristics would always
delay this join as long as possible; so even if another ordering is
better, we'd not find it.
I understand it now. Thanks for the explanation.
Thanks
Richard
Thanks
Richard
Richard Guo <guofenglinux@gmail.com> writes:
> On Sat, Mar 25, 2023 at 11:41 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Richard Guo <guofenglinux@gmail.com> writes:
>>> Should we instead mark the constant-TRUE clause with required_relids
>>> plus the OJ relid?
>> I do not think it matters.
> Yeah, I agree that it makes no difference currently. One day if we want
> to replace the is_pushed_down flag with checking to see if a clause's
> required_relids includes the OJ being formed in order to tell whether
> it's a filter or join clause, I think we'd need to make this change.
I did think about that ... but a constant-TRUE clause is going to be a
no-op no matter which classification you give it. We do have some work to
do in that area, but I think it's not an issue for this particular case.
regards, tom lane