Keep notnullattrs in RelOptInfo (Was part of UniqueKey patch series)

From 30fd21df7361edeaea2f6078ad5139a53b14340d Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Wed, 10 Feb 2021 10:58:06 +0800
Subject: [PATCH v1] Introduce notnullattrs field in RelOptInfo to indicate
 which attr

are not null in current query. The not null is judged by checking
pg_attribute and query's restrictinfo. The info is only maintained at
Base RelOptInfo and Partition's RelOptInfo level so far.
---
 src/backend/optimizer/path/allpaths.c  | 31 ++++++++++++++++++++++++++
 src/backend/optimizer/plan/initsplan.c | 10 +++++++++
 src/backend/optimizer/util/plancat.c   | 10 +++++++++
 src/include/nodes/pathnodes.h          |  2 ++
 4 files changed, 53 insertions(+)

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index cd3fdd259c..709d2f82ca 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -998,6 +998,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
 		RelOptInfo *childrel;
 		ListCell   *parentvars;
 		ListCell   *childvars;
+		int i = -1;
 
 		/* append_rel_list contains all append rels; ignore others */
 		if (appinfo->parent_relid != parentRTindex)
@@ -1054,6 +1055,36 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
 								   (Node *) rel->reltarget->exprs,
 								   1, &appinfo);
 
+		/* Copy notnullattrs. */
+		while ((i = bms_next_member(rel->notnullattrs, i)) > 0)
+		{
+			AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber;
+			AttrNumber child_attno;
+			if (attno == 0)
+			{
+				/* Whole row is not null, so must be same for child */
+				childrel->notnullattrs = bms_add_member(childrel->notnullattrs,
+														attno - FirstLowInvalidHeapAttributeNumber);
+				break;
+			}
+			if (attno < 0 )
+				/* no need to translate system column */
+				child_attno = attno;
+			else
+			{
+				Node * node = list_nth(appinfo->translated_vars, attno - 1);
+				if (!IsA(node, Var))
+					/* This may happens at UNION case, like (SELECT a FROM t1 UNION SELECT a + 3
+					 * FROM t2) t and we know t.a is not null
+					 */
+					continue;
+				child_attno = castNode(Var, node)->varattno;
+			}
+
+			childrel->notnullattrs = bms_add_member(childrel->notnullattrs,
+													child_attno - FirstLowInvalidHeapAttributeNumber);
+		}
+
 		/*
 		 * We have to make child entries in the EquivalenceClass data
 		 * structures as well.  This is needed either if the parent
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 02f813cebd..d27167dc76 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -829,6 +829,16 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
 		{
 			Node	   *qual = (Node *) lfirst(l);
 
+			/* Set the not null info now */
+			ListCell	*lc;
+			List		*non_nullable_vars = find_nonnullable_vars(qual);
+			foreach(lc, non_nullable_vars)
+			{
+				Var *var = lfirst_node(Var, lc);
+				RelOptInfo *rel = root->simple_rel_array[var->varno];
+				rel->notnullattrs = bms_add_member(rel->notnullattrs,
+												   var->varattno - FirstLowInvalidHeapAttributeNumber);
+			}
 			distribute_qual_to_rels(root, qual,
 									below_outer_join, JOIN_INNER,
 									root->qual_security_level,
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 177e6e336a..0c5a79f296 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -117,6 +117,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	Relation	relation;
 	bool		hasindex;
 	List	   *indexinfos = NIL;
+	int			i;
 
 	/*
 	 * We need not lock the relation since it was already locked, either by
@@ -474,6 +475,15 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	if (inhparent && relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		set_relation_partition_info(root, rel, relation);
 
+	Assert(rel->notnullattrs == NULL);
+	for(i = 0; i < relation->rd_att->natts; i++)
+	{
+		FormData_pg_attribute attr = relation->rd_att->attrs[i];
+		if (attr.attnotnull)
+			rel->notnullattrs = bms_add_member(rel->notnullattrs,
+											   attr.attnum - FirstLowInvalidHeapAttributeNumber);
+	}
+
 	table_close(relation, NoLock);
 
 	/*
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 0ec93e648c..2d81f6216d 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -710,6 +710,8 @@ typedef struct RelOptInfo
 	PlannerInfo *subroot;		/* if subquery */
 	List	   *subplan_params; /* if subquery */
 	int			rel_parallel_workers;	/* wanted number of parallel workers */
+	/* Not null attrs, start from -FirstLowInvalidHeapAttributeNumber */
+	Bitmapset		*notnullattrs;
 
 	/* Information about foreign tables and foreign joins */
 	Oid			serverid;		/* identifies server for the table or join */
-- 
2.21.0

From 30fd21df7361edeaea2f6078ad5139a53b14340d Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Wed, 10 Feb 2021 10:58:06 +0800
Subject: [PATCH v2 1/2] Introduce notnullattrs field in RelOptInfo to indicate
 which attr

are not null in current query. The not null is judged by checking
pg_attribute and query's restrictinfo. The info is only maintained at
Base RelOptInfo and Partition's RelOptInfo level so far.
---
 src/backend/optimizer/path/allpaths.c  | 31 ++++++++++++++++++++++++++
 src/backend/optimizer/plan/initsplan.c | 10 +++++++++
 src/backend/optimizer/util/plancat.c   | 10 +++++++++
 src/include/nodes/pathnodes.h          |  2 ++
 4 files changed, 53 insertions(+)

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index cd3fdd259c..709d2f82ca 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -998,6 +998,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
 		RelOptInfo *childrel;
 		ListCell   *parentvars;
 		ListCell   *childvars;
+		int i = -1;
 
 		/* append_rel_list contains all append rels; ignore others */
 		if (appinfo->parent_relid != parentRTindex)
@@ -1054,6 +1055,36 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
 								   (Node *) rel->reltarget->exprs,
 								   1, &appinfo);
 
+		/* Copy notnullattrs. */
+		while ((i = bms_next_member(rel->notnullattrs, i)) > 0)
+		{
+			AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber;
+			AttrNumber child_attno;
+			if (attno == 0)
+			{
+				/* Whole row is not null, so must be same for child */
+				childrel->notnullattrs = bms_add_member(childrel->notnullattrs,
+														attno - FirstLowInvalidHeapAttributeNumber);
+				break;
+			}
+			if (attno < 0 )
+				/* no need to translate system column */
+				child_attno = attno;
+			else
+			{
+				Node * node = list_nth(appinfo->translated_vars, attno - 1);
+				if (!IsA(node, Var))
+					/* This may happens at UNION case, like (SELECT a FROM t1 UNION SELECT a + 3
+					 * FROM t2) t and we know t.a is not null
+					 */
+					continue;
+				child_attno = castNode(Var, node)->varattno;
+			}
+
+			childrel->notnullattrs = bms_add_member(childrel->notnullattrs,
+													child_attno - FirstLowInvalidHeapAttributeNumber);
+		}
+
 		/*
 		 * We have to make child entries in the EquivalenceClass data
 		 * structures as well.  This is needed either if the parent
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 02f813cebd..d27167dc76 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -829,6 +829,16 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
 		{
 			Node	   *qual = (Node *) lfirst(l);
 
+			/* Set the not null info now */
+			ListCell	*lc;
+			List		*non_nullable_vars = find_nonnullable_vars(qual);
+			foreach(lc, non_nullable_vars)
+			{
+				Var *var = lfirst_node(Var, lc);
+				RelOptInfo *rel = root->simple_rel_array[var->varno];
+				rel->notnullattrs = bms_add_member(rel->notnullattrs,
+												   var->varattno - FirstLowInvalidHeapAttributeNumber);
+			}
 			distribute_qual_to_rels(root, qual,
 									below_outer_join, JOIN_INNER,
 									root->qual_security_level,
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 177e6e336a..0c5a79f296 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -117,6 +117,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	Relation	relation;
 	bool		hasindex;
 	List	   *indexinfos = NIL;
+	int			i;
 
 	/*
 	 * We need not lock the relation since it was already locked, either by
@@ -474,6 +475,15 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	if (inhparent && relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		set_relation_partition_info(root, rel, relation);
 
+	Assert(rel->notnullattrs == NULL);
+	for(i = 0; i < relation->rd_att->natts; i++)
+	{
+		FormData_pg_attribute attr = relation->rd_att->attrs[i];
+		if (attr.attnotnull)
+			rel->notnullattrs = bms_add_member(rel->notnullattrs,
+											   attr.attnum - FirstLowInvalidHeapAttributeNumber);
+	}
+
 	table_close(relation, NoLock);
 
 	/*
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 0ec93e648c..2d81f6216d 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -710,6 +710,8 @@ typedef struct RelOptInfo
 	PlannerInfo *subroot;		/* if subquery */
 	List	   *subplan_params; /* if subquery */
 	int			rel_parallel_workers;	/* wanted number of parallel workers */
+	/* Not null attrs, start from -FirstLowInvalidHeapAttributeNumber */
+	Bitmapset		*notnullattrs;
 
 	/* Information about foreign tables and foreign joins */
 	Oid			serverid;		/* identifies server for the table or join */
-- 
2.21.0

From 1c83cbe7589031440bcf38dff8e81199a507566a Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Tue, 16 Feb 2021 21:56:08 +0800
Subject: [PATCH v2 2/2] Reset notnullattrs for outer join

---
 src/backend/optimizer/util/relnode.c | 23 +++++++++++++++++++++++
 1 file changed, 23 insertions(+)

diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 731ff708b9..c06d60a051 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -555,6 +555,19 @@ add_join_rel(PlannerInfo *root, RelOptInfo *joinrel)
 	}
 }
 
+/*
+ * unset_notnullattrs
+ */
+static void
+unset_notnullattrs(PlannerInfo *root, Relids relids)
+{
+	int i = -1;
+	while ((i = bms_next_member(relids, i)) >= 0)
+	{
+		root->simple_rel_array[i]->notnullattrs = NULL;
+	}
+}
+
 /*
  * build_join_rel
  *	  Returns relation entry corresponding to the union of two given rels,
@@ -671,6 +684,16 @@ build_join_rel(PlannerInfo *root,
 	joinrel->partexprs = NULL;
 	joinrel->nullable_partexprs = NULL;
 
+	if (sjinfo->jointype == JOIN_LEFT)
+	{
+		unset_notnullattrs(root, inner_rel->relids);
+	}
+	else if (sjinfo->jointype == JOIN_FULL)
+	{
+		unset_notnullattrs(root, inner_rel->relids);
+		unset_notnullattrs(root, outer_rel->relids);
+	}
+
 	/* Compute information relevant to the foreign relations. */
 	set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
 
-- 
2.21.0

From 0e0ab487745bd5f4896f7c3c111eb9ee8f2197d8 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Wed, 10 Feb 2021 10:58:06 +0800
Subject: [PATCH v3 1/2] Introduce notnullattrs field in RelOptInfo to indicate
 which attr

are not null in current query. The not null is judged by checking
pg_attribute and query's restrictinfo. The info is only maintained at
Base RelOptInfo level.
---
 src/backend/optimizer/path/allpaths.c  | 31 ++++++++++++++++++++++++++
 src/backend/optimizer/plan/initsplan.c | 23 ++++++++++++++++++-
 src/backend/optimizer/util/plancat.c   | 10 +++++++++
 src/backend/optimizer/util/var.c       | 14 ++++++++++++
 src/include/nodes/pathnodes.h          |  7 ++++++
 src/include/optimizer/optimizer.h      |  2 ++
 6 files changed, 86 insertions(+), 1 deletion(-)

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index cd3fdd259c..709d2f82ca 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -998,6 +998,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
 		RelOptInfo *childrel;
 		ListCell   *parentvars;
 		ListCell   *childvars;
+		int i = -1;
 
 		/* append_rel_list contains all append rels; ignore others */
 		if (appinfo->parent_relid != parentRTindex)
@@ -1054,6 +1055,36 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
 								   (Node *) rel->reltarget->exprs,
 								   1, &appinfo);
 
+		/* Copy notnullattrs. */
+		while ((i = bms_next_member(rel->notnullattrs, i)) > 0)
+		{
+			AttrNumber attno = i + FirstLowInvalidHeapAttributeNumber;
+			AttrNumber child_attno;
+			if (attno == 0)
+			{
+				/* Whole row is not null, so must be same for child */
+				childrel->notnullattrs = bms_add_member(childrel->notnullattrs,
+														attno - FirstLowInvalidHeapAttributeNumber);
+				break;
+			}
+			if (attno < 0 )
+				/* no need to translate system column */
+				child_attno = attno;
+			else
+			{
+				Node * node = list_nth(appinfo->translated_vars, attno - 1);
+				if (!IsA(node, Var))
+					/* This may happens at UNION case, like (SELECT a FROM t1 UNION SELECT a + 3
+					 * FROM t2) t and we know t.a is not null
+					 */
+					continue;
+				child_attno = castNode(Var, node)->varattno;
+			}
+
+			childrel->notnullattrs = bms_add_member(childrel->notnullattrs,
+													child_attno - FirstLowInvalidHeapAttributeNumber);
+		}
+
 		/*
 		 * We have to make child entries in the EquivalenceClass data
 		 * structures as well.  This is needed either if the parent
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 02f813cebd..a929c25d41 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -706,6 +706,25 @@ deconstruct_jointree(PlannerInfo *root)
 	return result;
 }
 
+
+/*
+ * extract_notnull_attrs
+ */
+static void
+extract_notnull_attrs(PlannerInfo *root, Node *qual)
+{
+	/* Set the not null info now */
+	ListCell	*lc;
+	List		*non_nullable_vars = find_nonnullable_vars(qual);
+	foreach(lc, non_nullable_vars)
+	{
+		Var *var = lfirst_node(Var, lc);
+		RelOptInfo *rel = root->simple_rel_array[var->varno];
+		rel->notnullattrs = bms_add_member(rel->notnullattrs,
+										   var->varattno - FirstLowInvalidHeapAttributeNumber);
+	}
+}
+
 /*
  * deconstruct_recurse
  *	  One recursion level of deconstruct_jointree processing.
@@ -828,13 +847,13 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
 		foreach(l, (List *) f->quals)
 		{
 			Node	   *qual = (Node *) lfirst(l);
-
 			distribute_qual_to_rels(root, qual,
 									below_outer_join, JOIN_INNER,
 									root->qual_security_level,
 									*qualscope, NULL, NULL,
 									postponed_qual_list);
 		}
+		extract_notnull_attrs(root, f->quals);
 	}
 	else if (IsA(jtnode, JoinExpr))
 	{
@@ -948,6 +967,8 @@ deconstruct_recurse(PlannerInfo *root, Node *jtnode, bool below_outer_join,
 		root->nullable_baserels = bms_add_members(root->nullable_baserels,
 												  nullable_rels);
 
+		extract_notnull_attrs(root, j->quals);
+
 		/*
 		 * Try to process any quals postponed by children.  If they need
 		 * further postponement, add them to my output postponed_qual_list.
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 177e6e336a..0c5a79f296 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -117,6 +117,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	Relation	relation;
 	bool		hasindex;
 	List	   *indexinfos = NIL;
+	int			i;
 
 	/*
 	 * We need not lock the relation since it was already locked, either by
@@ -474,6 +475,15 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	if (inhparent && relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		set_relation_partition_info(root, rel, relation);
 
+	Assert(rel->notnullattrs == NULL);
+	for(i = 0; i < relation->rd_att->natts; i++)
+	{
+		FormData_pg_attribute attr = relation->rd_att->attrs[i];
+		if (attr.attnotnull)
+			rel->notnullattrs = bms_add_member(rel->notnullattrs,
+											   attr.attnum - FirstLowInvalidHeapAttributeNumber);
+	}
+
 	table_close(relation, NoLock);
 
 	/*
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index e307d6fbb0..f2551713cd 100644
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -867,3 +867,17 @@ alias_relid_set(Query *query, Relids relids)
 	}
 	return result;
 }
+
+
+/*
+ * is_var_nullable
+ */
+bool
+is_var_nullable(PlannerInfo *root, Var *var)
+{
+	RelOptInfo *rel = root->simple_rel_array[var->varno];
+
+	return !bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber,
+						  rel->notnullattrs) ||
+		bms_is_member(var->varno, root->nullable_baserels);
+}
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 0ec93e648c..e392eb1a7e 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -710,6 +710,13 @@ typedef struct RelOptInfo
 	PlannerInfo *subroot;		/* if subquery */
 	List	   *subplan_params; /* if subquery */
 	int			rel_parallel_workers;	/* wanted number of parallel workers */
+	/*
+	 * Not null attrs, the value are calculated by looking it pg_attribute and quals
+	 * However both cases are not reliable in some outer join case. So when
+	 * we want to check if a Var is nullable, function is_var_nullable is a good
+	 * place to start with, which is true positive.
+	 */
+	Bitmapset		*notnullattrs;
 
 	/* Information about foreign tables and foreign joins */
 	Oid			serverid;		/* identifies server for the table or join */
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index d587952b7d..a94b74947b 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -23,6 +23,7 @@
 #define OPTIMIZER_H
 
 #include "nodes/parsenodes.h"
+#include "nodes/bitmapset.h"
 
 /* Test if an expression node represents a SRF call.  Beware multiple eval! */
 #define IS_SRF_CALL(node) \
@@ -198,5 +199,6 @@ extern bool contain_vars_of_level(Node *node, int levelsup);
 extern int	locate_var_of_level(Node *node, int levelsup);
 extern List *pull_var_clause(Node *node, int flags);
 extern Node *flatten_join_alias_vars(Query *query, Node *node);
+extern bool is_var_nullable(PlannerInfo *root, Var *var);
 
 #endif							/* OPTIMIZER_H */
-- 
2.21.0

From 8252d9085e6b1087cfb7c86f079a8c8b9cdcf44d Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Thu, 18 Feb 2021 20:36:39 +0800
Subject: [PATCH v3 2/2] Some hack for local testing only.

---
 src/backend/optimizer/path/allpaths.c | 40 +++++++++++++++++++++++++++
 1 file changed, 40 insertions(+)

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 709d2f82ca..f01d1bc33f 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -176,6 +176,25 @@ make_one_rel(PlannerInfo *root, List *joinlist)
 		root->all_baserels = bms_add_member(root->all_baserels, brel->relid);
 	}
 
+	{
+		ListCell	*lc;
+		foreach(lc, root->processed_tlist)
+		{
+			TargetEntry *tle = lfirst_node(TargetEntry, lc);
+			if (IsA(tle->expr, Var))
+			{
+				Var *var = (Var *) tle->expr;
+				// (void)var;
+				elog(INFO, "rt=%d attno=%d nullable: %d",
+					 var->varno,
+					 var->varattno,
+					 is_var_nullable(root, var)
+					);
+			}
+		}
+	}
+
+
 	/* Mark base rels as to whether we care about fast-start plans */
 	set_base_rel_consider_startup(root);
 
@@ -1083,6 +1102,27 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
 
 			childrel->notnullattrs = bms_add_member(childrel->notnullattrs,
 													child_attno - FirstLowInvalidHeapAttributeNumber);
+
+			{
+				ListCell	*lc;
+				foreach(lc, childrel->reltarget->exprs)
+				{
+					if (IsA(lfirst(lc), Var))
+					{
+						Var *var = (Var *)lfirst(lc);
+
+						char *colname = strVal(
+							lfirst(list_nth_cell(root->simple_rte_array[var->varno]->eref->colnames, var->varattno - 1)));
+						elog(INFO, "parentRT: %d childRT: %d colname: %s varattno: %d, nullable: %d",
+							 rel->relid,
+							 childrel->relid,
+							 colname,
+							 var->varattno,
+							 is_var_nullable(root, var)
+							);
+					}
+				}
+			}
 		}
 
 		/*
-- 
2.21.0

From e1b957e3eeada364089ea25bd86921e39c2fabb7 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sat, 3 Jul 2021 21:06:32 +0800
Subject: [PATCH v1] add the not null attrs for RelOptInfo. Here is how it
 works.

For baserel, it records the notnull attrs as a bitmapset and store it to
RelOptInfo->notnull_attrs[0].  As for the joinrel, suppose the relids is {1,3,
5}, then the notnull_attrs[1/3/5] will be used to store notnull_attrs bitmapset
for relation 1,3,5 separately. I don't handle this stuff for all kinds of upper
relation and subquery so far.
---
 notnulltest.sql                       | 24 ++++++++
 src/backend/nodes/bitmapset.c         | 14 +++++
 src/backend/optimizer/path/allpaths.c | 36 ++++++++++++
 src/backend/optimizer/util/plancat.c  |  9 +++
 src/backend/optimizer/util/relnode.c  | 80 +++++++++++++++++++++++++++
 src/include/nodes/bitmapset.h         |  1 +
 src/include/nodes/pathnodes.h         |  6 ++
 7 files changed, 170 insertions(+)
 create mode 100644 notnulltest.sql

diff --git a/notnulltest.sql b/notnulltest.sql
new file mode 100644
index 0000000000..2a36bd0c7f
--- /dev/null
+++ b/notnulltest.sql
@@ -0,0 +1,24 @@
+create table t1(a int, b int not null, c int, d int);
+create table t2(a int, b int not null, c int, d int);
+
+-- single rel
+select * from t1;
+select * from t1 where a > 1;
+select * from t2 where a > 1 or c > 1;
+
+-- partitioned relation.
+create table p (a int, b int, c int not null) partition by range(a);
+create table p_1 partition of p for values from (0) to (10000) partition by list(b);
+create table p_1_1(b int,  c int not null, a int);
+alter table p_1 attach partition p_1_1 for values in (1);
+
+
+select * from p;
+select * from p where a > 1;
+
+
+-- test join:
+select * from t1, t2 where t1.a = t2.c;
+select t1.a, t2.b, t2.c from t1 left join t2 on t1.a = t2.c;
+select * from t1 full join t2 on t1.a = t2.c;
+
diff --git a/src/backend/nodes/bitmapset.c b/src/backend/nodes/bitmapset.c
index 649478b0d4..fa71f36aaf 100644
--- a/src/backend/nodes/bitmapset.c
+++ b/src/backend/nodes/bitmapset.c
@@ -663,6 +663,20 @@ bms_num_members(const Bitmapset *a)
 	return result;
 }
 
+/*
+ * bms_max_member - the max member in this bitmap.
+ */
+int
+bms_max_member(const Bitmapset *a)
+{
+	int result;
+	if (a == NULL || bms_is_empty(a))
+		elog(ERROR, "Must be an non-empty bitmapset.");
+	result = (a->nwords - 1) * BITS_PER_BITMAPWORD;
+	result += bmw_leftmost_one_pos(a->words[a->nwords - 1]);
+	return result;
+}
+
 /*
  * bms_membership - does a set have zero, one, or multiple members?
  *
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 353454b183..0dcb9e2337 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -354,6 +354,40 @@ set_base_rel_pathlists(PlannerInfo *root)
 	}
 }
 
+/*
+ * set_baserel_notnull_attrs
+ *
+ *	Set baserel's notnullattrs based on baserestrictinfo
+ */
+static void
+set_baserel_notnull_attrs(RelOptInfo *rel)
+{
+	List *clauses = extract_actual_clauses(rel->baserestrictinfo, false);
+	ListCell	*lc;
+	foreach(lc, find_nonnullable_vars((Node *)clauses))
+	{
+		Var *var = (Var *) lfirst(lc);
+		if (var->varno != rel->relid)
+		{
+			/* Lateral Join */
+			continue;
+		}
+		Assert(var->varno == rel->relid);
+		rel->notnull_attrs[0] = bms_add_member(rel->notnull_attrs[0],
+											   var->varattno - FirstLowInvalidHeapAttributeNumber);
+	}
+
+	/* Debug Only, Will be removed at last. */
+	if (false)
+	{
+		elog(INFO, "FirstLowInvalidHeapAttributeNumber = %d, BaseRel(%d), notnull_attrs = %s",
+			 FirstLowInvalidHeapAttributeNumber,
+			 rel->relid,
+			 bmsToString(rel->notnull_attrs[0])
+			);
+	}
+}
+
 /*
  * set_rel_size
  *	  Set size estimates for a base relation
@@ -457,6 +491,8 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
 		}
 	}
 
+	set_baserel_notnull_attrs(rel);
+
 	/*
 	 * We insist that all non-dummy rels have a nonzero rowcount estimate.
 	 */
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index c5194fdbbf..7d3b40090e 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -117,6 +117,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	Relation	relation;
 	bool		hasindex;
 	List	   *indexinfos = NIL;
+	int		i;
 
 	/*
 	 * We need not lock the relation since it was already locked, either by
@@ -471,6 +472,14 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	if (inhparent && relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		set_relation_partition_info(root, rel, relation);
 
+	for (i = 0; i < relation->rd_att->natts; i++)
+	{
+		FormData_pg_attribute attr = relation->rd_att->attrs[i];
+		if (attr.attnotnull)
+			rel->notnull_attrs[0] = bms_add_member(rel->notnull_attrs[0],
+												   attr.attnum - FirstLowInvalidHeapAttributeNumber);
+	}
+
 	table_close(relation, NoLock);
 
 	/*
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index e105a4d5f1..5300652e84 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -16,6 +16,7 @@
 
 #include <limits.h>
 
+#include "access/sysattr.h"
 #include "miscadmin.h"
 #include "nodes/nodeFuncs.h"
 #include "optimizer/appendinfo.h"
@@ -258,6 +259,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
 	rel->all_partrels = NULL;
 	rel->partexprs = NULL;
 	rel->nullable_partexprs = NULL;
+	rel->notnull_attrs = palloc0(sizeof(Bitmapset *) * 1);
 
 	/*
 	 * Pass assorted information down the inheritance hierarchy.
@@ -556,6 +558,81 @@ add_join_rel(PlannerInfo *root, RelOptInfo *joinrel)
 	}
 }
 
+static void
+copy_notnull_attrs_to_joinrel(RelOptInfo *joinrel, RelOptInfo *rel)
+{
+	int relid;
+	if (bms_get_singleton_member(rel->relids, &relid))
+		joinrel->notnull_attrs[relid] = bms_copy(rel->notnull_attrs[0]);
+	else
+	{
+		relid = -1;
+		while ((relid = bms_next_member(rel->relids, relid)) >= 0)
+			joinrel->notnull_attrs[relid] = bms_copy(rel->notnull_attrs[relid]);
+	}
+}
+
+/*
+ *
+ */
+static void
+set_joinrel_notnull_attrs(RelOptInfo *joinrel,
+						  RelOptInfo *outer_rel,
+						  RelOptInfo *inner_rel,
+						  List *restrictlist,
+						  SpecialJoinInfo *sjinfo)
+{
+	if (sjinfo->jointype == JOIN_FULL)
+		/* Both sides are nullable. */
+		return;
+	/* If it is not FULL join, the outer side is not changed. */
+	copy_notnull_attrs_to_joinrel(joinrel, outer_rel);
+	switch(sjinfo->jointype)
+	{
+		case JOIN_ANTI:
+		case JOIN_SEMI:
+		case JOIN_INNER:
+			copy_notnull_attrs_to_joinrel(joinrel, inner_rel);
+			{
+				ListCell	*lc;
+				List *clauses = extract_actual_clauses(restrictlist, false);
+				foreach(lc, find_nonnullable_vars((Node *) clauses))
+				{
+					Var *var = lfirst_node(Var, lc);
+					if (!bms_is_member(var->varno, joinrel->relids))
+					{
+						/* lateral join */
+						continue;
+					}
+					joinrel->notnull_attrs[var->varno] = bms_add_member(
+						joinrel->notnull_attrs[var->varno],
+						var->varattno - FirstLowInvalidHeapAttributeNumber);
+				}
+			}
+			break;
+		case JOIN_LEFT:
+			break;
+		default:
+			elog(ERROR, "Unexpected join type %d", sjinfo->jointype);
+	}
+	/* Debug Only, will be removed at last. */
+	if (false)
+	{
+		int relid = -1;
+		int eLevel = INFO;
+		elog(eLevel, "Dump notnull for JoinRel(%s)", bmsToString(joinrel->relids));
+		while((relid = bms_next_member(joinrel->relids, relid)) >= 0)
+		{
+			Bitmapset *notnullattrs = joinrel->notnull_attrs[relid];
+			if (notnullattrs != NULL)
+				elog(eLevel, "FirstLowInvalidHeapAttributeNumber = %d, RELID = (%d), notnull_attrs: %s",
+					 FirstLowInvalidHeapAttributeNumber,
+					 relid,
+					 bmsToString(notnullattrs));
+		}
+	}
+
+}
 /*
  * build_join_rel
  *	  Returns relation entry corresponding to the union of two given rels,
@@ -672,6 +749,7 @@ build_join_rel(PlannerInfo *root,
 	joinrel->all_partrels = NULL;
 	joinrel->partexprs = NULL;
 	joinrel->nullable_partexprs = NULL;
+	joinrel->notnull_attrs = palloc0(sizeof(Bitmapset *) * (bms_max_member(joinrel->relids) + 1));
 
 	/* Compute information relevant to the foreign relations. */
 	set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
@@ -763,6 +841,8 @@ build_join_rel(PlannerInfo *root,
 			lappend(root->join_rel_level[root->join_cur_level], joinrel);
 	}
 
+	set_joinrel_notnull_attrs(joinrel, outer_rel, inner_rel, restrictlist, sjinfo);
+
 	return joinrel;
 }
 
diff --git a/src/include/nodes/bitmapset.h b/src/include/nodes/bitmapset.h
index 1fd12de698..303873a546 100644
--- a/src/include/nodes/bitmapset.h
+++ b/src/include/nodes/bitmapset.h
@@ -94,6 +94,7 @@ extern bool bms_nonempty_difference(const Bitmapset *a, const Bitmapset *b);
 extern int	bms_singleton_member(const Bitmapset *a);
 extern bool bms_get_singleton_member(const Bitmapset *a, int *member);
 extern int	bms_num_members(const Bitmapset *a);
