Re: Performance improvement for joins where outer side is unique - Mailing list pgsql-hackers

Hello, The attached is non-search version of unique join.

It is not fully examined but looks to work as expected. Many
small changes make the patch larger but affected area is rather
small.

What do you think about this?

> Hello, I don't have enough time for now but made some
> considerations on this.
> 
> It might be a way that marking unique join peer at bottom and
> propagate up, not searching from top of join list.
> Around create_join_clause might be a candidate for it.
> I'll investigate that later.


regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index a951c55..b8a68b5 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -1151,9 +1151,16 @@ ExplainNode(PlanState *planstate, List *ancestors,
appendStringInfo(es->str," %s Join", jointype);                    else if (!IsA(plan, NestLoop))
appendStringInfoString(es->str, " Join");
 
+                    if (((Join *)plan)->inner_unique)
+                        appendStringInfoString(es->str, "(inner unique)");
+                }                else
+                {                    ExplainPropertyText("Join Type", jointype, es);
+                    ExplainPropertyText("Inner unique", 
+                            ((Join *)plan)->inner_unique?"true":"false", es);
+                }            }            break;        case T_SetOp:
diff --git a/src/backend/executor/nodeHashjoin.c b/src/backend/executor/nodeHashjoin.c
index 1d78cdf..d3b14e5 100644
--- a/src/backend/executor/nodeHashjoin.c
+++ b/src/backend/executor/nodeHashjoin.c
@@ -306,10 +306,11 @@ ExecHashJoin(HashJoinState *node)                    }                    /*
-                     * In a semijoin, we'll consider returning the first
-                     * match, but after that we're done with this outer tuple.
+                     * We'll consider returning the first match if the inner
+                     * is unique, but after that we're done with this outer
+                     * tuple.                     */
-                    if (node->js.jointype == JOIN_SEMI)
+                    if (node->js.inner_unique)                        node->hj_JoinState = HJ_NEED_NEW_OUTER;
         if (otherqual == NIL ||
 
@@ -451,6 +452,7 @@ ExecInitHashJoin(HashJoin *node, EState *estate, int eflags)    hjstate = makeNode(HashJoinState);
 hjstate->js.ps.plan = (Plan *) node;    hjstate->js.ps.state = estate;
 
+    hjstate->js.inner_unique = node->join.inner_unique;    /*     * Miscellaneous initialization
@@ -498,8 +500,10 @@ ExecInitHashJoin(HashJoin *node, EState *estate, int eflags)    /* set up null tuples for outer
joins,if needed */    switch (node->join.jointype)    {
 
-        case JOIN_INNER:        case JOIN_SEMI:
+            hjstate->js.inner_unique = true;
+            /* fall through */
+        case JOIN_INNER:            break;        case JOIN_LEFT:        case JOIN_ANTI:
diff --git a/src/backend/executor/nodeMergejoin.c b/src/backend/executor/nodeMergejoin.c
index 15742c5..3c21ffe 100644
--- a/src/backend/executor/nodeMergejoin.c
+++ b/src/backend/executor/nodeMergejoin.c
@@ -840,10 +840,11 @@ ExecMergeJoin(MergeJoinState *node)                    }                    /*
-                     * In a semijoin, we'll consider returning the first
-                     * match, but after that we're done with this outer tuple.
+                     * We'll consider returning the first match if the inner
+                     * is unique, but after that we're done with this outer
+                     * tuple.                     */
-                    if (node->js.jointype == JOIN_SEMI)
+                    if (node->js.inner_unique)                        node->mj_JoinState = EXEC_MJ_NEXTOUTER;
         qualResult = (otherqual == NIL ||
 
@@ -1486,6 +1487,8 @@ ExecInitMergeJoin(MergeJoin *node, EState *estate, int eflags)    mergestate->js.ps.plan = (Plan
*)node;    mergestate->js.ps.state = estate;
 
+    mergestate->js.inner_unique = node->join.inner_unique;
+    /*     * Miscellaneous initialization     *
@@ -1553,8 +1556,10 @@ ExecInitMergeJoin(MergeJoin *node, EState *estate, int eflags)    switch (node->join.jointype)
{
 
-        case JOIN_INNER:        case JOIN_SEMI:
+            mergestate->js.inner_unique = true;
+            /* fall through */
+        case JOIN_INNER:            mergestate->mj_FillOuter = false;            mergestate->mj_FillInner = false;
      break;
 
diff --git a/src/backend/executor/nodeNestloop.c b/src/backend/executor/nodeNestloop.c
index e66bcda..342c448 100644
--- a/src/backend/executor/nodeNestloop.c
+++ b/src/backend/executor/nodeNestloop.c
@@ -247,10 +247,10 @@ ExecNestLoop(NestLoopState *node)            }            /*
-             * In a semijoin, we'll consider returning the first match, but
-             * after that we're done with this outer tuple.
+             * We'll consider returning the first match if the inner is
+             * unique, but after that we're done with this outer tuple.             */
-            if (node->js.jointype == JOIN_SEMI)
+            if (node->js.inner_unique)                node->nl_NeedNewOuter = true;            if (otherqual == NIL ||
ExecQual(otherqual,econtext, false))
 
@@ -310,6 +310,8 @@ ExecInitNestLoop(NestLoop *node, EState *estate, int eflags)    nlstate->js.ps.plan = (Plan *)
node;   nlstate->js.ps.state = estate;
 
+    nlstate->js.inner_unique = node->join.inner_unique;
+    /*     * Miscellaneous initialization     *
@@ -354,8 +356,10 @@ ExecInitNestLoop(NestLoop *node, EState *estate, int eflags)    switch (node->join.jointype)    {
-        case JOIN_INNER:        case JOIN_SEMI:
+            nlstate->js.inner_unique = true;
+            /* fall through */
+        case JOIN_INNER:            break;        case JOIN_LEFT:        case JOIN_ANTI:
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 1da953f..8363216 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -26,18 +26,21 @@    ((path)->param_info && bms_overlap(PATH_REQ_OUTER(path), (rel)->relids))static void
sort_inner_and_outer(PlannerInfo*root, RelOptInfo *joinrel,
 
-                     RelOptInfo *outerrel, RelOptInfo *innerrel,
+                     RelOptInfo *outerrel,
+                     RelOptInfo *innerrel, bool inner_unique,                     List *restrictlist, List
*mergeclause_list,                    JoinType jointype, SpecialJoinInfo *sjinfo,                     Relids
param_source_rels,Relids extra_lateral_rels);static void match_unsorted_outer(PlannerInfo *root, RelOptInfo *joinrel,
 
-                     RelOptInfo *outerrel, RelOptInfo *innerrel,
+                     RelOptInfo *outerrel,
+                     RelOptInfo *innerrel, bool inner_unique,                     List *restrictlist, List
*mergeclause_list,                    JoinType jointype, SpecialJoinInfo *sjinfo,
SemiAntiJoinFactors*semifactors,                     Relids param_source_rels, Relids extra_lateral_rels);static void
hash_inner_and_outer(PlannerInfo*root, RelOptInfo *joinrel,
 
-                     RelOptInfo *outerrel, RelOptInfo *innerrel,
+                     RelOptInfo *outerrel,
+                     RelOptInfo *innerrel, bool inner_unique,                     List *restrictlist,
  JoinType jointype, SpecialJoinInfo *sjinfo,                     SemiAntiJoinFactors *semifactors,
 
@@ -49,7 +52,8 @@ static List *select_mergejoin_clauses(PlannerInfo *root,                         List *restrictlist,
                      JoinType jointype,                         bool *mergejoin_allowed);
 
-
+static inline bool clause_sides_match_join(RestrictInfo *rinfo, RelOptInfo *outerrel,
+                                           RelOptInfo *innerrel);/* * add_paths_to_joinrel
@@ -89,6 +93,7 @@ add_paths_to_joinrel(PlannerInfo *root,    Relids        param_source_rels = NULL;    Relids
extra_lateral_rels= NULL;    ListCell   *lc;
 
+    bool        inner_unique = false;    /*     * Find potential mergejoin clauses.  We can skip this if we are not
@@ -115,6 +120,31 @@ add_paths_to_joinrel(PlannerInfo *root,                                       &semifactors);
/*
+     * We can optimize inner loop execution for joins on which the inner rel
+     * is unique on the restrictlist.
+     */
+    if (jointype == JOIN_INNER &&
+         innerrel->rtekind == RTE_RELATION &&
+        restrictlist)
+    {
+        /* relation_has_unique_index_for adds some restrictions */
+        int org_len = list_length(restrictlist);
+        ListCell *lc;
+
+        foreach (lc, restrictlist)
+        {
+            clause_sides_match_join((RestrictInfo *) lfirst(lc),
+                                    outerrel, innerrel);
+        }
+        if (relation_has_unique_index_for(root, innerrel, restrictlist,
+                                          NIL, NIL))
+            inner_unique = true;
+
+        /* Remove restirictions added by the function */
+        list_truncate(restrictlist, org_len);
+    }
+
+    /*     * Decide whether it's sensible to generate parameterized paths for this     * joinrel, and if so, which
relationssuch paths should require.  There     * is usually no need to create a parameterized result path unless there
 
@@ -212,7 +242,7 @@ add_paths_to_joinrel(PlannerInfo *root,     * sorted.  Skip this if we can't mergejoin.     */
if(mergejoin_allowed)
 
-        sort_inner_and_outer(root, joinrel, outerrel, innerrel,
+        sort_inner_and_outer(root, joinrel, outerrel, innerrel, inner_unique,
restrictlist,mergeclause_list, jointype,                             sjinfo,
param_source_rels,extra_lateral_rels); 
@@ -225,7 +255,7 @@ add_paths_to_joinrel(PlannerInfo *root,     * joins at all, so it wouldn't work in the prohibited
caseseither.)     */    if (mergejoin_allowed)
 
-        match_unsorted_outer(root, joinrel, outerrel, innerrel,
+        match_unsorted_outer(root, joinrel, outerrel, innerrel, inner_unique,
restrictlist,mergeclause_list, jointype,                             sjinfo, &semifactors,
param_source_rels,extra_lateral_rels);
 
@@ -256,7 +286,7 @@ add_paths_to_joinrel(PlannerInfo *root,     * joins, because there may be no other alternative.
*/   if (enable_hashjoin || jointype == JOIN_FULL)
 
-        hash_inner_and_outer(root, joinrel, outerrel, innerrel,
+        hash_inner_and_outer(root, joinrel, outerrel, innerrel, inner_unique,
restrictlist,jointype,                             sjinfo, &semifactors,                             param_source_rels,
extra_lateral_rels);
@@ -277,6 +307,7 @@ try_nestloop_path(PlannerInfo *root,                  Relids extra_lateral_rels,
Path*outer_path,                  Path *inner_path,
 
+                  bool  inner_unique,                  List *restrict_clauses,                  List *pathkeys){
@@ -349,6 +380,7 @@ try_nestloop_path(PlannerInfo *root,                                      semifactors,
                       outer_path,                                      inner_path,
 
+                                      inner_unique,                                      restrict_clauses,
                        pathkeys,                                      required_outer));
 
@@ -374,6 +406,7 @@ try_mergejoin_path(PlannerInfo *root,                   Relids extra_lateral_rels,
Path *outer_path,                   Path *inner_path,
 
+                   bool     inner_unique,                   List *restrict_clauses,                   List *pathkeys,
                List *mergeclauses,
 
@@ -434,6 +467,7 @@ try_mergejoin_path(PlannerInfo *root,                                       sjinfo,
                     outer_path,                                       inner_path,
 
+                                       inner_unique,                                       restrict_clauses,
                           pathkeys,                                       required_outer,
 
@@ -463,6 +497,7 @@ try_hashjoin_path(PlannerInfo *root,                  Relids extra_lateral_rels,
Path*outer_path,                  Path *inner_path,
 
+                  bool  inner_unique,                  List *restrict_clauses,                  List *hashclauses){
@@ -510,6 +545,7 @@ try_hashjoin_path(PlannerInfo *root,                                      semifactors,
                       outer_path,                                      inner_path,
 
+                                      inner_unique,                                      restrict_clauses,
                        required_outer,                                      hashclauses));
 
@@ -574,6 +610,7 @@ sort_inner_and_outer(PlannerInfo *root,                     RelOptInfo *joinrel,
RelOptInfo *outerrel,                     RelOptInfo *innerrel,
 
+                     bool inner_unique,                     List *restrictlist,                     List
*mergeclause_list,                    JoinType jointype,
 
@@ -629,6 +666,7 @@ sort_inner_and_outer(PlannerInfo *root,                                                 inner_path,
sjinfo);       Assert(inner_path);        jointype = JOIN_INNER;
 
+        inner_unique = true;    }    /*
@@ -712,6 +750,7 @@ sort_inner_and_outer(PlannerInfo *root,                           extra_lateral_rels,
           outer_path,                           inner_path,
 
+                           inner_unique,                           restrictlist,
merge_pathkeys,                          cur_mergeclauses,
 
@@ -762,6 +801,7 @@ match_unsorted_outer(PlannerInfo *root,                     RelOptInfo *joinrel,
RelOptInfo *outerrel,                     RelOptInfo *innerrel,
 
+                     bool  inner_unique,                     List *restrictlist,                     List
*mergeclause_list,                    JoinType jointype,
 
@@ -832,6 +872,7 @@ match_unsorted_outer(PlannerInfo *root,        inner_cheapest_total = (Path *)
create_unique_path(root,innerrel, inner_cheapest_total, sjinfo);        Assert(inner_cheapest_total);
 
+        inner_unique = true;    }    else if (nestjoinOK)    {
@@ -901,6 +942,7 @@ match_unsorted_outer(PlannerInfo *root,                              extra_lateral_rels,
                 outerpath,                              inner_cheapest_total,
 
+                              inner_unique,                              restrictlist,
merge_pathkeys);       }
 
@@ -927,6 +969,7 @@ match_unsorted_outer(PlannerInfo *root,                                  extra_lateral_rels,
                         outerpath,                                  innerpath,
 
+                                  inner_unique,                                  restrictlist,
        merge_pathkeys);            }
 
@@ -942,6 +985,7 @@ match_unsorted_outer(PlannerInfo *root,                                  extra_lateral_rels,
                         outerpath,                                  matpath,
 
+                                  inner_unique,                                  restrictlist,
        merge_pathkeys);        }
 
@@ -998,6 +1042,7 @@ match_unsorted_outer(PlannerInfo *root,                           extra_lateral_rels,
            outerpath,                           inner_cheapest_total,
 
+                           inner_unique,                           restrictlist,
merge_pathkeys,                          mergeclauses,
 
@@ -1097,6 +1142,7 @@ match_unsorted_outer(PlannerInfo *root,                                   extra_lateral_rels,
                             outerpath,                                   innerpath,
 
+                                   inner_unique,                                   restrictlist,
           merge_pathkeys,                                   newclauses,
 
@@ -1143,6 +1189,7 @@ match_unsorted_outer(PlannerInfo *root,                                       extra_lateral_rels,
                                     outerpath,                                       innerpath,
 
+                                       inner_unique,                                       restrictlist,
                       merge_pathkeys,                                       newclauses,
 
@@ -1182,6 +1229,7 @@ hash_inner_and_outer(PlannerInfo *root,                     RelOptInfo *joinrel,
  RelOptInfo *outerrel,                     RelOptInfo *innerrel,
 
+                     bool inner_unique,                     List *restrictlist,                     JoinType jointype,
                   SpecialJoinInfo *sjinfo,
 
@@ -1264,6 +1312,7 @@ hash_inner_and_outer(PlannerInfo *root,                              extra_lateral_rels,
                   cheapest_total_outer,                              cheapest_total_inner,
 
+                              inner_unique,                              restrictlist,
hashclauses);           /* no possibility of cheap startup here */
 
@@ -1284,6 +1333,7 @@ hash_inner_and_outer(PlannerInfo *root,                              extra_lateral_rels,
                   cheapest_total_outer,                              cheapest_total_inner,
 
+                              true,                              restrictlist,
hashclauses);           if (cheapest_startup_outer != NULL &&
 
@@ -1297,6 +1347,7 @@ hash_inner_and_outer(PlannerInfo *root,                                  extra_lateral_rels,
                           cheapest_startup_outer,                                  cheapest_total_inner,
 
+                                  true,                                  restrictlist,
hashclauses);        }
 
@@ -1322,6 +1373,7 @@ hash_inner_and_outer(PlannerInfo *root,                                  extra_lateral_rels,
                           cheapest_startup_outer,                                  cheapest_total_inner,
 
+                                  inner_unique,                                  restrictlist,
        hashclauses);
 
@@ -1360,6 +1412,7 @@ hash_inner_and_outer(PlannerInfo *root,                                      extra_lateral_rels,
                                   outerpath,                                      innerpath,
 
+                                      inner_unique,                                      restrictlist,
                    hashclauses);                }
 
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index cb69c03..448c556 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -131,12 +131,12 @@ static BitmapAnd *make_bitmap_and(List *bitmapplans);static BitmapOr *make_bitmap_or(List
*bitmapplans);staticNestLoop *make_nestloop(List *tlist,              List *joinclauses, List *otherclauses, List
*nestParams,
-              Plan *lefttree, Plan *righttree,
+              Plan *lefttree, Plan *righttree, bool inner_unique,              JoinType jointype);static HashJoin
*make_hashjoin(List*tlist,              List *joinclauses, List *otherclauses,              List *hashclauses,
 
-              Plan *lefttree, Plan *righttree,
+              Plan *lefttree, Plan *righttree, bool inner_unique,              JoinType jointype);static Hash
*make_hash(Plan*lefttree,          Oid skewTable,
 
@@ -151,7 +151,7 @@ static MergeJoin *make_mergejoin(List *tlist,               Oid *mergecollations,               int
*mergestrategies,              bool *mergenullsfirst,
 
-               Plan *lefttree, Plan *righttree,
+               Plan *lefttree, Plan *righttree, bool inner_unique,               JoinType jointype);static Sort
*make_sort(PlannerInfo*root, Plan *lefttree, int numCols,          AttrNumber *sortColIdx, Oid *sortOperators,
 
@@ -2192,6 +2192,7 @@ create_nestloop_plan(PlannerInfo *root,                              nestParams,
           outer_plan,                              inner_plan,
 
+                              best_path->inner_unique,                              best_path->jointype);
copy_path_costsize(&join_plan->join.plan,&best_path->path);
 
@@ -2486,6 +2487,7 @@ create_mergejoin_plan(PlannerInfo *root,                               mergenullsfirst,
                   outer_plan,                               inner_plan,
 
+                               best_path->jpath.inner_unique,
best_path->jpath.jointype);   /* Costs of sort and material steps are included in path cost already */
 
@@ -2612,6 +2614,7 @@ create_hashjoin_plan(PlannerInfo *root,                              hashclauses,
            outer_plan,                              (Plan *) hash_plan,
 
+                              best_path->jpath.inner_unique,                              best_path->jpath.jointype);
 copy_path_costsize(&join_plan->join.plan, &best_path->jpath.path);
 
@@ -3717,6 +3720,7 @@ make_nestloop(List *tlist,              List *nestParams,              Plan *lefttree,
 Plan *righttree,
 
+              bool  inner_unique,              JoinType jointype){    NestLoop   *node = makeNode(NestLoop);
@@ -3729,6 +3733,7 @@ make_nestloop(List *tlist,    plan->righttree = righttree;    node->join.jointype = jointype;
node->join.joinqual= joinclauses;
 
+    node->join.inner_unique = inner_unique;    node->nestParams = nestParams;    return node;
@@ -3741,6 +3746,7 @@ make_hashjoin(List *tlist,              List *hashclauses,              Plan *lefttree,
  Plan *righttree,
 
+              bool  inner_unique,              JoinType jointype){    HashJoin   *node = makeNode(HashJoin);
@@ -3754,6 +3760,7 @@ make_hashjoin(List *tlist,    node->hashclauses = hashclauses;    node->join.jointype = jointype;
  node->join.joinqual = joinclauses;
 
+    node->join.inner_unique = inner_unique;    return node;}
@@ -3801,6 +3808,7 @@ make_mergejoin(List *tlist,               bool *mergenullsfirst,               Plan *lefttree,
          Plan *righttree,
 
+               bool inner_unique,               JoinType jointype){    MergeJoin  *node = makeNode(MergeJoin);
@@ -3818,6 +3826,7 @@ make_mergejoin(List *tlist,    node->mergeNullsFirst = mergenullsfirst;    node->join.jointype =
jointype;   node->join.joinqual = joinclauses;
 
+    node->join.inner_unique = inner_unique;    return node;}
@@ -4586,7 +4595,6 @@ make_unique(Plan *lefttree, List *distinctList)    node->numCols = numCols;    node->uniqColIdx =
uniqColIdx;   node->uniqOperators = uniqOperators;
 
-    return node;}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index faca30b..299a51d 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1135,8 +1135,8 @@ create_unique_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath,     */    if (rel->rtekind
==RTE_RELATION && sjinfo->semi_can_btree &&        relation_has_unique_index_for(root, rel, NIL,
 
-                                      sjinfo->semi_rhs_exprs,
-                                      sjinfo->semi_operators))
+                                       sjinfo->semi_rhs_exprs,
+                                       sjinfo->semi_operators))    {        pathnode->umethod = UNIQUE_PATH_NOOP;
 pathnode->path.rows = rel->rows;
 
@@ -1534,6 +1534,7 @@ create_nestloop_path(PlannerInfo *root,                     SemiAntiJoinFactors *semifactors,
               Path *outer_path,                     Path *inner_path,
 
+                     bool  inner_unique,                     List *restrict_clauses,                     List
*pathkeys,                    Relids required_outer)
 
@@ -1581,6 +1582,7 @@ create_nestloop_path(PlannerInfo *root,    pathnode->jointype = jointype;
pathnode->outerjoinpath= outer_path;    pathnode->innerjoinpath = inner_path;
 
+    pathnode->inner_unique = inner_unique;    pathnode->joinrestrictinfo = restrict_clauses;
final_cost_nestloop(root,pathnode, workspace, sjinfo, semifactors);
 
@@ -1615,6 +1617,7 @@ create_mergejoin_path(PlannerInfo *root,                      SpecialJoinInfo *sjinfo,
         Path *outer_path,                      Path *inner_path,
 
+                      bool  inner_unique,                      List *restrict_clauses,                      List
*pathkeys,                     Relids required_outer,
 
@@ -1638,6 +1641,7 @@ create_mergejoin_path(PlannerInfo *root,    pathnode->jpath.jointype = jointype;
pathnode->jpath.outerjoinpath= outer_path;    pathnode->jpath.innerjoinpath = inner_path;
 
+    pathnode->jpath.inner_unique = inner_unique;    pathnode->jpath.joinrestrictinfo = restrict_clauses;
pathnode->path_mergeclauses= mergeclauses;    pathnode->outersortkeys = outersortkeys;
 
@@ -1674,6 +1678,7 @@ create_hashjoin_path(PlannerInfo *root,                     SemiAntiJoinFactors *semifactors,
               Path *outer_path,                     Path *inner_path,
 
+                     bool  inner_unique,                     List *restrict_clauses,                     Relids
required_outer,                    List *hashclauses)
 
@@ -1706,6 +1711,7 @@ create_hashjoin_path(PlannerInfo *root,    pathnode->jpath.jointype = jointype;
pathnode->jpath.outerjoinpath= outer_path;    pathnode->jpath.innerjoinpath = inner_path;
 
+    pathnode->jpath.inner_unique = inner_unique;    pathnode->jpath.joinrestrictinfo = restrict_clauses;
pathnode->path_hashclauses= hashclauses;    /* final_cost_hashjoin will fill in pathnode->num_batches */
 
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 59b17f3..f86f806 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1562,6 +1562,7 @@ typedef struct JoinState    PlanState    ps;    JoinType    jointype;    List       *joinqual;
   /* JOIN quals (in addition to ps.qual) */
 
+    bool        inner_unique;} JoinState;/* ----------------
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 21cbfa8..122f2f4 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -543,6 +543,7 @@ typedef struct Join    Plan        plan;    JoinType    jointype;    List       *joinqual;
/*JOIN quals (in addition to plan.qual) */
 
+    bool        inner_unique;} Join;/* ----------------
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 334cf51..c1ebfdb 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -1030,6 +1030,7 @@ typedef struct JoinPath    Path       *outerjoinpath;    /* path for the outer side of the join
*/   Path       *innerjoinpath;    /* path for the inner side of the join */
 
+    bool        inner_unique;    List       *joinrestrictinfo;        /* RestrictInfos to apply to join */
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 9923f0e..cefcecc 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -94,6 +94,7 @@ extern NestPath *create_nestloop_path(PlannerInfo *root,                     SemiAntiJoinFactors
*semifactors,                    Path *outer_path,                     Path *inner_path,
 
+                     bool  inner_unique,                     List *restrict_clauses,                     List
*pathkeys,                    Relids required_outer);
 
@@ -105,6 +106,7 @@ extern MergePath *create_mergejoin_path(PlannerInfo *root,                      SpecialJoinInfo
*sjinfo,                     Path *outer_path,                      Path *inner_path,
 
+                      bool  inner_unique,                      List *restrict_clauses,                      List
*pathkeys,                     Relids required_outer,
 
@@ -120,6 +122,7 @@ extern HashPath *create_hashjoin_path(PlannerInfo *root,                     SemiAntiJoinFactors
*semifactors,                    Path *outer_path,                     Path *inner_path,
 
+                     bool  inner_unique,                     List *restrict_clauses,                     Relids
required_outer,                    List *hashclauses);
 
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
index dfae84e..ad1d673 100644
--- a/src/test/regress/expected/equivclass.out
+++ b/src/test/regress/expected/equivclass.out
@@ -186,7 +186,7 @@ explain (costs off)  select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2;
QUERYPLAN                -----------------------------------------
 
- Nested Loop
+ Nested Loop(inner unique)   ->  Seq Scan on ec2         Filter: (x1 = '42'::int8alias2)   ->  Index Scan using
ec1_pkeyon ec1
 
@@ -310,7 +310,7 @@ explain (costs off)         ->  Index Scan using ec1_expr3 on ec1 ec1_5         ->  Index Scan
usingec1_expr4 on ec1 ec1_6   ->  Materialize
 
-         ->  Merge Join
+         ->  Merge Join(inner unique)               Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1)               ->
MergeAppend                     Sort Key: (((ec1_1.ff + 2) + 1))
 
@@ -365,7 +365,7 @@ explain (costs off)  where ss1.x = ec1.f1 and ec1.ff = 42::int8;                     QUERY PLAN
                ----------------------------------------------------- 
- Merge Join
+ Merge Join(inner unique)   Merge Cond: ((((ec1_1.ff + 2) + 1)) = ec1.f1)   ->  Merge Append         Sort Key:
(((ec1_1.ff+ 2) + 1))
 
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 57fc910..b6e3024 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2614,8 +2614,8 @@ from nt3 as nt3where nt3.id = 1 and ss2.b3;                  QUERY PLAN
-----------------------------------------------
- Nested Loop
-   ->  Nested Loop
+ Nested Loop(inner unique)
+   ->  Nested Loop(inner unique)         ->  Index Scan using nt3_pkey on nt3               Index Cond: (id = 1)
 ->  Index Scan using nt2_pkey on nt2
 
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index f41bef1..aaf585d 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -248,7 +248,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);EXPLAIN (COSTS OFF) SELECT * FROM
documentNATURAL JOIN category WHERE f_leak(dtitle);                     QUERY PLAN
----------------------------------------------------
- Nested Loop
+ Nested Loop(inner unique)   ->  Subquery Scan on document         Filter: f_leak(document.dtitle)         ->  Seq
Scanon document document_1