aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--contrib/postgres_fdw/expected/postgres_fdw.out15
-rw-r--r--src/backend/commands/explain.c3
-rw-r--r--src/backend/executor/nodeHashjoin.c15
-rw-r--r--src/backend/optimizer/path/joinpath.c42
-rw-r--r--src/backend/optimizer/path/joinrels.c3
-rw-r--r--src/backend/optimizer/path/pathkeys.c3
-rw-r--r--src/backend/optimizer/prep/prepjointree.c6
-rw-r--r--src/include/nodes/nodes.h9
-rw-r--r--src/include/nodes/pathnodes.h6
-rw-r--r--src/test/regress/expected/join.out27
-rw-r--r--src/test/regress/expected/partition_join.out216
-rw-r--r--src/test/regress/expected/select_parallel.out32
-rw-r--r--src/test/regress/expected/updatable_views.out6
-rw-r--r--src/test/regress/sql/join.sql8
14 files changed, 220 insertions, 171 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index ea566d50341..1f223091949 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -4127,13 +4127,16 @@ RESET enable_sort;
-- subquery using immutable function (can be sent to remote)
PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c5) = '1970-01-17'::date) ORDER BY c1;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20);
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Foreign Scan
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
- Relations: (public.ft1 t1) SEMI JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" < 20)) AND EXISTS (SELECT NULL FROM "S 1"."T 1" r3 WHERE ((r3."C 1" > 10)) AND ((date(r3.c5) = '1970-01-17'::date)) AND ((r3.c3 = r1.c3))) ORDER BY r1."C 1" ASC NULLS LAST
-(4 rows)
+ Sort Key: t1.c1
+ -> Foreign Scan
+ Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
+ Relations: (public.ft1 t1) SEMI JOIN (public.ft2 t2)
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" < 20)) AND EXISTS (SELECT NULL FROM "S 1"."T 1" r3 WHERE ((r3."C 1" > 10)) AND ((date(r3.c5) = '1970-01-17'::date)) AND ((r3.c3 = r1.c3)))
+(7 rows)
EXECUTE st3(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 94511a5a024..30de9de9d4f 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1749,6 +1749,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
case JOIN_ANTI:
jointype = "Anti";
break;
+ case JOIN_RIGHT_SEMI:
+ jointype = "Right Semi";
+ break;
case JOIN_RIGHT_ANTI:
jointype = "Right Anti";
break;
diff --git a/src/backend/executor/nodeHashjoin.c b/src/backend/executor/nodeHashjoin.c
index dbf114cd5eb..c46764023df 100644
--- a/src/backend/executor/nodeHashjoin.c
+++ b/src/backend/executor/nodeHashjoin.c
@@ -534,6 +534,14 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
}
/*
+ * In a right-semijoin, we only need the first match for each
+ * inner tuple.
+ */
+ if (node->js.jointype == JOIN_RIGHT_SEMI &&
+ HeapTupleHeaderHasMatch(HJTUPLE_MINTUPLE(node->hj_CurTuple)))
+ continue;
+
+ /*
* We've got a match, but still need to test non-hashed quals.
* ExecScanHashBucket already set up all the state needed to
* call ExecQual.
@@ -549,10 +557,10 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
{
node->hj_MatchedOuter = true;
-
/*
- * This is really only needed if HJ_FILL_INNER(node), but
- * we'll avoid the branch and just set it always.
+ * This is really only needed if HJ_FILL_INNER(node) or if
+ * we are in a right-semijoin, but we'll avoid the branch
+ * and just set it always.
*/
if (!HeapTupleHeaderHasMatch(HJTUPLE_MINTUPLE(node->hj_CurTuple)))
HeapTupleHeaderSetMatch(HJTUPLE_MINTUPLE(node->hj_CurTuple));
@@ -779,6 +787,7 @@ ExecInitHashJoin(HashJoin *node, EState *estate, int eflags)
{
case JOIN_INNER:
case JOIN_SEMI:
+ case JOIN_RIGHT_SEMI:
break;
case JOIN_LEFT:
case JOIN_ANTI:
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 5be8da9e095..40eb58341c1 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -288,8 +288,8 @@ add_paths_to_joinrel(PlannerInfo *root,
* sorted. This includes both nestloops and mergejoins where the outer
* path is already ordered. Again, skip this if we can't mergejoin.
* (That's okay because we know that nestloop can't handle
- * right/right-anti/full joins at all, so it wouldn't work in the
- * prohibited cases either.)
+ * right/right-anti/right-semi/full joins at all, so it wouldn't work in
+ * the prohibited cases either.)
*/
if (mergejoin_allowed)
match_unsorted_outer(root, joinrel, outerrel, innerrel,
@@ -1729,6 +1729,13 @@ match_unsorted_outer(PlannerInfo *root,
ListCell *lc1;
/*
+ * For now we do not support RIGHT_SEMI join in mergejoin or nestloop
+ * join.
+ */
+ if (jointype == JOIN_RIGHT_SEMI)
+ return;
+
+ /*
* Nestloop only supports inner, left, semi, and anti joins. Also, if we
* are doing a right, right-anti or full mergejoin, we must use *all* the
* mergeclauses as join clauses, else we will not have a valid plan.
@@ -2297,12 +2304,13 @@ hash_inner_and_outer(PlannerInfo *root,
* total inner path will also be parallel-safe, but if not, we'll
* have to search for the cheapest safe, unparameterized inner
* path. If doing JOIN_UNIQUE_INNER, we can't use any alternative
- * inner path. If full, right, or right-anti join, we can't use
- * parallelism (building the hash table in each backend) because
- * no one process has all the match bits.
+ * inner path. If full, right, right-semi or right-anti join, we
+ * can't use parallelism (building the hash table in each backend)
+ * because no one process has all the match bits.
*/
if (save_jointype == JOIN_FULL ||
save_jointype == JOIN_RIGHT ||
+ save_jointype == JOIN_RIGHT_SEMI ||
save_jointype == JOIN_RIGHT_ANTI)
cheapest_safe_inner = NULL;
else if (cheapest_total_inner->parallel_safe)
@@ -2327,13 +2335,13 @@ hash_inner_and_outer(PlannerInfo *root,
* Returns a list of RestrictInfo nodes for those clauses.
*
* *mergejoin_allowed is normally set to true, but it is set to false if
- * this is a right/right-anti/full join and there are nonmergejoinable join
- * clauses. The executor's mergejoin machinery cannot handle such cases, so
- * we have to avoid generating a mergejoin plan. (Note that this flag does
- * NOT consider whether there are actually any mergejoinable clauses. This is
- * correct because in some cases we need to build a clauseless mergejoin.
- * Simply returning NIL is therefore not enough to distinguish safe from
- * unsafe cases.)
+ * this is a right-semi join, or this is a right/right-anti/full join and
+ * there are nonmergejoinable join clauses. The executor's mergejoin
+ * machinery cannot handle such cases, so we have to avoid generating a
+ * mergejoin plan. (Note that this flag does NOT consider whether there are
+ * actually any mergejoinable clauses. This is correct because in some
+ * cases we need to build a clauseless mergejoin. Simply returning NIL is
+ * therefore not enough to distinguish safe from unsafe cases.)
*
* We also mark each selected RestrictInfo to show which side is currently
* being considered as outer. These are transient markings that are only
@@ -2357,6 +2365,16 @@ select_mergejoin_clauses(PlannerInfo *root,
bool have_nonmergeable_joinclause = false;
ListCell *l;
+ /*
+ * For now we do not support RIGHT_SEMI join in mergejoin: the benefit of
+ * swapping inputs tends to be small here.
+ */
+ if (jointype == JOIN_RIGHT_SEMI)
+ {
+ *mergejoin_allowed = false;
+ return NIL;
+ }
+
foreach(l, restrictlist)
{
RestrictInfo *restrictinfo = (RestrictInfo *) lfirst(l);
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index db475e25b15..a3677f824fe 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -991,6 +991,9 @@ populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1,
add_paths_to_joinrel(root, joinrel, rel1, rel2,
JOIN_SEMI, sjinfo,
restrictlist);
+ add_paths_to_joinrel(root, joinrel, rel2, rel1,
+ JOIN_RIGHT_SEMI, sjinfo,
+ restrictlist);
}
/*
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 416fc4e240b..e25798972f6 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -1294,6 +1294,9 @@ build_join_pathkeys(PlannerInfo *root,
JoinType jointype,
List *outer_pathkeys)
{
+ /* RIGHT_SEMI should not come here */
+ Assert(jointype != JOIN_RIGHT_SEMI);
+
if (jointype == JOIN_FULL ||
jointype == JOIN_RIGHT ||
jointype == JOIN_RIGHT_ANTI)
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 5482ab85a76..969e257f70b 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -455,8 +455,8 @@ pull_up_sublinks_jointree_recurse(PlannerInfo *root, Node *jtnode,
* point of the available_rels machinations is to ensure that we only
* pull up quals for which that's okay.
*
- * We don't expect to see any pre-existing JOIN_SEMI, JOIN_ANTI, or
- * JOIN_RIGHT_ANTI jointypes here.
+ * We don't expect to see any pre-existing JOIN_SEMI, JOIN_ANTI,
+ * JOIN_RIGHT_SEMI, or JOIN_RIGHT_ANTI jointypes here.
*/
switch (j->jointype)
{
@@ -2950,7 +2950,7 @@ reduce_outer_joins_pass2(Node *jtnode,
* These could only have been introduced by pull_up_sublinks,
* so there's no way that upper quals could refer to their
* righthand sides, and no point in checking. We don't expect
- * to see JOIN_RIGHT_ANTI yet.
+ * to see JOIN_RIGHT_SEMI or JOIN_RIGHT_ANTI yet.
*/
break;
default:
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 855009fd6e2..0d71d821f71 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -306,6 +306,7 @@ typedef enum JoinType
*/
JOIN_SEMI, /* 1 copy of each LHS row that has match(es) */
JOIN_ANTI, /* 1 copy of each LHS row that has no match */
+ JOIN_RIGHT_SEMI, /* 1 copy of each RHS row that has match(es) */
JOIN_RIGHT_ANTI, /* 1 copy of each RHS row that has no match */
/*
@@ -322,10 +323,10 @@ typedef enum JoinType
/*
* OUTER joins are those for which pushed-down quals must behave differently
- * from the join's own quals. This is in fact everything except INNER and
- * SEMI joins. However, this macro must also exclude the JOIN_UNIQUE symbols
- * since those are temporary proxies for what will eventually be an INNER
- * join.
+ * from the join's own quals. This is in fact everything except INNER, SEMI
+ * and RIGHT_SEMI joins. However, this macro must also exclude the
+ * JOIN_UNIQUE symbols since those are temporary proxies for what will
+ * eventually be an INNER join.
*
* Note: semijoins are a hybrid case, but we choose to treat them as not
* being outer joins. This is okay principally because the SQL syntax makes
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 2ba297c1172..14ccfc1ac1c 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2823,9 +2823,9 @@ typedef struct PlaceHolderVar
* min_lefthand and min_righthand for higher joins.)
*
* jointype is never JOIN_RIGHT; a RIGHT JOIN is handled by switching
- * the inputs to make it a LEFT JOIN. It's never JOIN_RIGHT_ANTI either.
- * So the allowed values of jointype in a join_info_list member are only
- * LEFT, FULL, SEMI, or ANTI.
+ * the inputs to make it a LEFT JOIN. It's never JOIN_RIGHT_SEMI or
+ * JOIN_RIGHT_ANTI either. So the allowed values of jointype in a
+ * join_info_list member are only LEFT, FULL, SEMI, or ANTI.
*
* ojrelid is the RT index of the join RTE representing this outer join,
* if there is one. It is zero when jointype is INNER or SEMI, and can be
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 6b16c3a6769..9142dab171f 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -1905,23 +1905,24 @@ SELECT *
-- semijoin selectivity for <>
--
explain (costs off)
-select * from int4_tbl i4, tenk1 a
-where exists(select * from tenk1 b
- where a.twothousand = b.twothousand and a.fivethous <> b.fivethous)
- and i4.f1 = a.tenthous;
- QUERY PLAN
-----------------------------------------------
+select * from tenk1 a, tenk1 b
+where exists(select * from tenk1 c
+ where b.twothousand = c.twothousand and b.fivethous <> c.fivethous)
+ and a.tenthous = b.tenthous and a.tenthous < 5000;
+ QUERY PLAN
+-----------------------------------------------
Hash Semi Join
- Hash Cond: (a.twothousand = b.twothousand)
- Join Filter: (a.fivethous <> b.fivethous)
+ Hash Cond: (b.twothousand = c.twothousand)
+ Join Filter: (b.fivethous <> c.fivethous)
-> Hash Join
- Hash Cond: (a.tenthous = i4.f1)
- -> Seq Scan on tenk1 a
+ Hash Cond: (b.tenthous = a.tenthous)
+ -> Seq Scan on tenk1 b
-> Hash
- -> Seq Scan on int4_tbl i4
+ -> Seq Scan on tenk1 a
+ Filter: (tenthous < 5000)
-> Hash
- -> Seq Scan on tenk1 b
-(10 rows)
+ -> Seq Scan on tenk1 c
+(11 rows)
--
-- More complicated constructs
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 6d07f86b9bc..53591a4f2d5 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -2543,24 +2543,24 @@ SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a
Sort
Sort Key: t1.a
-> Append
- -> Hash Semi Join
- Hash Cond: (t1_1.a = t2_1.b)
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
+ -> Hash Right Semi Join
+ Hash Cond: (t2_1.b = t1_1.a)
+ -> Seq Scan on prt2_adv_p1 t2_1
-> Hash
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Hash Semi Join
- Hash Cond: (t1_2.a = t2_2.b)
- -> Seq Scan on prt1_adv_p2 t1_2
- Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p1 t1_1
+ Filter: (b = 0)
+ -> Hash Right Semi Join
+ Hash Cond: (t2_2.b = t1_2.a)
+ -> Seq Scan on prt2_adv_p2 t2_2
-> Hash
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Hash Semi Join
- Hash Cond: (t1_3.a = t2_3.b)
- -> Seq Scan on prt1_adv_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p2 t1_2
+ Filter: (b = 0)
+ -> Hash Right Semi Join
+ Hash Cond: (t2_3.b = t1_3.a)
+ -> Seq Scan on prt2_adv_p3 t2_3
-> Hash
- -> Seq Scan on prt2_adv_p3 t2_3
+ -> Seq Scan on prt1_adv_p3 t1_3
+ Filter: (b = 0)
(21 rows)
SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
@@ -2752,24 +2752,24 @@ SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a
Sort
Sort Key: t1.a
-> Append
- -> Hash Semi Join
- Hash Cond: (t1_1.a = t2_1.b)
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
+ -> Hash Right Semi Join
+ Hash Cond: (t2_1.b = t1_1.a)
+ -> Seq Scan on prt2_adv_p1 t2_1
-> Hash
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Hash Semi Join
- Hash Cond: (t1_2.a = t2_2.b)
- -> Seq Scan on prt1_adv_p2 t1_2
- Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p1 t1_1
+ Filter: (b = 0)
+ -> Hash Right Semi Join
+ Hash Cond: (t2_2.b = t1_2.a)
+ -> Seq Scan on prt2_adv_p2 t2_2
-> Hash
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Hash Semi Join
- Hash Cond: (t1_3.a = t2_3.b)
- -> Seq Scan on prt1_adv_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p2 t1_2
+ Filter: (b = 0)
+ -> Hash Right Semi Join
+ Hash Cond: (t2_3.b = t1_3.a)
+ -> Seq Scan on prt2_adv_p3 t2_3
-> Hash
- -> Seq Scan on prt2_adv_p3 t2_3
+ -> Seq Scan on prt1_adv_p3 t1_3
+ Filter: (b = 0)
(21 rows)
SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
@@ -3036,25 +3036,25 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a =
-- semi join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Sort
Sort Key: t1.a
- -> Hash Semi Join
- Hash Cond: (t1.a = t2.b)
+ -> Hash Right Semi Join
+ Hash Cond: (t2.b = t1.a)
-> Append
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p2 t1_2
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt2_adv_p1 t2_1
+ -> Seq Scan on prt2_adv_p2 t2_2
+ -> Seq Scan on prt2_adv_p3_1 t2_3
+ -> Seq Scan on prt2_adv_p3_2 t2_4
-> Hash
-> Append
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Seq Scan on prt2_adv_p3_1 t2_3
- -> Seq Scan on prt2_adv_p3_2 t2_4
+ -> Seq Scan on prt1_adv_p1 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p2 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p3 t1_3
+ Filter: (b = 0)
(17 rows)
-- left join
@@ -3433,27 +3433,30 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a =
-- semi join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
- -> Nested Loop Semi Join
- Join Filter: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
- -> Seq Scan on plt1_adv_p1 t1_1
- Filter: (b < 10)
+ -> Hash Right Semi Join
+ Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
-> Seq Scan on plt2_adv_p1 t2_1
- -> Nested Loop Semi Join
- Join Filter: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
- -> Seq Scan on plt1_adv_p2 t1_2
- Filter: (b < 10)
+ -> Hash
+ -> Seq Scan on plt1_adv_p1 t1_1
+ Filter: (b < 10)
+ -> Hash Right Semi Join
+ Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
-> Seq Scan on plt2_adv_p2 t2_2
- -> Nested Loop Semi Join
- Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
- -> Seq Scan on plt1_adv_p3 t1_3
- Filter: (b < 10)
+ -> Hash
+ -> Seq Scan on plt1_adv_p2 t1_2
+ Filter: (b < 10)
+ -> Hash Right Semi Join
+ Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
-> Seq Scan on plt2_adv_p3 t2_3
-(18 rows)
+ -> Hash
+ -> Seq Scan on plt1_adv_p3 t1_3
+ Filter: (b < 10)
+(21 rows)
SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
a | b | c
@@ -3623,27 +3626,30 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a =
-- semi join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
- -> Nested Loop Semi Join
- Join Filter: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
- -> Seq Scan on plt1_adv_p1 t1_1
- Filter: (b < 10)
+ -> Hash Right Semi Join
+ Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
-> Seq Scan on plt2_adv_p1 t2_1
- -> Nested Loop Semi Join
- Join Filter: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
- -> Seq Scan on plt1_adv_p2 t1_2
- Filter: (b < 10)
+ -> Hash
+ -> Seq Scan on plt1_adv_p1 t1_1
+ Filter: (b < 10)
+ -> Hash Right Semi Join
+ Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
-> Seq Scan on plt2_adv_p2 t2_2
- -> Nested Loop Semi Join
- Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
- -> Seq Scan on plt1_adv_p3 t1_3
- Filter: (b < 10)
+ -> Hash
+ -> Seq Scan on plt1_adv_p2 t1_2
+ Filter: (b < 10)
+ -> Hash Right Semi Join
+ Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
-> Seq Scan on plt2_adv_p3 t2_3
-(18 rows)
+ -> Hash
+ -> Seq Scan on plt1_adv_p3 t1_3
+ Filter: (b < 10)
+(21 rows)
SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
a | b | c
@@ -3839,25 +3845,25 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a =
-- semi join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Sort
Sort Key: t1.a
- -> Hash Semi Join
- Hash Cond: ((t1.a = t2.a) AND (t1.c = t2.c))
+ -> Hash Right Semi Join
+ Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
-> Append
- -> Seq Scan on plt1_adv_p1 t1_1
- Filter: (b < 10)
- -> Seq Scan on plt1_adv_p2 t1_2
- Filter: (b < 10)
- -> Seq Scan on plt1_adv_p3 t1_3
- Filter: (b < 10)
+ -> Seq Scan on plt2_adv_p1 t2_1
+ -> Seq Scan on plt2_adv_p2_1 t2_2
+ -> Seq Scan on plt2_adv_p2_2 t2_3
+ -> Seq Scan on plt2_adv_p3 t2_4
-> Hash
-> Append
- -> Seq Scan on plt2_adv_p1 t2_1
- -> Seq Scan on plt2_adv_p2_1 t2_2
- -> Seq Scan on plt2_adv_p2_2 t2_3
- -> Seq Scan on plt2_adv_p3 t2_4
+ -> Seq Scan on plt1_adv_p1 t1_1
+ Filter: (b < 10)
+ -> Seq Scan on plt1_adv_p2 t1_2
+ Filter: (b < 10)
+ -> Seq Scan on plt1_adv_p3 t1_3
+ Filter: (b < 10)
(17 rows)
-- left join
@@ -3987,28 +3993,30 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a =
-- semi join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------
Sort
Sort Key: t1.a
-> Append
- -> Hash Semi Join
- Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
- -> Seq Scan on plt1_adv_p1_null t1_1
- Filter: (b < 10)
+ -> Hash Right Semi Join
+ Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
+ -> Seq Scan on plt2_adv_p1 t2_1
-> Hash
- -> Seq Scan on plt2_adv_p1 t2_1
- -> Nested Loop Semi Join
- Join Filter: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
- -> Seq Scan on plt1_adv_p2 t1_2
- Filter: (b < 10)
+ -> Seq Scan on plt1_adv_p1_null t1_1
+ Filter: (b < 10)
+ -> Hash Right Semi Join
+ Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
-> Seq Scan on plt2_adv_p2 t2_2
- -> Nested Loop Semi Join
- Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
- -> Seq Scan on plt1_adv_p3 t1_3
- Filter: (b < 10)
+ -> Hash
+ -> Seq Scan on plt1_adv_p2 t1_2
+ Filter: (b < 10)
+ -> Hash Right Semi Join
+ Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
-> Seq Scan on plt2_adv_p3_null t2_3
-(19 rows)
+ -> Hash
+ -> Seq Scan on plt1_adv_p3 t1_3
+ Filter: (b < 10)
+(21 rows)
SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
a | b | c
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 87273fa635e..c96285d1bb6 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -1076,26 +1076,26 @@ reset role;
explain (costs off, verbose)
select count(*) from tenk1 a where (unique1, two) in
(select unique1, row_number() over() from tenk1 b);
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------
Aggregate
Output: count(*)
- -> Hash Semi Join
- Hash Cond: ((a.unique1 = b.unique1) AND (a.two = (row_number() OVER (?))))
- -> Gather
+ -> Hash Right Semi Join
+ Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER (?)) = a.two))
+ -> WindowAgg
+ Output: b.unique1, row_number() OVER (?)
+ -> Gather
+ Output: b.unique1
+ Workers Planned: 4
+ -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 b
+ Output: b.unique1
+ -> Hash
Output: a.unique1, a.two
- Workers Planned: 4
- -> Parallel Seq Scan on public.tenk1 a
+ -> Gather
Output: a.unique1, a.two
- -> Hash
- Output: b.unique1, (row_number() OVER (?))
- -> WindowAgg
- Output: b.unique1, row_number() OVER (?)
- -> Gather
- Output: b.unique1
- Workers Planned: 4
- -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 b
- Output: b.unique1
+ Workers Planned: 4
+ -> Parallel Seq Scan on public.tenk1 a
+ Output: a.unique1, a.two
(18 rows)
-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 1d1f568bc47..9c21b768002 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3287,10 +3287,10 @@ NOTICE: snooped value: 8
SELECT * FROM v1 WHERE b=8;
a | b | c | d
---+---+------+------
- 9 | 8 | t1 | t11d
- 9 | 8 | t11 | t11d
- 9 | 8 | t12 | t11d
9 | 8 | t111 | t11d
+ 9 | 8 | t12 | t11d
+ 9 | 8 | t11 | t11d
+ 9 | 8 | t1 | t11d
(4 rows)
DELETE FROM v1 WHERE snoop(a) AND leakproof(a); -- should not delete everything, just where a>5
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 8bfe3b7ba67..e3d26520832 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -214,10 +214,10 @@ SELECT *
-- semijoin selectivity for <>
--
explain (costs off)
-select * from int4_tbl i4, tenk1 a
-where exists(select * from tenk1 b
- where a.twothousand = b.twothousand and a.fivethous <> b.fivethous)
- and i4.f1 = a.tenthous;
+select * from tenk1 a, tenk1 b
+where exists(select * from tenk1 c
+ where b.twothousand = c.twothousand and b.fivethous <> c.fivethous)
+ and a.tenthous = b.tenthous and a.tenthous < 5000;
--
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-19 03:43:38 +0000