+extern int	bms_max_member(const Bitmapset *a);
 
 /* optimized tests when we don't need to know exact membership count: */
 extern BMS_Membership bms_membership(const Bitmapset *a);
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index b7b2817a5d..d6758f21e1 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -686,6 +686,12 @@ typedef struct RelOptInfo
 	/* default result targetlist for Paths scanning this relation */
 	struct PathTarget *reltarget;	/* list of Vars/Exprs, cost, width */
 
+	Bitmapset	**notnull_attrs; /* The attno which is not null after evalating
+								  * all the quals on this relation, for baserel,
+								  * the len would always 1. and for others the array
+								  * index is relid from relids.
+								  */
+
 	/* materialization information */
 	List	   *pathlist;		/* Path structures */
 	List	   *ppilist;		/* ParamPathInfos used in pathlist */
-- 
2.21.0

From 7799b005726b542cffa6e9a19704cabf235b214e Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Wed, 7 Jul 2021 16:02:01 +0800
Subject: [PATCH v1] design uniquekey v2

---
 src/backend/nodes/list.c                      |  16 +
 src/backend/optimizer/path/Makefile           |   3 +-
 src/backend/optimizer/path/allpaths.c         |   7 +-
 src/backend/optimizer/path/uniquekey.c        | 276 ++++++++++++++++++
 src/backend/optimizer/plan/planner.c          |   3 +
 src/backend/optimizer/util/plancat.c          |  17 ++
 src/include/nodes/nodes.h                     |   3 +-
 src/include/nodes/pathnodes.h                 |  16 +
 src/include/nodes/pg_list.h                   |   2 +
 src/include/optimizer/paths.h                 |   4 +
 src/include/optimizer/plancat.h               |   2 +
 src/test/regress/expected/join.out            |  11 +-
 src/test/regress/expected/select_distinct.out |  50 ++++
 src/test/regress/sql/select_distinct.sql      |  20 ++
 14 files changed, 420 insertions(+), 10 deletions(-)
 create mode 100644 src/backend/optimizer/path/uniquekey.c

diff --git a/src/backend/nodes/list.c b/src/backend/nodes/list.c
index 94fb236daf..3053ce38ea 100644
--- a/src/backend/nodes/list.c
+++ b/src/backend/nodes/list.c
@@ -702,6 +702,22 @@ list_member_oid(const List *list, Oid datum)
 	return false;
 }
 
+/*
+ * list_is_subset_ptr - is A a subset of B?
+ */
+bool
+list_is_subset_ptr(const List *a, const List *b)
+{
+	ListCell *lc;
+	foreach(lc, a)
+	{
+		if (!list_member_ptr(b, lfirst(lc)))
+			return false;
+	}
+	return true;
+}
+
+
 /*
  * Delete the n'th cell (counting from 0) in list.
  *
diff --git a/src/backend/optimizer/path/Makefile b/src/backend/optimizer/path/Makefile
index 1e199ff66f..63cc1505d9 100644
--- a/src/backend/optimizer/path/Makefile
+++ b/src/backend/optimizer/path/Makefile
@@ -21,6 +21,7 @@ OBJS = \
 	joinpath.o \
 	joinrels.o \
 	pathkeys.o \
-	tidpath.o
+	tidpath.o \
+	uniquekey.o
 
 include $(top_srcdir)/src/backend/common.mk
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 0dcb9e2337..f5d8a443f9 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -396,6 +396,9 @@ static void
 set_rel_size(PlannerInfo *root, RelOptInfo *rel,
 			 Index rti, RangeTblEntry *rte)
 {
+	/* Set the notnull before the UniqueKey populate */
+	set_baserel_notnull_attrs(rel);
+
 	if (rel->reloptkind == RELOPT_BASEREL &&
 		relation_excluded_by_constraints(root, rel, rte))
 	{
@@ -491,7 +494,7 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
 		}
 	}
 
-	set_baserel_notnull_attrs(rel);
+
 
 	/*
 	 * We insist that all non-dummy rels have a nonzero rowcount estimate.
@@ -616,6 +619,8 @@ set_plain_rel_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
 	 */
 	check_index_predicates(root, rel);
 
+	populate_baserel_uniquekeys(root, rel);
+
 	/* Mark rel with estimated output rows, width, etc */
 	set_baserel_size_estimates(root, rel);
 }
diff --git a/src/backend/optimizer/path/uniquekey.c b/src/backend/optimizer/path/uniquekey.c
new file mode 100644
index 0000000000..6111e9fa00
--- /dev/null
+++ b/src/backend/optimizer/path/uniquekey.c
@@ -0,0 +1,276 @@
+/*-------------------------------------------------------------------------
+ *
+ * pathkeys.c
+ *	  Utilities for maintaining uniquekey.
+ *
+ *
+ * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ *	  src/backend/optimizer/path/uniquekey.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/sysattr.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/pathnodes.h"
+#include "optimizer/paths.h"
+
+
+/*
+ * print_uniquekey
+ *	Used for easier reivew, should be removed before commit.
+ */
+static void
+print_uniquekey(PlannerInfo *root, RelOptInfo *rel)
+{
+	if (false)
+	{
+		ListCell	*lc;
+		elog(INFO, "Rel = %s", bmsToString(rel->relids));
+		foreach(lc, rel->uniquekeys)
+		{
+			UniqueKey *ukey = lfirst_node(UniqueKey, lc);
+			int i = -1;
+			elog(INFO, "UNIQUEKEY{indexes=%s, multinull=%d}",
+				 bmsToString(ukey->unique_expr_indexes),
+				 ukey->multi_nulls
+				);
+
+			while ((i = bms_next_member(ukey->unique_expr_indexes, i)) >= 0)
+			{
+				Node *node = (Node *) list_nth(root->unique_exprs, i);
+				if (IsA(node, SingleRow))
+					elog(INFO,
+						 "Expr(%d) SingleRow{relid = %d}",
+						 i, castNode(SingleRow, node)->relid);
+				else
+					elog(INFO,
+						 "EC(%d), %s", i, nodeToString(node)
+						);
+			}
+		}
+	}
+}
+
+static UniqueKey *
+make_uniquekey(Bitmapset *unique_expr_indexes, bool multi_null)
+{
+	UniqueKey *ukey = makeNode(UniqueKey);
+	ukey->unique_expr_indexes = unique_expr_indexes;
+	ukey->multi_nulls = multi_null;
+	return ukey;
+}
+
+
+static PathKey *
+find_matching_pathkey_expr(Expr *expr,  List *pathkeys, Relids relids)
+{
+	ListCell *lc;
+	foreach(lc, pathkeys)
+	{
+		PathKey * pathkey = lfirst_node(PathKey, lc);
+		if (find_ec_member_matching_expr(pathkey->pk_eclass, expr, relids))
+			return pathkey;
+	}
+	return NULL;
+}
+
+static bool
+add_uniquekey_for_uniqueindex(PlannerInfo *root, IndexOptInfo *unique_index,
+							  List *mergeable_const_peer, List *expr_opfamilies)
+{
+
+	List	*unique_ecs = NIL;
+	ListCell	*indexpr_item;
+	int	c = 0;
+	RelOptInfo *rel = unique_index->rel;
+	PathKey	*pathkey;
+	bool	multinull = false;
+
+	indexpr_item = list_head(unique_index->indexprs);
+	for (c = 0; c < unique_index->nkeycolumns; c++)
+	{
+		int attr = unique_index->indexkeys[c];
+		Expr *expr;
+		bool	matched_const = false;
+		ListCell	*lc1, *lc2;
+		if (attr > 0)
+		{
+			Var *var;
+			expr = list_nth_node(TargetEntry, unique_index->indextlist, c)->expr;
+			var = castNode(Var, expr);
+			Assert(IsA(expr, Var));
+			if (!bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber,
+							  rel->notnull_attrs[0]))
+				multinull = true;
+		}
+		else if (attr == 0)
+		{
+			/* Expression index */
+			expr = lfirst(indexpr_item);
+			indexpr_item = lnext(unique_index->indexprs, indexpr_item);
+			/* We can't grantee an FuncExpr will not return NULLs */
+			multinull = true;
+		}
+		else /* attr < 0 */
+		{
+			/* Index on OID is possible, not handle it for now. */
+			return false;
+		}
+
+		/*
+		 * Check index_col = Const case with regarding to opfamily checking
+		 * If so, we can remove the index_col from the final UniqueKey->exprs.
+		 */
+		forboth(lc1, mergeable_const_peer, lc2, expr_opfamilies)
+		{
+			if (list_member_oid((List *) lfirst(lc2), unique_index->opfamily[c]) &&
+				match_index_to_operand((Node *) lfirst(lc1), c, unique_index))
+			{
+				matched_const = true;
+				break;
+			}
+		}
+
+		if (matched_const)
+			continue;
+
+		/* Check if this expr exist in distinct_pathkey. */
+		pathkey = find_matching_pathkey_expr(expr, root->distinct_pathkeys, rel->relids);
+		if (!pathkey)
+			return false;
+		unique_ecs = lappend(unique_ecs, pathkey->pk_eclass);
+	}
+
+	{
+		Bitmapset *unique_exprs_index = bms_make_singleton(list_length(root->unique_exprs));
+		if (unique_ecs == NIL)
+		{
+			SingleRow *singlerow = makeNode(SingleRow);
+			singlerow->relid = rel->relid;
+			rel->uniquekeys = list_make1(make_uniquekey(unique_exprs_index, false /* multi-null */));
+			root->unique_exprs = lappend(root->unique_exprs, singlerow);
+			return true;
+		}
+		else
+		{
+			UniqueKey *ukey = make_uniquekey(unique_exprs_index, multinull);
+			root->unique_exprs = lappend(root->unique_exprs, unique_ecs);
+			rel->uniquekeys = lappend(rel->uniquekeys, ukey);
+			return false;
+		}
+	}
+	return false;
+}
+
+void
+populate_baserel_uniquekeys(PlannerInfo *root, RelOptInfo *rel)
+{
+	ListCell	*lc;
+	List	*mergeable_const_peer = NIL, *expr_opfamilies = NIL;
+	foreach(lc, rel->baserestrictinfo)
+	{
+		RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
+		if (rinfo->mergeopfamilies == NIL)
+			continue;
+
+		if (bms_is_empty(rinfo->left_relids))
+			mergeable_const_peer = lappend(mergeable_const_peer, get_rightop(rinfo->clause));
+		else if (bms_is_empty(rinfo->right_relids))
+			mergeable_const_peer = lappend(mergeable_const_peer, get_leftop(rinfo->clause));
+		else
+			continue;
+		expr_opfamilies = lappend(expr_opfamilies, rinfo->mergeopfamilies);
+	}
+
+	foreach(lc, rel->indexlist)
+	{
+		IndexOptInfo *index = (IndexOptInfo *)lfirst(lc);
+		if (!index->unique || !index->immediate ||
+			(index->indpred != NIL && !index->predOK))
+			continue;
+
+		if (add_uniquekey_for_uniqueindex(root, index,
+										  mergeable_const_peer,
+										  expr_opfamilies))
+			return;
+	}
+
+	print_uniquekey(root, rel);
+}
+
+
+static bool
+uniquekey_contains_in(PlannerInfo *root, UniqueKey *ukey, List *lecs, Relids relids)
+{
+	int i = -1;
+	while ((i = bms_next_member(ukey->unique_expr_indexes, i)) >= 0)
+	{
+		Node *expr = list_nth(root->unique_exprs, i);
+		if (IsA(expr, SingleRow))
+		{
+			/* Any column regarding SingleRow.relid is OK */
+			if (!bms_is_member(castNode(SingleRow, expr)->relid, relids))
+				return false;
+		}
+		else if (IsA(expr, List))
+		{
+			/* unique_expr is a List of EquivalenceClass * */
+			if (!list_is_subset_ptr((List*)expr, lecs))
+				return false;
+		}
+		else
+		{
+			/* Impossible to go here */
+			Assert(false);
+			return false;
+		}
+	}
+	return true;
+}
+
+
+bool
+relation_is_distinct_for(PlannerInfo *root, RelOptInfo *rel, List *distinct_pathkey)
+{
+	ListCell	*lc;
+	List	*lecs = NIL;
+	Relids	relids = NULL;
+	foreach(lc, distinct_pathkey)
+	{
+		PathKey *pathkey = lfirst(lc);
+		lecs = lappend(lecs, pathkey->pk_eclass);
+		/*
+		 * Note that ec_relids doesn't include child member, but
+		 * distinct would not operate on childrel as well.
+		 */
+		relids = bms_union(relids, pathkey->pk_eclass->ec_relids);
+	}
+
+	foreach(lc, rel->uniquekeys)
+	{
+		UniqueKey *ukey = lfirst(lc);
+		if (ukey->multi_nulls)
+			continue;
+
+		if (uniquekey_contains_in(root, ukey, lecs, relids))
+			return true;
+	}
+
+	return false;
+
+}
+
+
+static UniqueKey*
+__attribute__ ((unused))
+build_composited_uniquekey(UniqueKey *ukey1, UniqueKey *ukey2, bool multi_null)
+{
+	Bitmapset *unique_expr = bms_union(ukey1->unique_expr_indexes, ukey2->unique_expr_indexes);
+	return make_uniquekey(unique_expr, multi_null);
+
+}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 1868c4eff4..b9d4e6395e 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -4238,6 +4238,9 @@ create_distinct_paths(PlannerInfo *root,
 	Path	   *path;
 	ListCell   *lc;
 
+	if (relation_is_distinct_for(root, input_rel, root->distinct_pathkeys))
+		return input_rel;
+
 	/* For now, do all work in the (DISTINCT, NULL) upperrel */
 	distinct_rel = fetch_upper_rel(root, UPPERREL_DISTINCT, NULL);
 
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 7d3b40090e..4852d88c52 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -2079,6 +2079,23 @@ get_function_rows(PlannerInfo *root, Oid funcid, Node *node)
 	return result;
 }
 
+List *
+get_unique_indexes(RelOptInfo *rel)
+{
+	List	*res = NIL;
+	ListCell	*lc;
+	foreach(lc, rel->indexlist)
+	{
+		IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);
+
+		if (index->unique && index->immediate &&
+			(index->indpred == NIL || index->predOK))
+			res = lappend(res, index);
+	}
+	return res;
+}
+
+
 /*
  * has_unique_index
  *
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index d9e417bcd7..7a76413ba1 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -280,7 +280,8 @@ typedef enum NodeTag
 	T_RollupData,
 	T_GroupingSetData,
 	T_StatisticExtInfo,
-
+	T_UniqueKey,
+	T_SingleRow,
 	/*
 	 * TAGS FOR MEMORY NODES (memnodes.h)
 	 */
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index d6758f21e1..2ee9e0885b 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -246,6 +246,7 @@ struct PlannerInfo
 									 * subquery outputs */
 
 	List	   *eq_classes;		/* list of active EquivalenceClasses */
+	List	   *unique_exprs;		/* List of unique expr */
 
 	bool		ec_merging_done;	/* set true once ECs are canonical */
 
@@ -691,6 +692,7 @@ typedef struct RelOptInfo
 								  * the len would always 1. and for others the array
 								  * index is relid from relids.
 								  */
+	List		*uniquekeys; /* A list of UniqueKey. */
 
 	/* materialization information */
 	List	   *pathlist;		/* Path structures */
@@ -1067,6 +1069,20 @@ typedef struct PathKey
 	bool		pk_nulls_first; /* do NULLs come before normal values? */
 } PathKey;
 
+
+typedef struct UnqiueKey
+{
+	NodeTag	type;
+	Bitmapset	*unique_expr_indexes;
+	bool	multi_nulls;
+} UniqueKey;
+
+typedef struct SingleRow
+{
+	NodeTag	type;
+	Index		relid;
+} SingleRow;
+
 /*
  * VolatileFunctionStatus -- allows nodes to cache their
  * contain_volatile_functions properties. VOLATILITY_UNKNOWN means not yet
diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h
index 30f98c4595..bbe0209d7e 100644
--- a/src/include/nodes/pg_list.h
+++ b/src/include/nodes/pg_list.h
@@ -558,6 +558,8 @@ extern bool list_member_ptr(const List *list, const void *datum);
 extern bool list_member_int(const List *list, int datum);
 extern bool list_member_oid(const List *list, Oid datum);
 
+extern bool list_is_subset_ptr(const List *a, const List *b);
+
 extern pg_nodiscard List *list_delete(List *list, void *datum);
 extern pg_nodiscard List *list_delete_ptr(List *list, void *datum);
 extern pg_nodiscard List *list_delete_int(List *list, int datum);
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index f1d111063c..07a51d737a 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -255,4 +255,8 @@ extern PathKey *make_canonical_pathkey(PlannerInfo *root,
 extern void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 									List *live_childrels);
 
+extern void populate_baserel_uniquekeys(PlannerInfo *root,
+										RelOptInfo *baserel);
+extern bool relation_is_distinct_for(PlannerInfo *root, RelOptInfo *rel,
+									 List *distinct_pathkey);
 #endif							/* PATHS_H */
