Re: Planner : anti-join on left joins

Nicolas Adenis-Lamarre <nicolas(dot)adenis(dot)lamarre(at)gmail(dot)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