Re: A problem about join ordering

On Fri, Nov 11, 2022 at 11:24 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> > I'm wondering whether we need to insist on being strict for the lower
> > OJ's min_righthand. What if we instead check strictness for its whole
> > syn_righthand?
>
> Surely not. What if the only point of strictness is for a rel that
> isn't part of the min_righthand? Then we could end up re-ordering
> based on a condition that isn't actually strict for what we've
> chosen as the join's RHS.
>

I think I've got your point. You're right. And doing so would cause
another problem about ordering restriction as I observed. For the
following two forms

(A leftjoin (B leftjoin C on (Pbc)) on (Pab)) left join D on (Pbcd)

AND

((A leftjoin B on (Pab)) leftjoin C on (Pbc)) left join D on (Pbcd)

Assume Pbc is strict for B, Pbcd is strict for C but not strict for B.

After applying this change, in the first form the (BC)/D join will be
legal, while in the second form it is not.

>
> It might be possible to change the other part of the equation and
> consider the A/B join's min_righthand instead of syn_righthand
> while checking if Pcd uses A/B's RHS; but I'm not real sure about
> that either.
>

This seems a more plausible change. I tried this way and didn't find
any abnormal behaviour. But I'm not sure either. Maybe I need to try
harder.

Thanks
Richard