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.