Re: Planner : anti-join on left joins

Hi

čt 1. 1. 2026 v 10:34 odesílatel Nicolas Adenis-Lamarre <
nicolas(dot)adenis(dot)lamarre(at)gmail(dot)com> napsal:

> 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.
>
>
Please, don't use top posting style here
https://web.archive.org/web/20230608210806/idallen.com/topposting.html

https://wiki.postgresql.org/wiki/Mailing_Lists

Regards

Pavel

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