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 <[email protected]> a écrit : > > > Tom Lane <[email protected]> 于2026年1月1日周四 07:37写道： > >> Nicolas Adenis-Lamarre <[email protected]> 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 >
