Re: Planner : anti-join on left joins

On Wed, 31 Dec 2025 at 22:59, Nicolas Adenis-Lamarre
<nicolas(dot)adenis(dot)lamarre(at)gmail(dot)com> wrote:
> I'm a beginner about postgresql code (but i patched a lot of opensource programs while working on a linux distro).

Welcome.

> 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)

Please look at find_relation_notnullatts(). You may be able to check
the forced_null_vars against that Bitmapset, with care to offset by
FirstLowInvalidHeapAttributeNumber.

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

There's been work in that area before. Please search the archives for
UniqueKeys ([1]).

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

I think you risk breaking quite a few things there. There'd be quite a
large number of reasons to not do this and it seems quite difficult to
think of all of them upfront, which you'd need to do.

> - detect anti join on "not in(...)"

A few people have worked in that area before (some of it in [2], but I
think there was a more recent effort too). It might be worth reviewing
those discussions. Keep in mind that since then, the planner has more
infrastructure to know if Vars or Exprs can be NULL.

> - have a way to view the rewritten query ? (like explain)

I don't know what that means. It's not like there's a way to express
all the optimisations that were applied back into SQL.

David

[1] https://www.postgresql.org/search/?m=1&q=UniqueKeys&l=1&d=-1&s=d
[2] https://www.postgresql.org/message-id/flat/3793.1565689764%40linux-edt6#bf4b983d5744bca153c288904c038020