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
