Tom Lane <[email protected]> 于2026年10月15日周四 04:56写道： > > If we're going to refuse that, I wonder if we couldn't simplify > match_unique_clauses a great deal: just insist that the two OpExpr > clauses be equal(), after the ChangeVarNodes substitution on the outer > clause. Your proposal already breaks commuted-clause cases, which > seem like the only justification for all the futzing around with left > and right operands. I doubt that detecting that is of any real-world > value anyway. >
Attached is v2, following your suggestion to compare the complete clauses with equal() after the ChangeVarNodes substitution. I also removed the test added in v1. I ran the regression tests and found no plan differences. This suggests the existing tests don't cover cases where matching commuted restrictions enables self-join elimination. -- Thanks, Tender Wang
From 17e29bb5a1196af9710041f5673287dc67ec343b Mon Sep 17 00:00:00 2001 From: Tender Wang <[email protected]> Date: Thu, 15 Oct 2026 10:43:25 +0800 Subject: [PATCH v2] Simplify restriction matching in self-join elimination Use equal() to compare complete restriction clauses after substituting the relation ID, instead of comparing their operands separately. This ensures that the operator and input collation also match, preventing incorrect self-join elimination when restrictions have identical operands but different semantics. This deliberately forgoes matching commuted clauses in favor of simpler code. Remove the checks needed by the operand-matching logic and update the comments accordingly. --- src/backend/optimizer/plan/analyzejoins.c | 40 +++++------------------ 1 file changed, 8 insertions(+), 32 deletions(-) diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index 3156c658a79..b89a4cb092f 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -1718,10 +1718,12 @@ split_selfjoin_quals(PlannerInfo *root, List *joinquals, List **selfjoinquals, } /* - * Check for a case when uniqueness is at least partly derived from a - * baserestrictinfo clause. In this case, we have a chance to return only - * one row (if such clauses on both sides of SJ are equal) or nothing (if they - * are different). + * Check that each base restriction used to prove uniqueness of relid has + * a matching restriction in outer->baserestrictinfo, after replacing + * relid with outer->relid. + * + * Require equal() clauses, including the operator and input collation. + * We do not attempt to match commuted forms of the clauses. */ static bool match_unique_clauses(PlannerInfo *root, RelOptInfo *outer, List *uclauses, @@ -1730,45 +1732,19 @@ match_unique_clauses(PlannerInfo *root, RelOptInfo *outer, List *uclauses, foreach_node(RestrictInfo, rinfo, uclauses) { Expr *clause; - Node *iclause; - Node *c1; bool matched = false; Assert(outer->relid > 0 && relid > 0); - /* Only filters like f(R.x1,...,R.xN) == expr we should consider. */ - Assert(bms_is_empty(rinfo->left_relids) ^ - bms_is_empty(rinfo->right_relids)); - clause = (Expr *) copyObject(rinfo->clause); ChangeVarNodes((Node *) clause, relid, outer->relid, 0); - iclause = bms_is_empty(rinfo->left_relids) ? get_rightop(clause) : - get_leftop(clause); - c1 = bms_is_empty(rinfo->left_relids) ? get_leftop(clause) : - get_rightop(clause); - /* - * Compare these left and right sides with the corresponding sides of - * the outer's filters. If no one is detected - return immediately. + * Look for an identical restriction on the outer relation. */ foreach_node(RestrictInfo, orinfo, outer->baserestrictinfo) { - Node *oclause; - Node *c2; - - if (orinfo->mergeopfamilies == NIL) - /* Don't consider clauses that aren't similar to 'F(X)=G(Y)' */ - continue; - - Assert(is_opclause(orinfo->clause)); - - oclause = bms_is_empty(orinfo->left_relids) ? - get_rightop(orinfo->clause) : get_leftop(orinfo->clause); - c2 = (bms_is_empty(orinfo->left_relids) ? - get_leftop(orinfo->clause) : get_rightop(orinfo->clause)); - - if (equal(iclause, oclause) && equal(c1, c2)) + if (equal(clause, orinfo->clause)) { matched = true; break; -- 2.43.0
