Planner : anti-join on left joins

Hi,

while writting a book in 2025, i read an played with the postgresql code.
now my book is finished and i think that maybe a next step is to try to
patch postgresql for some optimisations. I'm a beginner about postgresql
code (but i patched a lot of opensource programs while working on a linux
distro).

this first email aim is to discuss about the fact that this kind of code
(just a draft here) have a chance to be commited or not (once comments
added, reident, cleaning, ...). Details about each implementation could be
discussed later.

There are some optimisations at the planner level that are not mandatory
when you know how it works, but all the year, i get query to optimize
because people doesn't know the pg internals or write not well written
queries. So, the patches i would like to suggest are more "non mandatory
optimisations".

like:
- 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.
it is not finished, but working for a demonstation (this is a quick and
dirty patch just to try if i were able to do it).
it shows me that it has drawbacks : for example : it requires to know the
details on some tables sooner on the planner, (and thus, sometimes, before
we detect that we could just remove a table => so we build some tables for
nothing except optimisations)

- remove unrequirered distinct, group by (select distinct id_unique from
people;)
- remove double order (select * from (select * from a order by x) order by
y) (where * doesn't containt functions based on row nums)
- detect anti join on "not in(...)"
- have a way to view the rewritten query ? (like explain)

and so on.