aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/optimizer/path/equivclass.c28
-rw-r--r--src/test/regress/expected/join.out10
-rw-r--r--src/test/regress/expected/partition_join.out14
-rw-r--r--src/test/regress/expected/tidscan.out2
4 files changed, 33 insertions, 21 deletions
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index f962ff82ad2..e65b967b1f0 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -1382,7 +1382,9 @@ generate_base_implied_equalities_broken(PlannerInfo *root,
* whenever we select a particular pair of EquivalenceMembers to join,
* we check to see if the pair matches any original clause (in ec_sources)
* or previously-built clause (in ec_derives). This saves memory and allows
- * re-use of information cached in RestrictInfos.
+ * re-use of information cached in RestrictInfos. We also avoid generating
+ * commutative duplicates, i.e. if the algorithm selects "a.x = b.y" but
+ * we already have "b.y = a.x", we return the existing clause.
*
* join_relids should always equal bms_union(outer_relids, inner_rel->relids).
* We could simplify this function's API by computing it internally, but in
@@ -1790,7 +1792,8 @@ select_equality_operator(EquivalenceClass *ec, Oid lefttype, Oid righttype)
/*
* create_join_clause
* Find or make a RestrictInfo comparing the two given EC members
- * with the given operator.
+ * with the given operator (or, possibly, its commutator, because
+ * the ordering of the operands in the result is not guaranteed).
*
* parent_ec is either equal to ec (if the clause is a potentially-redundant
* join clause) or NULL (if not). We have to treat this as part of the
@@ -1811,16 +1814,22 @@ create_join_clause(PlannerInfo *root,
/*
* Search to see if we already built a RestrictInfo for this pair of
* EquivalenceMembers. We can use either original source clauses or
- * previously-derived clauses. The check on opno is probably redundant,
- * but be safe ...
+ * previously-derived clauses, and a commutator clause is acceptable.
+ *
+ * We used to verify that opno matches, but that seems redundant: even if
+ * it's not identical, it'd better have the same effects, or the operator
+ * families we're using are broken.
*/
foreach(lc, ec->ec_sources)
{
rinfo = (RestrictInfo *) lfirst(lc);
if (rinfo->left_em == leftem &&
rinfo->right_em == rightem &&
- rinfo->parent_ec == parent_ec &&
- opno == ((OpExpr *) rinfo->clause)->opno)
+ rinfo->parent_ec == parent_ec)
+ return rinfo;
+ if (rinfo->left_em == rightem &&
+ rinfo->right_em == leftem &&
+ rinfo->parent_ec == parent_ec)
return rinfo;
}
@@ -1829,8 +1838,11 @@ create_join_clause(PlannerInfo *root,
rinfo = (RestrictInfo *) lfirst(lc);
if (rinfo->left_em == leftem &&
rinfo->right_em == rightem &&
- rinfo->parent_ec == parent_ec &&
- opno == ((OpExpr *) rinfo->clause)->opno)
+ rinfo->parent_ec == parent_ec)
+ return rinfo;
+ if (rinfo->left_em == rightem &&
+ rinfo->right_em == leftem &&
+ rinfo->parent_ec == parent_ec)
return rinfo;
}
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 08334761ae6..9b69a8c1221 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3149,7 +3149,7 @@ where i41.f1 > 0;
-> Seq Scan on int4_tbl i41
Filter: (f1 > 0)
-> Nested Loop
- Join Filter: (i41.f1 = i42.f1)
+ Join Filter: (i42.f1 = i41.f1)
-> Seq Scan on int8_tbl i81
-> Materialize
-> Seq Scan on int4_tbl i42
@@ -4871,7 +4871,7 @@ where ss.stringu2 !~* ss.case1;
QUERY PLAN
--------------------------------------------------------------------------------------------
Nested Loop
- Join Filter: (CASE t1.ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END = t0.f1)
+ Join Filter: (t0.f1 = CASE t1.ten WHEN 0 THEN 'doh!'::text ELSE NULL::text END)
-> Nested Loop
-> Seq Scan on int4_tbl i4
-> Index Scan using tenk1_unique2 on tenk1 t1
@@ -6533,7 +6533,7 @@ where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1;
-----------------------------------------
Merge Join
Merge Cond: (j1.id1 = j2.id1)
- Join Filter: (j1.id2 = j2.id2)
+ Join Filter: (j2.id2 = j1.id2)
-> Index Scan using j1_id1_idx on j1
-> Index Scan using j2_id1_idx on j2
(5 rows)
@@ -6555,7 +6555,7 @@ where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 = any (array[1]);
----------------------------------------------------
Merge Join
Merge Cond: (j1.id1 = j2.id1)
- Join Filter: (j1.id2 = j2.id2)
+ Join Filter: (j2.id2 = j1.id2)
-> Index Scan using j1_id1_idx on j1
-> Index Scan using j2_id1_idx on j2
Index Cond: (id1 = ANY ('{1}'::integer[]))
@@ -6578,7 +6578,7 @@ where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 >= any (array[1,5]);
-------------------------------------------------------
Merge Join
Merge Cond: (j1.id1 = j2.id1)
- Join Filter: (j1.id2 = j2.id2)
+ Join Filter: (j2.id2 = j1.id2)
-> Index Scan using j1_id1_idx on j1
-> Index Only Scan using j2_pkey on j2
Index Cond: (id1 >= ANY ('{1,5}'::integer[]))
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index bb5b7c47a45..b20facc19fb 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -304,7 +304,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t2.b FROM prt2 t2 WHERE t2.a = 0)
-> Seq Scan on prt2_p2 t2_2
Filter: (a = 0)
-> Nested Loop Semi Join
- Join Filter: (t1_3.a = t2_3.b)
+ Join Filter: (t2_3.b = t1_3.a)
-> Seq Scan on prt1_p3 t1_3
Filter: (b = 0)
-> Materialize
@@ -601,7 +601,7 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t
Sort Key: t1.a
-> Append
-> Nested Loop
- Join Filter: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
+ Join Filter: (((t3_1.a + t3_1.b) / 2) = t1_1.a)
-> Hash Join
Hash Cond: (t2_1.b = t1_1.a)
-> Seq Scan on prt2_p1 t2_1
@@ -611,7 +611,7 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t
-> Index Scan using iprt1_e_p1_ab2 on prt1_e_p1 t3_1
Index Cond: (((a + b) / 2) = t2_1.b)
-> Nested Loop
- Join Filter: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
+ Join Filter: (((t3_2.a + t3_2.b) / 2) = t1_2.a)
-> Hash Join
Hash Cond: (t2_2.b = t1_2.a)
-> Seq Scan on prt2_p2 t2_2
@@ -621,7 +621,7 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t
-> Index Scan using iprt1_e_p2_ab2 on prt1_e_p2 t3_2
Index Cond: (((a + b) / 2) = t2_2.b)
-> Nested Loop
- Join Filter: (t1_3.a = ((t3_3.a + t3_3.b) / 2))
+ Join Filter: (((t3_3.a + t3_3.b) / 2) = t1_3.a)
-> Hash Join
Hash Cond: (t2_3.b = t1_3.a)
-> Seq Scan on prt2_p3 t2_3
@@ -926,7 +926,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER
Sort Key: t1.a
-> Append
-> Nested Loop
- Join Filter: (t1_2.a = t1_5.b)
+ Join Filter: (t1_5.b = t1_2.a)
-> HashAggregate
Group Key: t1_5.b
-> Hash Join
@@ -939,7 +939,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER
Index Cond: (a = ((t2_1.a + t2_1.b) / 2))
Filter: (b = 0)
-> Nested Loop
- Join Filter: (t1_3.a = t1_6.b)
+ Join Filter: (t1_6.b = t1_3.a)
-> HashAggregate
Group Key: t1_6.b
-> Hash Join
@@ -952,7 +952,7 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER
Index Cond: (a = ((t2_2.a + t2_2.b) / 2))
Filter: (b = 0)
-> Nested Loop
- Join Filter: (t1_4.a = t1_7.b)
+ Join Filter: (t1_7.b = t1_4.a)
-> HashAggregate
Group Key: t1_7.b
-> Nested Loop
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index 13c3c360c25..f133b5a4ac7 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -119,7 +119,7 @@ FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1;
-> Seq Scan on tidscan t1
Filter: (id = 1)
-> Tid Scan on tidscan t2
- TID Cond: (ctid = t1.ctid)
+ TID Cond: (t1.ctid = ctid)
(5 rows)
SELECT t1.ctid, t1.*, t2.ctid, t2.*
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 13:39:02 +0000