Re: prerequisites of pull_up_sublinks

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.

David