Tender, not sure you see one suggestion i did. just in case you think it may be interesting, i modified for you 0001.
it just add (and reindent) ' if(forced_null_vars != NIL) ' around the optimisation while most of left join have probably no forced nulls, and thus it avoids to compute find_nonnullable_vars and have_var_is_notnull for them. Nicolas
From 4e8b5aba0d8bccf1a3b5c3b41492fbcf295d691e Mon Sep 17 00:00:00 2001 From: Tender Wang <[email protected]> Date: Thu, 1 Jan 2026 13:44:18 +0800 Subject: [PATCH 1/2] Reduce JOIN_LEFT TO JOIN_ANTI. Since we have collected notnull constraints, so we can detect an anti-join, if we were to check whether Vars coming from the RHS must be non-null because of table constraints. For example, SELECT ... FROM a LEFT JOIN b ON (a.x = b.y) WHERE b.z IS NULL; If the b.z has non-null table constraint, we can reduce JOIN_LEFT to JOIN_ANTI. --- src/backend/optimizer/prep/prepjointree.c | 90 ++++++++++++++++++----- 1 file changed, 73 insertions(+), 17 deletions(-) diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index c3b726e93e7..ef061ad1712 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -177,6 +177,8 @@ static Node *find_jointree_node_for_rel(Node *jtnode, int relid); static nullingrel_info *get_nullingrels(Query *parse); static void get_nullingrels_recurse(Node *jtnode, Relids upper_nullingrels, nullingrel_info *info); +static bool have_var_is_notnull(PlannerInfo *root, Relids right_rels, + List *forced_null_vars); /* @@ -3230,6 +3232,57 @@ reduce_outer_joins(PlannerInfo *root) } } +/* + * have_var_is_notnull + * check whether the vars from RHS of join have notnull constraints. + */ +static bool +have_var_is_notnull(PlannerInfo *root, Relids right_rels, List *forced_null_vars) +{ + ListCell *lc; + Bitmapset *cols; + int i; + int index; + RangeTblEntry *rte; + Bitmapset *notnullattnums; + bool result = false; + + i = 0; + foreach(lc, forced_null_vars) + { + cols = lfirst_node(Bitmapset, lc); + if (cols == NULL) + { + i++; + continue; + } + /* Skip if the var doesn't belong to RHS of join */ + if (!bms_is_member(i, right_rels)) + { + i++; + continue; + } + rte = rt_fetch(i, root->parse->rtable); + notnullattnums = find_relation_notnullatts(root, rte->relid); + index = -1; + while ((index = bms_next_member(cols, index)) >= 0) + { + AttrNumber attno = index + FirstLowInvalidHeapAttributeNumber; + if (bms_is_member(attno, notnullattnums)) + { + result = true; + break; + } + } + + if (result) + break; + i++; + } + + return result; +} + /* * reduce_outer_joins_pass1 - phase 1 data collection * @@ -3441,29 +3494,32 @@ reduce_outer_joins_pass2(Node *jtnode, /* * 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 + * higher qual levels. Since we have collected notnull constraints, so + * we can detect an anti-join, 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. + * For example, SELECT ... FROM a LEFT JOIN b ON (a.x = b.y) WHERE b.z IS NULL; + * if the b.z had non-null table constraint, we can reduce JOIN_LEFT to JOIN_ANTI. */ if (jointype == JOIN_LEFT) { - List *nonnullable_vars; - Bitmapset *overlap; + if(forced_null_vars != NIL) { + List *nonnullable_vars; + Bitmapset *overlap; - /* Find Vars in j->quals that must be non-null in joined rows */ - nonnullable_vars = find_nonnullable_vars(j->quals); + /* Find Vars in j->quals that must be non-null in joined rows */ + nonnullable_vars = find_nonnullable_vars(j->quals); - /* - * It's not sufficient to check whether nonnullable_vars and - * forced_null_vars overlap: we need to know if the overlap - * includes any RHS variables. - */ - overlap = mbms_overlap_sets(nonnullable_vars, forced_null_vars); - if (bms_overlap(overlap, right_state->relids)) - jointype = JOIN_ANTI; + /* + * It's not sufficient to check whether nonnullable_vars and + * forced_null_vars overlap: we need to know if the overlap + * includes any RHS variables. + */ + overlap = mbms_overlap_sets(nonnullable_vars, forced_null_vars); + if (bms_overlap(overlap, right_state->relids)) + jointype = JOIN_ANTI; + else if (have_var_is_notnull(root, right_state->relids, forced_null_vars)) + jointype = JOIN_ANTI; + } } /* -- 2.34.1
