Re: Planner : anti-join on left joins

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(at)gmail(dot)com> a écrit :

>
>
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 于2026年1月1日周四 07:37写道：
>
>> Nicolas Adenis-Lamarre <nicolas(dot)adenis(dot)lamarre(at)gmail(dot)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
>