Re: Planner : anti-join on left joins

Nicolas Adenis-Lamarre <nicolas.adenis.lamarre@gmail.com> writes:
> - detect anti join on "a left join b where x is null" where x is a non null
> var b (b being a rte)
> this is the object of the attached patched.

This is a perfectly reasonable thing to do, especially now that we've
built out some infrastructure that would help.  It doesn't look like
your patch is using that though.  Take a look at commits 904f6a593
and e2debb643.

BTW, it is not a good look for even a draft patch to not bother
updating adjacent comments that it falsifies, such as this in
reduce_outer_joins_pass2:

         * See if we can reduce JOIN_LEFT to JOIN_ANTI.  This is the case if
         * the join's own quals are strict for any var that was forced null by
         * higher qual levels.  NOTE: there are other ways that we could
         * detect an anti-join, in particular if we were to check whether Vars
         * coming from the RHS must be non-null because of table constraints.
         * That seems complicated and expensive though (in particular, one
         * would have to be wary of lower outer joins). For the moment this
         * seems sufficient.

In the long run, the comments are as important as the code, if not
even more so.  Keeping them accurate is not optional.

> - remove unrequirered distinct, group by (select distinct id_unique from
> people;)

Perhaps.  Not sure it's worth the trouble.

> - remove double order (select * from (select * from a order by x) order by
> y) (where * doesn't containt functions based on row nums)

I'd be inclined to think this is a bad idea.  If someone wrote that
they probably had a reason to want a double sort.

            regards, tom lane