Re: Converting NOT IN to anti-joins during planning

On Mon, 27 May 2019 at 20:43, Antonin Houska <ah(at)cybertec(dot)at> wrote:
> I've spent some time looking into this.

Thank you for having a look at this.

> One problem I see is that SubLink can be in the JOIN/ON clause and thus it's
> not necessarily at the top of the join tree. Consider this example:
>
> CREATE TABLE a(i int);
> CREATE TABLE b(j int);
> CREATE TABLE c(k int NOT NULL);
> CREATE TABLE d(l int);
>
> SELECT *
> FROM
> a
> JOIN b ON b.j NOT IN
> ( SELECT
> c.k
> FROM
> c)
> JOIN d ON b.j = d.l;

hmm yeah. Since the proofs that are being used in
expressions_are_not_nullable assume the join has already taken place,
then we'll either need to not use the join conditions are proofs in
that case, or just disable the optimisation instead. I think it's
fine to just disable the optimisation since it seem rather unlikely
that someone would write a join condition like that. Plus it seems
quite a bit more complex to validate that the optimisation would even
be correct if NULLs were not possible.

I've attached a patch which restricts the pullups to FromExpr quals.
Anything below a JoinExpr disables the optimisation now.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services