Re: prerequisites of pull_up_sublinks

On Wed, Apr 21, 2021 at 4:37 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Wed, 21 Apr 2021 at 14:55, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
> > * However, this optimization *only*
> > * works at the top level of WHERE or a JOIN/ON clause, because we cannot
> > * distinguish whether the ANY ought to return FALSE or NULL in cases
> > * involving NULL inputs. Also, in an outer join's ON clause we can only
> > * do this if the sublink is degenerate (ie, references only the nullable
> > * side of the join).
> >
> > I tried to write some SQLs but still can't understand the above
> comments. Any
> > help here?
>
> The code there is trying to convert sub links into joins.
>
> For example:
>
> explain select * from pg_Class where oid in (select attrelid from
> pg_attribute);
>
> can be implemented as a join rather than a subplan or hashed subplan.
> You should either see a Semi Join there or a regular join with the
> pg_attribute side uniquified.
>
> Check the plan when you change the above into NOT IN. We don't
> currently pull those up to become joins due to the fact that the null
> behaviour for NOT IN is not compatible with anti-joins.
>
> I just checked the "Not In to Join" thread some days ago, but didn't
realize it here. Thank you David for your hint.

--
Best Regards
Andy Fan (https://www.aliyun.com/)