diff --git a/src/include/optimizer/plancat.h b/src/include/optimizer/plancat.h
index 8d1d6c1b42..2dd4f525e6 100644
--- a/src/include/optimizer/plancat.h
+++ b/src/include/optimizer/plancat.h
@@ -41,6 +41,8 @@ extern bool relation_excluded_by_constraints(PlannerInfo *root,
 
 extern List *build_physical_tlist(PlannerInfo *root, RelOptInfo *rel);
 
+extern List *get_unique_indexes(RelOptInfo *rel);
+extern List *get_exprs_from_index(IndexOptInfo *indinfo, bool contains_includekey);
 extern bool has_unique_index(RelOptInfo *rel, AttrNumber attno);
 
 extern Selectivity restriction_selectivity(PlannerInfo *root,
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index fec0325e73..0631e8076d 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4604,18 +4604,15 @@ select d.* from d left join (select * from b group by b.id, b.c_id) s
 explain (costs off)
 select d.* from d left join (select distinct * from b) s
   on d.a = s.id;
-              QUERY PLAN              
---------------------------------------
+             QUERY PLAN             
+------------------------------------
  Merge Right Join
    Merge Cond: (b.id = d.a)
-   ->  Unique
-         ->  Sort
-               Sort Key: b.id, b.c_id
-               ->  Seq Scan on b
+   ->  Index Scan using b_pkey on b
    ->  Sort
          Sort Key: d.a
          ->  Seq Scan on d
-(9 rows)
+(6 rows)
 
 -- check join removal works when uniqueness of the join condition is enforced
 -- by a UNION
diff --git a/src/test/regress/expected/select_distinct.out b/src/test/regress/expected/select_distinct.out
index 11c6f50fbf..c5d32cec3a 100644
--- a/src/test/regress/expected/select_distinct.out
+++ b/src/test/regress/expected/select_distinct.out
@@ -306,3 +306,53 @@ SELECT null IS NOT DISTINCT FROM null as "yes";
  t
 (1 row)
 
+-- uniquekey test
+CREATE TABLE uktest(a int, b int, c int not null, d int, e int, f int, PRIMARY KEY(a, b));
+CREATE UNIQUE INDEX on uktest(c, d);
+EXPLAIN (COSTS OFF) SELECT DISTINCT * FROM uktest;
+     QUERY PLAN     
+--------------------
+ Seq Scan on uktest
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT c FROM uktest;
+        QUERY PLAN        
+--------------------------
+ HashAggregate
+   Group Key: c
+   ->  Seq Scan on uktest
+(3 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uktest;
+        QUERY PLAN        
+--------------------------
+ HashAggregate
+   Group Key: c, d
+   ->  Seq Scan on uktest
+(3 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uktest WHERE d > 10;
+     QUERY PLAN     
+--------------------
+ Seq Scan on uktest
+   Filter: (d > 10)
+(2 rows)
+
+-- UniqueKey expressions reduce due to c = Const.
+EXPLAIN (COSTS OFF) SELECT DISTINCT b FROM uktest where a = 1;
+               QUERY PLAN               
+----------------------------------------
+ Bitmap Heap Scan on uktest
+   Recheck Cond: (a = 1)
+   ->  Bitmap Index Scan on uktest_pkey
+         Index Cond: (a = 1)
+(4 rows)
+
+-- Single row case.
+EXPLAIN (COSTS OFF) SELECT DISTINCT c FROM uktest where a = 1 and b = 1;
+               QUERY PLAN               
+----------------------------------------
+ Index Scan using uktest_pkey on uktest
+   Index Cond: ((a = 1) AND (b = 1))
+(2 rows)
+
diff --git a/src/test/regress/sql/select_distinct.sql b/src/test/regress/sql/select_distinct.sql
index 33102744eb..706bcf37df 100644
--- a/src/test/regress/sql/select_distinct.sql
+++ b/src/test/regress/sql/select_distinct.sql
@@ -135,3 +135,23 @@ SELECT 1 IS NOT DISTINCT FROM 2 as "no";
 SELECT 2 IS NOT DISTINCT FROM 2 as "yes";
 SELECT 2 IS NOT DISTINCT FROM null as "no";
 SELECT null IS NOT DISTINCT FROM null as "yes";
+
+-- uniquekey test
+CREATE TABLE uktest(a int, b int, c int not null, d int, e int, f int, PRIMARY KEY(a, b));
+CREATE UNIQUE INDEX on uktest(c, d);
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT * FROM uktest;
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT c FROM uktest;
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uktest;
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uktest WHERE d > 10;
+
+-- UniqueKey expressions reduce due to c = Const.
+EXPLAIN (COSTS OFF) SELECT DISTINCT b FROM uktest where a = 1;
+
+-- Single row case.
+EXPLAIN (COSTS OFF) SELECT DISTINCT c FROM uktest where a = 1 and b = 1;
+
+
-- 
2.21.0

From f2d62ff4ceca2a33e557e5fb8bc4a2f6f3de872a Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sun, 25 Jul 2021 16:26:45 +0800
Subject: [PATCH v3 2/6] Just some utils functions.

---
 src/backend/nodes/list.c                | 21 ++++++++++++
 src/backend/optimizer/path/equivclass.c | 45 +++++++++++++++++++++++++
 src/backend/optimizer/util/tlist.c      | 19 +++++++++++
 src/include/nodes/pg_list.h             |  2 ++
 src/include/optimizer/optimizer.h       |  1 +
 src/include/optimizer/paths.h           |  6 ++++
 6 files changed, 94 insertions(+)

diff --git a/src/backend/nodes/list.c b/src/backend/nodes/list.c
index 94fb236daf..8f2b00012e 100644
--- a/src/backend/nodes/list.c
+++ b/src/backend/nodes/list.c
@@ -702,6 +702,27 @@ list_member_oid(const List *list, Oid datum)
 	return false;
 }
 
+
+/*
+ * list_is_subset_ptr - is A a subset of B?
+ */
+bool
+list_is_subset_ptr(const List *a, const List *b)
+{
+	ListCell *lc;
+	Assert(IsPointerList(a));
+	check_list_invariants(a);
+	Assert(IsPointerList(b));
+	check_list_invariants(b);
+	foreach(lc, a)
+	{
+		if (!list_member_ptr(b, lfirst(lc)))
+			return false;
+	}
+	return true;
+}
+
+
 /*
  * Delete the n'th cell (counting from 0) in list.
  *
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 2f688807af..b8ada6e8a3 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -770,6 +770,26 @@ get_eclass_for_sort_expr(PlannerInfo *root,
 	return newec;
 }
 
+/*
+ * find_ec_member_matching_expr
+ *		Locate an EquivalenceClass matching the given expr, if any;
+ *		return NULL if no match.
+ */
+EquivalenceClass *
+find_ec_matching_expr(PlannerInfo *root,
+					  Expr *expr,
+					  RelOptInfo *baserel)
+{
+	int i = -1;
+	while ((i = bms_next_member(baserel->eclass_indexes, i)) >= 0)
+	{
+		EquivalenceClass *ec = list_nth(root->eq_classes, i);
+		if (find_ec_member_matching_expr(ec, expr, baserel->relids))
+			return ec;
+	}
+	return NULL;
+}
+
 /*
  * find_ec_member_matching_expr
  *		Locate an EquivalenceClass member matching the given expr, if any;
@@ -960,6 +980,31 @@ find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
 	return NULL;
 }
 
+
+/*
+ * build_equivalanceclass_list_for_exprs
+ *
+ * 	Given a list of expr, find the related ECs for everyone of them.
+ * if any exprs has no EC related, return NIL.
+ */
+List *
+build_equivalanceclass_list_for_exprs(PlannerInfo *root,
+									  List *exprs,
+									  RelOptInfo *rel)
+{
+	ListCell	*lc;
+	List	*ecs = NIL;
+
+	foreach(lc, exprs)
+	{
+		EquivalenceClass *ec = find_ec_matching_expr(root, lfirst(lc), rel);
+		if (!ec)
+			return NIL;
+		ecs = lappend(ecs, ec);
+	}
+	return ecs;
+}
+
 /*
  * ec_useful_for_merging
  *	check if the ec exists in rel's merageable restrictinfo_lists.
diff --git a/src/backend/optimizer/util/tlist.c b/src/backend/optimizer/util/tlist.c
index 311579d059..5d8493c8d6 100644
--- a/src/backend/optimizer/util/tlist.c
+++ b/src/backend/optimizer/util/tlist.c
@@ -359,6 +359,25 @@ get_sortgroupclause_tle(SortGroupClause *sgClause,
 	return get_sortgroupref_tle(sgClause->tleSortGroupRef, targetList);
 }
 
+/*
+ * get_tle_from_expr
+ *
+ *	Find the targetlist entry matching the given expr and return it.
+ */
+TargetEntry *
+get_tle_from_expr(Expr *expr, List *targetlist)
+{
+	ListCell	*lc;
+
+	foreach(lc, targetlist)
+	{
+		TargetEntry *tle = (TargetEntry *) lfirst(lc);
+
+		if (equal(tle->expr, expr))
+			return tle;
+	}
+	return NULL;
+}
 /*
  * get_sortgroupclause_expr
  *		Find the targetlist entry matching the given SortGroupClause
diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h
index 30f98c4595..bbe0209d7e 100644
--- a/src/include/nodes/pg_list.h
+++ b/src/include/nodes/pg_list.h
@@ -558,6 +558,8 @@ extern bool list_member_ptr(const List *list, const void *datum);
 extern bool list_member_int(const List *list, int datum);
 extern bool list_member_oid(const List *list, Oid datum);
 
+extern bool list_is_subset_ptr(const List *a, const List *b);
+
 extern pg_nodiscard List *list_delete(List *list, void *datum);
 extern pg_nodiscard List *list_delete_ptr(List *list, void *datum);
 extern pg_nodiscard List *list_delete_int(List *list, int datum);
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 41b49b2662..5911014a32 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -171,6 +171,7 @@ extern TargetEntry *get_sortgroupref_tle(Index sortref,
 										 List *targetList);
 extern TargetEntry *get_sortgroupclause_tle(SortGroupClause *sgClause,
 											List *targetList);
+extern TargetEntry *get_tle_from_expr(Expr *expr, List *targetlist);
 extern Node *get_sortgroupclause_expr(SortGroupClause *sgClause,
 									  List *targetList);
 extern List *get_sortgrouplist_exprs(List *sgClauses,
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index a29f616423..e813d82483 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -135,6 +135,9 @@ extern EquivalenceClass *get_eclass_for_sort_expr(PlannerInfo *root,
 												  Index sortref,
 												  Relids rel,
 												  bool create_it);
+extern EquivalenceClass *find_ec_matching_expr(PlannerInfo *root,
+											   Expr *expr,
+											   RelOptInfo *rel);
 extern EquivalenceMember *find_ec_member_matching_expr(EquivalenceClass *ec,
 													   Expr *expr,
 													   Relids relids);
@@ -146,6 +149,9 @@ extern EquivalenceMember *find_computable_ec_member(PlannerInfo *root,
 extern bool ec_useful_for_merging(PlannerInfo *root, RelOptInfo *rel,
 								  EquivalenceClass *ec);
 extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
+extern List *build_equivalanceclass_list_for_exprs(PlannerInfo *root,
+												   List *exprs,
+												   RelOptInfo *rel);
 extern bool relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
 										 EquivalenceClass *ec,
 										 bool require_parallel_safe);
-- 
2.21.0

From c4ab5a7ff2f47d3159ba6450ae39ef98acb5f727 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Mon, 9 Aug 2021 19:43:58 +0800
Subject: [PATCH v3 5/6] Support UniqueKey on JoinRel.

---
 src/backend/optimizer/path/uniquekey.c  | 377 ++++++++++++++++++++++++
 src/backend/optimizer/util/relnode.c    |   2 +
 src/include/optimizer/paths.h           |   5 +
 src/test/regress/expected/uniquekey.out | 327 ++++++++++++++++++++
 src/test/regress/sql/uniquekey.sql      |  79 +++++
 5 files changed, 790 insertions(+)

diff --git a/src/backend/optimizer/path/uniquekey.c b/src/backend/optimizer/path/uniquekey.c
index c93075656d..badc843253 100644
--- a/src/backend/optimizer/path/uniquekey.c
+++ b/src/backend/optimizer/path/uniquekey.c
@@ -27,6 +27,26 @@ static bool add_uniquekey_for_uniqueindex(PlannerInfo *root,
 										  List *mergeable_const_peer,
 										  List *expr_opfamilies);
 
+static bool is_uniquekey_nulls_removed(PlannerInfo *root,
+									   UniqueKey *ukey,
+									   RelOptInfo *rel);
+static UniqueKey *adjust_uniquekey_multinull_for_joinrel(PlannerInfo *root,
+														 UniqueKey *joinrel_ukey,
+														 RelOptInfo *rel,
+														 bool below_outer_side);
+
+static bool populate_joinrel_uniquekey_for_rel(PlannerInfo *root, RelOptInfo *joinrel,
+											   RelOptInfo *rel, RelOptInfo *other_rel,
+											   List *restrictlist, JoinType jointype);
+static void populate_joinrel_composited_uniquekey(PlannerInfo *root,
+												  RelOptInfo *joinrel,
+												  RelOptInfo *outerrel,
+												  RelOptInfo *innerrel,
+												  List	*restrictlist,
+												  JoinType jointype,
+												  bool outeruk_still_valid,
+												  bool inneruk_still_valid);
+
 /* UniqueKey is subset of .. */
 static bool uniquekey_contains_in(PlannerInfo *root, UniqueKey *ukey,
 								  List *ecs, Relids relids);
@@ -35,6 +55,9 @@ static bool uniquekey_contains_in(PlannerInfo *root, UniqueKey *ukey,
 static bool unique_ecs_useful_for_distinct(PlannerInfo *root, List *ecs);
 static bool unique_ecs_useful_for_merging(PlannerInfo *root, RelOptInfo *rel,
 										  List *unique_ecs);
+static bool is_uniquekey_useful_afterjoin(PlannerInfo *root, UniqueKey *ukey,
+										  RelOptInfo *joinrel);
+
 /* Helper functions to create UniqueKey. */
 static UniqueKey *make_uniquekey(Bitmapset *unique_expr_indexes,
 								 bool multi_null,
@@ -90,6 +113,78 @@ populate_baserel_uniquekeys(PlannerInfo *root, RelOptInfo *rel)
 	print_uniquekey(root, rel);
 }
 
+/*
+ * populate_joinrel_uniquekeys
+ */
+void
+populate_joinrel_uniquekeys(PlannerInfo *root, RelOptInfo *joinrel,
+							RelOptInfo *outerrel, RelOptInfo *innerrel,
+							List *restrictlist, JoinType jointype)
+{
+	bool outeruk_still_valid = false, inneruk_still_valid = false;
+	if (jointype == JOIN_SEMI || jointype == JOIN_ANTI)
+	{
+		ListCell	*lc;
+		foreach(lc, outerrel->uniquekeys)
+		{
+			/*
+			 * SEMI/ANTI join can be used to remove NULL values as well.
+			 * So we need to adjust multi_nulls for join.
+			 */
+			joinrel->uniquekeys = lappend(joinrel->uniquekeys,
+										  adjust_uniquekey_multinull_for_joinrel(root,
+																				 lfirst(lc),
+																				 joinrel,
+																				 false));
+			return;
+		}
+	}
+
+	if (outerrel->uniquekeys == NIL || innerrel->uniquekeys == NIL)
+		return;
+
+	switch(jointype)
+	{
+		case JOIN_INNER:
+			outeruk_still_valid = populate_joinrel_uniquekey_for_rel(root, joinrel, outerrel,
+																	 innerrel, restrictlist, jointype);
+			inneruk_still_valid = populate_joinrel_uniquekey_for_rel(root, joinrel, innerrel,
+																	 outerrel, restrictlist, jointype);
+			break;
+
+		case JOIN_LEFT:
+			/*
+			 * For left join, we are sure the innerrel's multi_nulls would be true
+			 * and it can't become to multi_nulls=false any more. so just discard it
+			 * and only check the outerrel and composited ones.
+			 */
+			outeruk_still_valid = populate_joinrel_uniquekey_for_rel(root, joinrel, outerrel,
+																	 innerrel, restrictlist, jointype);
+			break;
+
+		case JOIN_FULL:
+			/*
+			 * Both sides would contains multi_nulls, don't maintain it
+			 * any more.
+			 */
+			break;
+
+		default:
+			elog(ERROR, "unexpected join_type %d", jointype);
+	}
+
+	populate_joinrel_composited_uniquekey(root, joinrel,
+										  outerrel,
+										  innerrel,
+										  restrictlist,
+										  jointype,
+										  outeruk_still_valid,
+										  inneruk_still_valid);
+
+
+	return;
+}
+
 /*
  * relation_is_distinct_for
  *		Check if the relation is distinct for.
@@ -238,6 +333,251 @@ add_uniquekey_for_uniqueindex(PlannerInfo *root, IndexOptInfo *unique_index,
 											 used_for_distinct));
 	return false;
 }
+
+/*
+ * is_uniquekey_nulls_removed
+ *
+ *	note this function will not consider the OUTER JOIN impacts. Caller should
+ * take care of it.
+ *	-- Use my way temporary (RelOptInfo.notnull_attrs) until Tom's is ready.
+ */
+static bool
+is_uniquekey_nulls_removed(PlannerInfo *root,
+						   UniqueKey *ukey,
+						   RelOptInfo *joinrel)
+{
+	int i = -1;
+
+	while((i = bms_next_member(ukey->unique_expr_indexes, i)) >= 0)
+	{
+		Node *node = list_nth(root->unique_exprs, i);
+		List	*ecs;
+		ListCell	*lc;
+		if (IsA(node, SingleRow))
+			continue;
+		ecs = castNode(List, node);
+		foreach(lc, ecs)
+		{
+			EquivalenceClass *ec = lfirst_node(EquivalenceClass, lc);
+			ListCell *emc;
+			foreach(emc, ec->ec_members)
+			{
+				EquivalenceMember *em = lfirst_node(EquivalenceMember, emc);
+				int relid;
+				Var *var;
+				Bitmapset *notnull_attrs;
+				if (!bms_is_subset(em->em_relids, joinrel->relids))
+					continue;
+
+				if (!bms_get_singleton_member(em->em_relids, &relid))
+					continue;
+
+				if (!IsA(em->em_expr, Var))
+					continue;
+
+				var = castNode(Var, em->em_expr);
+
+				if (relid != var->varno)
+					continue;
+
+				notnull_attrs = joinrel->notnull_attrs[var->varno];
+
+				if (!bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber,
+								   notnull_attrs))
+					return false;
+				else
+					break; /* Break to check next ECs */
+			}
+		}
+	}
+	return true;
+}
+
+/*
+ * adjust_uniquekey_multinull_for_joinrel
+ *
+ *	After the join, some NULL values can be removed due to join-clauses.
+ * but the outer join can generated null values again. Return the final
+ * state of the UniqueKey on joinrel.
+ */
+static UniqueKey *
+adjust_uniquekey_multinull_for_joinrel(PlannerInfo *root,
+									   UniqueKey *ukey,
+									   RelOptInfo *joinrel,
+									   bool below_outer_side)
+{
+	if (below_outer_side)
+	{
+		if (ukey->multi_nulls)
+			/* we need it to be multi_nulls, but it is already, just return it. */
+			return ukey;
+		else
+			/* we need it to be multi_nulls, but it is not, create a new one. */
+			return make_uniquekey(ukey->unique_expr_indexes,
+								  true,
+								  ukey->use_for_distinct);
+	}
+	else
+	{
+		/*
+		 * We need to check if the join clauses can remove the NULL values. However
+		 * if it doesn't contain NULL values at the first, we don't need to check it.
+		 */
+		if (!ukey->multi_nulls)
+			return ukey;
+		else
+		{
+			/*
+			 * Multi null values exists. It's time to check if the nulls values
+			 * are removed via outer join.
+			 */
+			if (!is_uniquekey_nulls_removed(root, ukey, joinrel))
+				/* null values can be removed, return the original one. */
+				return ukey;
+			else
+				return make_uniquekey(ukey->unique_expr_indexes,
+									  false, ukey->use_for_distinct);
+		}
+	}
+}
+
+/*
+ * populate_joinrel_uniquekey_for_rel
+ *
+ *    Check if rel.any_column = other_rel.unique_key_columns.
+ * The return value is if the rel->uniquekeys still valid.
+ */
+static bool
+populate_joinrel_uniquekey_for_rel(PlannerInfo *root, RelOptInfo *joinrel,
+								   RelOptInfo *rel, RelOptInfo *other_rel,
+								   List *restrictlist, JoinType type)
+{
+	bool	rel_keep_unique = false;
+	List *other_ecs = NIL;
+	Relids	other_relids = NULL;
+	ListCell	*lc;
+
+	/*
+	 * Gather all the other ECs regarding to rel, if all the unique ecs contains
+	 * in this list, then it hits our expectations.
+	 */
+	foreach(lc, restrictlist)
+	{
+		RestrictInfo *r = lfirst_node(RestrictInfo, lc);
+
+		if (r->mergeopfamilies == NIL)
+			continue;
+
+		if (bms_equal(r->left_relids, rel->relids) && r->right_ec != NULL)
+		{
+			other_ecs = lappend(other_ecs, r->right_ec);
+			other_relids = bms_add_members(other_relids, r->right_relids);
+		}
+		else if (bms_equal(r->right_relids, rel->relids) && r->left_ec != NULL)
+		{
+			other_ecs = lappend(other_ecs, r->right_ec);
+			other_relids = bms_add_members(other_relids, r->left_relids);
+		}
+	}
+
+	foreach(lc, other_rel->uniquekeys)
+	{
+		UniqueKey *ukey = lfirst_node(UniqueKey, lc);
+		if (uniquekey_contains_in(root, ukey, other_ecs, other_relids))
+		{
+			rel_keep_unique = true;
+			break;
+		}
+	}
+
+	if (!rel_keep_unique)
+		return false;
+
+	foreach(lc, rel->uniquekeys)
+	{
+
+		UniqueKey *ukey = lfirst_node(UniqueKey, lc);
+
+		if (is_uniquekey_useful_afterjoin(root, ukey, joinrel))
+		{
+			ukey = adjust_uniquekey_multinull_for_joinrel(root,
+														  ukey,
+														  joinrel,
+														  false /* outer_side, caller grantees this */);
+			joinrel->uniquekeys = lappend(joinrel->uniquekeys, ukey);
+		}
+	}
+
+	return true;
+}
+
+
+/*
+ * Populate_joinrel_composited_uniquekey
+ *
+ *	A composited unqiuekey is valid no matter with join type and restrictlist.
+ */
+static void
+populate_joinrel_composited_uniquekey(PlannerInfo *root,
+									  RelOptInfo *joinrel,
+									  RelOptInfo *outerrel,
+									  RelOptInfo *innerrel,
+									  List	*restrictlist,
+									  JoinType jointype,
+									  bool left_added,
+									  bool right_added)
+{
+	ListCell	*lc;
+	if (left_added || right_added)
+		/* No need to create the composited ones */
+		return;
+
+	foreach(lc, outerrel->uniquekeys)
+	{
+		UniqueKey	*outer_ukey = adjust_uniquekey_multinull_for_joinrel(root,
+																		 lfirst(lc),
+																		 joinrel,
+																		 jointype == JOIN_FULL);
+		ListCell	*lc2;
+
+		if (!is_uniquekey_useful_afterjoin(root, outer_ukey, joinrel))
+			continue;
+
+		foreach(lc2, innerrel->uniquekeys)
+		{
+			UniqueKey	*inner_ukey = adjust_uniquekey_multinull_for_joinrel(root,
+																			 lfirst(lc2),
+																			 joinrel,
+																			 (jointype == JOIN_FULL || jointype == JOIN_LEFT)
+				);
+
+			UniqueKey	*comp_ukey;
+
+			if (!is_uniquekey_useful_afterjoin(root, inner_ukey, joinrel))
+				continue;
+
+			comp_ukey = make_uniquekey(
+				/* unique_expr_indexes is easy, just union the both sides. */
+				bms_union(outer_ukey->unique_expr_indexes, inner_ukey->unique_expr_indexes),
+				/*
+				 * If both are !multi_nulls, then the composited one is !multi_null
+				 * no matter with jointype and join clauses. otherwise, it is multi
+				 * nulls no matter with other factors.
+				 *
+				 */
+				outer_ukey->multi_nulls || inner_ukey->multi_nulls,
+				/*
+				 * we need both sides are used in distinct to say the composited
+				 * one is used for distinct as well.
+				 */
+				outer_ukey->use_for_distinct && inner_ukey->use_for_distinct);
+
+			joinrel->uniquekeys = lappend(joinrel->uniquekeys, comp_ukey);
+		}
+	}
+}
+
+
 /*
  * uniquekey_contains_in
  *	Return if UniqueKey contains in the list of EquivalenceClass
@@ -333,6 +673,43 @@ unique_ecs_useful_for_merging(PlannerInfo *root, RelOptInfo *rel, List *unique_e
 
 	return true;
 }
+
+/*
+ * is_uniquekey_useful_afterjoin
+ *
+ *  is useful when it contains in distinct_pathkey or in mergable join clauses.
+ */
+static bool
+is_uniquekey_useful_afterjoin(PlannerInfo *root, UniqueKey *ukey,
+							 RelOptInfo *joinrel)
+{
+	int	i = -1;
+
+	if (ukey->use_for_distinct)
+		return true;
+
+	while((i = bms_next_member(ukey->unique_expr_indexes, i)) >= 0)
+	{
+		Node *exprs =  list_nth(root->unique_exprs, i);
+		if (IsA(exprs, List))
+		{
+			if (!unique_ecs_useful_for_merging(root, joinrel, (List *)exprs))
+				return false;
+		}
+		else
+		{
+			Assert(IsA(exprs, SingleRow));
+			/*
+			 * Ideally we should check if there are a expr on SingleRow
+			 * used in joinrel's joinclauses, but it can't be checked effectively
+			 * for now, so we just check the rest part. so just think
+			 * it is useful.
+			 */
+		}
+	}
+	return true;
+}
+
 /*
  *	make_uniquekey
  */
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index b75e1679e6..6695f9f2a6 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -846,6 +846,8 @@ build_join_rel(PlannerInfo *root,
 	}
 
 	set_joinrel_notnull_attrs(joinrel, outer_rel, inner_rel, restrictlist, sjinfo);
+	populate_joinrel_uniquekeys(root, joinrel, outer_rel, inner_rel,
+								restrictlist, sjinfo->jointype);
 
 	return joinrel;
 }
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 68b8b40ca9..f233837e59 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -265,6 +265,11 @@ extern void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 
 extern void populate_baserel_uniquekeys(PlannerInfo *root,
 										RelOptInfo *baserel);
+extern void populate_baserel_uniquekeys(PlannerInfo *root,
+										RelOptInfo *baserel);
+extern void populate_joinrel_uniquekeys(PlannerInfo *root, RelOptInfo *joinrel,
+										RelOptInfo *outerrel, RelOptInfo *innerrel,
+										List *restrictlist, JoinType jointype);
 extern bool relation_is_distinct_for(PlannerInfo *root, RelOptInfo *rel,
 									 List *distinct_pathkey);
 #endif							/* PATHS_H */
diff --git a/src/test/regress/expected/uniquekey.out b/src/test/regress/expected/uniquekey.out
index d9a8634e80..c2bd1fa619 100644
--- a/src/test/regress/expected/uniquekey.out
+++ b/src/test/regress/expected/uniquekey.out
@@ -80,3 +80,330 @@ EXPLAIN (COSTS OFF) SELECT DISTINCT ON(pk) d FROM uqk1;
  Seq Scan on uqk1
 (1 row)
 
+------------------------------------------------------
+-- Test UniqueKey on one side still valid after join.
+-----------------------------------------------------
+-- uqk1(c, d) is the uniquekey with mutli nulls at single relation access.
+-- so distinct is not no-op.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.a = uqk2.pk;
+                 QUERY PLAN                  
+---------------------------------------------
+ Unique
+   ->  Sort
+         Sort Key: uqk1.c, uqk1.d
+         ->  Hash Join
+               Hash Cond: (uqk1.a = uqk2.pk)
+               ->  Seq Scan on uqk1
+               ->  Hash
+                     ->  Seq Scan on uqk2
+(8 rows)
+
+-- Both uqk1 (c,d) are a valid uniquekey. 
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.c is NOT NULL AND uqk1.a = uqk2.pk;
+           QUERY PLAN            
+---------------------------------
+ Hash Join
+   Hash Cond: (uqk1.a = uqk2.pk)
+   ->  Seq Scan on uqk1
+         Filter: (c IS NOT NULL)
+   ->  Hash
+         ->  Seq Scan on uqk2
+(6 rows)
+
+-- uqk1.c is null at baserel, but the null values are removed after join.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE  uqk1.a = uqk2.pk and uqk1.c = uqk2.c;
+                       QUERY PLAN                        
+---------------------------------------------------------
+ Hash Join
+   Hash Cond: ((uqk1.a = uqk2.pk) AND (uqk1.c = uqk2.c))
+   ->  Seq Scan on uqk1
+   ->  Hash
+         ->  Seq Scan on uqk2
+(5 rows)
+
+-- uqk1.c is null at baserel, but the null values are removed after join
+-- but new null values are generated due to outer join again. so distinct
+-- is still needed.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1 right join uqk2
+on uqk1.a = uqk2.pk and uqk1.c = uqk2.c;
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Unique
+   ->  Sort
+         Sort Key: uqk1.c, uqk1.d
+         ->  Hash Right Join
+               Hash Cond: ((uqk1.a = uqk2.pk) AND (uqk1.c = uqk2.c))
+               ->  Seq Scan on uqk1
+               ->  Hash
+                     ->  Seq Scan on uqk2
+(8 rows)
+
+------------------------------------------------------
+-- Test join: Composited UniqueKey
+-----------------------------------------------------
+-- both t1.pk and t1.pk is valid uniquekey.
+EXPLAIN SELECT DISTINCT t1.pk, t2.pk FROM uqk1 t1 cross join uqk2 t2;
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Nested Loop  (cost=0.00..2.27 rows=15 width=8)
+   ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=5 width=4)
+   ->  Materialize  (cost=0.00..1.04 rows=3 width=4)
+         ->  Seq Scan on uqk2 t2  (cost=0.00..1.03 rows=3 width=4)
+(4 rows)
+
+SELECT DISTINCT t1.pk, t2.pk FROM uqk1 t1 cross join uqk2 t2 order by 1, 2;
+ pk | pk 
+----+----
+  1 |  1
+  1 |  4
+  1 |  5
+  2 |  1
+  2 |  4
+  2 |  5
+  3 |  1
+  3 |  4
+  3 |  5
+  4 |  1
+  4 |  4
+  4 |  5
+  5 |  1
+  5 |  4
+  5 |  5
+(15 rows)
+
+-- NOT OK, since t1.c includes multi nulls. 
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null;
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ HashAggregate  (cost=2.20..2.26 rows=6 width=12)
+   Group Key: t1.c, t1.d, t2.pk
+   ->  Nested Loop  (cost=0.00..2.16 rows=6 width=12)
+         ->  Seq Scan on uqk2 t2  (cost=0.00..1.03 rows=3 width=4)
+         ->  Materialize  (cost=0.00..1.06 rows=2 width=8)
+               ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=2 width=8)
+                     Filter: (c IS NULL)
+(7 rows)
+
+SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null order by 1, 2,3;
+ c | d | pk 
+---+---+----
+   | 4 |  1
+   | 4 |  4
+   | 4 |  5
+(3 rows)
+
+SELECT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null order by 1, 2,3;
+ c | d | pk 
+---+---+----
+   | 4 |  1
+   | 4 |  1
+   | 4 |  4
+   | 4 |  4
+   | 4 |  5
+   | 4 |  5
+(6 rows)
+
+-- let's remove the t1.c's multi null values
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null;
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Nested Loop  (cost=0.00..2.20 rows=9 width=12)
+   ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=3 width=8)
+         Filter: (c IS NOT NULL)
+   ->  Materialize  (cost=0.00..1.04 rows=3 width=4)
+         ->  Seq Scan on uqk2 t2  (cost=0.00..1.03 rows=3 width=4)
+(5 rows)
+
+SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null order by 1, 2, 3 ;
+ c | d | pk 
+---+---+----
+ 1 | 1 |  1
+ 1 | 1 |  4
+ 1 | 1 |  5
+ 2 | 2 |  1
+ 2 | 2 |  4
+ 2 | 2 |  5
+ 3 | 3 |  1
+ 3 | 3 |  4
+ 3 | 3 |  5
+(9 rows)
+
+SELECT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null order by 1, 2, 3;
+ c | d | pk 
+---+---+----
+ 1 | 1 |  1
+ 1 | 1 |  4
+ 1 | 1 |  5
+ 2 | 2 |  1
+ 2 | 2 |  4
+ 2 | 2 |  5
+ 3 | 3 |  1
+ 3 | 3 |  4
+ 3 | 3 |  5
+(9 rows)
+
+-- test onerow case with composited cases.
+-- t2.c is onerow. OK
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Nested Loop  (cost=0.00..2.12 rows=3 width=12)
+   ->  Seq Scan on uqk2 t2  (cost=0.00..1.04 rows=1 width=4)
+         Filter: (pk = 1)
+   ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=3 width=8)
+         Filter: (c IS NOT NULL)
+(5 rows)
+
+SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+ c | d | c 
+---+---+---
+ 1 | 1 | 1
+ 2 | 2 | 1
+ 3 | 3 | 1
+(3 rows)
+
+SELECT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+ c | d | c 
+---+---+---
+ 1 | 1 | 1
+ 2 | 2 | 1
+ 3 | 3 | 1
+(3 rows)
+
+-- t2.c is onerow, but t1.c has multi-nulls, NOt OK.
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ Unique  (cost=2.12..2.14 rows=2 width=12)
+   ->  Sort  (cost=2.12..2.12 rows=2 width=12)
+         Sort Key: t1.c, t1.d, t2.c
+         ->  Nested Loop  (cost=0.00..2.11 rows=2 width=12)
+               ->  Seq Scan on uqk2 t2  (cost=0.00..1.04 rows=1 width=4)
+                     Filter: (pk = 1)
+               ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=2 width=8)
+                     Filter: (c IS NULL)
+(8 rows)
+
+SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+ c | d | c 
+---+---+---
+   | 4 | 1
+(1 row)
+
+SELECT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+ c | d | c 
+---+---+---
+   | 4 | 1
+   | 4 | 1
+(2 rows)
+
+-- Test Semi/Anti JOIN
+EXPLAIN (COSTS OFF) SELECT DISTINCT pk FROM uqk1 WHERE d in (SELECT d FROM uqk2);
+           QUERY PLAN           
+--------------------------------
+ Hash Semi Join
+   Hash Cond: (uqk1.d = uqk2.d)
+   ->  Seq Scan on uqk1
+   ->  Hash
+         ->  Seq Scan on uqk2
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT pk FROM uqk1 WHERE d NOT in (SELECT d FROM uqk2);
+             QUERY PLAN             
+------------------------------------
+ Seq Scan on uqk1
+   Filter: (NOT (hashed SubPlan 1))
+   SubPlan 1
+     ->  Seq Scan on uqk2
+(4 rows)
+
+-----------------------------------
+-- Test Join: Special OneRow case.
+-----------------------------------
+-- Test Unique Key FOR one-row case, DISTINCT is NOT needed as well.
+-- uqk1.d is the a uniquekey due to onerow rule. uqk2.pk is pk.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.pk;
+            QUERY PLAN             
+-----------------------------------
+ Nested Loop
+   Join Filter: (uqk1.c = uqk2.pk)
+   ->  Seq Scan on uqk1
+         Filter: (pk = 1)
+   ->  Seq Scan on uqk2
+(5 rows)
+
+SELECT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.pk order BY 1;
+ d 
+---
+ 1
+(1 row)
+
+-- Both uqk1.d AND uqk2.c are the a uniquekey due to onerow rule
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1
+AND uqk2.pk = 1 AND uqk1.d = uqk2.d;
+            QUERY PLAN            
+----------------------------------
+ Nested Loop
+   Join Filter: (uqk1.d = uqk2.d)
+   ->  Seq Scan on uqk1
+         Filter: (pk = 1)
+   ->  Seq Scan on uqk2
+         Filter: (pk = 1)
+(6 rows)
+
+SELECT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk2.pk = 1
+AND uqk1.d = uqk2.d order BY 1;
+ d 
+---
+ 1
+(1 row)
+
+-- Both UniqueKey in targetList, so distinct is not needed.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk2.c FROM uqk1, uqk2 WHERE uqk1.pk = 2 AND uqk2.pk = 1;
+        QUERY PLAN        
+--------------------------
+ Nested Loop
+   ->  Seq Scan on uqk1
+         Filter: (pk = 2)
+   ->  Seq Scan on uqk2
+         Filter: (pk = 1)
+(5 rows)
+
+SELECT uqk1.c, uqk2.c FROM uqk1, uqk2 WHERE uqk1.pk = 2 AND uqk2.pk = 1 order BY 1, 2;
+ c | c 
+---+---
+ 2 | 1
+(1 row)
+
+-----------------------------------------
+-- Test more non-unique cases after join.
+-----------------------------------------
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.pk FROM uqk1, uqk2 WHERE uqk1.c = uqk2.c;
+                 QUERY PLAN                 
+--------------------------------------------
+ Unique
+   ->  Sort
+         Sort Key: uqk1.pk
+         ->  Hash Join
+               Hash Cond: (uqk1.c = uqk2.c)
+               ->  Seq Scan on uqk1
+               ->  Hash
+                     ->  Seq Scan on uqk2
+(8 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.c;
+                  QUERY PLAN                  
+----------------------------------------------
+ Unique
+   ->  Sort
+         Sort Key: uqk1.d
+         ->  Nested Loop
+               Join Filter: (uqk1.c = uqk2.c)
+               ->  Seq Scan on uqk1
+                     Filter: (pk = 1)
+               ->  Seq Scan on uqk2
+(8 rows)
+
diff --git a/src/test/regress/sql/uniquekey.sql b/src/test/regress/sql/uniquekey.sql
index a1b538d1c1..3f93872246 100644
--- a/src/test/regress/sql/uniquekey.sql
+++ b/src/test/regress/sql/uniquekey.sql
@@ -27,3 +27,82 @@ EXPLAIN (COSTS OFF) SELECT DISTINCT a FROM uqk1 WHERE c = 1 and d = 1;
 
 -- Test Distinct ON
 EXPLAIN (COSTS OFF) SELECT DISTINCT ON(pk) d FROM uqk1;
+
+------------------------------------------------------
+-- Test UniqueKey on one side still valid after join.
+-----------------------------------------------------
+-- uqk1(c, d) is the uniquekey with mutli nulls at single relation access.
+-- so distinct is not no-op.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.a = uqk2.pk;
+
+-- Both uqk1 (c,d) are a valid uniquekey. 
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.c is NOT NULL AND uqk1.a = uqk2.pk;
+
+-- uqk1.c is null at baserel, but the null values are removed after join.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE  uqk1.a = uqk2.pk and uqk1.c = uqk2.c;
+
+-- uqk1.c is null at baserel, but the null values are removed after join
+-- but new null values are generated due to outer join again. so distinct
+-- is still needed.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1 right join uqk2
+on uqk1.a = uqk2.pk and uqk1.c = uqk2.c;
+
+
+------------------------------------------------------
+-- Test join: Composited UniqueKey
+-----------------------------------------------------
+-- both t1.pk and t1.pk is valid uniquekey.
+EXPLAIN SELECT DISTINCT t1.pk, t2.pk FROM uqk1 t1 cross join uqk2 t2;
+SELECT DISTINCT t1.pk, t2.pk FROM uqk1 t1 cross join uqk2 t2 order by 1, 2;
+
+-- NOT OK, since t1.c includes multi nulls. 
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null;
+SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null order by 1, 2,3;
+SELECT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null order by 1, 2,3;
+
+-- let's remove the t1.c's multi null values
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null;
+SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null order by 1, 2, 3 ;
+SELECT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null order by 1, 2, 3;
+
+-- test onerow case with composited cases.
+
+-- t2.c is onerow. OK
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+SELECT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+
+-- t2.c is onerow, but t1.c has multi-nulls, NOt OK.
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+SELECT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+
+
+-- Test Semi/Anti JOIN
+EXPLAIN (COSTS OFF) SELECT DISTINCT pk FROM uqk1 WHERE d in (SELECT d FROM uqk2);
+EXPLAIN (COSTS OFF) SELECT DISTINCT pk FROM uqk1 WHERE d NOT in (SELECT d FROM uqk2);
+
+-----------------------------------
+-- Test Join: Special OneRow case.
+-----------------------------------
+-- Test Unique Key FOR one-row case, DISTINCT is NOT needed as well.
+-- uqk1.d is the a uniquekey due to onerow rule. uqk2.pk is pk.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.pk;
+SELECT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.pk order BY 1;
+-- Both uqk1.d AND uqk2.c are the a uniquekey due to onerow rule
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1
+AND uqk2.pk = 1 AND uqk1.d = uqk2.d;
+SELECT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk2.pk = 1
+AND uqk1.d = uqk2.d order BY 1;
+-- Both UniqueKey in targetList, so distinct is not needed.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk2.c FROM uqk1, uqk2 WHERE uqk1.pk = 2 AND uqk2.pk = 1;
+SELECT uqk1.c, uqk2.c FROM uqk1, uqk2 WHERE uqk1.pk = 2 AND uqk2.pk = 1 order BY 1, 2;
+
+-----------------------------------------
+-- Test more non-unique cases after join.
+-----------------------------------------
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.pk FROM uqk1, uqk2 WHERE uqk1.c = uqk2.c;
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.c;
-- 
2.21.0

From 867dbc9e866ce149b07ff55631598e0843046b61 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sat, 3 Jul 2021 21:06:32 +0800
Subject: [PATCH v3 3/6] add the not null attrs for RelOptInfo. Here is how it
 works.

For baserel, it records the notnull attrs as a bitmapset and store it to
RelOptInfo->notnull_attrs[0].  As for the joinrel, suppose the relids is {1,3,
5}, then the notnull_attrs[1/3/5] will be used to store notnull_attrs bitmapset
for relation 1,3,5 separately. I don't handle this stuff for all kinds of upper
relation and subquery so far since it doesn't pass the design review yet.
---
 notnulltest.sql                       | 24 ++++++++
 src/backend/nodes/bitmapset.c         | 14 +++++
 src/backend/optimizer/path/allpaths.c | 36 ++++++++++++
 src/backend/optimizer/util/plancat.c  |  9 +++
 src/backend/optimizer/util/relnode.c  | 80 +++++++++++++++++++++++++++
 src/include/nodes/bitmapset.h         |  1 +
 src/include/nodes/pathnodes.h         |  6 ++
 7 files changed, 170 insertions(+)
 create mode 100644 notnulltest.sql

diff --git a/notnulltest.sql b/notnulltest.sql
new file mode 100644
index 0000000000..2a36bd0c7f
--- /dev/null
+++ b/notnulltest.sql
@@ -0,0 +1,24 @@
+create table t1(a int, b int not null, c int, d int);
+create table t2(a int, b int not null, c int, d int);
+
+-- single rel
+select * from t1;
+select * from t1 where a > 1;
+select * from t2 where a > 1 or c > 1;
+
+-- partitioned relation.
+create table p (a int, b int, c int not null) partition by range(a);
+create table p_1 partition of p for values from (0) to (10000) partition by list(b);
+create table p_1_1(b int,  c int not null, a int);
+alter table p_1 attach partition p_1_1 for values in (1);
+
+
+select * from p;
+select * from p where a > 1;
+
+
+-- test join:
+select * from t1, t2 where t1.a = t2.c;
+select t1.a, t2.b, t2.c from t1 left join t2 on t1.a = t2.c;
+select * from t1 full join t2 on t1.a = t2.c;
+
diff --git a/src/backend/nodes/bitmapset.c b/src/backend/nodes/bitmapset.c
index 649478b0d4..fa71f36aaf 100644
--- a/src/backend/nodes/bitmapset.c
+++ b/src/backend/nodes/bitmapset.c
@@ -663,6 +663,20 @@ bms_num_members(const Bitmapset *a)
 	return result;
 }
 
