From 16dc2703c5413534d4989e08253e8f4fcb0e2aab Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 5 Apr 2023 16:59:00 -0400 Subject: Support "Right Anti Join" plan shapes. Merge and hash joins can support antijoin with the non-nullable input on the right, using very simple combinations of their existing logic for right join and anti join. This gives the planner more freedom about how to order the join. It's particularly useful for hash join, since we may now have the option to hash the smaller table instead of the larger. Richard Guo, reviewed by Ronan Dunklau and myself Discussion: https://postgr.es/m/CAMbWs48xh9hMzXzSy3VaPzGAz+fkxXXTUbCLohX1_L8THFRm2Q@mail.gmail.com --- src/backend/executor/nodeMergejoin.c | 33 +++++++++++++++++++++------------ 1 file changed, 21 insertions(+), 12 deletions(-) (limited to 'src/backend/executor/nodeMergejoin.c') diff --git a/src/backend/executor/nodeMergejoin.c b/src/backend/executor/nodeMergejoin.c index 809aa215c67..00f96d045e0 100644 --- a/src/backend/executor/nodeMergejoin.c +++ b/src/backend/executor/nodeMergejoin.c @@ -805,6 +805,14 @@ ExecMergeJoin(PlanState *pstate) break; } + /* + * In a right-antijoin, we never return a matched tuple. + * And we need to stay on the current outer tuple to + * continue scanning the inner side for matches. + */ + 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 +1071,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 +1340,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 +1562,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 +1587,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)) -- cgit v1.2.3