From 5149b2e2c2fec6d172c4997271c9fde1ebc01c86 Mon Sep 17 00:00:00 2001 From: Nicolas Adenis-Lamarre Date: Wed, 31 Dec 2025 10:35:01 +0100 Subject: [PATCH] planner: anti join on left joins Signed-off-by: Nicolas Adenis-Lamarre --- src/backend/optimizer/plan/planner.c | 4 ++ src/backend/optimizer/prep/prepjointree.c | 33 ++++++++----- src/backend/optimizer/util/clauses.c | 59 ++++++++++++++++++++++- src/include/optimizer/clauses.h | 1 + 4 files changed, 84 insertions(+), 13 deletions(-) diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 1268ea92b6f..32211c2ffc3 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -1228,6 +1228,10 @@ subquery_planner(PlannerGlobal *glob, Query *parse, char *plan_name, } parse->havingQual = (Node *) newHaving; + // nae temporary for tests + /* Set up RTE/RelOptInfo arrays */ + setup_simple_rel_arrays(root); + /* * If we have any outer joins, try to reduce them to plain inner joins. * This step is most easily done after we've done expression diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index c3b726e93e7..b4b9428acb7 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -3450,20 +3450,29 @@ reduce_outer_joins_pass2(Node *jtnode, */ if (jointype == JOIN_LEFT) { - List *nonnullable_vars; - Bitmapset *overlap; + if(forced_null_vars != NIL) { + List *nonnullable_vars; + List *nonnullable_cols = NULL; + 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. + */ + + nonnullable_cols = find_nonnullable_cols(root, j->rarg); + if(nonnullable_cols != NIL) { + mbms_add_members(nonnullable_vars, nonnullable_cols); + } + overlap = mbms_overlap_sets(nonnullable_vars, forced_null_vars); + + if (bms_overlap(overlap, right_state->relids)) + jointype = JOIN_ANTI; + } } /* diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 67b7de16fc5..a00d4bfa9a7 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -112,6 +112,7 @@ static bool contain_context_dependent_node_walker(Node *node, int *flags); static bool contain_leaked_vars_walker(Node *node, void *context); static Relids find_nonnullable_rels_walker(Node *node, bool top_level); static List *find_nonnullable_vars_walker(Node *node, bool top_level); +static List *find_nonnullable_cols_walker(PlannerInfo *root, Node *node); static bool is_strict_saop(ScalarArrayOpExpr *expr, bool falseOK); static bool convert_saop_to_hashed_saop_walker(Node *node, void *context); static Node *eval_const_expressions_mutator(Node *node, @@ -1736,10 +1737,11 @@ find_nonnullable_vars_walker(Node *node, bool top_level) { Var *var = (Var *) node; - if (var->varlevelsup == 0) + if (var->varlevelsup == 0) { result = mbms_add_member(result, var->varno, var->varattno - FirstLowInvalidHeapAttributeNumber); + } } else if (IsA(node, List)) { @@ -5906,3 +5908,58 @@ make_SAOP_expr(Oid oper, Node *leftexpr, Oid coltype, Oid arraycollid, return saopexpr; } + +List * +find_nonnullable_cols(PlannerInfo *root, Node *node) +{ + return find_nonnullable_cols_walker(root, node); +} + +/* + * find_nonnullable_cols + * Determine which Vars are forced nonnullable by given clause. + * + */ +static List * +find_nonnullable_cols_walker(PlannerInfo *root, Node *node) +{ + List *result = NIL; + int x; + + if (node == NULL) + return NIL; + + if (IsA(node, RangeTblRef)) { + ListCell *lc; + + int varno = ((RangeTblRef *) node)->rtindex; + (void) build_simple_rel(root, varno, NULL); + + RelOptInfo *rel; + rel = root->simple_rel_array[varno]; + + x = -1; + while ((x = bms_next_member(rel->notnullattnums, x)) >= 0) { + result = mbms_add_member(result, + varno, + x - FirstLowInvalidHeapAttributeNumber); + } + } + + else if (IsA(node, JoinExpr)) { + JoinExpr *j = (JoinExpr *) node; + + // we could consider as non null variables from other side via join clause expression, + // but not needed while this expression is analyzed too later + + if(j->jointype == JOIN_INNER || j->jointype == JOIN_LEFT) { + result = list_concat(result, find_nonnullable_cols_walker(root, j->larg)); + } + + if(j->jointype == JOIN_INNER || j->jointype == JOIN_RIGHT) { + result = list_concat(result, find_nonnullable_cols_walker(root, j->rarg)); + } + } + + return result; +} diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h index fc38eae5c5a..cfc5821fecd 100644 --- a/src/include/optimizer/clauses.h +++ b/src/include/optimizer/clauses.h @@ -40,6 +40,7 @@ extern bool contain_leaked_vars(Node *clause); extern Relids find_nonnullable_rels(Node *clause); extern List *find_nonnullable_vars(Node *clause); +extern List *find_nonnullable_cols(PlannerInfo *root, Node *clause); extern List *find_forced_null_vars(Node *node); extern Var *find_forced_null_var(Node *node); -- 2.34.1