Re: Planner : anti-join on left joins - Mailing list pgsql-hackers
| From | Nicolas Adenis-Lamarre |
|---|---|
| Subject | Re: Planner : anti-join on left joins |
| Date | |
| Msg-id | CACPGbcvDA1K9tUM6hnS_X0-vDjoCZANm9XZsFcXy_UrXuL-hEA@mail.gmail.com Whole thread Raw |
| In response to | Re: Planner : anti-join on left joins (Tender Wang <tndrwang@gmail.com>) |
| Responses |
Re: Planner : anti-join on left joins
|
| List | pgsql-hackers |
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.
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
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
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
pgsql-hackers by date: