Обсуждение: Planner : anti-join on left joins

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.

Thanks a lot for your answers.

>>In the long run, the comments are as important as the code

Reading postgresql comments as a book is something i really enjoy. it increases the learning curve by two orders of magnitude.

@Tender Wang

thanks a lot. about, the patch, i've some points:

- 1

in my original patch, i added the following test to avoid computing things like find_nonnullable_vars

it is a minor improvement, i don't know if that should be kept.

 + if(forced_null_vars != NIL)

- 2

i added locally some regressions tests for such detection, but i needed to add cols to tenk1 cause tenk1 has no "not null" columns nor primary keys.

i'm too new to tell if that's the correct way to do. however, i think that such regression tests must be added as it was already the case for the existing anti join detection (and it were on the tenk1 table).

- 3

your patch seems to not work on 2 of my tests (the 2nd one is not working with my patch too)

* when rhl is a subquery

select p.firstname
from people p
left join (select 5 aa, id_husband, divorce_place from mariages left join people on mariages.id_wife = people.id) m on m.id_husband = p.id
where divorce_place is not null -- divorce_place was set not null for my tests

* when b.z is a constant from a subquery (but while the previous one didn't, in your case, i guess it is normal)

select p.firstname
from people p
left join (select 5 aa, id_husband, divorce_place from mariages left join people on mariages.id_wife = people.id) m on m.id_husband = p.id
where aa is not null

I will review the commits and suggestions you told me to continue to learn,

I will reanalyze your patch more carefully to see why my tests are not ok.

but i'm new, i need more time.

Le jeu. 1 janv. 2026 à 07:24, Tender Wang <tndrwang@gmail.com> a écrit :

Tom Lane <tgl@sss.pgh.pa.us> 于2026年1月1日周四 07:37写道：

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.

Yes, after commits 904f6a593 and e2debb643, we have some infrastructure to use.

I provided a patch to implement this reduction using these infrastructure codes.

Please check the attached patch.

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.

I updated the comments in the attached patch as well.

And I test the regression in the src, all tests pass.

Any thoughts? 

--

Thanks,

Tender Wang