+/*
+ * bms_max_member - the max member in this bitmap.
+ */
+int
+bms_max_member(const Bitmapset *a)
+{
+	int result;
+	if (a == NULL || bms_is_empty(a))
+		elog(ERROR, "Must be an non-empty bitmapset.");
+	result = (a->nwords - 1) * BITS_PER_BITMAPWORD;
+	result += bmw_leftmost_one_pos(a->words[a->nwords - 1]);
+	return result;
+}
+
 /*
  * bms_membership - does a set have zero, one, or multiple members?
  *
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 296dd75c1b..a63a3dfe00 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -354,6 +354,40 @@ set_base_rel_pathlists(PlannerInfo *root)
 	}
 }
 
+/*
+ * set_baserel_notnull_attrs
+ *
+ *	Set baserel's notnullattrs based on baserestrictinfo
+ */
+static void
+set_baserel_notnull_attrs(RelOptInfo *rel)
+{
+	List *clauses = extract_actual_clauses(rel->baserestrictinfo, false);
+	ListCell	*lc;
+	foreach(lc, find_nonnullable_vars((Node *)clauses))
+	{
+		Var *var = (Var *) lfirst(lc);
+		if (var->varno != rel->relid)
+		{
+			/* Lateral Join */
+			continue;
+		}
+		Assert(var->varno == rel->relid);
+		rel->notnull_attrs[0] = bms_add_member(rel->notnull_attrs[0],
+											   var->varattno - FirstLowInvalidHeapAttributeNumber);
+	}
+
+	/* Debug Only, Will be removed at last. */
+	if (false)
+	{
+		elog(INFO, "FirstLowInvalidHeapAttributeNumber = %d, BaseRel(%d), notnull_attrs = %s",
+			 FirstLowInvalidHeapAttributeNumber,
+			 rel->relid,
+			 bmsToString(rel->notnull_attrs[0])
+			);
+	}
+}
+
 /*
  * set_rel_size
  *	  Set size estimates for a base relation
@@ -457,6 +491,8 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
 		}
 	}
 
+	set_baserel_notnull_attrs(rel);
+
 	/*
 	 * We insist that all non-dummy rels have a nonzero rowcount estimate.
 	 */
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index c5194fdbbf..7d3b40090e 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -117,6 +117,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	Relation	relation;
 	bool		hasindex;
 	List	   *indexinfos = NIL;
+	int		i;
 
 	/*
 	 * We need not lock the relation since it was already locked, either by
@@ -471,6 +472,14 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	if (inhparent && relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		set_relation_partition_info(root, rel, relation);
 
+	for (i = 0; i < relation->rd_att->natts; i++)
+	{
+		FormData_pg_attribute attr = relation->rd_att->attrs[i];
+		if (attr.attnotnull)
+			rel->notnull_attrs[0] = bms_add_member(rel->notnull_attrs[0],
+												   attr.attnum - FirstLowInvalidHeapAttributeNumber);
+	}
+
 	table_close(relation, NoLock);
 
 	/*
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 47769cea45..f017a7d52d 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -16,6 +16,7 @@
 
 #include <limits.h>
 
+#include "access/sysattr.h"
 #include "miscadmin.h"
 #include "nodes/nodeFuncs.h"
 #include "optimizer/appendinfo.h"
@@ -259,6 +260,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
 	rel->all_partrels = NULL;
 	rel->partexprs = NULL;
 	rel->nullable_partexprs = NULL;
+	rel->notnull_attrs = palloc0(sizeof(Bitmapset *) * 1);
 
 	/*
 	 * Pass assorted information down the inheritance hierarchy.
@@ -557,6 +559,81 @@ add_join_rel(PlannerInfo *root, RelOptInfo *joinrel)
 	}
 }
 
+static void
+copy_notnull_attrs_to_joinrel(RelOptInfo *joinrel, RelOptInfo *rel)
+{
+	int relid;
+	if (bms_get_singleton_member(rel->relids, &relid))
+		joinrel->notnull_attrs[relid] = bms_copy(rel->notnull_attrs[0]);
+	else
+	{
+		relid = -1;
+		while ((relid = bms_next_member(rel->relids, relid)) >= 0)
+			joinrel->notnull_attrs[relid] = bms_copy(rel->notnull_attrs[relid]);
+	}
+}
+
+/*
+ *
+ */
+static void
+set_joinrel_notnull_attrs(RelOptInfo *joinrel,
+						  RelOptInfo *outer_rel,
+						  RelOptInfo *inner_rel,
+						  List *restrictlist,
+						  SpecialJoinInfo *sjinfo)
+{
+	if (sjinfo->jointype == JOIN_FULL)
+		/* Both sides are nullable. */
+		return;
+	/* If it is not FULL join, the outer side is not changed. */
+	copy_notnull_attrs_to_joinrel(joinrel, outer_rel);
+	switch(sjinfo->jointype)
+	{
+		case JOIN_ANTI:
+		case JOIN_SEMI:
+		case JOIN_INNER:
+			copy_notnull_attrs_to_joinrel(joinrel, inner_rel);
+			{
+				ListCell	*lc;
+				List *clauses = extract_actual_clauses(restrictlist, false);
+				foreach(lc, find_nonnullable_vars((Node *) clauses))
+				{
+					Var *var = lfirst_node(Var, lc);
+					if (!bms_is_member(var->varno, joinrel->relids))
+					{
+						/* lateral join */
+						continue;
+					}
+					joinrel->notnull_attrs[var->varno] = bms_add_member(
+						joinrel->notnull_attrs[var->varno],
+						var->varattno - FirstLowInvalidHeapAttributeNumber);
+				}
+			}
+			break;
+		case JOIN_LEFT:
+			break;
+		default:
+			elog(ERROR, "Unexpected join type %d", sjinfo->jointype);
+	}
+	/* Debug Only, will be removed at last. */
+	if (false)
+	{
+		int relid = -1;
+		int eLevel = INFO;
+		elog(eLevel, "Dump notnull for JoinRel(%s)", bmsToString(joinrel->relids));
+		while((relid = bms_next_member(joinrel->relids, relid)) >= 0)
+		{
+			Bitmapset *notnullattrs = joinrel->notnull_attrs[relid];
+			if (notnullattrs != NULL)
+				elog(eLevel, "FirstLowInvalidHeapAttributeNumber = %d, RELID = (%d), notnull_attrs: %s",
+					 FirstLowInvalidHeapAttributeNumber,
+					 relid,
+					 bmsToString(notnullattrs));
+		}
+	}
+
+}
 /*
  * build_join_rel
  *	  Returns relation entry corresponding to the union of two given rels,
@@ -674,6 +751,7 @@ build_join_rel(PlannerInfo *root,
 	joinrel->all_partrels = NULL;
 	joinrel->partexprs = NULL;
 	joinrel->nullable_partexprs = NULL;
+	joinrel->notnull_attrs = palloc0(sizeof(Bitmapset *) * (bms_max_member(joinrel->relids) + 1));
 
 	/* Compute information relevant to the foreign relations. */
 	set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
@@ -765,6 +843,8 @@ build_join_rel(PlannerInfo *root,
 			lappend(root->join_rel_level[root->join_cur_level], joinrel);
 	}
 
+	set_joinrel_notnull_attrs(joinrel, outer_rel, inner_rel, restrictlist, sjinfo);
+
 	return joinrel;
 }
 
diff --git a/src/include/nodes/bitmapset.h b/src/include/nodes/bitmapset.h
index 1fd12de698..303873a546 100644
--- a/src/include/nodes/bitmapset.h
+++ b/src/include/nodes/bitmapset.h
@@ -94,6 +94,7 @@ extern bool bms_nonempty_difference(const Bitmapset *a, const Bitmapset *b);
 extern int	bms_singleton_member(const Bitmapset *a);
 extern bool bms_get_singleton_member(const Bitmapset *a, int *member);
 extern int	bms_num_members(const Bitmapset *a);
+extern int	bms_max_member(const Bitmapset *a);
 
 /* optimized tests when we don't need to know exact membership count: */
 extern BMS_Membership bms_membership(const Bitmapset *a);
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 6e068f2c8b..7bf1896e12 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -686,6 +686,12 @@ typedef struct RelOptInfo
 	/* default result targetlist for Paths scanning this relation */
 	struct PathTarget *reltarget;	/* list of Vars/Exprs, cost, width */
 
+	Bitmapset	**notnull_attrs; /* The attno which is not null after evalating
+								  * all the quals on this relation, for baserel,
+								  * the len would always 1. and for others the array
+								  * index is relid from relids.
+								  */
+
 	/* materialization information */
 	List	   *pathlist;		/* Path structures */
 	List	   *ppilist;		/* ParamPathInfos used in pathlist */
-- 
2.21.0

From ac411f30a1a30f58037234fffc7e88e6f2eb636c Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Mon, 9 Aug 2021 19:10:35 +0800
Subject: [PATCH v3 4/6] Support UniqueKey on BaseRel.

---
 src/backend/optimizer/path/Makefile     |   3 +-
 src/backend/optimizer/path/allpaths.c   |   7 +-
 src/backend/optimizer/path/uniquekey.c  | 400 ++++++++++++++++++++++++
 src/backend/optimizer/plan/planner.c    |  10 +
 src/backend/optimizer/util/relnode.c    |   2 +
 src/include/nodes/nodes.h               |   3 +-
 src/include/nodes/pathnodes.h           |  20 ++
 src/include/optimizer/paths.h           |   4 +
 src/test/regress/expected/join.out      |  11 +-
 src/test/regress/expected/uniquekey.out |  82 +++++
 src/test/regress/parallel_schedule      |   2 +-
 src/test/regress/sql/uniquekey.sql      |  29 ++
 12 files changed, 562 insertions(+), 11 deletions(-)
 create mode 100644 src/backend/optimizer/path/uniquekey.c
 create mode 100644 src/test/regress/expected/uniquekey.out
 create mode 100644 src/test/regress/sql/uniquekey.sql

diff --git a/src/backend/optimizer/path/Makefile b/src/backend/optimizer/path/Makefile
index 1e199ff66f..63cc1505d9 100644
--- a/src/backend/optimizer/path/Makefile
+++ b/src/backend/optimizer/path/Makefile
@@ -21,6 +21,7 @@ OBJS = \
 	joinpath.o \
 	joinrels.o \
 	pathkeys.o \
-	tidpath.o
+	tidpath.o \
+	uniquekey.o
 
 include $(top_srcdir)/src/backend/common.mk
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index a63a3dfe00..343253d694 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -396,6 +396,9 @@ static void
 set_rel_size(PlannerInfo *root, RelOptInfo *rel,
 			 Index rti, RangeTblEntry *rte)
 {
+	/* Set the notnull before the UniqueKey population. */
+	set_baserel_notnull_attrs(rel);
+
 	if (rel->reloptkind == RELOPT_BASEREL &&
 		relation_excluded_by_constraints(root, rel, rte))
 	{
@@ -491,7 +494,7 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
 		}
 	}
 
-	set_baserel_notnull_attrs(rel);
+
 
 	/*
 	 * We insist that all non-dummy rels have a nonzero rowcount estimate.
@@ -616,6 +619,8 @@ set_plain_rel_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
 	 */
 	check_index_predicates(root, rel);
 
+	populate_baserel_uniquekeys(root, rel);
+
 	/* Mark rel with estimated output rows, width, etc */
 	set_baserel_size_estimates(root, rel);
 }
diff --git a/src/backend/optimizer/path/uniquekey.c b/src/backend/optimizer/path/uniquekey.c
new file mode 100644
index 0000000000..c93075656d
--- /dev/null
+++ b/src/backend/optimizer/path/uniquekey.c
@@ -0,0 +1,400 @@
+/*-------------------------------------------------------------------------
+ *
+ * uniquekey.c
+ *	  Utilities for maintaining uniquekey.
+ *
+ *
+ * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ *	  src/backend/optimizer/path/uniquekey.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/sysattr.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/pathnodes.h"
+#include "optimizer/optimizer.h"
+#include "optimizer/paths.h"
+
+
+/* Functions to populate UniqueKey */
+static bool add_uniquekey_for_uniqueindex(PlannerInfo *root,
+										  IndexOptInfo *unique_index,
+										  List *mergeable_const_peer,
+										  List *expr_opfamilies);
+
+/* UniqueKey is subset of .. */
+static bool uniquekey_contains_in(PlannerInfo *root, UniqueKey *ukey,
+								  List *ecs, Relids relids);
+
+/* Avoid useless UniqueKey. */
+static bool unique_ecs_useful_for_distinct(PlannerInfo *root, List *ecs);
+static bool unique_ecs_useful_for_merging(PlannerInfo *root, RelOptInfo *rel,
+										  List *unique_ecs);
+/* Helper functions to create UniqueKey. */
+static UniqueKey *make_uniquekey(Bitmapset *unique_expr_indexes,
+								 bool multi_null,
+								 bool useful_for_distinct);
+static void mark_rel_singlerow(PlannerInfo *root, RelOptInfo *rel);
+
+/* Debug only */
+static void print_uniquekey(PlannerInfo *root, RelOptInfo *rel);
+
+/*
+ * populate_baserel_uniquekeys
+ */
+void
+populate_baserel_uniquekeys(PlannerInfo *root, RelOptInfo *rel)
+{
+	ListCell	*lc;
+	List	*mergeable_const_peer = NIL, *expr_opfamilies = NIL;
+
+	/*
+	 * ColX = {Const} AND ColY = {Const2} AND ColZ > {Const3},
+	 * gather ColX and ColY into mergeable_const_peer.
+	 */
+	foreach(lc, rel->baserestrictinfo)
+	{
+		RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
+
+		if (rinfo->mergeopfamilies == NIL)
+			continue;
+
+		if (bms_is_empty(rinfo->left_relids))
+			mergeable_const_peer = lappend(mergeable_const_peer, get_rightop(rinfo->clause));
+		else if (bms_is_empty(rinfo->right_relids))
+			mergeable_const_peer = lappend(mergeable_const_peer, get_leftop(rinfo->clause));
+		else
+			continue;
+		expr_opfamilies = lappend(expr_opfamilies, rinfo->mergeopfamilies);
+	}
+
+	foreach(lc, rel->indexlist)
+	{
+		IndexOptInfo *index = (IndexOptInfo *)lfirst(lc);
+		if (!index->unique || !index->immediate ||
+			(index->indpred != NIL && !index->predOK))
+			continue;
+
+		if (add_uniquekey_for_uniqueindex(root, index,
+										  mergeable_const_peer,
+										  expr_opfamilies))
+			/* Find a singlerow case, no need to go through any more. */
+			return;
+	}
+
+	print_uniquekey(root, rel);
+}
+
+/*
+ * relation_is_distinct_for
+ *		Check if the relation is distinct for.
+ */
+bool
+relation_is_distinct_for(PlannerInfo *root, RelOptInfo *rel, List *distinct_pathkey)
+{
+	ListCell	*lc;
+	List	*lecs = NIL;
+	Relids	relids = NULL;
+	foreach(lc, distinct_pathkey)
+	{
+		PathKey *pathkey = lfirst(lc);
+		lecs = lappend(lecs, pathkey->pk_eclass);
+		/*
+		 * Note that ec_relids doesn't include child member, but
+		 * distinct would not operate on childrel as well.
+		 */
+		relids = bms_union(relids, pathkey->pk_eclass->ec_relids);
+	}
+
+	foreach(lc, rel->uniquekeys)
+	{
+		UniqueKey *ukey = lfirst(lc);
+		if (ukey->multi_nulls)
+			continue;
+
+		if (uniquekey_contains_in(root, ukey, lecs, relids))
+			return true;
+	}
+	return false;
+}
+
+/*
+ * add_uniquekey_for_uniqueindex
+ *	 populate a UniqueKey if necessary, return true iff the UniqueKey is an
+ * SingleRow.
+ */
+static bool
+add_uniquekey_for_uniqueindex(PlannerInfo *root, IndexOptInfo *unique_index,
+							  List *mergeable_const_peer, List *expr_opfamilies)
+{
+	List	*unique_exprs = NIL, *unique_ecs = NIL;
+	ListCell	*indexpr_item;
+	int	c = 0;
+	RelOptInfo *rel = unique_index->rel;
+	bool	multinull = false;
+	bool	used_for_distinct = false;
+	Bitmapset *unique_exprs_index;
+
+	indexpr_item = list_head(unique_index->indexprs);
+	/* Gather all the non-const exprs */
+	for (c = 0; c < unique_index->nkeycolumns; c++)
+	{
+		int attr = unique_index->indexkeys[c];
+		Expr *expr;
+		bool	matched_const = false;
+		ListCell	*lc1, *lc2;
+		if (attr > 0)
+		{
+			Var *var;
+			expr = list_nth_node(TargetEntry, unique_index->indextlist, c)->expr;
+			var = castNode(Var, expr);
+			Assert(IsA(expr, Var));
+			if (!bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber,
+							  rel->notnull_attrs[0]))
+				multinull = true;
+		}
+		else if (attr == 0)
+		{
+			/* Expression index */
+			expr = lfirst(indexpr_item);
+			indexpr_item = lnext(unique_index->indexprs, indexpr_item);
+			/* We can't grantee an FuncExpr will not return NULLs */
+			multinull = true;
+		}
+		else /* attr < 0 */
+		{
+			/* Index on OID is possible, not handle it for now. */
+			return false;
+		}
+
+		/*
+		 * Check index_col = Const case with regarding to opfamily checking
+		 * If so, we can remove the index_col from the final UniqueKey->exprs.
+		 */
+		forboth(lc1, mergeable_const_peer, lc2, expr_opfamilies)
+		{
+			if (list_member_oid((List *) lfirst(lc2), unique_index->opfamily[c]) &&
+				match_index_to_operand((Node *) lfirst(lc1), c, unique_index))
+			{
+				matched_const = true;
+				break;
+			}
+		}
+
+		if (matched_const)
+			continue;
+
+		unique_exprs = lappend(unique_exprs, expr);
+	}
+
+	if (unique_exprs == NIL)
+	{
+		/*
+		 * SingleRow case. Checking if it is useful is ineffective
+		 * so just keep it.
+		 */
+		mark_rel_singlerow(root, rel);
+		return true;
+	}
+
+	unique_ecs = build_equivalanceclass_list_for_exprs(root, unique_exprs, rel);
+
+	if (unique_ecs == NIL)
+	{
+		/* It is neither used in distinct_pathkey nor mergeable clause */
+		return false;
+	}
+
+	/*
+	 * Check if we need to setup the UniqueKey and set the used_for_distinct accordingly.
+	 */
+	if (unique_ecs_useful_for_distinct(root, unique_ecs))
+	{
+		used_for_distinct = true;
+	}
+	else if (!unique_ecs_useful_for_merging(root, rel, unique_ecs))
+		/*
+		 * Neither used in distinct pathkey nor used in mergeable clause.
+		 * this is possible even if unique_ecs != NIL.
+		 */
+		return false;
+	else
+	{
+		/*
+		 * unique_ecs_useful_for_merging(root, rel, unique_ecs) is true,
+		 * we did nothing in this case.
+		 */
+	}
+	unique_exprs_index = bms_make_singleton(list_length(root->unique_exprs));
+	root->unique_exprs = lappend(root->unique_exprs, unique_ecs);
+	rel->uniquekeys = lappend(rel->uniquekeys,
+							  make_uniquekey(unique_exprs_index,
+											 multinull,
+											 used_for_distinct));
+	return false;
+}
+/*
+ * uniquekey_contains_in
+ *	Return if UniqueKey contains in the list of EquivalenceClass
+ * or the UniqueKey's SingleRow contains in relids.
+ *
+ */
+static bool
+uniquekey_contains_in(PlannerInfo *root, UniqueKey *ukey, List *ecs, Relids relids)
+{
+	int i = -1;
+	while ((i = bms_next_member(ukey->unique_expr_indexes, i)) >= 0)
+	{
+		Node *exprs = list_nth(root->unique_exprs, i);
+		if (IsA(exprs, SingleRow))
+		{
+			SingleRow *singlerow = castNode(SingleRow, exprs);
+			if (!bms_is_member(singlerow->relid, relids))
+				/*
+				 * UniqueKey request a ANY expr on relid on the relid(which
+				 * indicates we don't have other EquivalenceClass for this
+				 * relation), but the relid doesn't contains in relids, which
+				 * indicate there is no such Expr in target, then we are sure
+				 * to return false.
+				 */
+				return false;
+			else
+			{
+				/*
+				 * We have SingleRow on relid, and the relid is in relids.
+				 * We don't need to check any more for this expr. This is
+				 * right for sure.
+				 */
+			}
+		}
+		else
+		{
+			Assert(IsA(exprs, List));
+			if (!list_is_subset_ptr((List *)exprs, ecs))
+				return false;
+		}
+	}
+	return true;
+}
+
+/*
+ * unique_ecs_useful_for_distinct
+ *	return true if all the EquivalenceClass in ecs exists in root->distinct_pathkey.
+ */
+static bool
+unique_ecs_useful_for_distinct(PlannerInfo *root, List *ecs)
+{
+	ListCell *lc;
+	foreach(lc, ecs)
+	{
+		EquivalenceClass *ec = lfirst_node(EquivalenceClass, lc);
+		ListCell *p;
+		bool found = false;
+		foreach(p,  root->distinct_pathkeys)
+		{
+			PathKey *pathkey = lfirst_node(PathKey, p);
+			/*
+			 * Both of them should point to an element in root->eq_classes.
+			 * so the address should be same. and equal function doesn't
+			 * support EquivalenceClass yet.
+			 */
+			if (ec == pathkey->pk_eclass)
+			{
+				found = true;
+				break;
+			}
+		}
+		if (!found)
+			return false;
+	}
+	return true;
+}
+
+/*
+ * unique_ecs_useful_for_merging
+ *	return true if all the unique_ecs exists in rel's join restrictInfo.
+ */
+static bool
+unique_ecs_useful_for_merging(PlannerInfo *root, RelOptInfo *rel, List *unique_ecs)
+{
+	ListCell	*lc;
+
+	foreach(lc, unique_ecs)
+	{
+		EquivalenceClass *ec = lfirst(lc);
+		if (!ec_useful_for_merging(root, rel, ec))
+			return false;
+	}
+
+	return true;
+}
+/*
+ *	make_uniquekey
+ */
+static UniqueKey *
+make_uniquekey(Bitmapset *unique_expr_indexes, bool multi_null, bool useful_for_distinct)
+{
+	UniqueKey *ukey = makeNode(UniqueKey);
+	ukey->unique_expr_indexes = unique_expr_indexes;
+	ukey->multi_nulls = multi_null;
+	ukey->use_for_distinct = useful_for_distinct;
+	return ukey;
+}
+
+/*
+ * mark_rel_singlerow
+ *	mark a relation as singlerow.
+ */
+static void
+mark_rel_singlerow(PlannerInfo *root, RelOptInfo *rel)
+{
+	int exprs_pos = list_length(root->unique_exprs);
+	Bitmapset *unique_exprs_index = bms_make_singleton(exprs_pos);
+	SingleRow *singlerow = makeNode(SingleRow);
+	singlerow->relid = rel->relid;
+	root->unique_exprs = lappend(root->unique_exprs, singlerow);
+	rel->uniquekeys = list_make1(make_uniquekey(unique_exprs_index,
+												false /* multi-null */,
+												true /* arbitrary decision */));
+}
+
+/*
+ * print_uniquekey
+ *	Used for easier reivew, should be removed before commit.
+ */
+static void
+print_uniquekey(PlannerInfo *root, RelOptInfo *rel)
+{
+	if (false)
+	{
+		ListCell	*lc;
+		elog(INFO, "Rel = %s", bmsToString(rel->relids));
+		foreach(lc, rel->uniquekeys)
+		{
+			UniqueKey *ukey = lfirst_node(UniqueKey, lc);
+			int i = -1;
+			elog(INFO, "UNIQUEKEY{indexes=%s, multinull=%d}",
+				 bmsToString(ukey->unique_expr_indexes),
+				 ukey->multi_nulls
+				);
+
+			while ((i = bms_next_member(ukey->unique_expr_indexes, i)) >= 0)
+			{
+				Node *node = (Node *) list_nth(root->unique_exprs, i);
+				if (IsA(node, SingleRow))
+					elog(INFO,
+						 "Expr(%d) SingleRow{relid = %d}",
+						 i, castNode(SingleRow, node)->relid);
+				else
+					elog(INFO,
+						 "EC(%d), %s", i, nodeToString(node)
+						);
+			}
+		}
+	}
+}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 2cd691191c..ed2cff00fc 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -4238,6 +4238,16 @@ create_distinct_paths(PlannerInfo *root,
 	Path	   *path;
 	ListCell   *lc;
 
+	/*
+	 * distinct_pathkeys may be NIL if it distinctClause is sortable.
+	 * see standard_qp_callback. But for the efficiency of relation_is_distinct_for
+	 * we can't use distinctClause (rather than EC) here. Fortunately not sortable
+	 * clause is rare in real case.
+	 */
+	if (root->distinct_pathkeys &&
+		relation_is_distinct_for(root, input_rel, root->distinct_pathkeys))
+		return input_rel;
+
 	/* For now, do all work in the (DISTINCT, NULL) upperrel */
 	distinct_rel = fetch_upper_rel(root, UPPERREL_DISTINCT, NULL);
 
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index f017a7d52d..b75e1679e6 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -261,6 +261,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
 	rel->partexprs = NULL;
 	rel->nullable_partexprs = NULL;
 	rel->notnull_attrs = palloc0(sizeof(Bitmapset *) * 1);
+	rel->uniquekeys = NIL;
 
 	/*
 	 * Pass assorted information down the inheritance hierarchy.
@@ -752,6 +753,7 @@ build_join_rel(PlannerInfo *root,
 	joinrel->partexprs = NULL;
 	joinrel->nullable_partexprs = NULL;
 	joinrel->notnull_attrs = palloc0(sizeof(Bitmapset *) * (bms_max_member(joinrel->relids) + 1));
+	joinrel->uniquekeys = NIL;
 
 	/* Compute information relevant to the foreign relations. */
 	set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 6a4d82f0a8..1bf220f373 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -280,7 +280,8 @@ typedef enum NodeTag
 	T_RollupData,
 	T_GroupingSetData,
 	T_StatisticExtInfo,
-
+	T_UniqueKey,
+	T_SingleRow,
 	/*
 	 * TAGS FOR MEMORY NODES (memnodes.h)
 	 */
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 7bf1896e12..3581b03a75 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -246,6 +246,7 @@ struct PlannerInfo
 									 * subquery outputs */
 
 	List	   *eq_classes;		/* list of active EquivalenceClasses */
+	List	   *unique_exprs;		/* List of unique expr */
 
 	bool		ec_merging_done;	/* set true once ECs are canonical */
 
@@ -691,6 +692,7 @@ typedef struct RelOptInfo
 								  * the len would always 1. and for others the array
 								  * index is relid from relids.
 								  */
+	List		*uniquekeys; /* A list of UniqueKey. */
 
 	/* materialization information */
 	List	   *pathlist;		/* Path structures */
@@ -1070,6 +1072,24 @@ typedef struct PathKey
 	bool		pk_nulls_first; /* do NULLs come before normal values? */
 } PathKey;
 
+
+typedef struct UnqiueKey
+{
+	NodeTag	type;
+	Bitmapset	*unique_expr_indexes;
+	bool	multi_nulls;
+	bool	use_for_distinct;  /* true if it is used in distinct-pathkey, in this case
+								* we would never check if we should discard it during
+								* join search.
+								*/
+} UniqueKey;
+
+typedef struct SingleRow
+{
+	NodeTag	type;
+	Index		relid;
+} SingleRow;
+
 /*
  * VolatileFunctionStatus -- allows nodes to cache their
  * contain_volatile_functions properties. VOLATILITY_UNKNOWN means not yet
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index e813d82483..68b8b40ca9 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -263,4 +263,8 @@ extern PathKey *make_canonical_pathkey(PlannerInfo *root,
 extern void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 									List *live_childrels);
 
+extern void populate_baserel_uniquekeys(PlannerInfo *root,
+										RelOptInfo *baserel);
+extern bool relation_is_distinct_for(PlannerInfo *root, RelOptInfo *rel,
+									 List *distinct_pathkey);
 #endif							/* PATHS_H */
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index f3589d0dbb..db72374302 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4612,18 +4612,15 @@ select d.* from d left join (select * from b group by b.id, b.c_id) s
 explain (costs off)
 select d.* from d left join (select distinct * from b) s
   on d.a = s.id;
