v5-0001-Using-each-rel-as-both-outer-and-inner-for-anti-j.patch
application/octet-stream
Filename: v5-0001-Using-each-rel-as-both-outer-and-inner-for-anti-j.patch
Type: application/octet-stream
Part: 0
Patch
Format: format-patch
Series: patch v5-0001
Subject: Using each rel as both outer and inner for anti joins
| File | + | − |
|---|---|---|
| src/backend/commands/explain.c | 3 | 0 |
| src/backend/executor/nodeHashjoin.c | 25 | 16 |
| src/backend/executor/nodeMergejoin.c | 20 | 12 |
| src/backend/optimizer/path/costsize.c | 2 | 1 |
| src/backend/optimizer/path/joinpath.c | 31 | 24 |
| src/backend/optimizer/path/joinrels.c | 3 | 0 |
| src/backend/optimizer/path/pathkeys.c | 6 | 4 |
| src/backend/optimizer/plan/setrefs.c | 1 | 0 |
| src/include/nodes/execnodes.h | 2 | 1 |
| src/include/nodes/nodes.h | 3 | 1 |
| src/test/regress/expected/partition_join.out | 142 | 134 |
From 12aabf4cc858e00e266d1b5224685b7496f352c2 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 8 Aug 2022 14:44:01 +0800
Subject: [PATCH v5] Using each rel as both outer and inner for anti joins
---
src/backend/commands/explain.c | 3 +
src/backend/executor/nodeHashjoin.c | 41 +--
src/backend/executor/nodeMergejoin.c | 32 ++-
src/backend/optimizer/path/costsize.c | 3 +-
src/backend/optimizer/path/joinpath.c | 55 ++--
src/backend/optimizer/path/joinrels.c | 3 +
src/backend/optimizer/path/pathkeys.c | 10 +-
src/backend/optimizer/plan/setrefs.c | 1 +
src/include/nodes/execnodes.h | 3 +-
src/include/nodes/nodes.h | 4 +-
src/test/regress/expected/partition_join.out | 276 ++++++++++---------
11 files changed, 238 insertions(+), 193 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index e078456b19..cb093f31f1 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1550,6 +1550,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
case JOIN_ANTI:
jointype = "Anti";
break;
+ case JOIN_RIGHT_ANTI:
+ jointype = "Right Anti";
+ break;
default:
jointype = "???";
break;
diff --git a/src/backend/executor/nodeHashjoin.c b/src/backend/executor/nodeHashjoin.c
index 87403e2478..140db24afe 100644
--- a/src/backend/executor/nodeHashjoin.c
+++ b/src/backend/executor/nodeHashjoin.c
@@ -217,10 +217,10 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
/*
* If the outer relation is completely empty, and it's not
- * right/full join, we can quit without building the hash
- * table. However, for an inner join it is only a win to
- * check this when the outer relation's startup cost is less
- * than the projected cost of building the hash table.
+ * right/right-anti/full join, we can quit without building
+ * the hash table. However, for an inner join it is only a
+ * win to check this when the outer relation's startup cost is
+ * less than the projected cost of building the hash table.
* Otherwise it's best to build the hash table first and see
* if the inner relation is empty. (When it's a left join, we
* should always make this check, since we aren't going to be
@@ -458,11 +458,10 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
if (parallel)
{
/*
- * Full/right outer joins are currently not supported
- * for parallel joins, so we don't need to set the
- * match bit. Experiments show that it's worth
- * avoiding the shared memory traffic on large
- * systems.
+ * Full/right/right-anti outer joins are currently not
+ * supported for parallel joins, so we don't need to
+ * set the match bit. Experiments show that it's worth
+ * avoiding the shared memory traffic on large systems.
*/
Assert(!HJ_FILL_INNER(node));
}
@@ -482,6 +481,14 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
continue;
}
+ /*
+ * In a right-antijoin, we never return a matched tuple.
+ * And we need to use current outer tuple to scan the
+ * bucket for matches
+ */
+ if (node->js.jointype == JOIN_RIGHT_ANTI)
+ continue;
+
/*
* If we only need to join to the first matching inner
* tuple, then consider returning this one, but after that
@@ -527,9 +534,10 @@ ExecHashJoinImpl(PlanState *pstate, bool parallel)
case HJ_FILL_INNER_TUPLES:
/*
- * We have finished a batch, but we are doing right/full join,
- * so any unmatched inner tuples in the hashtable have to be
- * emitted before we continue to the next batch.
+ * We have finished a batch, but we are doing
+ * right/right-anti/full join, so any unmatched inner tuples in
+ * the hashtable have to be emitted before we continue to the
+ * next batch.
*/
if (!ExecScanHashTableForUnmatched(node, econtext))
{
@@ -694,6 +702,7 @@ ExecInitHashJoin(HashJoin *node, EState *estate, int eflags)
ExecInitNullTupleSlot(estate, innerDesc, &TTSOpsVirtual);
break;
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
hjstate->hj_NullOuterTupleSlot =
ExecInitNullTupleSlot(estate, outerDesc, &TTSOpsVirtual);
break;
@@ -987,8 +996,8 @@ ExecHashJoinNewBatch(HashJoinState *hjstate)
* side, but there are exceptions:
*
* 1. In a left/full outer join, we have to process outer batches even if
- * the inner batch is empty. Similarly, in a right/full outer join, we
- * have to process inner batches even if the outer batch is empty.
+ * the inner batch is empty. Similarly, in a right/right-anti/full outer
+ * join, we have to process inner batches even if the outer batch is empty.
*
* 2. If we have increased nbatch since the initial estimate, we have to
* scan inner batches since they might contain tuples that need to be
@@ -1308,8 +1317,8 @@ ExecReScanHashJoin(HashJoinState *node)
/*
* Okay to reuse the hash table; needn't rescan inner, either.
*
- * However, if it's a right/full join, we'd better reset the
- * inner-tuple match flags contained in the table.
+ * However, if it's a right/right-anti/full join, we'd better reset
+ * the inner-tuple match flags contained in the table.
*/
if (HJ_FILL_INNER(node))
ExecHashTableResetMatchFlags(node->hj_HashTable);
diff --git a/src/backend/executor/nodeMergejoin.c b/src/backend/executor/nodeMergejoin.c
index fed345eae5..b16d2a066b 100644
--- a/src/backend/executor/nodeMergejoin.c
+++ b/src/backend/executor/nodeMergejoin.c
@@ -805,6 +805,13 @@ ExecMergeJoin(PlanState *pstate)
break;
}
+ /*
+ * In a right-antijoin, we never return a matched tuple.
+ * And we need to use current outer tuple for further scans
+ */
+ if (node->js.jointype == JOIN_RIGHT_ANTI)
+ break;
+
/*
* If we only need to join to the first matching inner
* tuple, then consider returning this one, but after that
@@ -1063,12 +1070,12 @@ ExecMergeJoin(PlanState *pstate)
* them will match this new outer tuple and therefore
* won't be emitted as fill tuples. This works *only*
* because we require the extra joinquals to be constant
- * when doing a right or full join --- otherwise some of
- * the rescanned tuples might fail the extra joinquals.
- * This obviously won't happen for a constant-true extra
- * joinqual, while the constant-false case is handled by
- * forcing the merge clause to never match, so we never
- * get here.
+ * when doing a right, right-anti or full join ---
+ * otherwise some of the rescanned tuples might fail the
+ * extra joinquals. This obviously won't happen for a
+ * constant-true extra joinqual, while the constant-false
+ * case is handled by forcing the merge clause to never
+ * match, so we never get here.
*/
if (!node->mj_SkipMarkRestore)
{
@@ -1332,8 +1339,8 @@ ExecMergeJoin(PlanState *pstate)
/*
* EXEC_MJ_ENDOUTER means we have run out of outer tuples, but
- * are doing a right/full join and therefore must null-fill
- * any remaining unmatched inner tuples.
+ * are doing a right/right-anti/full join and therefore must
+ * null-fill any remaining unmatched inner tuples.
*/
case EXEC_MJ_ENDOUTER:
MJ_printf("ExecMergeJoin: EXEC_MJ_ENDOUTER\n");
@@ -1554,14 +1561,15 @@ ExecInitMergeJoin(MergeJoin *node, EState *estate, int eflags)
ExecInitNullTupleSlot(estate, innerDesc, &TTSOpsVirtual);
break;
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
mergestate->mj_FillOuter = false;
mergestate->mj_FillInner = true;
mergestate->mj_NullOuterTupleSlot =
ExecInitNullTupleSlot(estate, outerDesc, &TTSOpsVirtual);
/*
- * Can't handle right or full join with non-constant extra
- * joinclauses. This should have been caught by planner.
+ * Can't handle right, right-anti or full join with non-constant
+ * extra joinclauses. This should have been caught by planner.
*/
if (!check_constant_qual(node->join.joinqual,
&mergestate->mj_ConstFalseJoin))
@@ -1578,8 +1586,8 @@ ExecInitMergeJoin(MergeJoin *node, EState *estate, int eflags)
ExecInitNullTupleSlot(estate, innerDesc, &TTSOpsVirtual);
/*
- * Can't handle right or full join with non-constant extra
- * joinclauses. This should have been caught by planner.
+ * Can't handle right, right-anti or full join with non-constant
+ * extra joinclauses. This should have been caught by planner.
*/
if (!check_constant_qual(node->join.joinqual,
&mergestate->mj_ConstFalseJoin))
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index fb28e6411a..2a82692801 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -3645,7 +3645,8 @@ initial_cost_mergejoin(PlannerInfo *root, JoinCostWorkspace *workspace,
outerstartsel = 0.0;
outerendsel = 1.0;
}
- else if (jointype == JOIN_RIGHT)
+ else if (jointype == JOIN_RIGHT ||
+ jointype == JOIN_RIGHT_ANTI)
{
innerstartsel = 0.0;
innerendsel = 1.0;
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 2a3f0ab7bf..6a3b5adcba 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -284,8 +284,9 @@ add_paths_to_joinrel(PlannerInfo *root,
* 2. Consider paths where the outer relation need not be explicitly
* 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/full
- * joins at all, so it wouldn't work in the prohibited cases either.)
+ * (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.)
*/
if (mergejoin_allowed)
match_unsorted_outer(root, joinrel, outerrel, innerrel,
@@ -1204,14 +1205,15 @@ sort_inner_and_outer(PlannerInfo *root,
* If the joinrel is parallel-safe, we may be able to consider a partial
* merge join. However, we can't handle JOIN_UNIQUE_OUTER, because the
* outer path will be partial, and therefore we won't be able to properly
- * guarantee uniqueness. Similarly, we can't handle JOIN_FULL and
- * JOIN_RIGHT, because they can produce false null extended rows. Also,
- * the resulting path must not be parameterized.
+ * guarantee uniqueness. Similarly, we can't handle JOIN_FULL, JOIN_RIGHT
+ * and JOIN_RIGHT_ANTI, because they can produce false null extended rows.
+ * Also, the resulting path must not be parameterized.
*/
if (joinrel->consider_parallel &&
save_jointype != JOIN_UNIQUE_OUTER &&
save_jointype != JOIN_FULL &&
save_jointype != JOIN_RIGHT &&
+ save_jointype != JOIN_RIGHT_ANTI &&
outerrel->partial_pathlist != NIL &&
bms_is_empty(joinrel->lateral_relids))
{
@@ -1606,10 +1608,10 @@ match_unsorted_outer(PlannerInfo *root,
/*
* Nestloop only supports inner, left, semi, and anti joins. Also, if we
- * are doing a right or full mergejoin, we must use *all* the mergeclauses
- * as join clauses, else we will not have a valid plan. (Although these
- * two flags are currently inverses, keep them separate for clarity and
- * possible future changes.)
+ * 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.
+ * (Although these two flags are currently inverses, keep them separate
+ * for clarity and possible future changes.)
*/
switch (jointype)
{
@@ -1621,6 +1623,7 @@ match_unsorted_outer(PlannerInfo *root,
useallclauses = false;
break;
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
case JOIN_FULL:
nestjoinOK = false;
useallclauses = true;
@@ -1792,13 +1795,14 @@ match_unsorted_outer(PlannerInfo *root,
* handle JOIN_UNIQUE_OUTER, because the outer path will be partial, and
* therefore we won't be able to properly guarantee uniqueness. Nor can
* we handle joins needing lateral rels, since partial paths must not be
- * parameterized. Similarly, we can't handle JOIN_FULL and JOIN_RIGHT,
- * because they can produce false null extended rows.
+ * parameterized. Similarly, we can't handle JOIN_FULL, JOIN_RIGHT and
+ * JOIN_RIGHT_ANTI, because they can produce false null extended rows.
*/
if (joinrel->consider_parallel &&
save_jointype != JOIN_UNIQUE_OUTER &&
save_jointype != JOIN_FULL &&
save_jointype != JOIN_RIGHT &&
+ save_jointype != JOIN_RIGHT_ANTI &&
outerrel->partial_pathlist != NIL &&
bms_is_empty(joinrel->lateral_relids))
{
@@ -2134,17 +2138,19 @@ hash_inner_and_outer(PlannerInfo *root,
* partial hash join. However, we can't handle JOIN_UNIQUE_OUTER,
* because the outer path will be partial, and therefore we won't be
* able to properly guarantee uniqueness. Similarly, we can't handle
- * JOIN_FULL and JOIN_RIGHT, because they can produce false null
- * extended rows. Also, the resulting path must not be parameterized.
- * We would be able to support JOIN_FULL and JOIN_RIGHT for Parallel
- * Hash, since in that case we're back to a single hash table with a
- * single set of match bits for each batch, but that will require
- * figuring out a deadlock-free way to wait for the probe to finish.
+ * JOIN_FULL, JOIN_RIGHT and JOIN_RIGHT_ANTI, because they can produce
+ * false null extended rows. Also, the resulting path must not be
+ * parameterized. We would be able to support JOIN_FULL, JOIN_RIGHT
+ * and JOIN_RIGHT_ANTI for Parallel Hash, since in that case we're
+ * back to a single hash table with a single set of match bits for
+ * each batch, but that will require figuring out a deadlock-free way
+ * to wait for the probe to finish.
*/
if (joinrel->consider_parallel &&
save_jointype != JOIN_UNIQUE_OUTER &&
save_jointype != JOIN_FULL &&
save_jointype != JOIN_RIGHT &&
+ save_jointype != JOIN_RIGHT_ANTI &&
outerrel->partial_pathlist != NIL &&
bms_is_empty(joinrel->lateral_relids))
{
@@ -2201,11 +2207,11 @@ hash_inner_and_outer(PlannerInfo *root,
* Select mergejoin clauses that are usable for a particular join.
* 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/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
+ * *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.)
@@ -2251,8 +2257,8 @@ select_mergejoin_clauses(PlannerInfo *root,
{
/*
* The executor can handle extra joinquals that are constants, but
- * not anything else, when doing right/full merge join. (The
- * reason to support constants is so we can do FULL JOIN ON
+ * not anything else, when doing right/right-anti/full merge join.
+ * (The reason to support constants is so we can do FULL JOIN ON
* FALSE.)
*/
if (!restrictinfo->clause || !IsA(restrictinfo->clause, Const))
@@ -2307,6 +2313,7 @@ select_mergejoin_clauses(PlannerInfo *root,
switch (jointype)
{
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
case JOIN_FULL:
*mergejoin_allowed = !have_nonmergeable_joinclause;
break;
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index 9da3ff2f9a..c6e5e0195c 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -915,6 +915,9 @@ populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1,
add_paths_to_joinrel(root, joinrel, rel1, rel2,
JOIN_ANTI, sjinfo,
restrictlist);
+ add_paths_to_joinrel(root, joinrel, rel2, rel1,
+ JOIN_RIGHT_ANTI, sjinfo,
+ restrictlist);
break;
default:
/* other values not expected here */
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 1fa7fc99b5..4504cd1fd5 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -1626,9 +1626,9 @@ find_var_for_subquery_tle(RelOptInfo *rel, TargetEntry *tle)
* Build the path keys for a join relation constructed by mergejoin or
* nestloop join. This is normally the same as the outer path's keys.
*
- * EXCEPTION: in a FULL or RIGHT join, we cannot treat the result as
- * having the outer path's path keys, because null lefthand rows may be
- * inserted at random points. It must be treated as unsorted.
+ * EXCEPTION: in a FULL, RIGHT or RIGHT_ANTI join, we cannot treat the
+ * result as having the outer path's path keys, because null lefthand rows
+ * may be inserted at random points. It must be treated as unsorted.
*
* We truncate away any pathkeys that are uninteresting for higher joins.
*
@@ -1644,7 +1644,9 @@ build_join_pathkeys(PlannerInfo *root,
JoinType jointype,
List *outer_pathkeys)
{
- if (jointype == JOIN_FULL || jointype == JOIN_RIGHT)
+ if (jointype == JOIN_FULL ||
+ jointype == JOIN_RIGHT ||
+ jointype == JOIN_RIGHT_ANTI)
return NIL;
/*
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 1cb0abdbc1..51f548041e 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -2252,6 +2252,7 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset)
inner_itlist->has_non_vars = false;
break;
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
outer_itlist->has_non_vars = false;
break;
case JOIN_FULL:
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 01b1727fc0..3b3f5b5fd8 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2051,7 +2051,8 @@ typedef struct MergeJoinState
* OuterTupleSlot is empty!)
* hj_OuterTupleSlot tuple slot for outer tuples
* hj_HashTupleSlot tuple slot for inner (hashed) tuples
- * hj_NullOuterTupleSlot prepared null tuple for right/full outer joins
+ * hj_NullOuterTupleSlot prepared null tuple for right/right-anti/full
+ * outer joins
* hj_NullInnerTupleSlot prepared null tuple for left/full outer joins
* hj_FirstOuterTupleSlot first tuple retrieved from outer plan
* hj_JoinState current state of ExecHashJoin state machine
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index cdd6debfa0..4a9aa2308f 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -300,6 +300,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_ANTI, /* 1 copy of each RHS row that has no match */
/*
* These codes are used internally in the planner, but are not supported
@@ -332,7 +333,8 @@ typedef enum JoinType
((1 << JOIN_LEFT) | \
(1 << JOIN_FULL) | \
(1 << JOIN_RIGHT) | \
- (1 << JOIN_ANTI))) != 0)
+ (1 << JOIN_ANTI) | \
+ (1 << JOIN_RIGHT_ANTI))) != 0)
/*
* AggStrategy -
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 03926a8413..852b740416 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -2403,24 +2403,24 @@ SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t
Sort
Sort Key: t1.a
-> Append
- -> Hash Anti Join
- Hash Cond: (t1_1.a = t2_1.b)
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
+ -> Hash Right Anti 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 Anti 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 Anti 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 Anti 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 Anti 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 NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
@@ -2638,24 +2638,24 @@ SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t
Sort
Sort Key: t1.a
-> Append
- -> Hash Anti Join
- Hash Cond: (t1_1.a = t2_1.b)
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
+ -> Hash Right Anti 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 Anti 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 Anti 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 Anti 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 Anti 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 NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
@@ -2671,26 +2671,26 @@ SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t
-- partitions on the nullable side
EXPLAIN (COSTS OFF)
SELECT t1.* FROM prt2_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt1_adv t2 WHERE t1.b = t2.a) AND t1.a = 0 ORDER BY t1.b;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------
Sort
Sort Key: t1.b
- -> Hash Anti Join
- Hash Cond: (t1.b = t2.a)
+ -> Hash Right Anti Join
+ Hash Cond: (t2.a = t1.b)
-> Append
- -> Seq Scan on prt2_adv_p1 t1_1
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_p2 t1_2
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_p3 t1_3
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_extra t1_4
- Filter: (a = 0)
+ -> Seq Scan on prt1_adv_p1 t2_1
+ -> Seq Scan on prt1_adv_p2 t2_2
+ -> Seq Scan on prt1_adv_p3 t2_3
-> Hash
-> Append
- -> Seq Scan on prt1_adv_p1 t2_1
- -> Seq Scan on prt1_adv_p2 t2_2
- -> Seq Scan on prt1_adv_p3 t2_3
+ -> Seq Scan on prt2_adv_p1 t1_1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_p2 t1_2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_p3 t1_3
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_extra t1_4
+ Filter: (a = 0)
(18 rows)
-- full join; currently we can't do partitioned join if there are no matched
@@ -2858,25 +2858,25 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 LEFT JOIN prt2_adv t2 ON (t1.a =
-- anti join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM prt1_adv t1 WHERE NOT 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 Anti Join
- Hash Cond: (t1.a = t2.b)
+ -> Hash Right Anti 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)
-- full join
@@ -3280,27 +3280,30 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a =
-- anti join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE NOT 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 Anti 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 Anti 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 Anti 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 Anti 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 Anti 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 Anti 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 NOT 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
@@ -3493,27 +3496,30 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt2_adv t1 LEFT JOIN plt1_adv t2 ON (t1.a =
-- anti join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE NOT 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 Anti 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 Anti 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 Anti 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 Anti 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 Anti 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 Anti 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 NOT 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
@@ -3526,26 +3532,26 @@ SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t
-- partitions on the nullable side
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt2_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt1_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 Anti Join
- Hash Cond: ((t1.a = t2.a) AND (t1.c = t2.c))
+ -> Hash Right Anti Join
+ Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
-> Append
- -> Seq Scan on plt2_adv_extra t1_1
- Filter: (b < 10)
- -> Seq Scan on plt2_adv_p1 t1_2
- Filter: (b < 10)
- -> Seq Scan on plt2_adv_p2 t1_3
- Filter: (b < 10)
- -> Seq Scan on plt2_adv_p3 t1_4
- Filter: (b < 10)
+ -> Seq Scan on plt1_adv_p1 t2_1
+ -> Seq Scan on plt1_adv_p2 t2_2
+ -> Seq Scan on plt1_adv_p3 t2_3
-> Hash
-> Append
- -> Seq Scan on plt1_adv_p1 t2_1
- -> Seq Scan on plt1_adv_p2 t2_2
- -> Seq Scan on plt1_adv_p3 t2_3
+ -> Seq Scan on plt2_adv_extra t1_1
+ Filter: (b < 10)
+ -> Seq Scan on plt2_adv_p1 t1_2
+ Filter: (b < 10)
+ -> Seq Scan on plt2_adv_p2 t1_3
+ Filter: (b < 10)
+ -> Seq Scan on plt2_adv_p3 t1_4
+ Filter: (b < 10)
(18 rows)
-- full join; currently we can't do partitioned join if there are no matched
@@ -3655,25 +3661,25 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a =
-- anti join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE NOT 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 Anti Join
- Hash Cond: ((t1.a = t2.a) AND (t1.c = t2.c))
+ -> Hash Right Anti 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)
-- full join
@@ -3830,28 +3836,30 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a =
-- anti join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM plt1_adv t1 WHERE NOT 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 Anti 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 Anti 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 Anti 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 Anti 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 Anti 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 Anti 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 NOT 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
--
2.31.0