On Wed, Apr 21, 2021 at 4:37 PM David Rowley <dgrowle...@gmail.com> wrote:
> On Wed, 21 Apr 2021 at 14:55, Andy Fan <zhihui.fan1...@gmail.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/)