-              QUERY PLAN              
---------------------------------------
+             QUERY PLAN             
+------------------------------------
  Merge Right Join
    Merge Cond: (b.id = d.a)
-   ->  Unique
-         ->  Sort
-               Sort Key: b.id, b.c_id
-               ->  Seq Scan on b
+   ->  Index Scan using b_pkey on b
    ->  Sort
          Sort Key: d.a
          ->  Seq Scan on d
-(9 rows)
+(6 rows)
 
 -- check join removal works when uniqueness of the join condition is enforced
 -- by a UNION
diff --git a/src/test/regress/expected/uniquekey.out b/src/test/regress/expected/uniquekey.out
new file mode 100644
index 0000000000..d9a8634e80
--- /dev/null
+++ b/src/test/regress/expected/uniquekey.out
@@ -0,0 +1,82 @@
+CREATE TABLE uqk1(a int, pk int primary key, c int,  d int);
+CREATE TABLE uqk2(a int, pk int primary key, c int,  d int);
+INSERT INTO uqk1 VALUES(1, 1, 1, 1), (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, null, 4), (5, 5, null, 4);
+INSERT INTO uqk2 VALUES(1, 1, 1, 1), (4, 4, 4, 4), (5, 5, 5, 5);
+ANALYZE uqk1;
+ANALYZE uqk2;
+-- Test single table primary key.
+EXPLAIN (COSTS OFF) SELECT DISTINCT * FROM uqk1;
+    QUERY PLAN    
+------------------
+ Seq Scan on uqk1
+(1 row)
+
+-- Test EC case.
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE d = pk;
+     QUERY PLAN     
+--------------------
+ Seq Scan on uqk1
+   Filter: (d = pk)
+(2 rows)
+
+-- Test UniqueKey indexes.
+CREATE UNIQUE INDEX uqk1_ukcd ON uqk1(c, d);
+-- Test not null quals and not null per catalog.
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1;
+       QUERY PLAN       
+------------------------
+ HashAggregate
+   Group Key: c, d
+   ->  Seq Scan on uqk1
+(3 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1 WHERE c is NOT NULL;
+              QUERY PLAN               
+---------------------------------------
+ Unique
+   ->  Sort
+         Sort Key: c, d
+         ->  Seq Scan on uqk1
+               Filter: (c IS NOT NULL)
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE c = 1;
+          QUERY PLAN           
+-------------------------------
+ Unique
+   ->  Sort
+         Sort Key: d
+         ->  Seq Scan on uqk1
+               Filter: (c = 1)
+(5 rows)
+
+ALTER TABLE uqk1 ALTER COLUMN d SET NOT NULL;
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1 WHERE c is NOT NULL;
+        QUERY PLAN         
+---------------------------
+ Seq Scan on uqk1
+   Filter: (c IS NOT NULL)
+(2 rows)
+
+-- Test UniqueKey column reduction.
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE c = 1;
+    QUERY PLAN     
+-------------------
+ Seq Scan on uqk1
+   Filter: (c = 1)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT a FROM uqk1 WHERE c = 1 and d = 1;
+           QUERY PLAN            
+---------------------------------
+ Seq Scan on uqk1
+   Filter: ((c = 1) AND (d = 1))
+(2 rows)
+
+-- Test Distinct ON
+EXPLAIN (COSTS OFF) SELECT DISTINCT ON(pk) d FROM uqk1;
+    QUERY PLAN    
+------------------
+ Seq Scan on uqk1
+(1 row)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 7be89178f0..41454448f1 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -72,7 +72,7 @@ test: sanity_check
 # ----------
 ignore: random
 test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update delete namespace prepared_xacts
-
+test: uniquekey
 # ----------
 # Another group of parallel tests
 # ----------
diff --git a/src/test/regress/sql/uniquekey.sql b/src/test/regress/sql/uniquekey.sql
new file mode 100644
index 0000000000..a1b538d1c1
--- /dev/null
+++ b/src/test/regress/sql/uniquekey.sql
@@ -0,0 +1,29 @@
+CREATE TABLE uqk1(a int, pk int primary key, c int,  d int);
+CREATE TABLE uqk2(a int, pk int primary key, c int,  d int);
+INSERT INTO uqk1 VALUES(1, 1, 1, 1), (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, null, 4), (5, 5, null, 4);
+INSERT INTO uqk2 VALUES(1, 1, 1, 1), (4, 4, 4, 4), (5, 5, 5, 5);
+ANALYZE uqk1;
+ANALYZE uqk2;
+
+-- Test single table primary key.
+EXPLAIN (COSTS OFF) SELECT DISTINCT * FROM uqk1;
+
+-- Test EC case.
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE d = pk;
+
+-- Test UniqueKey indexes.
+CREATE UNIQUE INDEX uqk1_ukcd ON uqk1(c, d);
+
+-- Test not null quals and not null per catalog.
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1;
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1 WHERE c is NOT NULL;
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE c = 1;
+ALTER TABLE uqk1 ALTER COLUMN d SET NOT NULL;
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1 WHERE c is NOT NULL;
+
+-- Test UniqueKey column reduction.
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE c = 1;
+EXPLAIN (COSTS OFF) SELECT DISTINCT a FROM uqk1 WHERE c = 1 and d = 1;
+
+-- Test Distinct ON
+EXPLAIN (COSTS OFF) SELECT DISTINCT ON(pk) d FROM uqk1;
-- 
2.21.0

From e5e97f0006d9292632a32b8f53d639ec7ecfa298 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Mon, 9 Aug 2021 19:46:13 +0800
Subject: [PATCH v3 6/6] Maintain the UniqueKey on Subquery and UpperRel level.

---
 src/backend/optimizer/path/allpaths.c   |   2 +
 src/backend/optimizer/path/pathkeys.c   |   3 +-
 src/backend/optimizer/path/uniquekey.c  | 179 ++++++++++++++++++++++++
 src/backend/optimizer/plan/planner.c    |  17 ++-
 src/include/optimizer/paths.h           |   6 +
 src/test/regress/expected/uniquekey.out |  36 +++++
 src/test/regress/sql/uniquekey.sql      |  21 +++
 7 files changed, 261 insertions(+), 3 deletions(-)

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 343253d694..9225bf738e 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -2288,6 +2288,8 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 		return;
 	}
 
+	populate_subquery_uniquekeys(root, rel, sub_final_rel);
+
 	/*
 	 * Mark rel with estimated output rows, width, etc.  Note that we have to
 	 * do this before generating outer-query paths, else cost_subqueryscan is
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 21f4f2d4bc..9a8a5b654c 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -33,7 +33,6 @@ static bool pathkey_is_redundant(PathKey *new_pathkey, List *pathkeys);
 static bool matches_boolean_partition_clause(RestrictInfo *rinfo,
 											 RelOptInfo *partrel,
 											 int partkeycol);
-static Var *find_var_for_subquery_tle(RelOptInfo *rel, TargetEntry *tle);
 static bool right_merge_direction(PlannerInfo *root, PathKey *pathkey);
 
 
@@ -1035,7 +1034,7 @@ convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
  * We need this to ensure that we don't return pathkeys describing values
  * that are unavailable above the level of the subquery scan.
  */
-static Var *
+Var *
 find_var_for_subquery_tle(RelOptInfo *rel, TargetEntry *tle)
 {
 	ListCell   *lc;
diff --git a/src/backend/optimizer/path/uniquekey.c b/src/backend/optimizer/path/uniquekey.c
index badc843253..3bc0b8f3cc 100644
--- a/src/backend/optimizer/path/uniquekey.c
+++ b/src/backend/optimizer/path/uniquekey.c
@@ -47,6 +47,13 @@ static void populate_joinrel_composited_uniquekey(PlannerInfo *root,
 												  bool outeruk_still_valid,
 												  bool inneruk_still_valid);
 
+static void convert_subquery_uniquekey(PlannerInfo *root, RelOptInfo *rel, UniqueKey *sub_ukey);
+static EquivalenceClass * find_outer_ec_with_subquery_em(PlannerInfo *root, RelOptInfo *rel,
+														 EquivalenceClass *sub_ec,
+														 EquivalenceMember *sub_em);
+static List *convert_subquery_eclass_list(PlannerInfo *root, RelOptInfo *rel,
+										  List *sub_eclass_list);
+
 /* UniqueKey is subset of .. */
 static bool uniquekey_contains_in(PlannerInfo *root, UniqueKey *ukey,
 								  List *ecs, Relids relids);
@@ -185,6 +192,57 @@ populate_joinrel_uniquekeys(PlannerInfo *root, RelOptInfo *joinrel,
 	return;
 }
 
+/*
+ * populate_subquery_uniquekeys
+ *
+ * 'rel': outer query's RelOptInfo for the subquery relation.
+ * 'subquery_uniquekeys': the subquery's output pathkeys, in its terms.
+ * 'subquery_tlist': the subquery's output targetlist, in its terms.
+ *
+ *  subquery issues: a). tlist mapping.  b). interesting uniquekey. c). not nulls.
+ */
+void
+populate_subquery_uniquekeys(PlannerInfo *root, RelOptInfo *rel, RelOptInfo *sub_final_rel)
+{
+	List	*sub_uniquekeys = sub_final_rel->uniquekeys;
+	ListCell	*lc;
+	foreach(lc, sub_uniquekeys)
+	{
+		UniqueKey *sub_ukey = lfirst_node(UniqueKey, lc);
+		convert_subquery_uniquekey(root, rel, sub_ukey);
+	}
+}
+
+/*
+ * populate_uniquekeys_from_pathkeys
+ *
+ */
+void
+populate_uniquekeys_from_pathkeys(PlannerInfo *root, RelOptInfo *rel, List *pathkeys)
+{
+	ListCell *lc;
+	List	*unique_exprs = NIL;
+	if (pathkeys == NIL)
+		return;
+	foreach(lc, pathkeys)
+	{
+		PathKey *pathkey = lfirst(lc);
+		unique_exprs = lappend(unique_exprs, pathkey->pk_eclass);
+	}
+	rel->uniquekeys = list_make1(
+		make_uniquekey(bms_make_singleton(list_length(root->unique_exprs)),
+					   false,
+					   true));
+	root->unique_exprs = lappend(root->unique_exprs, unique_exprs);
+}
+
+
+void
+simple_copy_uniquekeys(RelOptInfo *tarrel, RelOptInfo *srcrel)
+{
+	tarrel->uniquekeys = srcrel->uniquekeys;
+}
+
 /*
  * relation_is_distinct_for
  *		Check if the relation is distinct for.
@@ -710,6 +768,127 @@ is_uniquekey_useful_afterjoin(PlannerInfo *root, UniqueKey *ukey,
 	return true;
 }
 
+/*
+ * find_outer_ec_with_subquery_em
+ *
+ *	Given a em in subquery, return the related EquivalenceClass outside.
+ */
+static EquivalenceClass *
+find_outer_ec_with_subquery_em(PlannerInfo *root, RelOptInfo *rel,
+							   EquivalenceClass *sub_ec, EquivalenceMember *sub_em)
+{
+	TargetEntry *sub_tle;
+	Var *outer_var;
+	EquivalenceClass *outer_ec;
+
+	sub_tle = get_tle_from_expr(sub_em->em_expr, rel->subroot->processed_tlist);
+
+	if (!sub_tle)
+		return NULL;
+
+	outer_var = find_var_for_subquery_tle(rel, sub_tle);
+	if (!outer_var)
+		return NULL;
+
+	outer_ec = get_eclass_for_sort_expr(root,
+										(Expr *)outer_var,
+										NULL,
+										sub_ec->ec_opfamilies,
+										sub_em->em_datatype,
+										sub_ec->ec_collation,
+										0,
+										rel->relids,
+										false);
+	return outer_ec;
+}
+
+
+/*
+ * convert_subquery_eclass_list
+ *
+ *		Given a list of eclass in subquery, find the corresponding eclass in outer side.
+ * return NULL if no related eclass outside is found for any eclass in subquery.
+ */
+static List *
+convert_subquery_eclass_list(PlannerInfo *root, RelOptInfo *rel, List *sub_eclass_list)
+{
+	ListCell	*lc;
+	List	*ec_list = NIL;
+	foreach(lc, sub_eclass_list)
+	{
+		EquivalenceClass *sub_ec = lfirst_node(EquivalenceClass, lc);
+		EquivalenceClass *ec = NULL;
+		ListCell	*emc;
+		foreach(emc, sub_ec->ec_members)
+		{
+			EquivalenceMember *sub_em = lfirst(emc);
+			if ((ec = find_outer_ec_with_subquery_em(root, rel, sub_ec, sub_em)) != NULL)
+				break;
+		}
+		if (!ec)
+			return NIL;
+		ec_list = lappend(ec_list, ec);
+	}
+	return ec_list;
+}
+
+
+/*
+ * convert_subquery_uniquekey
+ *
+ */
+static void
+convert_subquery_uniquekey(PlannerInfo *root, RelOptInfo *rel, UniqueKey *sub_ukey)
+{
+	PlannerInfo *sub_root = rel->subroot;
+	List	*unique_exprs_list = NIL;
+	Bitmapset	*unique_exprs_indexes = NULL;
+	UniqueKey	*ukey = NULL;
+	int i = -1;
+	ListCell	*lc;
+	while((i = bms_next_member(sub_ukey->unique_expr_indexes, i)) >= 0)
+	{
+		Node *sub_eq_list = list_nth(sub_root->unique_exprs, i);
+		if (IsA(sub_eq_list, SingleRow))
+		{
+			/*
+			 * TODO: Unclear what to do, don't think it hard before the overall
+			 * design is accepted.
+			 */
+			return;
+		}
+		else
+		{
+			List *upper_eq_list;
+			Assert(IsA(sub_eq_list, List));
+			/*
+			 * Note: upper_eq_list is just part of uniquekey's exprs, to covert the whole
+			 * UniqueKey, we needs all the parts are shown in the upper rel.
+			 */
+			upper_eq_list = convert_subquery_eclass_list(root, rel, (List *)sub_eq_list);
+			if (upper_eq_list == NIL)
+			{
+				if (unique_exprs_list != NIL)
+					pfree(unique_exprs_list);
+				return;
+			}
+			unique_exprs_list = lappend(unique_exprs_list, upper_eq_list);
+		}
+	}
+
+	foreach(lc, unique_exprs_list)
+	{
+		unique_exprs_indexes = bms_add_member(unique_exprs_indexes, list_length(root->unique_exprs));
+		root->unique_exprs = lappend(root->unique_exprs, lfirst(lc));
+	}
+
+	ukey = make_uniquekey(unique_exprs_indexes,
+						  sub_ukey->multi_nulls,
+						  /* TODO: need check again, case SELECT * FROM (SELECT u FROM x OFFSET 0) v where x.u = 0; */
+						  true);
+	rel->uniquekeys = lappend(rel->uniquekeys, ukey);
+}
+
 /*
  *	make_uniquekey
  */
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index ed2cff00fc..a268737a4c 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1650,7 +1650,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
 	 * Now we are prepared to build the final-output upperrel.
 	 */
 	final_rel = fetch_upper_rel(root, UPPERREL_FINAL, NULL);
-
+	simple_copy_uniquekeys(final_rel, current_rel);
 	/*
 	 * If the input rel is marked consider_parallel and there's nothing that's
 	 * not parallel-safe in the LIMIT clause, then the final_rel can be marked
@@ -3622,6 +3622,19 @@ create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
 									  gd,
 									  extra->targetList);
 
+	if (root->parse->groupingSets)
+	{
+		/* nothing to do */
+	}
+	else if (root->parse->groupClause && root->group_pathkeys != NIL)
+	{
+		populate_uniquekeys_from_pathkeys(root, grouped_rel, root->group_pathkeys);
+	}
+	else
+	{
+		/* SingleRow Case */
+	}
+
 	/* Build final grouping paths */
 	add_paths_to_grouping_rel(root, input_rel, grouped_rel,
 							  partially_grouped_rel, agg_costs, gd,
@@ -4251,6 +4264,8 @@ create_distinct_paths(PlannerInfo *root,
 	/* For now, do all work in the (DISTINCT, NULL) upperrel */
 	distinct_rel = fetch_upper_rel(root, UPPERREL_DISTINCT, NULL);
 
+	populate_uniquekeys_from_pathkeys(root, distinct_rel, root->distinct_pathkeys);
+
 	/*
 	 * We don't compute anything at this level, so distinct_rel will be
 	 * parallel-safe if the input rel is parallel-safe.  In particular, if
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index f233837e59..b9570807af 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -265,11 +265,17 @@ extern void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 
 extern void populate_baserel_uniquekeys(PlannerInfo *root,
 										RelOptInfo *baserel);
+extern Var *find_var_for_subquery_tle(RelOptInfo *rel, TargetEntry *tle);
 extern void populate_baserel_uniquekeys(PlannerInfo *root,
 										RelOptInfo *baserel);
 extern void populate_joinrel_uniquekeys(PlannerInfo *root, RelOptInfo *joinrel,
 										RelOptInfo *outerrel, RelOptInfo *innerrel,
 										List *restrictlist, JoinType jointype);
+extern void populate_uniquekeys_from_pathkeys(PlannerInfo *root, RelOptInfo *rel,
+											  List *pathkeys);
+extern void populate_subquery_uniquekeys(PlannerInfo *root, RelOptInfo *rel,
+										 RelOptInfo *sub_final_rel);
+extern void simple_copy_uniquekeys(RelOptInfo *srcrel, RelOptInfo *tarrel);
 extern bool relation_is_distinct_for(PlannerInfo *root, RelOptInfo *rel,
 									 List *distinct_pathkey);
 #endif							/* PATHS_H */
diff --git a/src/test/regress/expected/uniquekey.out b/src/test/regress/expected/uniquekey.out
index c2bd1fa619..a86e413fcf 100644
--- a/src/test/regress/expected/uniquekey.out
+++ b/src/test/regress/expected/uniquekey.out
@@ -407,3 +407,39 @@ EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND
                ->  Seq Scan on uqk2
 (8 rows)
 
+-----------------------------------------
+-- Test DISTINCT/GROUP BY CASE.
+-----------------------------------------
+--------------------------------------------------------------------------------------------
+-- Test subquery cases.
+-- Note that current the UniqueKey still not push down the interesting UniqueKey to subquery.
+-- like uniquekey, so the below test case need a "DISTINCT" in subquery to make sure the
+-- UniqueKey is maintain.
+--------------------------------------------------------------------------------------------
+-- Test a normal case - one side
+EXPLAIN SELECT DISTINCT v.* FROM
+(SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.a = uqk2.pk AND uqk1.c is not null offset 0) v;
+                           QUERY PLAN                           
+----------------------------------------------------------------
+ Hash Join  (cost=1.07..2.14 rows=2 width=8)
+   Hash Cond: (uqk1.a = uqk2.pk)
+   ->  Seq Scan on uqk1  (cost=0.00..1.05 rows=3 width=12)
+         Filter: (c IS NOT NULL)
+   ->  Hash  (cost=1.03..1.03 rows=3 width=4)
+         ->  Seq Scan on uqk2  (cost=0.00..1.03 rows=3 width=4)
+(6 rows)
+
+-- Test a normal case - composited side.
+EXPLAIN SELECT DISTINCT v.* FROM
+(SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null OFFSET 0)
+v;
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Nested Loop  (cost=0.00..2.20 rows=9 width=12)
+   ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=3 width=8)
+         Filter: (c IS NOT NULL)
+   ->  Materialize  (cost=0.00..1.04 rows=3 width=4)
+         ->  Seq Scan on uqk2 t2  (cost=0.00..1.03 rows=3 width=4)
+(5 rows)
+
diff --git a/src/test/regress/sql/uniquekey.sql b/src/test/regress/sql/uniquekey.sql
index 3f93872246..22e236eafb 100644
--- a/src/test/regress/sql/uniquekey.sql
+++ b/src/test/regress/sql/uniquekey.sql
@@ -106,3 +106,24 @@ SELECT uqk1.c, uqk2.c FROM uqk1, uqk2 WHERE uqk1.pk = 2 AND uqk2.pk = 1 order BY
 -----------------------------------------
 EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.pk FROM uqk1, uqk2 WHERE uqk1.c = uqk2.c;
 EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.c;
+
+-----------------------------------------
+-- Test DISTINCT/GROUP BY CASE.
+-----------------------------------------
+
+
+--------------------------------------------------------------------------------------------
+-- Test subquery cases.
+-- Note that current the UniqueKey still not push down the interesting UniqueKey to subquery.
+-- like uniquekey, so the below test case need a "DISTINCT" in subquery to make sure the
+-- UniqueKey is maintain.
+--------------------------------------------------------------------------------------------
+-- Test a normal case - one side
+EXPLAIN SELECT DISTINCT v.* FROM
+(SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.a = uqk2.pk AND uqk1.c is not null offset 0) v;
+
+-- Test a normal case - composited side.
+EXPLAIN SELECT DISTINCT v.* FROM
+(SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null OFFSET 0)
+v;
-- 
2.21.0

From 32886c5b14709d1f7ad27e5e024c3e083b97026c Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sun, 25 Jul 2021 16:28:30 +0800
Subject: [PATCH v3 1/6] Just refactor pathkeys_useful_for_merging, split a new
 function

ec_useful_for_merging which is useful for UniqueKey as well.
---
 src/backend/optimizer/path/equivclass.c | 42 +++++++++++++++++++++++++
 src/backend/optimizer/path/pathkeys.c   | 33 +------------------
 src/include/optimizer/paths.h           |  2 ++
 3 files changed, 45 insertions(+), 32 deletions(-)

diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 6f1abbe47d..2f688807af 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -960,6 +960,48 @@ find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
 	return NULL;
 }
 
+/*
+ * ec_useful_for_merging
+ *	check if the ec exists in rel's merageable restrictinfo_lists.
+ */
+bool
+ec_useful_for_merging(PlannerInfo *root, RelOptInfo *rel,
+					  EquivalenceClass *ec)
+{
+	/*
+	 * First look into the EquivalenceClass to see if there are any members
+	 * not yet joined to the rel.
+	 */
+	if (rel->has_eclass_joins &&
+		eclass_useful_for_merging(root, ec, rel))
+		return true;
+	else
+	{
+		/*
+		 * Otherwise search the rel's joininfo list, which contains
+		 * non-EquivalenceClass-derivable join clauses that might
+		 * nonetheless be mergejoinable.
+		 */
+		ListCell	*j;
+		foreach(j, rel->joininfo)
+		{
+			RestrictInfo *restrictinfo = (RestrictInfo *) lfirst(j);
+
+			if (restrictinfo->mergeopfamilies == NIL)
+				continue;
+			update_mergeclause_eclasses(root, restrictinfo);
+
+			if (ec == restrictinfo->left_ec ||
+			    ec == restrictinfo->right_ec)
+			{
+				return true;
+			}
+		}
+	}
+
+	return false;
+}
+
 /*
  * relation_can_be_sorted_early
  *		Can this relation be sorted on this EC before the final output step?
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 216dd26385..21f4f2d4bc 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -1751,43 +1751,12 @@ pathkeys_useful_for_merging(PlannerInfo *root, RelOptInfo *rel, List *pathkeys)
 	{
 		PathKey    *pathkey = (PathKey *) lfirst(i);
 		bool		matched = false;
-		ListCell   *j;
 
 		/* If "wrong" direction, not useful for merging */
 		if (!right_merge_direction(root, pathkey))
 			break;
 
-		/*
-		 * First look into the EquivalenceClass of the pathkey, to see if
-		 * there are any members not yet joined to the rel.  If so, it's
-		 * surely possible to generate a mergejoin clause using them.
-		 */
-		if (rel->has_eclass_joins &&
-			eclass_useful_for_merging(root, pathkey->pk_eclass, rel))
-			matched = true;
-		else
-		{
-			/*
-			 * Otherwise search the rel's joininfo list, which contains
-			 * non-EquivalenceClass-derivable join clauses that might
-			 * nonetheless be mergejoinable.
-			 */
-			foreach(j, rel->joininfo)
-			{
-				RestrictInfo *restrictinfo = (RestrictInfo *) lfirst(j);
-
-				if (restrictinfo->mergeopfamilies == NIL)
-					continue;
-				update_mergeclause_eclasses(root, restrictinfo);
-
-				if (pathkey->pk_eclass == restrictinfo->left_ec ||
-					pathkey->pk_eclass == restrictinfo->right_ec)
-				{
-					matched = true;
-					break;
-				}
-			}
-		}
+		matched = ec_useful_for_merging(root, rel, pathkey->pk_eclass);
 
 		/*
 		 * If we didn't find a mergeclause, we're done --- any additional
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index f1d111063c..a29f616423 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -143,6 +143,8 @@ extern EquivalenceMember *find_computable_ec_member(PlannerInfo *root,
 													List *exprs,
 													Relids relids,
 													bool require_parallel_safe);
+extern bool ec_useful_for_merging(PlannerInfo *root, RelOptInfo *rel,
+								  EquivalenceClass *ec);
 extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
 extern bool relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
 										 EquivalenceClass *ec,
-- 
2.21.0

From e1940cc02e97da2e84fd062feecc0a38bdbedd4e Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Mon, 9 Aug 2021 19:43:58 +0800
Subject: [PATCH v4 5/6] Support UniqueKey on JoinRel.

---
 src/backend/optimizer/path/uniquekey.c  | 379 ++++++++++++++++++++++++
 src/backend/optimizer/util/relnode.c    |   2 +
 src/include/optimizer/paths.h           |   5 +
 src/test/regress/expected/uniquekey.out | 327 ++++++++++++++++++++
 src/test/regress/sql/uniquekey.sql      |  79 +++++
 5 files changed, 792 insertions(+)

diff --git a/src/backend/optimizer/path/uniquekey.c b/src/backend/optimizer/path/uniquekey.c
index c93075656d..815ade02c3 100644
--- a/src/backend/optimizer/path/uniquekey.c
+++ b/src/backend/optimizer/path/uniquekey.c
@@ -27,6 +27,26 @@ static bool add_uniquekey_for_uniqueindex(PlannerInfo *root,
 										  List *mergeable_const_peer,
 										  List *expr_opfamilies);
 
+static bool is_uniquekey_nulls_removed(PlannerInfo *root,
+									   UniqueKey *ukey,
+									   RelOptInfo *rel);
+static UniqueKey *adjust_uniquekey_multinull_for_joinrel(PlannerInfo *root,
+														 UniqueKey *joinrel_ukey,
+														 RelOptInfo *rel,
+														 bool below_outer_side);
+
+static bool populate_joinrel_uniquekey_for_rel(PlannerInfo *root, RelOptInfo *joinrel,
+											   RelOptInfo *rel, RelOptInfo *other_rel,
+											   List *restrictlist, JoinType jointype);
+static void populate_joinrel_composite_uniquekey(PlannerInfo *root,
+												 RelOptInfo *joinrel,
+												 RelOptInfo *outerrel,
+												 RelOptInfo *innerrel,
+												 List	*restrictlist,
+												 JoinType jointype,
+												 bool outeruk_still_valid,
+												 bool inneruk_still_valid);
+
 /* UniqueKey is subset of .. */
 static bool uniquekey_contains_in(PlannerInfo *root, UniqueKey *ukey,
 								  List *ecs, Relids relids);
