aboutsummaryrefslogtreecommitdiff
path: root/src/backend/optimizer/path/joinpath.c
diff options
context:
space:
mode:
authorTom Lane <tgl@sss.pgh.pa.us>2023-04-05 16:59:00 -0400
committerTom Lane <tgl@sss.pgh.pa.us>2023-04-05 16:59:09 -0400
commit16dc2703c5413534d4989e08253e8f4fcb0e2aab (patch)
treec25f739d183d93510418b734ebd36fc3e2e9fde9 /src/backend/optimizer/path/joinpath.c
parentdad50f677c42de207168a3f08982ba23c9fc6720 (diff)
downloadpostgresql-16dc2703c5413534d4989e08253e8f4fcb0e2aab.tar.gz
postgresql-16dc2703c5413534d4989e08253e8f4fcb0e2aab.zip
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
Diffstat (limited to 'src/backend/optimizer/path/joinpath.c')
-rw-r--r--src/backend/optimizer/path/joinpath.c49
1 files changed, 28 insertions, 21 deletions
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index fbeb338c98d..cd80e61fd75 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -286,8 +286,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,
@@ -1261,14 +1262,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))
{
@@ -1663,10 +1665,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)
{
@@ -1678,6 +1680,7 @@ match_unsorted_outer(PlannerInfo *root,
useallclauses = false;
break;
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
case JOIN_FULL:
nestjoinOK = false;
useallclauses = true;
@@ -1849,13 +1852,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))
{
@@ -2228,11 +2232,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 or right 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, 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)
+ if (save_jointype == JOIN_FULL ||
+ save_jointype == JOIN_RIGHT ||
+ save_jointype == JOIN_RIGHT_ANTI)
cheapest_safe_inner = NULL;
else if (cheapest_total_inner->parallel_safe)
cheapest_safe_inner = cheapest_total_inner;
@@ -2256,10 +2262,10 @@ 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/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
+ * 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.)
@@ -2305,8 +2311,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))
@@ -2349,6 +2355,7 @@ select_mergejoin_clauses(PlannerInfo *root,
switch (jointype)
{
case JOIN_RIGHT:
+ case JOIN_RIGHT_ANTI:
case JOIN_FULL:
*mergejoin_allowed = !have_nonmergeable_joinclause;
break;