On Mon, Jan 5, 2026 at 7:59 PM Tender Wang <tndrwang@gmail.com> wrote:
> Yeah, I forgot to add your suggestion. Please check the v4 patch.
> I combined the code and tests into one patch and also added it to [1] commitfest.
I looked through the patch and I like the idea. This is a very nice
optimization opportunity.
Regarding the implementation, I'm afraid that this patch can be wrong
in a couple of cases.
* When checking NOT NULL constraints to see if a var is non-nullable,
this patch overlooks cases where the var can be nullable due to
lower-level outer joins. For example, given tables t1, t2, t3 with
schema (a NOT NULL, b, c), this patch would produce an incorrect plan
for the query below.
explain (costs off)
select * from t1 left join
(t2 left join t3 on t2.c = t3.c) on t1.b = t2.b
where t3.a is null;
QUERY PLAN
----------------------------------------
Hash Anti Join
Hash Cond: (t1.b = t2.b)
-> Seq Scan on t1
-> Hash
-> Hash Left Join
Hash Cond: (t2.c = t3.c)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t3
(9 rows)
Although t3.a references a not-null column, it can become null due to
the t2/t3 join. So the upper left join should not be reduced to an
anti join.
* This patch doesn't account for inheritance parent tables, where some
child tables might have a NOT NULL constraint for a column while
others do not.
The second point is straightforward to fix, but the first is more
tricky since we did not record varnullingrels in forced_null_vars.
One simple fix would be to only perform this optimization when
right_state->contains_outer is false (meaning no outer joins exist in
the RHS). However, this would be too restrictive and miss many
optimization opportunities.
Instead, I'm considering recording the relids of base rels that are
nullable within each subtree in reduce_outer_joins_pass1_state. This
would allow us to skip Vars that come from those rels when checking
NOT NULL constraints. Something like attached.
- Richard