@@ -35,6 +55,9 @@ static bool uniquekey_contains_in(PlannerInfo *root, UniqueKey *ukey,
 static bool unique_ecs_useful_for_distinct(PlannerInfo *root, List *ecs);
 static bool unique_ecs_useful_for_merging(PlannerInfo *root, RelOptInfo *rel,
 										  List *unique_ecs);
+static bool is_uniquekey_useful_afterjoin(PlannerInfo *root, UniqueKey *ukey,
+										  RelOptInfo *joinrel);
+
 /* Helper functions to create UniqueKey. */
 static UniqueKey *make_uniquekey(Bitmapset *unique_expr_indexes,
 								 bool multi_null,
@@ -90,6 +113,78 @@ populate_baserel_uniquekeys(PlannerInfo *root, RelOptInfo *rel)
 	print_uniquekey(root, rel);
 }
 
+/*
+ * populate_joinrel_uniquekeys
+ */
+void
+populate_joinrel_uniquekeys(PlannerInfo *root, RelOptInfo *joinrel,
+							RelOptInfo *outerrel, RelOptInfo *innerrel,
+							List *restrictlist, JoinType jointype)
+{
+	bool outeruk_still_valid = false, inneruk_still_valid = false;
+	if (jointype == JOIN_SEMI || jointype == JOIN_ANTI)
+	{
+		ListCell	*lc;
+		foreach(lc, outerrel->uniquekeys)
+		{
+			/*
+			 * SEMI/ANTI join can be used to remove NULL values as well.
+			 * So we need to adjust multi_nulls for join.
+			 */
+			joinrel->uniquekeys = lappend(joinrel->uniquekeys,
+										  adjust_uniquekey_multinull_for_joinrel(root,
+																				 lfirst(lc),
+																				 joinrel,
+																				 false));
+		}
+		return;
+	}
+
+	if (outerrel->uniquekeys == NIL || innerrel->uniquekeys == NIL)
+		return;
+
+	switch(jointype)
+	{
+		case JOIN_INNER:
+			outeruk_still_valid = populate_joinrel_uniquekey_for_rel(root, joinrel, outerrel,
+																	 innerrel, restrictlist, jointype);
+			inneruk_still_valid = populate_joinrel_uniquekey_for_rel(root, joinrel, innerrel,
+																	 outerrel, restrictlist, jointype);
+			break;
+
+		case JOIN_LEFT:
+			/*
+			 * For left join, we are sure the innerrel's multi_nulls would be true
+			 * and it can't become to multi_nulls=false any more. so just discard it
+			 * and only check the outerrel and composited ones.
+			 */
+			outeruk_still_valid = populate_joinrel_uniquekey_for_rel(root, joinrel, outerrel,
+																	 innerrel, restrictlist, jointype);
+			break;
+
+		case JOIN_FULL:
+			/*
+			 * Both sides would contains multi_nulls, don't maintain it
+			 * any more.
+			 */
+			break;
+
+		default:
+			elog(ERROR, "unexpected join_type %d", jointype);
+	}
+
+	populate_joinrel_composite_uniquekey(root, joinrel,
+										 outerrel,
+										 innerrel,
+										 restrictlist,
+										 jointype,
+										 outeruk_still_valid,
+										 inneruk_still_valid);
+
+
+	return;
+}
+
 /*
  * relation_is_distinct_for
  *		Check if the relation is distinct for.
@@ -238,6 +333,253 @@ add_uniquekey_for_uniqueindex(PlannerInfo *root, IndexOptInfo *unique_index,
 											 used_for_distinct));
 	return false;
 }
+
+/*
+ * is_uniquekey_nulls_removed
+ *
+ *	note this function will not consider the OUTER JOIN impacts. Caller should
+ * take care of it.
+ *	-- Use my way temporary (RelOptInfo.notnull_attrs) until Tom's is ready.
+ */
+static bool
+is_uniquekey_nulls_removed(PlannerInfo *root,
+						   UniqueKey *ukey,
+						   RelOptInfo *joinrel)
+{
+	int i = -1;
+
+	while((i = bms_next_member(ukey->unique_expr_indexes, i)) >= 0)
+	{
+		Node *node = list_nth(root->unique_exprs, i);
+		List	*ecs;
+		ListCell	*lc;
+		if (IsA(node, SingleRow))
+			continue;
+		ecs = castNode(List, node);
+		foreach(lc, ecs)
+		{
+			EquivalenceClass *ec = lfirst_node(EquivalenceClass, lc);
+			ListCell *emc;
+			foreach(emc, ec->ec_members)
+			{
+				EquivalenceMember *em = lfirst_node(EquivalenceMember, emc);
+				int relid;
+				Var *var;
+				Bitmapset *notnull_attrs;
+				if (!bms_is_subset(em->em_relids, joinrel->relids))
+					continue;
+
+				if (!bms_get_singleton_member(em->em_relids, &relid))
+					continue;
+
+				if (!IsA(em->em_expr, Var))
+					continue;
+
+				var = castNode(Var, em->em_expr);
+
+				if (relid != var->varno)
+					continue;
+
+				notnull_attrs = joinrel->notnull_attrs[var->varno];
+
+				if (!bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber,
+								   notnull_attrs))
+					return false;
+				else
+					break; /* Break to check next ECs */
+			}
+		}
+	}
+	return true;
+}
+
+/*
+ * adjust_uniquekey_multinull_for_joinrel
+ *
+ *	After the join, some NULL values can be removed due to join-clauses.
+ * but the outer join can generated null values again. Return the final
+ * state of the UniqueKey on joinrel.
+ */
+static UniqueKey *
+adjust_uniquekey_multinull_for_joinrel(PlannerInfo *root,
+									   UniqueKey *ukey,
+									   RelOptInfo *joinrel,
+									   bool below_outer_side)
+{
+	if (below_outer_side)
+	{
+		if (ukey->multi_nulls)
+			/* we need it to be multi_nulls, but it is already, just return it. */
+			return ukey;
+		else
+			/* we need it to be multi_nulls, but it is not, create a new one. */
+			return make_uniquekey(ukey->unique_expr_indexes,
+								  true,
+								  ukey->use_for_distinct);
+	}
+	else
+	{
+		/*
+		 * We need to check if the join clauses can remove the NULL values. However
+		 * if it doesn't contain NULL values at the first, we don't need to check it.
+		 */
+		if (!ukey->multi_nulls)
+			return ukey;
+		else
+		{
+			/*
+			 * Multi null values exists. It's time to check if the nulls values
+			 * are removed via outer join.
+			 */
+			if (!is_uniquekey_nulls_removed(root, ukey, joinrel))
+				/* null values can be removed, return the original one. */
+				return ukey;
+			else
+				return make_uniquekey(ukey->unique_expr_indexes,
+									  false, ukey->use_for_distinct);
+		}
+	}
+}
+
+/*
+ * populate_joinrel_uniquekey_for_rel
+ *
+ *    Check if rel.any_column = other_rel.unique_key_columns.
+ * The return value is if the rel->uniquekeys still valid. If
+ * yes, added the uniquekeys in rel to joinrel and return true.
+ * otherwise, return false.
+ */
+static bool
+populate_joinrel_uniquekey_for_rel(PlannerInfo *root, RelOptInfo *joinrel,
+								   RelOptInfo *rel, RelOptInfo *other_rel,
+								   List *restrictlist, JoinType type)
+{
+	bool	rel_keep_unique = false;
+	List *other_ecs = NIL;
+	Relids	other_relids = NULL;
+	ListCell	*lc;
+
+	/*
+	 * Gather all the other ECs regarding to rel, if all the unique ecs contains
+	 * in this list, then it hits our expectations.
+	 */
+	foreach(lc, restrictlist)
+	{
+		RestrictInfo *r = lfirst_node(RestrictInfo, lc);
+
+		if (r->mergeopfamilies == NIL)
+			continue;
+
+		if (bms_equal(r->left_relids, rel->relids) && r->right_ec != NULL)
+		{
+			other_ecs = lappend(other_ecs, r->right_ec);
+			other_relids = bms_add_members(other_relids, r->right_relids);
+		}
+		else if (bms_equal(r->right_relids, rel->relids) && r->left_ec != NULL)
+		{
+			other_ecs = lappend(other_ecs, r->right_ec);
+			other_relids = bms_add_members(other_relids, r->left_relids);
+		}
+	}
+
+	foreach(lc, other_rel->uniquekeys)
+	{
+		UniqueKey *ukey = lfirst_node(UniqueKey, lc);
+		if (uniquekey_contains_in(root, ukey, other_ecs, other_relids))
+		{
+			rel_keep_unique = true;
+			break;
+		}
+	}
+
+	if (!rel_keep_unique)
+		return false;
+
+	foreach(lc, rel->uniquekeys)
+	{
+
+		UniqueKey *ukey = lfirst_node(UniqueKey, lc);
+
+		if (is_uniquekey_useful_afterjoin(root, ukey, joinrel))
+		{
+			ukey = adjust_uniquekey_multinull_for_joinrel(root,
+														  ukey,
+														  joinrel,
+														  false /* outer_side, caller grantees this */);
+			joinrel->uniquekeys = lappend(joinrel->uniquekeys, ukey);
+		}
+	}
+
+	return true;
+}
+
+
+/*
+ * Populate_joinrel_composited_uniquekey
+ *
+ *	A composited unqiuekey is valid no matter with join type and restrictlist.
+ */
+static void
+populate_joinrel_composite_uniquekey(PlannerInfo *root,
+									 RelOptInfo *joinrel,
+									 RelOptInfo *outerrel,
+									 RelOptInfo *innerrel,
+									 List	*restrictlist,
+									 JoinType jointype,
+									 bool left_added,
+									 bool right_added)
+{
+	ListCell	*lc;
+	if (left_added || right_added)
+		/* No need to create the composited ones */
+		return;
+
+	foreach(lc, outerrel->uniquekeys)
+	{
+		UniqueKey	*outer_ukey = adjust_uniquekey_multinull_for_joinrel(root,
+																		 lfirst(lc),
+																		 joinrel,
+																		 jointype == JOIN_FULL);
+		ListCell	*lc2;
+
+		if (!is_uniquekey_useful_afterjoin(root, outer_ukey, joinrel))
+			continue;
+
+		foreach(lc2, innerrel->uniquekeys)
+		{
+			UniqueKey	*inner_ukey = adjust_uniquekey_multinull_for_joinrel(root,
+																			 lfirst(lc2),
+																			 joinrel,
+																			 (jointype == JOIN_FULL || jointype == JOIN_LEFT)
+				);
+
+			UniqueKey	*comp_ukey;
+
+			if (!is_uniquekey_useful_afterjoin(root, inner_ukey, joinrel))
+				continue;
+
+			comp_ukey = make_uniquekey(
+				/* unique_expr_indexes is easy, just union the both sides. */
+				bms_union(outer_ukey->unique_expr_indexes, inner_ukey->unique_expr_indexes),
+				/*
+				 * If both are !multi_nulls, then the composited one is !multi_null
+				 * no matter with jointype and join clauses. otherwise, it is multi
+				 * nulls no matter with other factors.
+				 *
+				 */
+				outer_ukey->multi_nulls || inner_ukey->multi_nulls,
+				/*
+				 * we need both sides are used in distinct to say the composited
+				 * one is used for distinct as well.
+				 */
+				outer_ukey->use_for_distinct && inner_ukey->use_for_distinct);
+
+			joinrel->uniquekeys = lappend(joinrel->uniquekeys, comp_ukey);
+		}
+	}
+}
+
+
 /*
  * uniquekey_contains_in
  *	Return if UniqueKey contains in the list of EquivalenceClass
@@ -333,6 +675,43 @@ unique_ecs_useful_for_merging(PlannerInfo *root, RelOptInfo *rel, List *unique_e
 
 	return true;
 }
+
+/*
+ * is_uniquekey_useful_afterjoin
+ *
+ *  is useful when it contains in distinct_pathkey or in mergable join clauses.
+ */
+static bool
+is_uniquekey_useful_afterjoin(PlannerInfo *root, UniqueKey *ukey,
+							 RelOptInfo *joinrel)
+{
+	int	i = -1;
+
+	if (ukey->use_for_distinct)
+		return true;
+
+	while((i = bms_next_member(ukey->unique_expr_indexes, i)) >= 0)
+	{
+		Node *exprs =  list_nth(root->unique_exprs, i);
+		if (IsA(exprs, List))
+		{
+			if (!unique_ecs_useful_for_merging(root, joinrel, (List *)exprs))
+				return false;
+		}
+		else
+		{
+			Assert(IsA(exprs, SingleRow));
+			/*
+			 * Ideally we should check if there are a expr on SingleRow
+			 * used in joinrel's joinclauses, but it can't be checked effectively
+			 * for now, so we just check the rest part. so just think
+			 * it is useful.
+			 */
+		}
+	}
+	return true;
+}
+
 /*
  *	make_uniquekey
  */
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index b75e1679e6..6695f9f2a6 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -846,6 +846,8 @@ build_join_rel(PlannerInfo *root,
 	}
 
 	set_joinrel_notnull_attrs(joinrel, outer_rel, inner_rel, restrictlist, sjinfo);
+	populate_joinrel_uniquekeys(root, joinrel, outer_rel, inner_rel,
+								restrictlist, sjinfo->jointype);
 
 	return joinrel;
 }
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 68b8b40ca9..f233837e59 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -265,6 +265,11 @@ extern void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 
 extern void populate_baserel_uniquekeys(PlannerInfo *root,
 										RelOptInfo *baserel);
+extern void populate_baserel_uniquekeys(PlannerInfo *root,
+										RelOptInfo *baserel);
+extern void populate_joinrel_uniquekeys(PlannerInfo *root, RelOptInfo *joinrel,
+										RelOptInfo *outerrel, RelOptInfo *innerrel,
+										List *restrictlist, JoinType jointype);
 extern bool relation_is_distinct_for(PlannerInfo *root, RelOptInfo *rel,
 									 List *distinct_pathkey);
 #endif							/* PATHS_H */
diff --git a/src/test/regress/expected/uniquekey.out b/src/test/regress/expected/uniquekey.out
index d9a8634e80..c2bd1fa619 100644
--- a/src/test/regress/expected/uniquekey.out
+++ b/src/test/regress/expected/uniquekey.out
@@ -80,3 +80,330 @@ EXPLAIN (COSTS OFF) SELECT DISTINCT ON(pk) d FROM uqk1;
  Seq Scan on uqk1
 (1 row)
 
+------------------------------------------------------
+-- Test UniqueKey on one side still valid after join.
+-----------------------------------------------------
+-- uqk1(c, d) is the uniquekey with mutli nulls at single relation access.
+-- so distinct is not no-op.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.a = uqk2.pk;
+                 QUERY PLAN                  
+---------------------------------------------
+ Unique
+   ->  Sort
+         Sort Key: uqk1.c, uqk1.d
+         ->  Hash Join
+               Hash Cond: (uqk1.a = uqk2.pk)
+               ->  Seq Scan on uqk1
+               ->  Hash
+                     ->  Seq Scan on uqk2
+(8 rows)
+
+-- Both uqk1 (c,d) are a valid uniquekey. 
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.c is NOT NULL AND uqk1.a = uqk2.pk;
+           QUERY PLAN            
+---------------------------------
+ Hash Join
+   Hash Cond: (uqk1.a = uqk2.pk)
+   ->  Seq Scan on uqk1
+         Filter: (c IS NOT NULL)
+   ->  Hash
+         ->  Seq Scan on uqk2
+(6 rows)
+
+-- uqk1.c is null at baserel, but the null values are removed after join.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE  uqk1.a = uqk2.pk and uqk1.c = uqk2.c;
+                       QUERY PLAN                        
+---------------------------------------------------------
+ Hash Join
+   Hash Cond: ((uqk1.a = uqk2.pk) AND (uqk1.c = uqk2.c))
+   ->  Seq Scan on uqk1
+   ->  Hash
+         ->  Seq Scan on uqk2
+(5 rows)
+
+-- uqk1.c is null at baserel, but the null values are removed after join
+-- but new null values are generated due to outer join again. so distinct
+-- is still needed.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1 right join uqk2
+on uqk1.a = uqk2.pk and uqk1.c = uqk2.c;
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Unique
+   ->  Sort
+         Sort Key: uqk1.c, uqk1.d
+         ->  Hash Right Join
+               Hash Cond: ((uqk1.a = uqk2.pk) AND (uqk1.c = uqk2.c))
+               ->  Seq Scan on uqk1
+               ->  Hash
+                     ->  Seq Scan on uqk2
+(8 rows)
+
+------------------------------------------------------
+-- Test join: Composited UniqueKey
+-----------------------------------------------------
+-- both t1.pk and t1.pk is valid uniquekey.
+EXPLAIN SELECT DISTINCT t1.pk, t2.pk FROM uqk1 t1 cross join uqk2 t2;
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Nested Loop  (cost=0.00..2.27 rows=15 width=8)
+   ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=5 width=4)
+   ->  Materialize  (cost=0.00..1.04 rows=3 width=4)
+         ->  Seq Scan on uqk2 t2  (cost=0.00..1.03 rows=3 width=4)
+(4 rows)
+
+SELECT DISTINCT t1.pk, t2.pk FROM uqk1 t1 cross join uqk2 t2 order by 1, 2;
+ pk | pk 
+----+----
+  1 |  1
+  1 |  4
+  1 |  5
+  2 |  1
+  2 |  4
+  2 |  5
+  3 |  1
+  3 |  4
+  3 |  5
+  4 |  1
+  4 |  4
+  4 |  5
+  5 |  1
+  5 |  4
+  5 |  5
+(15 rows)
+
+-- NOT OK, since t1.c includes multi nulls. 
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null;
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ HashAggregate  (cost=2.20..2.26 rows=6 width=12)
+   Group Key: t1.c, t1.d, t2.pk
+   ->  Nested Loop  (cost=0.00..2.16 rows=6 width=12)
+         ->  Seq Scan on uqk2 t2  (cost=0.00..1.03 rows=3 width=4)
+         ->  Materialize  (cost=0.00..1.06 rows=2 width=8)
+               ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=2 width=8)
+                     Filter: (c IS NULL)
+(7 rows)
+
+SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null order by 1, 2,3;
+ c | d | pk 
+---+---+----
+   | 4 |  1
+   | 4 |  4
+   | 4 |  5
+(3 rows)
+
+SELECT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null order by 1, 2,3;
+ c | d | pk 
+---+---+----
+   | 4 |  1
+   | 4 |  1
+   | 4 |  4
+   | 4 |  4
+   | 4 |  5
+   | 4 |  5
+(6 rows)
+
+-- let's remove the t1.c's multi null values
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null;
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Nested Loop  (cost=0.00..2.20 rows=9 width=12)
+   ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=3 width=8)
+         Filter: (c IS NOT NULL)
+   ->  Materialize  (cost=0.00..1.04 rows=3 width=4)
+         ->  Seq Scan on uqk2 t2  (cost=0.00..1.03 rows=3 width=4)
+(5 rows)
+
+SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null order by 1, 2, 3 ;
+ c | d | pk 
+---+---+----
+ 1 | 1 |  1
+ 1 | 1 |  4
+ 1 | 1 |  5
+ 2 | 2 |  1
+ 2 | 2 |  4
+ 2 | 2 |  5
+ 3 | 3 |  1
+ 3 | 3 |  4
+ 3 | 3 |  5
+(9 rows)
+
+SELECT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null order by 1, 2, 3;
+ c | d | pk 
+---+---+----
+ 1 | 1 |  1
+ 1 | 1 |  4
+ 1 | 1 |  5
+ 2 | 2 |  1
+ 2 | 2 |  4
+ 2 | 2 |  5
+ 3 | 3 |  1
+ 3 | 3 |  4
+ 3 | 3 |  5
+(9 rows)
+
+-- test onerow case with composited cases.
+-- t2.c is onerow. OK
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Nested Loop  (cost=0.00..2.12 rows=3 width=12)
+   ->  Seq Scan on uqk2 t2  (cost=0.00..1.04 rows=1 width=4)
+         Filter: (pk = 1)
+   ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=3 width=8)
+         Filter: (c IS NOT NULL)
+(5 rows)
+
+SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+ c | d | c 
+---+---+---
+ 1 | 1 | 1
+ 2 | 2 | 1
+ 3 | 3 | 1
+(3 rows)
+
+SELECT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+ c | d | c 
+---+---+---
+ 1 | 1 | 1
+ 2 | 2 | 1
+ 3 | 3 | 1
+(3 rows)
+
+-- t2.c is onerow, but t1.c has multi-nulls, NOt OK.
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+                               QUERY PLAN                                
+-------------------------------------------------------------------------
+ Unique  (cost=2.12..2.14 rows=2 width=12)
+   ->  Sort  (cost=2.12..2.12 rows=2 width=12)
+         Sort Key: t1.c, t1.d, t2.c
+         ->  Nested Loop  (cost=0.00..2.11 rows=2 width=12)
+               ->  Seq Scan on uqk2 t2  (cost=0.00..1.04 rows=1 width=4)
+                     Filter: (pk = 1)
+               ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=2 width=8)
+                     Filter: (c IS NULL)
+(8 rows)
+
+SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+ c | d | c 
+---+---+---
+   | 4 | 1
+(1 row)
+
+SELECT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+ c | d | c 
+---+---+---
+   | 4 | 1
+   | 4 | 1
+(2 rows)
+
+-- Test Semi/Anti JOIN
+EXPLAIN (COSTS OFF) SELECT DISTINCT pk FROM uqk1 WHERE d in (SELECT d FROM uqk2);
+           QUERY PLAN           
+--------------------------------
+ Hash Semi Join
+   Hash Cond: (uqk1.d = uqk2.d)
+   ->  Seq Scan on uqk1
+   ->  Hash
+         ->  Seq Scan on uqk2
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT pk FROM uqk1 WHERE d NOT in (SELECT d FROM uqk2);
+             QUERY PLAN             
+------------------------------------
+ Seq Scan on uqk1
+   Filter: (NOT (hashed SubPlan 1))
+   SubPlan 1
+     ->  Seq Scan on uqk2
+(4 rows)
+
+-----------------------------------
+-- Test Join: Special OneRow case.
+-----------------------------------
+-- Test Unique Key FOR one-row case, DISTINCT is NOT needed as well.
+-- uqk1.d is the a uniquekey due to onerow rule. uqk2.pk is pk.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.pk;
+            QUERY PLAN             
+-----------------------------------
+ Nested Loop
+   Join Filter: (uqk1.c = uqk2.pk)
+   ->  Seq Scan on uqk1
+         Filter: (pk = 1)
+   ->  Seq Scan on uqk2
+(5 rows)
+
+SELECT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.pk order BY 1;
+ d 
+---
+ 1
+(1 row)
+
+-- Both uqk1.d AND uqk2.c are the a uniquekey due to onerow rule
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1
+AND uqk2.pk = 1 AND uqk1.d = uqk2.d;
+            QUERY PLAN            
+----------------------------------
+ Nested Loop
+   Join Filter: (uqk1.d = uqk2.d)
+   ->  Seq Scan on uqk1
+         Filter: (pk = 1)
+   ->  Seq Scan on uqk2
+         Filter: (pk = 1)
+(6 rows)
+
+SELECT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk2.pk = 1
+AND uqk1.d = uqk2.d order BY 1;
+ d 
+---
+ 1
+(1 row)
+
+-- Both UniqueKey in targetList, so distinct is not needed.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk2.c FROM uqk1, uqk2 WHERE uqk1.pk = 2 AND uqk2.pk = 1;
+        QUERY PLAN        
+--------------------------
+ Nested Loop
+   ->  Seq Scan on uqk1
+         Filter: (pk = 2)
+   ->  Seq Scan on uqk2
+         Filter: (pk = 1)
+(5 rows)
+
+SELECT uqk1.c, uqk2.c FROM uqk1, uqk2 WHERE uqk1.pk = 2 AND uqk2.pk = 1 order BY 1, 2;
+ c | c 
+---+---
+ 2 | 1
+(1 row)
+
+-----------------------------------------
+-- Test more non-unique cases after join.
+-----------------------------------------
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.pk FROM uqk1, uqk2 WHERE uqk1.c = uqk2.c;
+                 QUERY PLAN                 
+--------------------------------------------
+ Unique
+   ->  Sort
+         Sort Key: uqk1.pk
+         ->  Hash Join
+               Hash Cond: (uqk1.c = uqk2.c)
+               ->  Seq Scan on uqk1
+               ->  Hash
+                     ->  Seq Scan on uqk2
+(8 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.c;
+                  QUERY PLAN                  
+----------------------------------------------
+ Unique
+   ->  Sort
+         Sort Key: uqk1.d
+         ->  Nested Loop
+               Join Filter: (uqk1.c = uqk2.c)
+               ->  Seq Scan on uqk1
+                     Filter: (pk = 1)
+               ->  Seq Scan on uqk2
+(8 rows)
+
diff --git a/src/test/regress/sql/uniquekey.sql b/src/test/regress/sql/uniquekey.sql
index a1b538d1c1..3f93872246 100644
--- a/src/test/regress/sql/uniquekey.sql
+++ b/src/test/regress/sql/uniquekey.sql
@@ -27,3 +27,82 @@ EXPLAIN (COSTS OFF) SELECT DISTINCT a FROM uqk1 WHERE c = 1 and d = 1;
 
 -- Test Distinct ON
 EXPLAIN (COSTS OFF) SELECT DISTINCT ON(pk) d FROM uqk1;
+
+------------------------------------------------------
+-- Test UniqueKey on one side still valid after join.
+-----------------------------------------------------
+-- uqk1(c, d) is the uniquekey with mutli nulls at single relation access.
+-- so distinct is not no-op.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.a = uqk2.pk;
+
+-- Both uqk1 (c,d) are a valid uniquekey. 
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.c is NOT NULL AND uqk1.a = uqk2.pk;
+
+-- uqk1.c is null at baserel, but the null values are removed after join.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE  uqk1.a = uqk2.pk and uqk1.c = uqk2.c;
+
+-- uqk1.c is null at baserel, but the null values are removed after join
+-- but new null values are generated due to outer join again. so distinct
+-- is still needed.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1 right join uqk2
+on uqk1.a = uqk2.pk and uqk1.c = uqk2.c;
+
+
+------------------------------------------------------
+-- Test join: Composited UniqueKey
+-----------------------------------------------------
+-- both t1.pk and t1.pk is valid uniquekey.
+EXPLAIN SELECT DISTINCT t1.pk, t2.pk FROM uqk1 t1 cross join uqk2 t2;
+SELECT DISTINCT t1.pk, t2.pk FROM uqk1 t1 cross join uqk2 t2 order by 1, 2;
+
+-- NOT OK, since t1.c includes multi nulls. 
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null;
+SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null order by 1, 2,3;
+SELECT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is null order by 1, 2,3;
+
+-- let's remove the t1.c's multi null values
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null;
+SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null order by 1, 2, 3 ;
+SELECT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null order by 1, 2, 3;
+
+-- test onerow case with composited cases.
+
+-- t2.c is onerow. OK
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+SELECT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null and t2.pk = 1;
+
+-- t2.c is onerow, but t1.c has multi-nulls, NOt OK.
+EXPLAIN SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+SELECT DISTINCT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+SELECT t1.c, t1.d, t2.c FROM uqk1 t1 cross join uqk2 t2 where t1.c is null and t2.pk = 1;
+
+
+-- Test Semi/Anti JOIN
+EXPLAIN (COSTS OFF) SELECT DISTINCT pk FROM uqk1 WHERE d in (SELECT d FROM uqk2);
+EXPLAIN (COSTS OFF) SELECT DISTINCT pk FROM uqk1 WHERE d NOT in (SELECT d FROM uqk2);
+
+-----------------------------------
+-- Test Join: Special OneRow case.
+-----------------------------------
+-- Test Unique Key FOR one-row case, DISTINCT is NOT needed as well.
+-- uqk1.d is the a uniquekey due to onerow rule. uqk2.pk is pk.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.pk;
+SELECT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.pk order BY 1;
+-- Both uqk1.d AND uqk2.c are the a uniquekey due to onerow rule
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1
+AND uqk2.pk = 1 AND uqk1.d = uqk2.d;
+SELECT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk2.pk = 1
+AND uqk1.d = uqk2.d order BY 1;
+-- Both UniqueKey in targetList, so distinct is not needed.
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.c, uqk2.c FROM uqk1, uqk2 WHERE uqk1.pk = 2 AND uqk2.pk = 1;
+SELECT uqk1.c, uqk2.c FROM uqk1, uqk2 WHERE uqk1.pk = 2 AND uqk2.pk = 1 order BY 1, 2;
+
+-----------------------------------------
+-- Test more non-unique cases after join.
+-----------------------------------------
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.pk FROM uqk1, uqk2 WHERE uqk1.c = uqk2.c;
+EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.c;
-- 
2.21.0

From f2d62ff4ceca2a33e557e5fb8bc4a2f6f3de872a Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sun, 25 Jul 2021 16:26:45 +0800
Subject: [PATCH v4 2/6] Just some utils functions.

---
 src/backend/nodes/list.c                | 21 ++++++++++++
 src/backend/optimizer/path/equivclass.c | 45 +++++++++++++++++++++++++
 src/backend/optimizer/util/tlist.c      | 19 +++++++++++
 src/include/nodes/pg_list.h             |  2 ++
 src/include/optimizer/optimizer.h       |  1 +
 src/include/optimizer/paths.h           |  6 ++++
 6 files changed, 94 insertions(+)

diff --git a/src/backend/nodes/list.c b/src/backend/nodes/list.c
index 94fb236daf..8f2b00012e 100644
--- a/src/backend/nodes/list.c
+++ b/src/backend/nodes/list.c
@@ -702,6 +702,27 @@ list_member_oid(const List *list, Oid datum)
 	return false;
 }
 
