prerequisites of pull_up_sublinks

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

prerequisites of pull_up_sublinks

Andy Fan
Hi,

I'm reading the pull_up_sublinks, and find the below comments.

 * 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?

--
Best Regards
Reply | Threaded
Open this post in threaded view
|

Re: prerequisites of pull_up_sublinks

David Rowley
On Wed, 21 Apr 2021 at 14:55, Andy Fan <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: prerequisites of pull_up_sublinks

Andy Fan


On Wed, Apr 21, 2021 at 4:37 PM David Rowley <[hidden email]> wrote:
On Wed, 21 Apr 2021 at 14:55, Andy Fan <[hidden email]> 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