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
From 30428c24ad378f5df4082e07f10fd61dda6d4873 Mon Sep 17 00:00:00 2001 From: Tender Wang <[email protected]> Date: Thu, 1 Jan 2026 13:44:18 +0800 Subject: [PATCH] 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 | 64 +++++++++++++++++++++-- 1 file changed, 59 insertions(+), 5 deletions(-) diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index c3b726e93e7..4ff27de5a58 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,12 +3494,11 @@ 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) { @@ -3464,6 +3516,8 @@ reduce_outer_joins_pass2(Node *jtnode, 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