+
+/*
+ * list_is_subset_ptr - is A a subset of B?
+ */
+bool
+list_is_subset_ptr(const List *a, const List *b)
+{
+	ListCell *lc;
+	Assert(IsPointerList(a));
+	check_list_invariants(a);
+	Assert(IsPointerList(b));
+	check_list_invariants(b);
+	foreach(lc, a)
+	{
+		if (!list_member_ptr(b, lfirst(lc)))
+			return false;
+	}
+	return true;
+}
+
+
 /*
  * Delete the n'th cell (counting from 0) in list.
  *
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 2f688807af..b8ada6e8a3 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -770,6 +770,26 @@ get_eclass_for_sort_expr(PlannerInfo *root,
 	return newec;
 }
 
+/*
+ * find_ec_member_matching_expr
+ *		Locate an EquivalenceClass matching the given expr, if any;
+ *		return NULL if no match.
+ */
+EquivalenceClass *
+find_ec_matching_expr(PlannerInfo *root,
+					  Expr *expr,
+					  RelOptInfo *baserel)
+{
+	int i = -1;
+	while ((i = bms_next_member(baserel->eclass_indexes, i)) >= 0)
+	{
+		EquivalenceClass *ec = list_nth(root->eq_classes, i);
+		if (find_ec_member_matching_expr(ec, expr, baserel->relids))
+			return ec;
+	}
+	return NULL;
+}
+
 /*
  * find_ec_member_matching_expr
  *		Locate an EquivalenceClass member matching the given expr, if any;
@@ -960,6 +980,31 @@ find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
 	return NULL;
 }
 
+
+/*
+ * build_equivalanceclass_list_for_exprs
+ *
+ * 	Given a list of expr, find the related ECs for everyone of them.
+ * if any exprs has no EC related, return NIL.
+ */
+List *
+build_equivalanceclass_list_for_exprs(PlannerInfo *root,
+									  List *exprs,
+									  RelOptInfo *rel)
+{
+	ListCell	*lc;
+	List	*ecs = NIL;
+
+	foreach(lc, exprs)
+	{
+		EquivalenceClass *ec = find_ec_matching_expr(root, lfirst(lc), rel);
+		if (!ec)
+			return NIL;
+		ecs = lappend(ecs, ec);
+	}
+	return ecs;
+}
+
 /*
  * ec_useful_for_merging
  *	check if the ec exists in rel's merageable restrictinfo_lists.
diff --git a/src/backend/optimizer/util/tlist.c b/src/backend/optimizer/util/tlist.c
index 311579d059..5d8493c8d6 100644
--- a/src/backend/optimizer/util/tlist.c
+++ b/src/backend/optimizer/util/tlist.c
@@ -359,6 +359,25 @@ get_sortgroupclause_tle(SortGroupClause *sgClause,
 	return get_sortgroupref_tle(sgClause->tleSortGroupRef, targetList);
 }
 
+/*
+ * get_tle_from_expr
+ *
+ *	Find the targetlist entry matching the given expr and return it.
+ */
+TargetEntry *
+get_tle_from_expr(Expr *expr, List *targetlist)
+{
+	ListCell	*lc;
+
+	foreach(lc, targetlist)
+	{
+		TargetEntry *tle = (TargetEntry *) lfirst(lc);
+
+		if (equal(tle->expr, expr))
+			return tle;
+	}
+	return NULL;
+}
 /*
  * get_sortgroupclause_expr
  *		Find the targetlist entry matching the given SortGroupClause
diff --git a/src/include/nodes/pg_list.h b/src/include/nodes/pg_list.h
index 30f98c4595..bbe0209d7e 100644
--- a/src/include/nodes/pg_list.h
+++ b/src/include/nodes/pg_list.h
@@ -558,6 +558,8 @@ extern bool list_member_ptr(const List *list, const void *datum);
 extern bool list_member_int(const List *list, int datum);
 extern bool list_member_oid(const List *list, Oid datum);
 
+extern bool list_is_subset_ptr(const List *a, const List *b);
+
 extern pg_nodiscard List *list_delete(List *list, void *datum);
 extern pg_nodiscard List *list_delete_ptr(List *list, void *datum);
 extern pg_nodiscard List *list_delete_int(List *list, int datum);
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 41b49b2662..5911014a32 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -171,6 +171,7 @@ extern TargetEntry *get_sortgroupref_tle(Index sortref,
 										 List *targetList);
 extern TargetEntry *get_sortgroupclause_tle(SortGroupClause *sgClause,
 											List *targetList);
+extern TargetEntry *get_tle_from_expr(Expr *expr, List *targetlist);
 extern Node *get_sortgroupclause_expr(SortGroupClause *sgClause,
 									  List *targetList);
 extern List *get_sortgrouplist_exprs(List *sgClauses,
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index a29f616423..e813d82483 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -135,6 +135,9 @@ extern EquivalenceClass *get_eclass_for_sort_expr(PlannerInfo *root,
 												  Index sortref,
 												  Relids rel,
 												  bool create_it);
+extern EquivalenceClass *find_ec_matching_expr(PlannerInfo *root,
+											   Expr *expr,
+											   RelOptInfo *rel);
 extern EquivalenceMember *find_ec_member_matching_expr(EquivalenceClass *ec,
 													   Expr *expr,
 													   Relids relids);
@@ -146,6 +149,9 @@ extern EquivalenceMember *find_computable_ec_member(PlannerInfo *root,
 extern bool ec_useful_for_merging(PlannerInfo *root, RelOptInfo *rel,
 								  EquivalenceClass *ec);
 extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
+extern List *build_equivalanceclass_list_for_exprs(PlannerInfo *root,
+												   List *exprs,
+												   RelOptInfo *rel);
 extern bool relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
 										 EquivalenceClass *ec,
 										 bool require_parallel_safe);
-- 
2.21.0

From ac411f30a1a30f58037234fffc7e88e6f2eb636c Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Mon, 9 Aug 2021 19:10:35 +0800
Subject: [PATCH v4 4/6] Support UniqueKey on BaseRel.

---
 src/backend/optimizer/path/Makefile     |   3 +-
 src/backend/optimizer/path/allpaths.c   |   7 +-
 src/backend/optimizer/path/uniquekey.c  | 400 ++++++++++++++++++++++++
 src/backend/optimizer/plan/planner.c    |  10 +
 src/backend/optimizer/util/relnode.c    |   2 +
 src/include/nodes/nodes.h               |   3 +-
 src/include/nodes/pathnodes.h           |  20 ++
 src/include/optimizer/paths.h           |   4 +
 src/test/regress/expected/join.out      |  11 +-
 src/test/regress/expected/uniquekey.out |  82 +++++
 src/test/regress/parallel_schedule      |   2 +-
 src/test/regress/sql/uniquekey.sql      |  29 ++
 12 files changed, 562 insertions(+), 11 deletions(-)
 create mode 100644 src/backend/optimizer/path/uniquekey.c
 create mode 100644 src/test/regress/expected/uniquekey.out
 create mode 100644 src/test/regress/sql/uniquekey.sql

diff --git a/src/backend/optimizer/path/Makefile b/src/backend/optimizer/path/Makefile
index 1e199ff66f..63cc1505d9 100644
--- a/src/backend/optimizer/path/Makefile
+++ b/src/backend/optimizer/path/Makefile
@@ -21,6 +21,7 @@ OBJS = \
 	joinpath.o \
 	joinrels.o \
 	pathkeys.o \
-	tidpath.o
+	tidpath.o \
+	uniquekey.o
 
 include $(top_srcdir)/src/backend/common.mk
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index a63a3dfe00..343253d694 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -396,6 +396,9 @@ static void
 set_rel_size(PlannerInfo *root, RelOptInfo *rel,
 			 Index rti, RangeTblEntry *rte)
 {
+	/* Set the notnull before the UniqueKey population. */
+	set_baserel_notnull_attrs(rel);
+
 	if (rel->reloptkind == RELOPT_BASEREL &&
 		relation_excluded_by_constraints(root, rel, rte))
 	{
@@ -491,7 +494,7 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
 		}
 	}
 
-	set_baserel_notnull_attrs(rel);
+
 
 	/*
 	 * We insist that all non-dummy rels have a nonzero rowcount estimate.
@@ -616,6 +619,8 @@ set_plain_rel_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
 	 */
 	check_index_predicates(root, rel);
 
+	populate_baserel_uniquekeys(root, rel);
+
 	/* Mark rel with estimated output rows, width, etc */
 	set_baserel_size_estimates(root, rel);
 }
diff --git a/src/backend/optimizer/path/uniquekey.c b/src/backend/optimizer/path/uniquekey.c
new file mode 100644
index 0000000000..c93075656d
--- /dev/null
+++ b/src/backend/optimizer/path/uniquekey.c
@@ -0,0 +1,400 @@
+/*-------------------------------------------------------------------------
+ *
+ * uniquekey.c
+ *	  Utilities for maintaining uniquekey.
+ *
+ *
+ * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ *	  src/backend/optimizer/path/uniquekey.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/sysattr.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/pathnodes.h"
+#include "optimizer/optimizer.h"
+#include "optimizer/paths.h"
+
+
+/* Functions to populate UniqueKey */
+static bool add_uniquekey_for_uniqueindex(PlannerInfo *root,
+										  IndexOptInfo *unique_index,
+										  List *mergeable_const_peer,
+										  List *expr_opfamilies);
+
+/* UniqueKey is subset of .. */
+static bool uniquekey_contains_in(PlannerInfo *root, UniqueKey *ukey,
+								  List *ecs, Relids relids);
+
+/* Avoid useless UniqueKey. */
+static bool unique_ecs_useful_for_distinct(PlannerInfo *root, List *ecs);
+static bool unique_ecs_useful_for_merging(PlannerInfo *root, RelOptInfo *rel,
+										  List *unique_ecs);
+/* Helper functions to create UniqueKey. */
+static UniqueKey *make_uniquekey(Bitmapset *unique_expr_indexes,
+								 bool multi_null,
+								 bool useful_for_distinct);
+static void mark_rel_singlerow(PlannerInfo *root, RelOptInfo *rel);
+
+/* Debug only */
+static void print_uniquekey(PlannerInfo *root, RelOptInfo *rel);
+
+/*
+ * populate_baserel_uniquekeys
+ */
+void
+populate_baserel_uniquekeys(PlannerInfo *root, RelOptInfo *rel)
+{
+	ListCell	*lc;
+	List	*mergeable_const_peer = NIL, *expr_opfamilies = NIL;
+
+	/*
+	 * ColX = {Const} AND ColY = {Const2} AND ColZ > {Const3},
+	 * gather ColX and ColY into mergeable_const_peer.
+	 */
+	foreach(lc, rel->baserestrictinfo)
+	{
+		RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
+
+		if (rinfo->mergeopfamilies == NIL)
+			continue;
+
+		if (bms_is_empty(rinfo->left_relids))
+			mergeable_const_peer = lappend(mergeable_const_peer, get_rightop(rinfo->clause));
+		else if (bms_is_empty(rinfo->right_relids))
+			mergeable_const_peer = lappend(mergeable_const_peer, get_leftop(rinfo->clause));
+		else
+			continue;
+		expr_opfamilies = lappend(expr_opfamilies, rinfo->mergeopfamilies);
+	}
+
+	foreach(lc, rel->indexlist)
+	{
+		IndexOptInfo *index = (IndexOptInfo *)lfirst(lc);
+		if (!index->unique || !index->immediate ||
+			(index->indpred != NIL && !index->predOK))
+			continue;
+
+		if (add_uniquekey_for_uniqueindex(root, index,
+										  mergeable_const_peer,
+										  expr_opfamilies))
+			/* Find a singlerow case, no need to go through any more. */
+			return;
+	}
+
+	print_uniquekey(root, rel);
+}
+
+/*
+ * relation_is_distinct_for
+ *		Check if the relation is distinct for.
+ */
+bool
+relation_is_distinct_for(PlannerInfo *root, RelOptInfo *rel, List *distinct_pathkey)
+{
+	ListCell	*lc;
+	List	*lecs = NIL;
+	Relids	relids = NULL;
+	foreach(lc, distinct_pathkey)
+	{
+		PathKey *pathkey = lfirst(lc);
+		lecs = lappend(lecs, pathkey->pk_eclass);
+		/*
+		 * Note that ec_relids doesn't include child member, but
+		 * distinct would not operate on childrel as well.
+		 */
+		relids = bms_union(relids, pathkey->pk_eclass->ec_relids);
+	}
+
+	foreach(lc, rel->uniquekeys)
+	{
+		UniqueKey *ukey = lfirst(lc);
+		if (ukey->multi_nulls)
+			continue;
+
+		if (uniquekey_contains_in(root, ukey, lecs, relids))
+			return true;
+	}
+	return false;
+}
+
+/*
+ * add_uniquekey_for_uniqueindex
+ *	 populate a UniqueKey if necessary, return true iff the UniqueKey is an
+ * SingleRow.
+ */
+static bool
+add_uniquekey_for_uniqueindex(PlannerInfo *root, IndexOptInfo *unique_index,
+							  List *mergeable_const_peer, List *expr_opfamilies)
+{
+	List	*unique_exprs = NIL, *unique_ecs = NIL;
+	ListCell	*indexpr_item;
+	int	c = 0;
+	RelOptInfo *rel = unique_index->rel;
+	bool	multinull = false;
+	bool	used_for_distinct = false;
+	Bitmapset *unique_exprs_index;
+
+	indexpr_item = list_head(unique_index->indexprs);
+	/* Gather all the non-const exprs */
+	for (c = 0; c < unique_index->nkeycolumns; c++)
+	{
+		int attr = unique_index->indexkeys[c];
+		Expr *expr;
+		bool	matched_const = false;
+		ListCell	*lc1, *lc2;
+		if (attr > 0)
+		{
+			Var *var;
+			expr = list_nth_node(TargetEntry, unique_index->indextlist, c)->expr;
+			var = castNode(Var, expr);
+			Assert(IsA(expr, Var));
+			if (!bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber,
+							  rel->notnull_attrs[0]))
+				multinull = true;
+		}
+		else if (attr == 0)
+		{
+			/* Expression index */
+			expr = lfirst(indexpr_item);
+			indexpr_item = lnext(unique_index->indexprs, indexpr_item);
+			/* We can't grantee an FuncExpr will not return NULLs */
+			multinull = true;
+		}
+		else /* attr < 0 */
+		{
+			/* Index on OID is possible, not handle it for now. */
+			return false;
+		}
+
+		/*
+		 * Check index_col = Const case with regarding to opfamily checking
+		 * If so, we can remove the index_col from the final UniqueKey->exprs.
+		 */
+		forboth(lc1, mergeable_const_peer, lc2, expr_opfamilies)
+		{
+			if (list_member_oid((List *) lfirst(lc2), unique_index->opfamily[c]) &&
+				match_index_to_operand((Node *) lfirst(lc1), c, unique_index))
+			{
+				matched_const = true;
+				break;
+			}
+		}
+
+		if (matched_const)
+			continue;
+
+		unique_exprs = lappend(unique_exprs, expr);
+	}
+
+	if (unique_exprs == NIL)
+	{
+		/*
+		 * SingleRow case. Checking if it is useful is ineffective
+		 * so just keep it.
+		 */
+		mark_rel_singlerow(root, rel);
+		return true;
+	}
+
+	unique_ecs = build_equivalanceclass_list_for_exprs(root, unique_exprs, rel);
+
+	if (unique_ecs == NIL)
+	{
+		/* It is neither used in distinct_pathkey nor mergeable clause */
+		return false;
+	}
+
+	/*
+	 * Check if we need to setup the UniqueKey and set the used_for_distinct accordingly.
+	 */
+	if (unique_ecs_useful_for_distinct(root, unique_ecs))
+	{
+		used_for_distinct = true;
+	}
+	else if (!unique_ecs_useful_for_merging(root, rel, unique_ecs))
+		/*
+		 * Neither used in distinct pathkey nor used in mergeable clause.
+		 * this is possible even if unique_ecs != NIL.
+		 */
+		return false;
+	else
+	{
+		/*
+		 * unique_ecs_useful_for_merging(root, rel, unique_ecs) is true,
+		 * we did nothing in this case.
+		 */
+	}
+	unique_exprs_index = bms_make_singleton(list_length(root->unique_exprs));
+	root->unique_exprs = lappend(root->unique_exprs, unique_ecs);
+	rel->uniquekeys = lappend(rel->uniquekeys,
+							  make_uniquekey(unique_exprs_index,
+											 multinull,
+											 used_for_distinct));
+	return false;
+}
+/*
+ * uniquekey_contains_in
+ *	Return if UniqueKey contains in the list of EquivalenceClass
+ * or the UniqueKey's SingleRow contains in relids.
+ *
+ */
+static bool
+uniquekey_contains_in(PlannerInfo *root, UniqueKey *ukey, List *ecs, Relids relids)
+{
+	int i = -1;
+	while ((i = bms_next_member(ukey->unique_expr_indexes, i)) >= 0)
+	{
+		Node *exprs = list_nth(root->unique_exprs, i);
+		if (IsA(exprs, SingleRow))
+		{
+			SingleRow *singlerow = castNode(SingleRow, exprs);
+			if (!bms_is_member(singlerow->relid, relids))
+				/*
+				 * UniqueKey request a ANY expr on relid on the relid(which
+				 * indicates we don't have other EquivalenceClass for this
+				 * relation), but the relid doesn't contains in relids, which
+				 * indicate there is no such Expr in target, then we are sure
+				 * to return false.
+				 */
+				return false;
+			else
+			{
+				/*
+				 * We have SingleRow on relid, and the relid is in relids.
+				 * We don't need to check any more for this expr. This is
+				 * right for sure.
+				 */
+			}
+		}
+		else
+		{
+			Assert(IsA(exprs, List));
+			if (!list_is_subset_ptr((List *)exprs, ecs))
+				return false;
+		}
+	}
+	return true;
+}
+
+/*
+ * unique_ecs_useful_for_distinct
+ *	return true if all the EquivalenceClass in ecs exists in root->distinct_pathkey.
+ */
+static bool
+unique_ecs_useful_for_distinct(PlannerInfo *root, List *ecs)
+{
+	ListCell *lc;
+	foreach(lc, ecs)
+	{
+		EquivalenceClass *ec = lfirst_node(EquivalenceClass, lc);
+		ListCell *p;
+		bool found = false;
+		foreach(p,  root->distinct_pathkeys)
+		{
+			PathKey *pathkey = lfirst_node(PathKey, p);
+			/*
+			 * Both of them should point to an element in root->eq_classes.
+			 * so the address should be same. and equal function doesn't
+			 * support EquivalenceClass yet.
+			 */
+			if (ec == pathkey->pk_eclass)
+			{
+				found = true;
+				break;
+			}
+		}
+		if (!found)
+			return false;
+	}
+	return true;
+}
+
+/*
+ * unique_ecs_useful_for_merging
+ *	return true if all the unique_ecs exists in rel's join restrictInfo.
+ */
+static bool
+unique_ecs_useful_for_merging(PlannerInfo *root, RelOptInfo *rel, List *unique_ecs)
+{
+	ListCell	*lc;
+
+	foreach(lc, unique_ecs)
+	{
+		EquivalenceClass *ec = lfirst(lc);
+		if (!ec_useful_for_merging(root, rel, ec))
+			return false;
+	}
+
+	return true;
+}
+/*
+ *	make_uniquekey
+ */
+static UniqueKey *
+make_uniquekey(Bitmapset *unique_expr_indexes, bool multi_null, bool useful_for_distinct)
+{
+	UniqueKey *ukey = makeNode(UniqueKey);
+	ukey->unique_expr_indexes = unique_expr_indexes;
+	ukey->multi_nulls = multi_null;
+	ukey->use_for_distinct = useful_for_distinct;
+	return ukey;
+}
+
+/*
+ * mark_rel_singlerow
+ *	mark a relation as singlerow.
+ */
+static void
+mark_rel_singlerow(PlannerInfo *root, RelOptInfo *rel)
+{
+	int exprs_pos = list_length(root->unique_exprs);
+	Bitmapset *unique_exprs_index = bms_make_singleton(exprs_pos);
+	SingleRow *singlerow = makeNode(SingleRow);
+	singlerow->relid = rel->relid;
+	root->unique_exprs = lappend(root->unique_exprs, singlerow);
+	rel->uniquekeys = list_make1(make_uniquekey(unique_exprs_index,
+												false /* multi-null */,
+												true /* arbitrary decision */));
+}
+
+/*
+ * print_uniquekey
+ *	Used for easier reivew, should be removed before commit.
+ */
+static void
+print_uniquekey(PlannerInfo *root, RelOptInfo *rel)
+{
+	if (false)
+	{
+		ListCell	*lc;
+		elog(INFO, "Rel = %s", bmsToString(rel->relids));
+		foreach(lc, rel->uniquekeys)
+		{
+			UniqueKey *ukey = lfirst_node(UniqueKey, lc);
+			int i = -1;
+			elog(INFO, "UNIQUEKEY{indexes=%s, multinull=%d}",
+				 bmsToString(ukey->unique_expr_indexes),
+				 ukey->multi_nulls
+				);
+
+			while ((i = bms_next_member(ukey->unique_expr_indexes, i)) >= 0)
+			{
+				Node *node = (Node *) list_nth(root->unique_exprs, i);
+				if (IsA(node, SingleRow))
+					elog(INFO,
+						 "Expr(%d) SingleRow{relid = %d}",
+						 i, castNode(SingleRow, node)->relid);
+				else
+					elog(INFO,
+						 "EC(%d), %s", i, nodeToString(node)
+						);
+			}
+		}
+	}
+}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 2cd691191c..ed2cff00fc 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -4238,6 +4238,16 @@ create_distinct_paths(PlannerInfo *root,
 	Path	   *path;
 	ListCell   *lc;
 
+	/*
+	 * distinct_pathkeys may be NIL if it distinctClause is sortable.
+	 * see standard_qp_callback. But for the efficiency of relation_is_distinct_for
+	 * we can't use distinctClause (rather than EC) here. Fortunately not sortable
+	 * clause is rare in real case.
+	 */
+	if (root->distinct_pathkeys &&
+		relation_is_distinct_for(root, input_rel, root->distinct_pathkeys))
+		return input_rel;
+
 	/* For now, do all work in the (DISTINCT, NULL) upperrel */
 	distinct_rel = fetch_upper_rel(root, UPPERREL_DISTINCT, NULL);
 
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index f017a7d52d..b75e1679e6 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -261,6 +261,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
 	rel->partexprs = NULL;
 	rel->nullable_partexprs = NULL;
 	rel->notnull_attrs = palloc0(sizeof(Bitmapset *) * 1);
+	rel->uniquekeys = NIL;
 
 	/*
 	 * Pass assorted information down the inheritance hierarchy.
@@ -752,6 +753,7 @@ build_join_rel(PlannerInfo *root,
 	joinrel->partexprs = NULL;
 	joinrel->nullable_partexprs = NULL;
 	joinrel->notnull_attrs = palloc0(sizeof(Bitmapset *) * (bms_max_member(joinrel->relids) + 1));
+	joinrel->uniquekeys = NIL;
 
 	/* Compute information relevant to the foreign relations. */
 	set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 6a4d82f0a8..1bf220f373 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -280,7 +280,8 @@ typedef enum NodeTag
 	T_RollupData,
 	T_GroupingSetData,
 	T_StatisticExtInfo,
-
+	T_UniqueKey,
+	T_SingleRow,
 	/*
 	 * TAGS FOR MEMORY NODES (memnodes.h)
 	 */
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 7bf1896e12..3581b03a75 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -246,6 +246,7 @@ struct PlannerInfo
 									 * subquery outputs */
 
 	List	   *eq_classes;		/* list of active EquivalenceClasses */
+	List	   *unique_exprs;		/* List of unique expr */
 
 	bool		ec_merging_done;	/* set true once ECs are canonical */
 
@@ -691,6 +692,7 @@ typedef struct RelOptInfo
 								  * the len would always 1. and for others the array
 								  * index is relid from relids.
 								  */
+	List		*uniquekeys; /* A list of UniqueKey. */
 
 	/* materialization information */
 	List	   *pathlist;		/* Path structures */
@@ -1070,6 +1072,24 @@ typedef struct PathKey
 	bool		pk_nulls_first; /* do NULLs come before normal values? */
 } PathKey;
 
+
+typedef struct UnqiueKey
+{
+	NodeTag	type;
+	Bitmapset	*unique_expr_indexes;
+	bool	multi_nulls;
+	bool	use_for_distinct;  /* true if it is used in distinct-pathkey, in this case
+								* we would never check if we should discard it during
+								* join search.
+								*/
+} UniqueKey;
+
+typedef struct SingleRow
+{
+	NodeTag	type;
+	Index		relid;
+} SingleRow;
+
 /*
  * VolatileFunctionStatus -- allows nodes to cache their
  * contain_volatile_functions properties. VOLATILITY_UNKNOWN means not yet
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index e813d82483..68b8b40ca9 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -263,4 +263,8 @@ extern PathKey *make_canonical_pathkey(PlannerInfo *root,
 extern void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 									List *live_childrels);
 
+extern void populate_baserel_uniquekeys(PlannerInfo *root,
+										RelOptInfo *baserel);
+extern bool relation_is_distinct_for(PlannerInfo *root, RelOptInfo *rel,
+									 List *distinct_pathkey);
 #endif							/* PATHS_H */
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index f3589d0dbb..db72374302 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4612,18 +4612,15 @@ select d.* from d left join (select * from b group by b.id, b.c_id) s
 explain (costs off)
 select d.* from d left join (select distinct * from b) s
   on d.a = s.id;
-              QUERY PLAN              
---------------------------------------
+             QUERY PLAN             
+------------------------------------
  Merge Right Join
    Merge Cond: (b.id = d.a)
-   ->  Unique
-         ->  Sort
-               Sort Key: b.id, b.c_id
-               ->  Seq Scan on b
+   ->  Index Scan using b_pkey on b
    ->  Sort
          Sort Key: d.a
          ->  Seq Scan on d
-(9 rows)
+(6 rows)
 
 -- check join removal works when uniqueness of the join condition is enforced
 -- by a UNION
diff --git a/src/test/regress/expected/uniquekey.out b/src/test/regress/expected/uniquekey.out
new file mode 100644
index 0000000000..d9a8634e80
--- /dev/null
+++ b/src/test/regress/expected/uniquekey.out
@@ -0,0 +1,82 @@
+CREATE TABLE uqk1(a int, pk int primary key, c int,  d int);
+CREATE TABLE uqk2(a int, pk int primary key, c int,  d int);
+INSERT INTO uqk1 VALUES(1, 1, 1, 1), (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, null, 4), (5, 5, null, 4);
+INSERT INTO uqk2 VALUES(1, 1, 1, 1), (4, 4, 4, 4), (5, 5, 5, 5);
+ANALYZE uqk1;
+ANALYZE uqk2;
+-- Test single table primary key.
+EXPLAIN (COSTS OFF) SELECT DISTINCT * FROM uqk1;
+    QUERY PLAN    
+------------------
+ Seq Scan on uqk1
+(1 row)
+
+-- Test EC case.
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE d = pk;
+     QUERY PLAN     
+--------------------
+ Seq Scan on uqk1
+   Filter: (d = pk)
+(2 rows)
+
+-- Test UniqueKey indexes.
+CREATE UNIQUE INDEX uqk1_ukcd ON uqk1(c, d);
+-- Test not null quals and not null per catalog.
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1;
+       QUERY PLAN       
+------------------------
+ HashAggregate
+   Group Key: c, d
+   ->  Seq Scan on uqk1
+(3 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1 WHERE c is NOT NULL;
+              QUERY PLAN               
+---------------------------------------
+ Unique
+   ->  Sort
+         Sort Key: c, d
+         ->  Seq Scan on uqk1
+               Filter: (c IS NOT NULL)
+(5 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE c = 1;
+          QUERY PLAN           
+-------------------------------
+ Unique
+   ->  Sort
+         Sort Key: d
+         ->  Seq Scan on uqk1
+               Filter: (c = 1)
+(5 rows)
+
+ALTER TABLE uqk1 ALTER COLUMN d SET NOT NULL;
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1 WHERE c is NOT NULL;
+        QUERY PLAN         
+---------------------------
+ Seq Scan on uqk1
+   Filter: (c IS NOT NULL)
+(2 rows)
+
+-- Test UniqueKey column reduction.
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE c = 1;
+    QUERY PLAN     
+-------------------
+ Seq Scan on uqk1
+   Filter: (c = 1)
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT DISTINCT a FROM uqk1 WHERE c = 1 and d = 1;
+           QUERY PLAN            
+---------------------------------
+ Seq Scan on uqk1
+   Filter: ((c = 1) AND (d = 1))
+(2 rows)
+
+-- Test Distinct ON
+EXPLAIN (COSTS OFF) SELECT DISTINCT ON(pk) d FROM uqk1;
+    QUERY PLAN    
+------------------
+ Seq Scan on uqk1
+(1 row)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 7be89178f0..41454448f1 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -72,7 +72,7 @@ test: sanity_check
 # ----------
 ignore: random
 test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index update delete namespace prepared_xacts
-
+test: uniquekey
 # ----------
 # Another group of parallel tests
 # ----------
diff --git a/src/test/regress/sql/uniquekey.sql b/src/test/regress/sql/uniquekey.sql
new file mode 100644
index 0000000000..a1b538d1c1
--- /dev/null
+++ b/src/test/regress/sql/uniquekey.sql
@@ -0,0 +1,29 @@
+CREATE TABLE uqk1(a int, pk int primary key, c int,  d int);
+CREATE TABLE uqk2(a int, pk int primary key, c int,  d int);
+INSERT INTO uqk1 VALUES(1, 1, 1, 1), (2, 2, 2, 2), (3, 3, 3, 3), (4, 4, null, 4), (5, 5, null, 4);
+INSERT INTO uqk2 VALUES(1, 1, 1, 1), (4, 4, 4, 4), (5, 5, 5, 5);
+ANALYZE uqk1;
+ANALYZE uqk2;
+
+-- Test single table primary key.
+EXPLAIN (COSTS OFF) SELECT DISTINCT * FROM uqk1;
+
+-- Test EC case.
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE d = pk;
+
+-- Test UniqueKey indexes.
+CREATE UNIQUE INDEX uqk1_ukcd ON uqk1(c, d);
+
+-- Test not null quals and not null per catalog.
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1;
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1 WHERE c is NOT NULL;
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE c = 1;
+ALTER TABLE uqk1 ALTER COLUMN d SET NOT NULL;
+EXPLAIN (COSTS OFF) SELECT DISTINCT c, d FROM uqk1 WHERE c is NOT NULL;
+
+-- Test UniqueKey column reduction.
+EXPLAIN (COSTS OFF) SELECT DISTINCT d FROM uqk1 WHERE c = 1;
+EXPLAIN (COSTS OFF) SELECT DISTINCT a FROM uqk1 WHERE c = 1 and d = 1;
+
+-- Test Distinct ON
+EXPLAIN (COSTS OFF) SELECT DISTINCT ON(pk) d FROM uqk1;
-- 
2.21.0

From 32886c5b14709d1f7ad27e5e024c3e083b97026c Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sun, 25 Jul 2021 16:28:30 +0800
Subject: [PATCH v4 1/6] Just refactor pathkeys_useful_for_merging, split a new
 function

ec_useful_for_merging which is useful for UniqueKey as well.
---
 src/backend/optimizer/path/equivclass.c | 42 +++++++++++++++++++++++++
 src/backend/optimizer/path/pathkeys.c   | 33 +------------------
 src/include/optimizer/paths.h           |  2 ++
 3 files changed, 45 insertions(+), 32 deletions(-)

diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 6f1abbe47d..2f688807af 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -960,6 +960,48 @@ find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel)
 	return NULL;
 }
 
+/*
+ * ec_useful_for_merging
+ *	check if the ec exists in rel's merageable restrictinfo_lists.
+ */
+bool
+ec_useful_for_merging(PlannerInfo *root, RelOptInfo *rel,
+					  EquivalenceClass *ec)
+{
+	/*
+	 * First look into the EquivalenceClass to see if there are any members
+	 * not yet joined to the rel.
+	 */
+	if (rel->has_eclass_joins &&
+		eclass_useful_for_merging(root, ec, rel))
+		return true;
+	else
+	{
+		/*
+		 * Otherwise search the rel's joininfo list, which contains
+		 * non-EquivalenceClass-derivable join clauses that might
+		 * nonetheless be mergejoinable.
+		 */
+		ListCell	*j;
+		foreach(j, rel->joininfo)
+		{
+			RestrictInfo *restrictinfo = (RestrictInfo *) lfirst(j);
+
+			if (restrictinfo->mergeopfamilies == NIL)
+				continue;
+			update_mergeclause_eclasses(root, restrictinfo);
+
+			if (ec == restrictinfo->left_ec ||
+			    ec == restrictinfo->right_ec)
+			{
+				return true;
+			}
+		}
+	}
+
+	return false;
+}
+
 /*
  * relation_can_be_sorted_early
  *		Can this relation be sorted on this EC before the final output step?
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 216dd26385..21f4f2d4bc 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -1751,43 +1751,12 @@ pathkeys_useful_for_merging(PlannerInfo *root, RelOptInfo *rel, List *pathkeys)
 	{
 		PathKey    *pathkey = (PathKey *) lfirst(i);
 		bool		matched = false;
-		ListCell   *j;
 
 		/* If "wrong" direction, not useful for merging */
 		if (!right_merge_direction(root, pathkey))
 			break;
 
-		/*
-		 * First look into the EquivalenceClass of the pathkey, to see if
-		 * there are any members not yet joined to the rel.  If so, it's
-		 * surely possible to generate a mergejoin clause using them.
-		 */
-		if (rel->has_eclass_joins &&
-			eclass_useful_for_merging(root, pathkey->pk_eclass, rel))
-			matched = true;
-		else
-		{
-			/*
-			 * Otherwise search the rel's joininfo list, which contains
-			 * non-EquivalenceClass-derivable join clauses that might
-			 * nonetheless be mergejoinable.
-			 */
-			foreach(j, rel->joininfo)
-			{
-				RestrictInfo *restrictinfo = (RestrictInfo *) lfirst(j);
-
-				if (restrictinfo->mergeopfamilies == NIL)
-					continue;
-				update_mergeclause_eclasses(root, restrictinfo);
-
-				if (pathkey->pk_eclass == restrictinfo->left_ec ||
-					pathkey->pk_eclass == restrictinfo->right_ec)
-				{
-					matched = true;
-					break;
-				}
-			}
-		}
+		matched = ec_useful_for_merging(root, rel, pathkey->pk_eclass);
 
 		/*
 		 * If we didn't find a mergeclause, we're done --- any additional
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index f1d111063c..a29f616423 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -143,6 +143,8 @@ extern EquivalenceMember *find_computable_ec_member(PlannerInfo *root,
 													List *exprs,
 													Relids relids,
 													bool require_parallel_safe);
+extern bool ec_useful_for_merging(PlannerInfo *root, RelOptInfo *rel,
+								  EquivalenceClass *ec);
 extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
 extern bool relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
 										 EquivalenceClass *ec,
-- 
2.21.0

From 867dbc9e866ce149b07ff55631598e0843046b61 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sat, 3 Jul 2021 21:06:32 +0800
Subject: [PATCH v4 3/6] add the not null attrs for RelOptInfo. Here is how it
 works.

For baserel, it records the notnull attrs as a bitmapset and store it to
RelOptInfo->notnull_attrs[0].  As for the joinrel, suppose the relids is {1,3,
5}, then the notnull_attrs[1/3/5] will be used to store notnull_attrs bitmapset
for relation 1,3,5 separately. I don't handle this stuff for all kinds of upper
relation and subquery so far since it doesn't pass the design review yet.
---
 notnulltest.sql                       | 24 ++++++++
 src/backend/nodes/bitmapset.c         | 14 +++++
 src/backend/optimizer/path/allpaths.c | 36 ++++++++++++
 src/backend/optimizer/util/plancat.c  |  9 +++
 src/backend/optimizer/util/relnode.c  | 80 +++++++++++++++++++++++++++
 src/include/nodes/bitmapset.h         |  1 +
 src/include/nodes/pathnodes.h         |  6 ++
 7 files changed, 170 insertions(+)
 create mode 100644 notnulltest.sql

diff --git a/notnulltest.sql b/notnulltest.sql
new file mode 100644
index 0000000000..2a36bd0c7f
--- /dev/null
+++ b/notnulltest.sql
@@ -0,0 +1,24 @@
+create table t1(a int, b int not null, c int, d int);
+create table t2(a int, b int not null, c int, d int);
+
+-- single rel
+select * from t1;
+select * from t1 where a > 1;
+select * from t2 where a > 1 or c > 1;
+
+-- partitioned relation.
+create table p (a int, b int, c int not null) partition by range(a);
+create table p_1 partition of p for values from (0) to (10000) partition by list(b);
+create table p_1_1(b int,  c int not null, a int);
+alter table p_1 attach partition p_1_1 for values in (1);
+
+
+select * from p;
+select * from p where a > 1;
+
+
+-- test join:
+select * from t1, t2 where t1.a = t2.c;
+select t1.a, t2.b, t2.c from t1 left join t2 on t1.a = t2.c;
+select * from t1 full join t2 on t1.a = t2.c;
+
diff --git a/src/backend/nodes/bitmapset.c b/src/backend/nodes/bitmapset.c
index 649478b0d4..fa71f36aaf 100644
--- a/src/backend/nodes/bitmapset.c
+++ b/src/backend/nodes/bitmapset.c
@@ -663,6 +663,20 @@ bms_num_members(const Bitmapset *a)
 	return result;
 }
 
+/*
+ * bms_max_member - the max member in this bitmap.
+ */
+int
+bms_max_member(const Bitmapset *a)
+{
+	int result;
+	if (a == NULL || bms_is_empty(a))
+		elog(ERROR, "Must be an non-empty bitmapset.");
+	result = (a->nwords - 1) * BITS_PER_BITMAPWORD;
+	result += bmw_leftmost_one_pos(a->words[a->nwords - 1]);
+	return result;
+}
+
 /*
  * bms_membership - does a set have zero, one, or multiple members?
  *
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 296dd75c1b..a63a3dfe00 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -354,6 +354,40 @@ set_base_rel_pathlists(PlannerInfo *root)
 	}
 }
 
+/*
+ * set_baserel_notnull_attrs
+ *
+ *	Set baserel's notnullattrs based on baserestrictinfo
+ */
+static void
+set_baserel_notnull_attrs(RelOptInfo *rel)
+{
+	List *clauses = extract_actual_clauses(rel->baserestrictinfo, false);
+	ListCell	*lc;
+	foreach(lc, find_nonnullable_vars((Node *)clauses))
+	{
+		Var *var = (Var *) lfirst(lc);
+		if (var->varno != rel->relid)
+		{
+			/* Lateral Join */
+			continue;
+		}
+		Assert(var->varno == rel->relid);
+		rel->notnull_attrs[0] = bms_add_member(rel->notnull_attrs[0],
+											   var->varattno - FirstLowInvalidHeapAttributeNumber);
+	}
+
+	/* Debug Only, Will be removed at last. */
+	if (false)
+	{
+		elog(INFO, "FirstLowInvalidHeapAttributeNumber = %d, BaseRel(%d), notnull_attrs = %s",
+			 FirstLowInvalidHeapAttributeNumber,
+			 rel->relid,
+			 bmsToString(rel->notnull_attrs[0])
+			);
+	}
+}
+
 /*
  * set_rel_size
  *	  Set size estimates for a base relation
@@ -457,6 +491,8 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
 		}
 	}
 
+	set_baserel_notnull_attrs(rel);
+
 	/*
 	 * We insist that all non-dummy rels have a nonzero rowcount estimate.
 	 */
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index c5194fdbbf..7d3b40090e 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -117,6 +117,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	Relation	relation;
 	bool		hasindex;
 	List	   *indexinfos = NIL;
+	int		i;
 
 	/*
 	 * We need not lock the relation since it was already locked, either by
@@ -471,6 +472,14 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
 	if (inhparent && relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
 		set_relation_partition_info(root, rel, relation);
 
+	for (i = 0; i < relation->rd_att->natts; i++)
+	{
+		FormData_pg_attribute attr = relation->rd_att->attrs[i];
+		if (attr.attnotnull)
+			rel->notnull_attrs[0] = bms_add_member(rel->notnull_attrs[0],
+												   attr.attnum - FirstLowInvalidHeapAttributeNumber);
+	}
+
 	table_close(relation, NoLock);
 
 	/*
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 47769cea45..f017a7d52d 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -16,6 +16,7 @@
 
 #include <limits.h>
 
+#include "access/sysattr.h"
 #include "miscadmin.h"
 #include "nodes/nodeFuncs.h"
 #include "optimizer/appendinfo.h"
@@ -259,6 +260,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
 	rel->all_partrels = NULL;
 	rel->partexprs = NULL;
 	rel->nullable_partexprs = NULL;
+	rel->notnull_attrs = palloc0(sizeof(Bitmapset *) * 1);
 
 	/*
 	 * Pass assorted information down the inheritance hierarchy.
@@ -557,6 +559,81 @@ add_join_rel(PlannerInfo *root, RelOptInfo *joinrel)
 	}
 }
 
+static void
+copy_notnull_attrs_to_joinrel(RelOptInfo *joinrel, RelOptInfo *rel)
+{
+	int relid;
+	if (bms_get_singleton_member(rel->relids, &relid))
+		joinrel->notnull_attrs[relid] = bms_copy(rel->notnull_attrs[0]);
+	else
+	{
+		relid = -1;
+		while ((relid = bms_next_member(rel->relids, relid)) >= 0)
+			joinrel->notnull_attrs[relid] = bms_copy(rel->notnull_attrs[relid]);
+	}
+}
+
+/*
+ *
+ */
+static void
+set_joinrel_notnull_attrs(RelOptInfo *joinrel,
+						  RelOptInfo *outer_rel,
+						  RelOptInfo *inner_rel,
+						  List *restrictlist,
+						  SpecialJoinInfo *sjinfo)
+{
+	if (sjinfo->jointype == JOIN_FULL)
+		/* Both sides are nullable. */
+		return;
+	/* If it is not FULL join, the outer side is not changed. */
+	copy_notnull_attrs_to_joinrel(joinrel, outer_rel);
+	switch(sjinfo->jointype)
+	{
+		case JOIN_ANTI:
+		case JOIN_SEMI:
+		case JOIN_INNER:
+			copy_notnull_attrs_to_joinrel(joinrel, inner_rel);
+			{
+				ListCell	*lc;
+				List *clauses = extract_actual_clauses(restrictlist, false);
+				foreach(lc, find_nonnullable_vars((Node *) clauses))
+				{
+					Var *var = lfirst_node(Var, lc);
+					if (!bms_is_member(var->varno, joinrel->relids))
+					{
+						/* lateral join */
+						continue;
+					}
+					joinrel->notnull_attrs[var->varno] = bms_add_member(
+						joinrel->notnull_attrs[var->varno],
+						var->varattno - FirstLowInvalidHeapAttributeNumber);
+				}
+			}
+			break;
+		case JOIN_LEFT:
+			break;
+		default:
+			elog(ERROR, "Unexpected join type %d", sjinfo->jointype);
+	}
+	/* Debug Only, will be removed at last. */
+	if (false)
+	{
+		int relid = -1;
+		int eLevel = INFO;
+		elog(eLevel, "Dump notnull for JoinRel(%s)", bmsToString(joinrel->relids));
+		while((relid = bms_next_member(joinrel->relids, relid)) >= 0)
+		{
+			Bitmapset *notnullattrs = joinrel->notnull_attrs[relid];
+			if (notnullattrs != NULL)
+				elog(eLevel, "FirstLowInvalidHeapAttributeNumber = %d, RELID = (%d), notnull_attrs: %s",
+					 FirstLowInvalidHeapAttributeNumber,
+					 relid,
+					 bmsToString(notnullattrs));
+		}
+	}
+
+}
 /*
  * build_join_rel
  *	  Returns relation entry corresponding to the union of two given rels,
@@ -674,6 +751,7 @@ build_join_rel(PlannerInfo *root,
 	joinrel->all_partrels = NULL;
 	joinrel->partexprs = NULL;
 	joinrel->nullable_partexprs = NULL;
+	joinrel->notnull_attrs = palloc0(sizeof(Bitmapset *) * (bms_max_member(joinrel->relids) + 1));
 
 	/* Compute information relevant to the foreign relations. */
 	set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
@@ -765,6 +843,8 @@ build_join_rel(PlannerInfo *root,
 			lappend(root->join_rel_level[root->join_cur_level], joinrel);
 	}
 
+	set_joinrel_notnull_attrs(joinrel, outer_rel, inner_rel, restrictlist, sjinfo);
+
 	return joinrel;
 }
 
diff --git a/src/include/nodes/bitmapset.h b/src/include/nodes/bitmapset.h
index 1fd12de698..303873a546 100644
--- a/src/include/nodes/bitmapset.h
+++ b/src/include/nodes/bitmapset.h
@@ -94,6 +94,7 @@ extern bool bms_nonempty_difference(const Bitmapset *a, const Bitmapset *b);
 extern int	bms_singleton_member(const Bitmapset *a);
 extern bool bms_get_singleton_member(const Bitmapset *a, int *member);
 extern int	bms_num_members(const Bitmapset *a);
+extern int	bms_max_member(const Bitmapset *a);
 
 /* optimized tests when we don't need to know exact membership count: */
 extern BMS_Membership bms_membership(const Bitmapset *a);
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 6e068f2c8b..7bf1896e12 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -686,6 +686,12 @@ typedef struct RelOptInfo
 	/* default result targetlist for Paths scanning this relation */
 	struct PathTarget *reltarget;	/* list of Vars/Exprs, cost, width */
 
+	Bitmapset	**notnull_attrs; /* The attno which is not null after evalating
+								  * all the quals on this relation, for baserel,
+								  * the len would always 1. and for others the array
+								  * index is relid from relids.
+								  */
+
 	/* materialization information */
 	List	   *pathlist;		/* Path structures */
 	List	   *ppilist;		/* ParamPathInfos used in pathlist */
-- 
2.21.0

From 54b2f71215c864aa96f7424a7fb57d984de8c7a3 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Mon, 9 Aug 2021 19:46:13 +0800
Subject: [PATCH v4 6/6] Maintain the UniqueKey on Subquery and UpperRel level.

---
 src/backend/optimizer/path/allpaths.c   |   2 +
 src/backend/optimizer/path/pathkeys.c   |   3 +-
 src/backend/optimizer/path/uniquekey.c  | 179 ++++++++++++++++++++++++
 src/backend/optimizer/plan/planner.c    |  17 ++-
 src/include/optimizer/paths.h           |   6 +
 src/test/regress/expected/uniquekey.out |  36 +++++
 src/test/regress/sql/uniquekey.sql      |  21 +++
 7 files changed, 261 insertions(+), 3 deletions(-)

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 343253d694..9225bf738e 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -2288,6 +2288,8 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 		return;
 	}
 
+	populate_subquery_uniquekeys(root, rel, sub_final_rel);
+
 	/*
 	 * Mark rel with estimated output rows, width, etc.  Note that we have to
 	 * do this before generating outer-query paths, else cost_subqueryscan is
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 21f4f2d4bc..9a8a5b654c 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -33,7 +33,6 @@ static bool pathkey_is_redundant(PathKey *new_pathkey, List *pathkeys);
 static bool matches_boolean_partition_clause(RestrictInfo *rinfo,
 											 RelOptInfo *partrel,
 											 int partkeycol);
-static Var *find_var_for_subquery_tle(RelOptInfo *rel, TargetEntry *tle);
 static bool right_merge_direction(PlannerInfo *root, PathKey *pathkey);
 
 
@@ -1035,7 +1034,7 @@ convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
  * We need this to ensure that we don't return pathkeys describing values
  * that are unavailable above the level of the subquery scan.
  */
-static Var *
+Var *
 find_var_for_subquery_tle(RelOptInfo *rel, TargetEntry *tle)
 {
 	ListCell   *lc;
diff --git a/src/backend/optimizer/path/uniquekey.c b/src/backend/optimizer/path/uniquekey.c
index 815ade02c3..a073563c59 100644
--- a/src/backend/optimizer/path/uniquekey.c
+++ b/src/backend/optimizer/path/uniquekey.c
@@ -47,6 +47,13 @@ static void populate_joinrel_composite_uniquekey(PlannerInfo *root,
 												 bool outeruk_still_valid,
 												 bool inneruk_still_valid);
 
+static void convert_subquery_uniquekey(PlannerInfo *root, RelOptInfo *rel, UniqueKey *sub_ukey);
+static EquivalenceClass * find_outer_ec_with_subquery_em(PlannerInfo *root, RelOptInfo *rel,
+														 EquivalenceClass *sub_ec,
+														 EquivalenceMember *sub_em);
+static List *convert_subquery_eclass_list(PlannerInfo *root, RelOptInfo *rel,
+										  List *sub_eclass_list);
+
 /* UniqueKey is subset of .. */
 static bool uniquekey_contains_in(PlannerInfo *root, UniqueKey *ukey,
 								  List *ecs, Relids relids);
@@ -185,6 +192,57 @@ populate_joinrel_uniquekeys(PlannerInfo *root, RelOptInfo *joinrel,
 	return;
 }
 
+/*
+ * populate_subquery_uniquekeys
+ *
+ * 'rel': outer query's RelOptInfo for the subquery relation.
+ * 'subquery_uniquekeys': the subquery's output pathkeys, in its terms.
+ * 'subquery_tlist': the subquery's output targetlist, in its terms.
+ *
+ *  subquery issues: a). tlist mapping.  b). interesting uniquekey. c). not nulls.
+ */
+void
+populate_subquery_uniquekeys(PlannerInfo *root, RelOptInfo *rel, RelOptInfo *sub_final_rel)
+{
+	List	*sub_uniquekeys = sub_final_rel->uniquekeys;
+	ListCell	*lc;
+	foreach(lc, sub_uniquekeys)
+	{
+		UniqueKey *sub_ukey = lfirst_node(UniqueKey, lc);
+		convert_subquery_uniquekey(root, rel, sub_ukey);
+	}
+}
+
+/*
+ * populate_uniquekeys_from_pathkeys
+ *
+ */
+void
+populate_uniquekeys_from_pathkeys(PlannerInfo *root, RelOptInfo *rel, List *pathkeys)
+{
+	ListCell *lc;
+	List	*unique_exprs = NIL;
+	if (pathkeys == NIL)
+		return;
+	foreach(lc, pathkeys)
+	{
+		PathKey *pathkey = lfirst(lc);
+		unique_exprs = lappend(unique_exprs, pathkey->pk_eclass);
+	}
+	rel->uniquekeys = list_make1(
+		make_uniquekey(bms_make_singleton(list_length(root->unique_exprs)),
+					   false,
+					   true));
+	root->unique_exprs = lappend(root->unique_exprs, unique_exprs);
+}
+
+
+void
+simple_copy_uniquekeys(RelOptInfo *tarrel, RelOptInfo *srcrel)
+{
+	tarrel->uniquekeys = srcrel->uniquekeys;
+}
+
 /*
  * relation_is_distinct_for
  *		Check if the relation is distinct for.
@@ -712,6 +770,127 @@ is_uniquekey_useful_afterjoin(PlannerInfo *root, UniqueKey *ukey,
 	return true;
 }
 
+/*
+ * find_outer_ec_with_subquery_em
+ *
+ *	Given a em in subquery, return the related EquivalenceClass outside.
+ */
+static EquivalenceClass *
+find_outer_ec_with_subquery_em(PlannerInfo *root, RelOptInfo *rel,
+							   EquivalenceClass *sub_ec, EquivalenceMember *sub_em)
+{
+	TargetEntry *sub_tle;
+	Var *outer_var;
+	EquivalenceClass *outer_ec;
+
+	sub_tle = get_tle_from_expr(sub_em->em_expr, rel->subroot->processed_tlist);
+
+	if (!sub_tle)
+		return NULL;
+
+	outer_var = find_var_for_subquery_tle(rel, sub_tle);
+	if (!outer_var)
+		return NULL;
+
+	outer_ec = get_eclass_for_sort_expr(root,
+										(Expr *)outer_var,
+										NULL,
+										sub_ec->ec_opfamilies,
+										sub_em->em_datatype,
+										sub_ec->ec_collation,
+										0,
+										rel->relids,
+										false);
+	return outer_ec;
+}
+
+
+/*
+ * convert_subquery_eclass_list
+ *
+ *		Given a list of eclass in subquery, find the corresponding eclass in outer side.
+ * return NULL if no related eclass outside is found for any eclass in subquery.
+ */
+static List *
+convert_subquery_eclass_list(PlannerInfo *root, RelOptInfo *rel, List *sub_eclass_list)
+{
+	ListCell	*lc;
+	List	*ec_list = NIL;
+	foreach(lc, sub_eclass_list)
+	{
+		EquivalenceClass *sub_ec = lfirst_node(EquivalenceClass, lc);
+		EquivalenceClass *ec = NULL;
+		ListCell	*emc;
+		foreach(emc, sub_ec->ec_members)
+		{
+			EquivalenceMember *sub_em = lfirst(emc);
+			if ((ec = find_outer_ec_with_subquery_em(root, rel, sub_ec, sub_em)) != NULL)
+				break;
+		}
+		if (!ec)
+			return NIL;
+		ec_list = lappend(ec_list, ec);
+	}
+	return ec_list;
+}
+
+
+/*
+ * convert_subquery_uniquekey
+ *
+ */
+static void
+convert_subquery_uniquekey(PlannerInfo *root, RelOptInfo *rel, UniqueKey *sub_ukey)
+{
+	PlannerInfo *sub_root = rel->subroot;
+	List	*unique_exprs_list = NIL;
+	Bitmapset	*unique_exprs_indexes = NULL;
+	UniqueKey	*ukey = NULL;
+	int i = -1;
+	ListCell	*lc;
+	while((i = bms_next_member(sub_ukey->unique_expr_indexes, i)) >= 0)
+	{
+		Node *sub_eq_list = list_nth(sub_root->unique_exprs, i);
+		if (IsA(sub_eq_list, SingleRow))
+		{
+			/*
+			 * TODO: Unclear what to do, don't think it hard before the overall
+			 * design is accepted.
+			 */
+			return;
+		}
+		else
+		{
+			List *upper_eq_list;
+			Assert(IsA(sub_eq_list, List));
+			/*
+			 * Note: upper_eq_list is just part of uniquekey's exprs, to covert the whole
+			 * UniqueKey, we needs all the parts are shown in the upper rel.
+			 */
+			upper_eq_list = convert_subquery_eclass_list(root, rel, (List *)sub_eq_list);
+			if (upper_eq_list == NIL)
+			{
+				if (unique_exprs_list != NIL)
+					pfree(unique_exprs_list);
+				return;
+			}
+			unique_exprs_list = lappend(unique_exprs_list, upper_eq_list);
+		}
+	}
+
+	foreach(lc, unique_exprs_list)
+	{
+		unique_exprs_indexes = bms_add_member(unique_exprs_indexes, list_length(root->unique_exprs));
+		root->unique_exprs = lappend(root->unique_exprs, lfirst(lc));
+	}
+
+	ukey = make_uniquekey(unique_exprs_indexes,
+						  sub_ukey->multi_nulls,
+						  /* TODO: need check again, case SELECT * FROM (SELECT u FROM x OFFSET 0) v where x.u = 0; */
+						  true);
+	rel->uniquekeys = lappend(rel->uniquekeys, ukey);
+}
+
 /*
  *	make_uniquekey
  */
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index ed2cff00fc..a268737a4c 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1650,7 +1650,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
 	 * Now we are prepared to build the final-output upperrel.
 	 */
 	final_rel = fetch_upper_rel(root, UPPERREL_FINAL, NULL);
-
+	simple_copy_uniquekeys(final_rel, current_rel);
 	/*
 	 * If the input rel is marked consider_parallel and there's nothing that's
 	 * not parallel-safe in the LIMIT clause, then the final_rel can be marked
@@ -3622,6 +3622,19 @@ create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
 									  gd,
 									  extra->targetList);
 
+	if (root->parse->groupingSets)
+	{
+		/* nothing to do */
+	}
+	else if (root->parse->groupClause && root->group_pathkeys != NIL)
+	{
+		populate_uniquekeys_from_pathkeys(root, grouped_rel, root->group_pathkeys);
+	}
+	else
+	{
+		/* SingleRow Case */
+	}
+
 	/* Build final grouping paths */
 	add_paths_to_grouping_rel(root, input_rel, grouped_rel,
 							  partially_grouped_rel, agg_costs, gd,
@@ -4251,6 +4264,8 @@ create_distinct_paths(PlannerInfo *root,
 	/* For now, do all work in the (DISTINCT, NULL) upperrel */
 	distinct_rel = fetch_upper_rel(root, UPPERREL_DISTINCT, NULL);
 
+	populate_uniquekeys_from_pathkeys(root, distinct_rel, root->distinct_pathkeys);
+
 	/*
 	 * We don't compute anything at this level, so distinct_rel will be
 	 * parallel-safe if the input rel is parallel-safe.  In particular, if
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index f233837e59..b9570807af 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -265,11 +265,17 @@ extern void add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
 
 extern void populate_baserel_uniquekeys(PlannerInfo *root,
 										RelOptInfo *baserel);
+extern Var *find_var_for_subquery_tle(RelOptInfo *rel, TargetEntry *tle);
 extern void populate_baserel_uniquekeys(PlannerInfo *root,
 										RelOptInfo *baserel);
 extern void populate_joinrel_uniquekeys(PlannerInfo *root, RelOptInfo *joinrel,
 										RelOptInfo *outerrel, RelOptInfo *innerrel,
 										List *restrictlist, JoinType jointype);
+extern void populate_uniquekeys_from_pathkeys(PlannerInfo *root, RelOptInfo *rel,
+											  List *pathkeys);
+extern void populate_subquery_uniquekeys(PlannerInfo *root, RelOptInfo *rel,
+										 RelOptInfo *sub_final_rel);
+extern void simple_copy_uniquekeys(RelOptInfo *srcrel, RelOptInfo *tarrel);
 extern bool relation_is_distinct_for(PlannerInfo *root, RelOptInfo *rel,
 									 List *distinct_pathkey);
 #endif							/* PATHS_H */
diff --git a/src/test/regress/expected/uniquekey.out b/src/test/regress/expected/uniquekey.out
index c2bd1fa619..a86e413fcf 100644
--- a/src/test/regress/expected/uniquekey.out
+++ b/src/test/regress/expected/uniquekey.out
@@ -407,3 +407,39 @@ EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND
                ->  Seq Scan on uqk2
 (8 rows)
 
+-----------------------------------------
+-- Test DISTINCT/GROUP BY CASE.
+-----------------------------------------
+--------------------------------------------------------------------------------------------
+-- Test subquery cases.
+-- Note that current the UniqueKey still not push down the interesting UniqueKey to subquery.
+-- like uniquekey, so the below test case need a "DISTINCT" in subquery to make sure the
+-- UniqueKey is maintain.
+--------------------------------------------------------------------------------------------
+-- Test a normal case - one side
+EXPLAIN SELECT DISTINCT v.* FROM
+(SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.a = uqk2.pk AND uqk1.c is not null offset 0) v;
+                           QUERY PLAN                           
+----------------------------------------------------------------
+ Hash Join  (cost=1.07..2.14 rows=2 width=8)
+   Hash Cond: (uqk1.a = uqk2.pk)
+   ->  Seq Scan on uqk1  (cost=0.00..1.05 rows=3 width=12)
+         Filter: (c IS NOT NULL)
+   ->  Hash  (cost=1.03..1.03 rows=3 width=4)
+         ->  Seq Scan on uqk2  (cost=0.00..1.03 rows=3 width=4)
+(6 rows)
+
+-- Test a normal case - composited side.
+EXPLAIN SELECT DISTINCT v.* FROM
+(SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null OFFSET 0)
+v;
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Nested Loop  (cost=0.00..2.20 rows=9 width=12)
+   ->  Seq Scan on uqk1 t1  (cost=0.00..1.05 rows=3 width=8)
+         Filter: (c IS NOT NULL)
+   ->  Materialize  (cost=0.00..1.04 rows=3 width=4)
+         ->  Seq Scan on uqk2 t2  (cost=0.00..1.03 rows=3 width=4)
+(5 rows)
+
diff --git a/src/test/regress/sql/uniquekey.sql b/src/test/regress/sql/uniquekey.sql
index 3f93872246..22e236eafb 100644
--- a/src/test/regress/sql/uniquekey.sql
+++ b/src/test/regress/sql/uniquekey.sql
@@ -106,3 +106,24 @@ SELECT uqk1.c, uqk2.c FROM uqk1, uqk2 WHERE uqk1.pk = 2 AND uqk2.pk = 1 order BY
 -----------------------------------------
 EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.pk FROM uqk1, uqk2 WHERE uqk1.c = uqk2.c;
 EXPLAIN (COSTS OFF) SELECT DISTINCT uqk1.d FROM uqk1, uqk2 WHERE uqk1.pk = 1 AND uqk1.c = uqk2.c;
+
+-----------------------------------------
+-- Test DISTINCT/GROUP BY CASE.
+-----------------------------------------
+
+
+--------------------------------------------------------------------------------------------
+-- Test subquery cases.
+-- Note that current the UniqueKey still not push down the interesting UniqueKey to subquery.
+-- like uniquekey, so the below test case need a "DISTINCT" in subquery to make sure the
+-- UniqueKey is maintain.
+--------------------------------------------------------------------------------------------
+-- Test a normal case - one side
+EXPLAIN SELECT DISTINCT v.* FROM
+(SELECT DISTINCT uqk1.c, uqk1.d FROM uqk1, uqk2
+WHERE uqk1.a = uqk2.pk AND uqk1.c is not null offset 0) v;
+
+-- Test a normal case - composited side.
+EXPLAIN SELECT DISTINCT v.* FROM
+(SELECT DISTINCT t1.c, t1.d, t2.pk FROM uqk1 t1 cross join uqk2 t2 where t1.c is not null OFFSET 0)
+v;
-- 
2.21.0