Extend more usecase for planning time partition pruning and init partition pruning.

From 8e6652d90d9225329a813896a9c9d4f87f54be2d Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sun, 24 Jan 2021 18:00:06 +0800
Subject: [PATCH v2 2/2] Build some implied pruning quals to extend the usecase
 of Planning

time partition pruning and init partition pruning.
---
 src/backend/optimizer/plan/createplan.c       |   3 +
 src/backend/optimizer/util/inherit.c          |  11 +-
 src/backend/partitioning/partprune.c          | 295 +++++++++++++-
 src/include/partitioning/partprune.h          |   4 +-
 .../regress/expected/partition_aggregate.out  |  21 +-
 src/test/regress/expected/partition_join.out  |  18 +-
 src/test/regress/expected/partition_prune.out | 383 +++++++++++++++---
 src/test/regress/sql/partition_prune.sql      | 110 +++++
 8 files changed, 759 insertions(+), 86 deletions(-)

diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 6c8305c977..0b93e92cc7 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -1244,6 +1244,9 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path, int flags)
 			prunequal = list_concat(prunequal, prmquals);
 		}
 
+		prunequal = list_concat(prunequal,
+								build_implied_pruning_quals(root, best_path->path.parent));
+
 		if (prunequal != NIL)
 			partpruneinfo =
 				make_partition_pruneinfo(root, rel,
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.c
index be1c9ddd96..b816901cff 100644
--- a/src/backend/optimizer/util/inherit.c
+++ b/src/backend/optimizer/util/inherit.c
@@ -327,22 +327,13 @@ expand_partitioned_rtentry(PlannerInfo *root, RelOptInfo *relinfo,
 	 * that survive pruning.  Below, we will initialize child objects for the
 	 * surviving partitions.
 	 */
-	live_parts = prune_append_rel_partitions(relinfo);
+	live_parts = prune_append_rel_partitions(root, relinfo);
 
 	/* Expand simple_rel_array and friends to hold child objects. */
 	num_live_parts = bms_num_members(live_parts);
 	if (num_live_parts > 0)
 		expand_planner_arrays(root, num_live_parts);
 
-	/*
-	 * We also store partition RelOptInfo pointers in the parent relation.
-	 * Since we're palloc0'ing, slots corresponding to pruned partitions will
-	 * contain NULL.
-	 */
-	Assert(relinfo->part_rels == NULL);
-	relinfo->part_rels = (RelOptInfo **)
-		palloc0(relinfo->nparts * sizeof(RelOptInfo *));
-
 	/*
 	 * Create a child RTE for each live partition.  Note that unlike
 	 * traditional inheritance, we don't need a child RTE for the partitioned
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
index d08739127b..f7611b4f0d 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -48,7 +48,9 @@
 #include "optimizer/appendinfo.h"
 #include "optimizer/cost.h"
 #include "optimizer/optimizer.h"
+#include "optimizer/restrictinfo.h"
 #include "optimizer/pathnode.h"
+#include "optimizer/paths.h"
 #include "parser/parsetree.h"
 #include "partitioning/partbounds.h"
 #include "partitioning/partprune.h"
@@ -750,9 +752,9 @@ gen_partprune_steps(RelOptInfo *rel, List *clauses, PartClauseTarget target,
  * Callers must ensure that 'rel' is a partitioned table.
  */
 Bitmapset *
-prune_append_rel_partitions(RelOptInfo *rel)
+prune_append_rel_partitions(PlannerInfo *root, RelOptInfo *rel)
 {
-	List	   *clauses = rel->baserestrictinfo;
+	List	   *clauses = extract_actual_clauses(rel->baserestrictinfo, false);
 	List	   *pruning_steps;
 	GeneratePruningStepsContext gcontext;
 	PartitionPruneContext context;
@@ -763,6 +765,19 @@ prune_append_rel_partitions(RelOptInfo *rel)
 	if (rel->nparts == 0)
 		return NULL;
 
+	/*
+	 * We also store partition RelOptInfo pointers in the parent relation.
+	 * Since we're palloc0'ing, slots corresponding to pruned partitions will
+	 * contain NULL. We have to set part_rels here so that the IS_PARTITIONED_REL
+	 * can be true sooner.  gen_extra_qual_for_pruning depends on it.
+	 */
+	Assert(rel->part_rels == NULL);
+	rel->part_rels = (RelOptInfo **)
+		palloc0(rel->nparts * sizeof(RelOptInfo *));
+
+	if (enable_partition_pruning)
+		clauses = list_concat(clauses, build_implied_pruning_quals(root, rel));
+
 	/*
 	 * If pruning is disabled or if there are no clauses to prune with, return
 	 * all partitions.
@@ -3691,3 +3706,279 @@ partkey_datum_from_expr(PartitionPruneContext *context,
 		*value = ExecEvalExprSwitchContext(exprstate, ectx, isnull);
 	}
 }
+
+
+/*
+ * expr_in_one_side
+ * 	return true if the expr is in one side of the clause.
+ */
+static bool
+expr_in_one_side(Expr *clause, Expr *expr)
+{
+	ListCell	*lc;
+	if (IsA(clause, OpExpr))
+	{
+		OpExpr	*opexpr = (OpExpr *)clause;
+		foreach(lc, opexpr->args)
+		{
+			Expr *lexpr = lfirst(lc);
+			while(IsA(lexpr, RelabelType))
+				lexpr = ((RelabelType *)lexpr)->arg;
+
+			if (equal(lexpr, expr))
+				return true;
+		}
+	}
+	else if (IsA(clause, BoolExpr))
+	{
+		BoolExpr	*bool_expr = (BoolExpr *)clause;
+		switch(bool_expr->boolop)
+		{
+			case OR_EXPR:
+				foreach(lc, bool_expr->args)
+				{
+					if (!expr_in_one_side(lfirst(lc), expr))
+						return false;
+				}
+				return true;
+			case AND_EXPR:
+				foreach(lc, bool_expr->args)
+				{
+					if (expr_in_one_side(lfirst(lc), expr))
+						return false;
+				}
+				return false;
+		    case NOT_EXPR:
+				return false;
+		}
+	}
+	else if (IsA(clause, ScalarArrayOpExpr))
+	{
+		ScalarArrayOpExpr *scalar_array_opexpr = (ScalarArrayOpExpr *) clause;
+		Expr	*leftop = linitial(scalar_array_opexpr->args);
+		while (IsA(leftop, RelabelType))
+			leftop = ((RelabelType *)leftop)->arg;
+		return equal(leftop, expr);
+	}
+	return false;
+}
+
+
+/*
+ * build_implied_quals
+ * 	Suppose this_var = other_var, so if this_var appears in rel->baserestrictinfo,
+ * we will build another RestrictInfo with this_var is replaced with other_var
+ * for other_rel.
+ */
+static List *
+build_implied_quals(RelOptInfo *rel,  Var *this_var,  Var *other_var)
+{
+	List	*res = NIL;
+	ListCell	*lc;
+	foreach(lc, rel->baserestrictinfo)
+	{
+		RestrictInfo *rinfo = lfirst(lc);
+		RestrictInfo *rinfo_cpy;
+		Expr *clause;
+
+		if (rinfo->pseudoconstant)
+			continue;
+
+		clause = rinfo->clause;
+		if (!expr_in_one_side(clause, (Expr *)this_var))
+			continue;
+
+		rinfo_cpy = copyObject(rinfo);
+		ChangeVarNodesExtend((Node *)rinfo_cpy->clause, this_var->varno,
+							 other_var->varno, other_var->varattno, 0);
+		res = lappend(res, rinfo_cpy);
+	}
+	return res;
+}
+
+
+/*
+ * build_implied_pruning_qual_from_eclass_join
+ */
+static List*
+build_implied_pruning_qual_from_eclass_join(PlannerInfo *root, RelOptInfo *rel)
+{
+    List	*res = NIL;
+    Bitmapset 	*matching_ecs = get_eclass_indexes_for_relids(root, rel->relids);
+    int i = -1;
+	while ((i = bms_next_member(matching_ecs, i)) >= 0)
+	{
+		EquivalenceClass *cur_ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
+		EquivalenceMember *cur_em;
+		List *other_exprs = NIL;
+		Var	*self_expr = NULL;
+		ListCell	*lc;
+
+		if (cur_ec->ec_has_const || list_length(cur_ec->ec_members) <= 1)
+			continue;
+
+		foreach(lc, cur_ec->ec_members)
+		{
+			int relid;
+			Expr *expr;
+			cur_em = (EquivalenceMember *) lfirst(lc);
+
+			if (cur_em->em_is_child)
+				/* no need to handle */
+				continue;
+
+			if (!bms_is_empty(cur_em->em_nullable_relids))
+			{
+				/*
+                 * XXX: Not sure what em_nullable_relids is, even t1 left join t2
+                 * on t1.a = t2.a, the em_nullable_relids is empty. Just ignore
+                 * it for now for safety.
+                 */
+				continue;
+			}
+
+			if (!bms_get_singleton_member(cur_em->em_relids, &relid))
+			{
+				/* Not clear how to handle, and probably not worth the trouble. */
+				continue;
+			}
+			expr = cur_em->em_expr;
+
+			while (IsA(expr, RelabelType))
+				expr = ((RelabelType *)cur_em->em_expr)->arg;
+
+			if (!IsA(expr, Var))
+				/* See comments in build_implied_pruning_quals */
+				continue;
+
+			if (bms_equal(rel->relids, cur_em->em_relids))
+			{
+				if (match_expr_to_partition_keys(expr, rel, false) == -1)
+					/* Not a partition key, useless for us. */
+					continue;
+
+				self_expr = (Var *)expr;
+			}
+			else
+				other_exprs = lappend(other_exprs, expr);
+		}
+
+		if (!self_expr)
+			return NIL;
+
+		foreach(lc, other_exprs)
+		{
+			Var *other_var = lfirst_node(Var, lc);
+			RelOptInfo *other_rel = root->simple_rel_array[other_var->varno];
+			/* Now we will check if the other_var is used in other_rel->restrictinfo */
+			res = list_concat(res, build_implied_quals(other_rel, other_var, self_expr));
+		}
+    }
+    return res;
+}
+
+
+/*
+ * build_implied_pruning_quals_from_joininfo
+ *
+ * 	Just build pruning_quals if rel.partkey = other_rel.var.  Not including
+ *  other operators except the '='.
+ */
+static List *
+build_implied_pruning_quals_from_joininfo(PlannerInfo *root, RelOptInfo *rel)
+{
+    List	*res = NIL;
+	ListCell	*lc;
+	PartitionScheme part_scheme;
+	Assert(IS_PARTITIONED_REL(rel));
+
+	part_scheme = rel->part_scheme;
+	foreach(lc, rel->joininfo)
+	{
+		RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+		OpExpr	*opexpr;
+		Expr *leftop, *rightop;
+		Var *part_key_var, *other_var;
+		int part_key_index = -1;
+		Oid	partopfamily, op_lefttype, op_righttype;
+		int op_strategy = InvalidStrategy;
+
+		if(bms_is_subset(rel->relids, rinfo->outer_relids))
+			/*
+			 * We can't build quals for a outer relation from the
+			 * other side's baserestictinfo
+			 */
+			continue;
+
+		if (!IsA(rinfo->clause, OpExpr))
+			/* Unclear how to handle it and probably not common */
+			continue;
+
+		opexpr = (OpExpr *) rinfo->clause;
+
+		leftop = (Expr *) get_leftop(opexpr);
+		while (IsA(leftop, RelabelType))
+			leftop = ((RelabelType *) leftop)->arg;
+		rightop = (Expr *) get_rightop(opexpr);
+		while (IsA(rightop, RelabelType))
+			rightop = ((RelabelType *) rightop)->arg;
+
+		if (!IsA(leftop, Var) || !IsA(rightop, Var))
+			continue;
+
+		if ((part_key_index = match_expr_to_partition_keys(leftop, rel, true)) != -1)
+		{
+			part_key_var = (Var *)leftop;
+			other_var = (Var *) rightop;
+		}
+		else if ((part_key_index = match_expr_to_partition_keys(rightop, rel, true)) != -1)
+		{
+			part_key_var = (Var *)rightop;
+			other_var = (Var *)leftop;
+		}
+		else
+			continue;
+
+		partopfamily = part_scheme->partopfamily[part_key_index];
+
+		get_op_opfamily_properties(opexpr->opno, partopfamily, false,
+								   &op_strategy, &op_lefttype, &op_righttype);
+
+		if (op_strategy != BTEqualStrategyNumber)
+			continue;
+
+		res = list_concat(res,
+			build_implied_quals(root->simple_rel_array[other_var->varno],
+								other_var, part_key_var));
+	}
+    return res;
+}
+
+
+/*
+ * build_implied_pruning_quals
+ *
+ *  build implied pruning quals for a partitioned rel, The general idea is
+ * if p.partkey = t.a and t.a appearing in t's baserestrictinfo, we can
+ * add another similar restrictinfo for p as pruning clauses. To make
+ * the copied and modified RestrictInfo easy to build, we only support
+ * partkey is Var and the other expr is Var as well (if so, we are able to
+ * build it with ChangeVarNodesExtend).
+ */
+List *
+build_implied_pruning_quals(PlannerInfo *root, RelOptInfo *rel)
+{
+    List	*res = NIL;
+
+    if (!IS_PARTITIONED_REL(rel))
+        return res;
+
+    if (rel->has_eclass_joins)
+        res = list_concat(res,
+                          build_implied_pruning_qual_from_eclass_join(root, rel));
+
+    if (rel->joininfo)
+        res = list_concat(res,
+                          build_implied_pruning_quals_from_joininfo(root, rel));
+    return res;
+}
diff --git a/src/include/partitioning/partprune.h b/src/include/partitioning/partprune.h
index 5f51e73a4d..b9dc3373d1 100644
--- a/src/include/partitioning/partprune.h
+++ b/src/include/partitioning/partprune.h
@@ -68,11 +68,13 @@ typedef struct PartitionPruneContext
 #define PruneCxtStateIdx(partnatts, step_id, keyno) \
 	((partnatts) * (step_id) + (keyno))
 
+extern List *build_implied_pruning_quals(struct PlannerInfo *root, struct RelOptInfo *rel);
 extern PartitionPruneInfo *make_partition_pruneinfo(struct PlannerInfo *root,
 													struct RelOptInfo *parentrel,
 													List *subpaths,
 													List *prunequal);
-extern Bitmapset *prune_append_rel_partitions(struct RelOptInfo *rel);
+extern Bitmapset *prune_append_rel_partitions(struct PlannerInfo *root,
+											  struct RelOptInfo *rel);
 extern Bitmapset *get_matching_partitions(PartitionPruneContext *context,
 										  List *pruning_steps);
 
diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out
index dfa4b036b5..97820d1673 100644
--- a/src/test/regress/expected/partition_aggregate.out
+++ b/src/test/regress/expected/partition_aggregate.out
@@ -727,12 +727,12 @@ SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y G
 -- But right now we are unable to do partitionwise join in this case.
 EXPLAIN (COSTS OFF)
 SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20  GROUP BY a.x, b.y ORDER BY 1, 2;
-                             QUERY PLAN                             
---------------------------------------------------------------------
- Sort
-   Sort Key: pagg_tab1.x, pagg_tab2.y
-   ->  HashAggregate
-         Group Key: pagg_tab1.x, pagg_tab2.y
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ GroupAggregate
+   Group Key: pagg_tab1.x, pagg_tab2.y
+   ->  Sort
+         Sort Key: pagg_tab1.x, pagg_tab2.y
          ->  Hash Left Join
                Hash Cond: (pagg_tab1.x = pagg_tab2.y)
                Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20))
@@ -742,12 +742,9 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOI
                      ->  Seq Scan on pagg_tab1_p2 pagg_tab1_2
                            Filter: (x < 20)
                ->  Hash
-                     ->  Append
-                           ->  Seq Scan on pagg_tab2_p2 pagg_tab2_1
-                                 Filter: (y > 10)
-                           ->  Seq Scan on pagg_tab2_p3 pagg_tab2_2
-                                 Filter: (y > 10)
-(18 rows)
+                     ->  Seq Scan on pagg_tab2_p2 pagg_tab2
+                           Filter: (y > 10)
+(15 rows)
 
 SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20  GROUP BY a.x, b.y ORDER BY 1, 2;
  x  | y  | count 
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 0057f41caa..7286b8a72a 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -214,18 +214,15 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JO
    Sort Key: prt1.a, prt2.b
    ->  Hash Right Join
          Hash Cond: (prt2.b = prt1.a)
-         ->  Append
-               ->  Seq Scan on prt2_p2 prt2_1
-                     Filter: (b > 250)
-               ->  Seq Scan on prt2_p3 prt2_2
-                     Filter: (b > 250)
+         ->  Seq Scan on prt2_p2 prt2
+               Filter: (b > 250)
          ->  Hash
                ->  Append
                      ->  Seq Scan on prt1_p1 prt1_1
                            Filter: ((a < 450) AND (b = 0))
                      ->  Seq Scan on prt1_p2 prt1_2
                            Filter: ((a < 450) AND (b = 0))
-(15 rows)
+(12 rows)
 
 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
   a  |  c   |  b  |  c   
@@ -1186,12 +1183,9 @@ SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT *
                            Filter: ((a < 450) AND (b = 0))
          ->  Sort
                Sort Key: prt2.b
-               ->  Append
-                     ->  Seq Scan on prt2_p2 prt2_1
-                           Filter: (b > 250)
-                     ->  Seq Scan on prt2_p3 prt2_2
-                           Filter: (b > 250)
-(18 rows)
+               ->  Seq Scan on prt2_p2 prt2
+                     Filter: (b > 250)
+(15 rows)
 
 SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
   a  |  b  
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index bde29e38a9..4f24e78f50 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2103,19 +2103,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
-                                 Index Cond: (a = a.a)
-(27 rows)
+(15 rows)
 
 -- Ensure the same partitions are pruned when we make the nested loop
 -- parameter an Expr rather than a plain Param.
@@ -2170,19 +2158,13 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
+                           ->  Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_4 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
+                           ->  Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_5 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
+                           ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_6 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (actual rows=N loops=N)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (actual rows=N loops=N)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (actual rows=N loops=N)
-                                 Index Cond: (a = a.a)
-(27 rows)
+(21 rows)
 
 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
                                         explain_parallel_append                                         
@@ -2203,19 +2185,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
-                                 Index Cond: (a = a.a)
-(28 rows)
+(16 rows)
 
 delete from lprt_a where a = 1;
 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
@@ -2237,19 +2207,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (never executed)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
-                                 Index Cond: (a = a.a)
-(28 rows)
+(16 rows)
 
 reset enable_hashjoin;
 reset enable_mergejoin;
@@ -3902,3 +3860,330 @@ explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 a
 drop table hp_contradict_test;
 drop operator class part_test_int4_ops2 using hash;
 drop operator ===(int4, int4);
+create table p (a int, b int, c character varying(8)) partition by list(c);
+create table p1  partition of p for values in ('000001');
+create table p2  partition of p for values in ('000002');
+create table p3  partition of p for values in ('000003');
+create table q (a int, c character varying(8), b int) partition by list(c);
+create table q1  partition of q for values in ('000001');
+create table q2  partition of q for values in ('000002');
+create table q3  partition of q for values in ('000003');
+create table o (a int, c character varying(8), b int) partition by list(c);
+create table o1  partition of o for values in ('000001');
+create table o2 partition of o for values in ('000002');
+create table o3  partition of o for values in ('000003');
+analyze p;
+analyze q;
+analyze o;
+set plan_cache_mode to force_generic_plan ;
+-- inner join
+prepare s as select * from p, q where (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+(12 rows)
+
+prepare s2 as select * from p inner join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s2('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+(12 rows)
+
+-- left join
+-- q can have the init partititon prune, but p can't since it is the outer join.
+prepare s3 as select * from p left join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s3('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+(12 rows)
+
+-- p can't do the init partition prune since it is the outer join. so p can't as well.
+prepare s4 as select * from p left join q on (p.c = $1 or p.c = $2) and p.c = q.c;
+explain (costs off) execute s4('000001', '000002');
+                                         QUERY PLAN                                          
+---------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: (((p.c)::text = (q.c)::text) AND (((p.c)::text = $1) OR ((p.c)::text = $2)))
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Append
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+         ->  Seq Scan on q3 q_3
+(10 rows)
+
+-- left join is reduced to inner join, so both p and q can do the pruning.
+prepare s5 as select * from p left join q on p.c = q.c where (q.c = $1 or q.c = $2);
+explain (costs off) execute s5('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+(12 rows)
+
+-- left join can't be reduced to inner join, but p.c is not in ON clause so q can.
+-- and q is right table in left join, so it can as well.
+prepare s6 as select * from p left join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s6('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on p1 p_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on p2 p_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+(12 rows)
+
+-- full outer join.
+-- both p & q can't do the init partition prune.
+prepare s7 as select * from p full join q on p.c = q.c and (p.c = $1 or p.c = $2);
+explain (costs off) execute s7('000001', '000002');
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Hash Full Join
+   Hash Cond: ((p.c)::text = (q.c)::text)
+   Join Filter: (((p.c)::text = $1) OR ((p.c)::text = $2))
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Hash
+         ->  Append
+               ->  Seq Scan on q1 q_1
+               ->  Seq Scan on q2 q_2
+               ->  Seq Scan on q3 q_3
+(12 rows)
+
+-- can be reduced to p left join q, so both can.
+prepare s8 as select * from p full join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s8('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on p1 p_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on p2 p_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+(12 rows)
+
+deallocate s;
+deallocate s2;
+deallocate s3;
+deallocate s4;
+deallocate s5;
+deallocate s6;
+deallocate s7;
+deallocate s8;
+set plan_cache_mode to force_custom_plan;
+-- inner join
+prepare s as select * from p, q where (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+(10 rows)
+
+prepare s2 as select * from p inner join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s2('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+(10 rows)
+
+-- left join
+-- q can have the planning partititon prune, but p can't since it is the outer join.
+prepare s3 as select * from p left join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s3('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Append
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+(11 rows)
+
+-- p can't do the planning partition prune since it is the outer join. so p can't as well.
+prepare s4 as select * from p left join q on (p.c = $1 or p.c = $2) and p.c = q.c;
+explain (costs off) execute s4('000001', '000002');
+                                                     QUERY PLAN                                                      
+---------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: (((p.c)::text = (q.c)::text) AND (((p.c)::text = '000001'::text) OR ((p.c)::text = '000002'::text)))
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Append
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+         ->  Seq Scan on q3 q_3
+(10 rows)
+
+-- left join is reduced to inner join, so both p and q can do the pruning.
+prepare s5 as select * from p left join q on p.c = q.c where (q.c = $1 or q.c = $2);
+explain (costs off) execute s5('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+(10 rows)
+
+-- left join can't be reduced to inner join, but p.c is not in ON clause so q can.
+-- and q is right table in left join, so it can as well.
+prepare s6 as select * from p left join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s6('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on p2 p_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+   ->  Append
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+(10 rows)
+
+-- full outer join.
+-- both p & q can't do the planning partition prune.
+prepare s7 as select * from p full join q on p.c = q.c and (p.c = $1 or p.c = $2);
+explain (costs off) execute s7('000001', '000002');
+                                    QUERY PLAN                                     
+-----------------------------------------------------------------------------------
+ Hash Full Join
+   Hash Cond: ((p.c)::text = (q.c)::text)
+   Join Filter: (((p.c)::text = '000001'::text) OR ((p.c)::text = '000002'::text))
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Hash
+         ->  Append
+               ->  Seq Scan on q1 q_1
+               ->  Seq Scan on q2 q_2
+               ->  Seq Scan on q3 q_3
+(12 rows)
+
+-- can be reduced to p left join q, so both can.
+prepare s8 as select * from p full join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s8('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on p2 p_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+   ->  Append
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+(10 rows)
+
+deallocate s;
+deallocate s2;
+deallocate s3;
+deallocate s4;
+deallocate s5;
+deallocate s6;
+deallocate s7;
+deallocate s8;
+reset plan_cache_mode;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 6ccb52ad1d..f15cc3d731 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -1185,3 +1185,113 @@ explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 a
 drop table hp_contradict_test;
 drop operator class part_test_int4_ops2 using hash;
 drop operator ===(int4, int4);
+
+create table p (a int, b int, c character varying(8)) partition by list(c);
+create table p1  partition of p for values in ('000001');
+create table p2  partition of p for values in ('000002');
+create table p3  partition of p for values in ('000003');
+
+create table q (a int, c character varying(8), b int) partition by list(c);
+create table q1  partition of q for values in ('000001');
+create table q2  partition of q for values in ('000002');
+create table q3  partition of q for values in ('000003');
+
+create table o (a int, c character varying(8), b int) partition by list(c);
+create table o1  partition of o for values in ('000001');
+create table o2 partition of o for values in ('000002');
+create table o3  partition of o for values in ('000003');
+
+analyze p;
+analyze q;
+analyze o;
+
+set plan_cache_mode to force_generic_plan ;
+-- inner join
+prepare s as select * from p, q where (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s('000001', '000002');
+
+prepare s2 as select * from p inner join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s2('000001', '000002');
+
+-- left join
+-- q can have the init partititon prune, but p can't since it is the outer join.
+prepare s3 as select * from p left join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s3('000001', '000002');
+
+-- p can't do the init partition prune since it is the outer join. so p can't as well.
+prepare s4 as select * from p left join q on (p.c = $1 or p.c = $2) and p.c = q.c;
+explain (costs off) execute s4('000001', '000002');
+
+-- left join is reduced to inner join, so both p and q can do the pruning.
+prepare s5 as select * from p left join q on p.c = q.c where (q.c = $1 or q.c = $2);
+explain (costs off) execute s5('000001', '000002');
+
+-- left join can't be reduced to inner join, but p.c is not in ON clause so q can.
+-- and q is right table in left join, so it can as well.
+prepare s6 as select * from p left join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s6('000001', '000002');
+
+-- full outer join.
+-- both p & q can't do the init partition prune.
+prepare s7 as select * from p full join q on p.c = q.c and (p.c = $1 or p.c = $2);
+explain (costs off) execute s7('000001', '000002');
+
+-- can be reduced to p left join q, so both can.
+prepare s8 as select * from p full join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s8('000001', '000002');
+
+deallocate s;
+deallocate s2;
+deallocate s3;
+deallocate s4;
+deallocate s5;
+deallocate s6;
+deallocate s7;
+deallocate s8;
+
+set plan_cache_mode to force_custom_plan;
+-- inner join
+prepare s as select * from p, q where (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s('000001', '000002');
+
+prepare s2 as select * from p inner join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s2('000001', '000002');
+
+-- left join
+-- q can have the planning partititon prune, but p can't since it is the outer join.
+prepare s3 as select * from p left join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s3('000001', '000002');
+
+-- p can't do the planning partition prune since it is the outer join. so p can't as well.
+prepare s4 as select * from p left join q on (p.c = $1 or p.c = $2) and p.c = q.c;
+explain (costs off) execute s4('000001', '000002');
+
+-- left join is reduced to inner join, so both p and q can do the pruning.
+prepare s5 as select * from p left join q on p.c = q.c where (q.c = $1 or q.c = $2);
+explain (costs off) execute s5('000001', '000002');
+
+-- left join can't be reduced to inner join, but p.c is not in ON clause so q can.
+-- and q is right table in left join, so it can as well.
+prepare s6 as select * from p left join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s6('000001', '000002');
+
+-- full outer join.
+-- both p & q can't do the planning partition prune.
+prepare s7 as select * from p full join q on p.c = q.c and (p.c = $1 or p.c = $2);
+explain (costs off) execute s7('000001', '000002');
+
+-- can be reduced to p left join q, so both can.
+prepare s8 as select * from p full join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s8('000001', '000002');
+
+deallocate s;
+deallocate s2;
+deallocate s3;
+deallocate s4;
+deallocate s5;
+deallocate s6;
+deallocate s7;
+deallocate s8;
+
+
+reset plan_cache_mode;
-- 
2.21.0

From fd2ddc952e63c5f53e8e761c98f2605baa73b8e4 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sun, 24 Jan 2021 17:43:34 +0800
Subject: [PATCH v2 1/2] Make some static functions as extern and extend
 ChangeVarNodes can

change var->attno as well.
---
 src/backend/optimizer/path/equivclass.c |  4 +---
 src/backend/optimizer/util/relnode.c    |  4 +---
 src/backend/rewrite/rewriteManip.c      | 13 +++++++++++++
 src/include/optimizer/pathnode.h        |  2 ++
 src/include/optimizer/paths.h           |  2 ++
 src/include/rewrite/rewriteManip.h      |  2 ++
 6 files changed, 21 insertions(+), 6 deletions(-)

diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 0188c1e9a1..a4a54a497f 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -64,8 +64,6 @@ static bool reconsider_outer_join_clause(PlannerInfo *root,
 										 bool outer_on_left);
 static bool reconsider_full_join_clause(PlannerInfo *root,
 										RestrictInfo *rinfo);
-static Bitmapset *get_eclass_indexes_for_relids(PlannerInfo *root,
-												Relids relids);
 static Bitmapset *get_common_eclass_indexes(PlannerInfo *root, Relids relids1,
 											Relids relids2);
 
@@ -3036,7 +3034,7 @@ is_redundant_with_indexclauses(RestrictInfo *rinfo, List *indexclauses)
  *		Build and return a Bitmapset containing the indexes into root's
  *		eq_classes list for all eclasses that mention any of these relids
  */
-static Bitmapset *
+Bitmapset *
 get_eclass_indexes_for_relids(PlannerInfo *root, Relids relids)
 {
 	Bitmapset  *ec_indexes = NULL;
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 731ff708b9..74c8c9bc88 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -62,8 +62,6 @@ static void build_joinrel_partition_info(RelOptInfo *joinrel,
 static bool have_partkey_equi_join(RelOptInfo *joinrel,
 								   RelOptInfo *rel1, RelOptInfo *rel2,
 								   JoinType jointype, List *restrictlist);
-static int	match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
-										 bool strict_op);
 static void set_joinrel_partition_key_exprs(RelOptInfo *joinrel,
 											RelOptInfo *outer_rel, RelOptInfo *inner_rel,
 											JoinType jointype);
@@ -1817,7 +1815,7 @@ have_partkey_equi_join(RelOptInfo *joinrel,
  * partition key using a strict operator.  This allows us to consider
  * nullable as well as nonnullable partition keys.
  */
-static int
+int
 match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel, bool strict_op)
 {
 	int			cnt;
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index d4e0b8b4de..aac04a7f9e 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -498,6 +498,7 @@ typedef struct
 {
 	int			rt_index;
 	int			new_index;
+	int			new_attno;
 	int			sublevels_up;
 } ChangeVarNodes_context;
 
@@ -514,6 +515,11 @@ ChangeVarNodes_walker(Node *node, ChangeVarNodes_context *context)
 			var->varno == context->rt_index)
 		{
 			var->varno = context->new_index;
+			if (context->new_attno != MaxAttrNumber + 1)
+			{
+				var->varattno = context->new_attno;
+				var->varattnosyn = context->new_attno;  /* Question. */
+			}
 			/* If the syntactic referent is same RTE, fix it too */
 			if (var->varnosyn == context->rt_index)
 				var->varnosyn = context->new_index;
@@ -606,13 +612,20 @@ ChangeVarNodes_walker(Node *node, ChangeVarNodes_context *context)
 								  (void *) context);
 }
 
+
 void
 ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
+{
+	return ChangeVarNodesExtend(node, rt_index, new_index, MaxAttrNumber + 1, sublevels_up);
+}
+void
+ChangeVarNodesExtend(Node *node, int rt_index, int new_index, int new_attno, int sublevels_up)
 {
 	ChangeVarNodes_context context;
 
 	context.rt_index = rt_index;
 	context.new_index = new_index;
+	context.new_attno = new_attno;
 	context.sublevels_up = sublevels_up;
 
 	/*
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 8dfc36a4e1..7f311fdafe 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -319,5 +319,7 @@ extern RelOptInfo *build_child_join_rel(PlannerInfo *root,
 										RelOptInfo *outer_rel, RelOptInfo *inner_rel,
 										RelOptInfo *parent_joinrel, List *restrictlist,
 										SpecialJoinInfo *sjinfo, JoinType jointype);
+extern int	match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
+										 bool strict_op);
 
 #endif							/* PATHNODE_H */
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 035d3e1206..5fc73a055f 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -165,6 +165,8 @@ extern void add_child_join_rel_equivalences(PlannerInfo *root,
 											AppendRelInfo **appinfos,
 											RelOptInfo *parent_rel,
 											RelOptInfo *child_rel);
+extern Bitmapset *get_eclass_indexes_for_relids(PlannerInfo *root,
+												Relids relids);
 extern List *generate_implied_equalities_for_column(PlannerInfo *root,
 													RelOptInfo *rel,
 													ec_matches_callback_type callback,
diff --git a/src/include/rewrite/rewriteManip.h b/src/include/rewrite/rewriteManip.h
index 812414e63f..659d95f5dc 100644
--- a/src/include/rewrite/rewriteManip.h
+++ b/src/include/rewrite/rewriteManip.h
@@ -44,6 +44,8 @@ typedef enum ReplaceVarsNoMatchOption
 extern void OffsetVarNodes(Node *node, int offset, int sublevels_up);
 extern void ChangeVarNodes(Node *node, int old_varno, int new_varno,
 						   int sublevels_up);
+extern void ChangeVarNodesExtend(Node *node, int rt_index, int new_index,
+								int new_attno, int sublevels_up);
 extern void IncrementVarSublevelsUp(Node *node, int delta_sublevels_up,
 									int min_sublevels_up);
 extern void IncrementVarSublevelsUp_rtable(List *rtable,
-- 
2.21.0

From 8c35c02346da687e2cf8a19b587d3e81c1b6ad87 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sun, 24 Jan 2021 17:43:34 +0800
Subject: [PATCH v3 1/2] Make some static functions as extern and extend
 ChangeVarNodes can

change var->attno as well.
---
 src/backend/optimizer/path/equivclass.c |  4 +---
 src/backend/optimizer/util/relnode.c    |  4 +---
 src/backend/rewrite/rewriteManip.c      | 13 +++++++++++++
 src/include/optimizer/pathnode.h        |  2 ++
 src/include/optimizer/paths.h           |  2 ++
 src/include/rewrite/rewriteManip.h      |  2 ++
 6 files changed, 21 insertions(+), 6 deletions(-)

diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 0188c1e9a1..a4a54a497f 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -64,8 +64,6 @@ static bool reconsider_outer_join_clause(PlannerInfo *root,
 										 bool outer_on_left);
 static bool reconsider_full_join_clause(PlannerInfo *root,
 										RestrictInfo *rinfo);
-static Bitmapset *get_eclass_indexes_for_relids(PlannerInfo *root,
-												Relids relids);
 static Bitmapset *get_common_eclass_indexes(PlannerInfo *root, Relids relids1,
 											Relids relids2);
 
@@ -3036,7 +3034,7 @@ is_redundant_with_indexclauses(RestrictInfo *rinfo, List *indexclauses)
  *		Build and return a Bitmapset containing the indexes into root's
  *		eq_classes list for all eclasses that mention any of these relids
  */
-static Bitmapset *
+Bitmapset *
 get_eclass_indexes_for_relids(PlannerInfo *root, Relids relids)
 {
 	Bitmapset  *ec_indexes = NULL;
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 731ff708b9..74c8c9bc88 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -62,8 +62,6 @@ static void build_joinrel_partition_info(RelOptInfo *joinrel,
 static bool have_partkey_equi_join(RelOptInfo *joinrel,
 								   RelOptInfo *rel1, RelOptInfo *rel2,
 								   JoinType jointype, List *restrictlist);
-static int	match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
-										 bool strict_op);
 static void set_joinrel_partition_key_exprs(RelOptInfo *joinrel,
 											RelOptInfo *outer_rel, RelOptInfo *inner_rel,
 											JoinType jointype);
@@ -1817,7 +1815,7 @@ have_partkey_equi_join(RelOptInfo *joinrel,
  * partition key using a strict operator.  This allows us to consider
  * nullable as well as nonnullable partition keys.
  */
-static int
+int
 match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel, bool strict_op)
 {
 	int			cnt;
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index d4e0b8b4de..aac04a7f9e 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -498,6 +498,7 @@ typedef struct
 {
 	int			rt_index;
 	int			new_index;
+	int			new_attno;
 	int			sublevels_up;
 } ChangeVarNodes_context;
 
@@ -514,6 +515,11 @@ ChangeVarNodes_walker(Node *node, ChangeVarNodes_context *context)
 			var->varno == context->rt_index)
 		{
 			var->varno = context->new_index;
+			if (context->new_attno != MaxAttrNumber + 1)
+			{
+				var->varattno = context->new_attno;
+				var->varattnosyn = context->new_attno;  /* Question. */
+			}
 			/* If the syntactic referent is same RTE, fix it too */
 			if (var->varnosyn == context->rt_index)
 				var->varnosyn = context->new_index;
@@ -606,13 +612,20 @@ ChangeVarNodes_walker(Node *node, ChangeVarNodes_context *context)
 								  (void *) context);
 }
 
+
 void
 ChangeVarNodes(Node *node, int rt_index, int new_index, int sublevels_up)
+{
+	return ChangeVarNodesExtend(node, rt_index, new_index, MaxAttrNumber + 1, sublevels_up);
+}
+void
+ChangeVarNodesExtend(Node *node, int rt_index, int new_index, int new_attno, int sublevels_up)
 {
 	ChangeVarNodes_context context;
 
 	context.rt_index = rt_index;
 	context.new_index = new_index;
+	context.new_attno = new_attno;
 	context.sublevels_up = sublevels_up;
 
 	/*
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 8dfc36a4e1..7f311fdafe 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -319,5 +319,7 @@ extern RelOptInfo *build_child_join_rel(PlannerInfo *root,
 										RelOptInfo *outer_rel, RelOptInfo *inner_rel,
 										RelOptInfo *parent_joinrel, List *restrictlist,
 										SpecialJoinInfo *sjinfo, JoinType jointype);
+extern int	match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
+										 bool strict_op);
 
 #endif							/* PATHNODE_H */
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 035d3e1206..5fc73a055f 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -165,6 +165,8 @@ extern void add_child_join_rel_equivalences(PlannerInfo *root,
 											AppendRelInfo **appinfos,
 											RelOptInfo *parent_rel,
 											RelOptInfo *child_rel);
+extern Bitmapset *get_eclass_indexes_for_relids(PlannerInfo *root,
+												Relids relids);
 extern List *generate_implied_equalities_for_column(PlannerInfo *root,
 													RelOptInfo *rel,
 													ec_matches_callback_type callback,
diff --git a/src/include/rewrite/rewriteManip.h b/src/include/rewrite/rewriteManip.h
index 812414e63f..659d95f5dc 100644
--- a/src/include/rewrite/rewriteManip.h
+++ b/src/include/rewrite/rewriteManip.h
@@ -44,6 +44,8 @@ typedef enum ReplaceVarsNoMatchOption
 extern void OffsetVarNodes(Node *node, int offset, int sublevels_up);
 extern void ChangeVarNodes(Node *node, int old_varno, int new_varno,
 						   int sublevels_up);
+extern void ChangeVarNodesExtend(Node *node, int rt_index, int new_index,
+								int new_attno, int sublevels_up);
 extern void IncrementVarSublevelsUp(Node *node, int delta_sublevels_up,
 									int min_sublevels_up);
 extern void IncrementVarSublevelsUp_rtable(List *rtable,
-- 
2.21.0

From dc7f1278f62929e74764ade6dd4b2a8fa09cf5f4 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E4=B8=80=E6=8C=83?= <yizhi.fzh@alibaba-inc.com>
Date: Sun, 24 Jan 2021 18:00:06 +0800
Subject: [PATCH v3 2/2] Build some implied pruning quals to extend the usecase
 of Planning

time partition pruning and init partition pruning.
---
 src/backend/optimizer/plan/createplan.c       |   3 +
 src/backend/optimizer/util/inherit.c          |  11 +-
 src/backend/optimizer/util/restrictinfo.c     |  22 +
 src/backend/partitioning/partprune.c          | 295 +++++++++++++-
 src/include/optimizer/restrictinfo.h          |   1 +
 src/include/partitioning/partprune.h          |   4 +-
 .../regress/expected/partition_aggregate.out  |  21 +-
 src/test/regress/expected/partition_join.out  |  18 +-
 src/test/regress/expected/partition_prune.out | 383 +++++++++++++++---
 src/test/regress/sql/partition_prune.sql      | 110 +++++
 10 files changed, 782 insertions(+), 86 deletions(-)

diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 6c8305c977..0b93e92cc7 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -1244,6 +1244,9 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path, int flags)
 			prunequal = list_concat(prunequal, prmquals);
 		}
 
+		prunequal = list_concat(prunequal,
+								build_implied_pruning_quals(root, best_path->path.parent));
+
 		if (prunequal != NIL)
 			partpruneinfo =
 				make_partition_pruneinfo(root, rel,
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.c
index be1c9ddd96..b816901cff 100644
--- a/src/backend/optimizer/util/inherit.c
+++ b/src/backend/optimizer/util/inherit.c
@@ -327,22 +327,13 @@ expand_partitioned_rtentry(PlannerInfo *root, RelOptInfo *relinfo,
 	 * that survive pruning.  Below, we will initialize child objects for the
 	 * surviving partitions.
 	 */
-	live_parts = prune_append_rel_partitions(relinfo);
+	live_parts = prune_append_rel_partitions(root, relinfo);
 
 	/* Expand simple_rel_array and friends to hold child objects. */
 	num_live_parts = bms_num_members(live_parts);
 	if (num_live_parts > 0)
 		expand_planner_arrays(root, num_live_parts);
 
-	/*
-	 * We also store partition RelOptInfo pointers in the parent relation.
-	 * Since we're palloc0'ing, slots corresponding to pruned partitions will
-	 * contain NULL.
-	 */
-	Assert(relinfo->part_rels == NULL);
-	relinfo->part_rels = (RelOptInfo **)
-		palloc0(relinfo->nparts * sizeof(RelOptInfo *));
-
 	/*
 	 * Create a child RTE for each live partition.  Note that unlike
 	 * traditional inheritance, we don't need a child RTE for the partitioned
diff --git a/src/backend/optimizer/util/restrictinfo.c b/src/backend/optimizer/util/restrictinfo.c
index eb113d94c1..bc2fb8a2e7 100644
--- a/src/backend/optimizer/util/restrictinfo.c
+++ b/src/backend/optimizer/util/restrictinfo.c
@@ -423,6 +423,28 @@ get_actual_clauses(List *restrictinfo_list)
 	return result;
 }
 
+
+/*
+ * extract_rinfo_clauses
+ *
+ *	extract the base clauses from restrictinfo_list.
+ */
+List *
+extract_rinfo_clauses(List *restrictinfo_list)
+{
+	List	   *result = NIL;
+	ListCell   *l;
+
+	foreach(l, restrictinfo_list)
+	{
+		RestrictInfo *rinfo = lfirst_node(RestrictInfo, l);
+
+		result = lappend(result, rinfo->clause);
+	}
+	return result;
+
+}
+
 /*
  * extract_actual_clauses
  *
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
index d08739127b..c17c1c4b84 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -48,7 +48,9 @@
 #include "optimizer/appendinfo.h"
 #include "optimizer/cost.h"
 #include "optimizer/optimizer.h"
+#include "optimizer/restrictinfo.h"
 #include "optimizer/pathnode.h"
+#include "optimizer/paths.h"
 #include "parser/parsetree.h"
 #include "partitioning/partbounds.h"
 #include "partitioning/partprune.h"
@@ -750,9 +752,9 @@ gen_partprune_steps(RelOptInfo *rel, List *clauses, PartClauseTarget target,
  * Callers must ensure that 'rel' is a partitioned table.
  */
 Bitmapset *
-prune_append_rel_partitions(RelOptInfo *rel)
+prune_append_rel_partitions(PlannerInfo *root, RelOptInfo *rel)
 {
-	List	   *clauses = rel->baserestrictinfo;
+	List	   *clauses = extract_rinfo_clauses(rel->baserestrictinfo);
 	List	   *pruning_steps;
 	GeneratePruningStepsContext gcontext;
 	PartitionPruneContext context;
@@ -763,6 +765,19 @@ prune_append_rel_partitions(RelOptInfo *rel)
 	if (rel->nparts == 0)
 		return NULL;
 
+	/*
+	 * We also store partition RelOptInfo pointers in the parent relation.
+	 * Since we're palloc0'ing, slots corresponding to pruned partitions will
+	 * contain NULL. We have to set part_rels here so that the IS_PARTITIONED_REL
+	 * can be true sooner.  gen_extra_qual_for_pruning depends on it.
+	 */
+	Assert(rel->part_rels == NULL);
+	rel->part_rels = (RelOptInfo **)
+		palloc0(rel->nparts * sizeof(RelOptInfo *));
+
+	if (enable_partition_pruning)
+		clauses = list_concat(clauses, build_implied_pruning_quals(root, rel));
+
 	/*
 	 * If pruning is disabled or if there are no clauses to prune with, return
 	 * all partitions.
@@ -3691,3 +3706,279 @@ partkey_datum_from_expr(PartitionPruneContext *context,
 		*value = ExecEvalExprSwitchContext(exprstate, ectx, isnull);
 	}
 }
+
+
+/*
+ * expr_in_one_side
+ * 	return true if the expr is in one side of the clause.
+ */
+static bool
+expr_in_one_side(Expr *clause, Expr *expr)
+{
+	ListCell	*lc;
+	if (IsA(clause, OpExpr))
+	{
+		OpExpr	*opexpr = (OpExpr *)clause;
+		foreach(lc, opexpr->args)
+		{
+			Expr *lexpr = lfirst(lc);
+			while(IsA(lexpr, RelabelType))
+				lexpr = ((RelabelType *)lexpr)->arg;
+
+			if (equal(lexpr, expr))
+				return true;
+		}
+	}
+	else if (IsA(clause, BoolExpr))
+	{
+		BoolExpr	*bool_expr = (BoolExpr *)clause;
+		switch(bool_expr->boolop)
+		{
+			case OR_EXPR:
+				foreach(lc, bool_expr->args)
+				{
+					if (!expr_in_one_side(lfirst(lc), expr))
+						return false;
+				}
+				return true;
+			case AND_EXPR:
+				foreach(lc, bool_expr->args)
+				{
+					if (expr_in_one_side(lfirst(lc), expr))
+						return false;
+				}
+				return false;
+			case NOT_EXPR:
+				return false;
+		}
+	}
+	else if (IsA(clause, ScalarArrayOpExpr))
+	{
+		ScalarArrayOpExpr *scalar_array_opexpr = (ScalarArrayOpExpr *) clause;
+		Expr	*leftop = linitial(scalar_array_opexpr->args);
+		while (IsA(leftop, RelabelType))
+			leftop = ((RelabelType *)leftop)->arg;
+		return equal(leftop, expr);
+	}
+	return false;
+}
+
+
+/*
+ * build_implied_quals
+ * 	Suppose this_var = other_var, so if this_var appears in rel->baserestrictinfo,
+ * we will build another RestrictInfo with this_var is replaced with other_var
+ * for other_rel.
+ */
+static List *
+build_implied_quals(RelOptInfo *rel,  Var *this_var,  Var *other_var)
+{
+	List	*res = NIL;
+	ListCell	*lc;
+	foreach(lc, rel->baserestrictinfo)
+	{
+		RestrictInfo *rinfo = lfirst(lc);
+		RestrictInfo *rinfo_cpy;
+		Expr *clause;
+
+		if (rinfo->pseudoconstant)
+			continue;
+
+		clause = rinfo->clause;
+		if (!expr_in_one_side(clause, (Expr *)this_var))
+			continue;
+
+		rinfo_cpy = copyObject(rinfo);
+		ChangeVarNodesExtend((Node *)rinfo_cpy->clause, this_var->varno,
+							 other_var->varno, other_var->varattno, 0);
+		res = lappend(res, rinfo_cpy);
+	}
+	return res;
+}
+
+
+/*
+ * build_implied_pruning_qual_from_eclass_join
+ */
+static List*
+build_implied_pruning_qual_from_eclass_join(PlannerInfo *root, RelOptInfo *rel)
+{
+	List	*res = NIL;
+	Bitmapset 	*matching_ecs = get_eclass_indexes_for_relids(root, rel->relids);
+	int i = -1;
+	while ((i = bms_next_member(matching_ecs, i)) >= 0)
+	{
+		EquivalenceClass *cur_ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
+		EquivalenceMember *cur_em;
+		List *other_exprs = NIL;
+		Var	*self_expr = NULL;
+		ListCell	*lc;
+
+		if (cur_ec->ec_has_const || list_length(cur_ec->ec_members) <= 1)
+			continue;
+
+		foreach(lc, cur_ec->ec_members)
+		{
+			int relid;
+			Expr *expr;
+			cur_em = (EquivalenceMember *) lfirst(lc);
+
+			if (cur_em->em_is_child)
+				/* no need to handle */
+				continue;
+
+			if (!bms_is_empty(cur_em->em_nullable_relids))
+			{
+				/*
+				 * XXX: Not sure what em_nullable_relids is, even t1 left join t2
+				 * on t1.a = t2.a, the em_nullable_relids is empty. Just ignore
+				 * it for now for safety.
+				 */
+				continue;
+			}
+
+			if (!bms_get_singleton_member(cur_em->em_relids, &relid))
+			{
+				/* Not clear how to handle, and probably not worth the trouble. */
+				continue;
+			}
+			expr = cur_em->em_expr;
+
+			while (IsA(expr, RelabelType))
+				expr = ((RelabelType *)cur_em->em_expr)->arg;
+
+			if (!IsA(expr, Var))
+				/* See comments in build_implied_pruning_quals */
+				continue;
+
+			if (bms_equal(rel->relids, cur_em->em_relids))
+			{
+				if (match_expr_to_partition_keys(expr, rel, false) == -1)
+					/* Not a partition key, useless for us. */
+					continue;
+
+				self_expr = (Var *)expr;
+			}
+			else
+				other_exprs = lappend(other_exprs, expr);
+		}
+
+		if (!self_expr)
+			return NIL;
+
+		foreach(lc, other_exprs)
+		{
+			Var *other_var = lfirst_node(Var, lc);
+			RelOptInfo *other_rel = root->simple_rel_array[other_var->varno];
+			/* Now we will check if the other_var is used in other_rel->restrictinfo */
+			res = list_concat(res, build_implied_quals(other_rel, other_var, self_expr));
+		}
+	}
+	return res;
+}
+
+
+/*
+ * build_implied_pruning_quals_from_joininfo
+ *
+ * 	Just build pruning_quals if rel.partkey = other_rel.var.  Not including
+ *  other operators except the '='.
+ */
+static List *
+build_implied_pruning_quals_from_joininfo(PlannerInfo *root, RelOptInfo *rel)
+{
+	List	*res = NIL;
+	ListCell	*lc;
+	PartitionScheme part_scheme;
+	Assert(IS_PARTITIONED_REL(rel));
+
+	part_scheme = rel->part_scheme;
+	foreach(lc, rel->joininfo)
+	{
+		RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+		OpExpr	*opexpr;
+		Expr *leftop, *rightop;
+		Var *part_key_var, *other_var;
+		int part_key_index = -1;
+		Oid	partopfamily, op_lefttype, op_righttype;
+		int op_strategy = InvalidStrategy;
+
+		if(bms_is_subset(rel->relids, rinfo->outer_relids))
+			/*
+			 * We can't build quals for a outer relation from the
+			 * other side's baserestictinfo
+			 */
+			continue;
+
+		if (!IsA(rinfo->clause, OpExpr))
+			/* Unclear how to handle it and probably not common */
+			continue;
+
+		opexpr = (OpExpr *) rinfo->clause;
+
+		leftop = (Expr *) get_leftop(opexpr);
+		while (IsA(leftop, RelabelType))
+			leftop = ((RelabelType *) leftop)->arg;
+		rightop = (Expr *) get_rightop(opexpr);
+		while (IsA(rightop, RelabelType))
+			rightop = ((RelabelType *) rightop)->arg;
+
+		if (!IsA(leftop, Var) || !IsA(rightop, Var))
+			continue;
+
+		if ((part_key_index = match_expr_to_partition_keys(leftop, rel, true)) != -1)
+		{
+			part_key_var = (Var *)leftop;
+			other_var = (Var *) rightop;
+		}
+		else if ((part_key_index = match_expr_to_partition_keys(rightop, rel, true)) != -1)
+		{
+			part_key_var = (Var *)rightop;
+			other_var = (Var *)leftop;
+		}
+		else
+			continue;
+
+		partopfamily = part_scheme->partopfamily[part_key_index];
+
+		get_op_opfamily_properties(opexpr->opno, partopfamily, false,
+								   &op_strategy, &op_lefttype, &op_righttype);
+
+		if (op_strategy != BTEqualStrategyNumber)
+			continue;
+
+		res = list_concat(res,
+			build_implied_quals(root->simple_rel_array[other_var->varno],
+								other_var, part_key_var));
+	}
+	return res;
+}
+
+
+/*
+ * build_implied_pruning_quals
+ *
+ *  build implied pruning quals for a partitioned rel, The general idea is
+ * if p.partkey = t.a and t.a appearing in t's baserestrictinfo, we can
+ * add another similar restrictinfo for p as pruning clauses. To make
+ * the copied and modified RestrictInfo easy to build, we only support
+ * partkey is Var and the other expr is Var as well (if so, we are able to
+ * build it with ChangeVarNodesExtend).
+ */
+List *
+build_implied_pruning_quals(PlannerInfo *root, RelOptInfo *rel)
+{
+	List	*res = NIL;
+
+	if (!IS_PARTITIONED_REL(rel))
+		return res;
+
+	if (rel->has_eclass_joins)
+		res = list_concat(res,
+						  build_implied_pruning_qual_from_eclass_join(root, rel));
+
+	if (rel->joininfo)
+		res = list_concat(res,
+						  build_implied_pruning_quals_from_joininfo(root, rel));
+	return res;
+}
diff --git a/src/include/optimizer/restrictinfo.h b/src/include/optimizer/restrictinfo.h
index 0165ffde37..a296e823b1 100644
--- a/src/include/optimizer/restrictinfo.h
+++ b/src/include/optimizer/restrictinfo.h
@@ -35,6 +35,7 @@ extern bool restriction_is_or_clause(RestrictInfo *restrictinfo);
 extern bool restriction_is_securely_promotable(RestrictInfo *restrictinfo,
 											   RelOptInfo *rel);
 extern List *get_actual_clauses(List *restrictinfo_list);
+extern List * extract_rinfo_clauses(List *restrictinfo_list);
 extern List *extract_actual_clauses(List *restrictinfo_list,
 									bool pseudoconstant);
 extern void extract_actual_join_clauses(List *restrictinfo_list,
diff --git a/src/include/partitioning/partprune.h b/src/include/partitioning/partprune.h
index 5f51e73a4d..b9dc3373d1 100644
--- a/src/include/partitioning/partprune.h
+++ b/src/include/partitioning/partprune.h
@@ -68,11 +68,13 @@ typedef struct PartitionPruneContext
 #define PruneCxtStateIdx(partnatts, step_id, keyno) \
 	((partnatts) * (step_id) + (keyno))
 
+extern List *build_implied_pruning_quals(struct PlannerInfo *root, struct RelOptInfo *rel);
 extern PartitionPruneInfo *make_partition_pruneinfo(struct PlannerInfo *root,
 													struct RelOptInfo *parentrel,
 													List *subpaths,
 													List *prunequal);
-extern Bitmapset *prune_append_rel_partitions(struct RelOptInfo *rel);
+extern Bitmapset *prune_append_rel_partitions(struct PlannerInfo *root,
+											  struct RelOptInfo *rel);
 extern Bitmapset *get_matching_partitions(PartitionPruneContext *context,
 										  List *pruning_steps);
 
diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out
index dfa4b036b5..97820d1673 100644
--- a/src/test/regress/expected/partition_aggregate.out
+++ b/src/test/regress/expected/partition_aggregate.out
@@ -727,12 +727,12 @@ SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y G
 -- But right now we are unable to do partitionwise join in this case.
 EXPLAIN (COSTS OFF)
 SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20  GROUP BY a.x, b.y ORDER BY 1, 2;
-                             QUERY PLAN                             
---------------------------------------------------------------------
- Sort
-   Sort Key: pagg_tab1.x, pagg_tab2.y
-   ->  HashAggregate
-         Group Key: pagg_tab1.x, pagg_tab2.y
+                           QUERY PLAN                            
+-----------------------------------------------------------------
+ GroupAggregate
+   Group Key: pagg_tab1.x, pagg_tab2.y
+   ->  Sort
+         Sort Key: pagg_tab1.x, pagg_tab2.y
          ->  Hash Left Join
                Hash Cond: (pagg_tab1.x = pagg_tab2.y)
                Filter: ((pagg_tab1.x > 5) OR (pagg_tab2.y < 20))
@@ -742,12 +742,9 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOI
                      ->  Seq Scan on pagg_tab1_p2 pagg_tab1_2
                            Filter: (x < 20)
                ->  Hash
-                     ->  Append
-                           ->  Seq Scan on pagg_tab2_p2 pagg_tab2_1
-                                 Filter: (y > 10)
-                           ->  Seq Scan on pagg_tab2_p3 pagg_tab2_2
-                                 Filter: (y > 10)
-(18 rows)
+                     ->  Seq Scan on pagg_tab2_p2 pagg_tab2
+                           Filter: (y > 10)
+(15 rows)
 
 SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20  GROUP BY a.x, b.y ORDER BY 1, 2;
  x  | y  | count 
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 0057f41caa..7286b8a72a 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -214,18 +214,15 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JO
    Sort Key: prt1.a, prt2.b
    ->  Hash Right Join
          Hash Cond: (prt2.b = prt1.a)
-         ->  Append
-               ->  Seq Scan on prt2_p2 prt2_1
-                     Filter: (b > 250)
-               ->  Seq Scan on prt2_p3 prt2_2
-                     Filter: (b > 250)
+         ->  Seq Scan on prt2_p2 prt2
+               Filter: (b > 250)
          ->  Hash
                ->  Append
                      ->  Seq Scan on prt1_p1 prt1_1
                            Filter: ((a < 450) AND (b = 0))
                      ->  Seq Scan on prt1_p2 prt1_2
                            Filter: ((a < 450) AND (b = 0))
-(15 rows)
+(12 rows)
 
 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
   a  |  c   |  b  |  c   
@@ -1186,12 +1183,9 @@ SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT *
                            Filter: ((a < 450) AND (b = 0))
          ->  Sort
                Sort Key: prt2.b
-               ->  Append
-                     ->  Seq Scan on prt2_p2 prt2_1
-                           Filter: (b > 250)
-                     ->  Seq Scan on prt2_p3 prt2_2
-                           Filter: (b > 250)
-(18 rows)
+               ->  Seq Scan on prt2_p2 prt2
+                     Filter: (b > 250)
+(15 rows)
 
 SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
   a  |  b  
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index bde29e38a9..4f24e78f50 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -2103,19 +2103,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
-                                 Index Cond: (a = a.a)
-(27 rows)
+(15 rows)
 
 -- Ensure the same partitions are pruned when we make the nested loop
 -- parameter an Expr rather than a plain Param.
@@ -2170,19 +2158,13 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
+                           ->  Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_4 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
+                           ->  Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_5 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
+                           ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_6 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (actual rows=N loops=N)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (actual rows=N loops=N)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (actual rows=N loops=N)
-                                 Index Cond: (a = a.a)
-(27 rows)
+(21 rows)
 
 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
                                         explain_parallel_append                                         
@@ -2203,19 +2185,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
-                                 Index Cond: (a = a.a)
-(28 rows)
+(16 rows)
 
 delete from lprt_a where a = 1;
 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
@@ -2237,19 +2207,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on
                                  Index Cond: (a = a.a)
                            ->  Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (never executed)
                                  Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
-                                 Index Cond: (a = a.a)
-                           ->  Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
-                                 Index Cond: (a = a.a)
-(28 rows)
+(16 rows)
 
 reset enable_hashjoin;
 reset enable_mergejoin;
@@ -3902,3 +3860,330 @@ explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 a
 drop table hp_contradict_test;
 drop operator class part_test_int4_ops2 using hash;
 drop operator ===(int4, int4);
+create table p (a int, b int, c character varying(8)) partition by list(c);
+create table p1  partition of p for values in ('000001');
+create table p2  partition of p for values in ('000002');
+create table p3  partition of p for values in ('000003');
+create table q (a int, c character varying(8), b int) partition by list(c);
+create table q1  partition of q for values in ('000001');
+create table q2  partition of q for values in ('000002');
+create table q3  partition of q for values in ('000003');
+create table o (a int, c character varying(8), b int) partition by list(c);
+create table o1  partition of o for values in ('000001');
+create table o2 partition of o for values in ('000002');
+create table o3  partition of o for values in ('000003');
+analyze p;
+analyze q;
+analyze o;
+set plan_cache_mode to force_generic_plan ;
+-- inner join
+prepare s as select * from p, q where (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+(12 rows)
+
+prepare s2 as select * from p inner join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s2('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+(12 rows)
+
+-- left join
+-- q can have the init partititon prune, but p can't since it is the outer join.
+prepare s3 as select * from p left join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s3('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+(12 rows)
+
+-- p can't do the init partition prune since it is the outer join. so p can't as well.
+prepare s4 as select * from p left join q on (p.c = $1 or p.c = $2) and p.c = q.c;
+explain (costs off) execute s4('000001', '000002');
+                                         QUERY PLAN                                          
+---------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: (((p.c)::text = (q.c)::text) AND (((p.c)::text = $1) OR ((p.c)::text = $2)))
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Append
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+         ->  Seq Scan on q3 q_3
+(10 rows)
+
+-- left join is reduced to inner join, so both p and q can do the pruning.
+prepare s5 as select * from p left join q on p.c = q.c where (q.c = $1 or q.c = $2);
+explain (costs off) execute s5('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+(12 rows)
+
+-- left join can't be reduced to inner join, but p.c is not in ON clause so q can.
+-- and q is right table in left join, so it can as well.
+prepare s6 as select * from p left join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s6('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on p1 p_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on p2 p_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+(12 rows)
+
+-- full outer join.
+-- both p & q can't do the init partition prune.
+prepare s7 as select * from p full join q on p.c = q.c and (p.c = $1 or p.c = $2);
+explain (costs off) execute s7('000001', '000002');
+                        QUERY PLAN                         
+-----------------------------------------------------------
+ Hash Full Join
+   Hash Cond: ((p.c)::text = (q.c)::text)
+   Join Filter: (((p.c)::text = $1) OR ((p.c)::text = $2))
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Hash
+         ->  Append
+               ->  Seq Scan on q1 q_1
+               ->  Seq Scan on q2 q_2
+               ->  Seq Scan on q3 q_3
+(12 rows)
+
+-- can be reduced to p left join q, so both can.
+prepare s8 as select * from p full join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s8('000001', '000002');
+                          QUERY PLAN                          
+--------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on p1 p_1
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+         ->  Seq Scan on p2 p_2
+               Filter: (((c)::text = $1) OR ((c)::text = $2))
+   ->  Append
+         Subplans Removed: 1
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+(12 rows)
+
+deallocate s;
+deallocate s2;
+deallocate s3;
+deallocate s4;
+deallocate s5;
+deallocate s6;
+deallocate s7;
+deallocate s8;
+set plan_cache_mode to force_custom_plan;
+-- inner join
+prepare s as select * from p, q where (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+(10 rows)
+
+prepare s2 as select * from p inner join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s2('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+(10 rows)
+
+-- left join
+-- q can have the planning partititon prune, but p can't since it is the outer join.
+prepare s3 as select * from p left join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s3('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Append
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+(11 rows)
+
+-- p can't do the planning partition prune since it is the outer join. so p can't as well.
+prepare s4 as select * from p left join q on (p.c = $1 or p.c = $2) and p.c = q.c;
+explain (costs off) execute s4('000001', '000002');
+                                                     QUERY PLAN                                                      
+---------------------------------------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: (((p.c)::text = (q.c)::text) AND (((p.c)::text = '000001'::text) OR ((p.c)::text = '000002'::text)))
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Append
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+         ->  Seq Scan on q3 q_3
+(10 rows)
+
+-- left join is reduced to inner join, so both p and q can do the pruning.
+prepare s5 as select * from p left join q on p.c = q.c where (q.c = $1 or q.c = $2);
+explain (costs off) execute s5('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+   ->  Append
+         ->  Seq Scan on q1 q_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on q2 q_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+(10 rows)
+
+-- left join can't be reduced to inner join, but p.c is not in ON clause so q can.
+-- and q is right table in left join, so it can as well.
+prepare s6 as select * from p left join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s6('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on p2 p_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+   ->  Append
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+(10 rows)
+
+-- full outer join.
+-- both p & q can't do the planning partition prune.
+prepare s7 as select * from p full join q on p.c = q.c and (p.c = $1 or p.c = $2);
+explain (costs off) execute s7('000001', '000002');
+                                    QUERY PLAN                                     
+-----------------------------------------------------------------------------------
+ Hash Full Join
+   Hash Cond: ((p.c)::text = (q.c)::text)
+   Join Filter: (((p.c)::text = '000001'::text) OR ((p.c)::text = '000002'::text))
+   ->  Append
+         ->  Seq Scan on p1 p_1
+         ->  Seq Scan on p2 p_2
+         ->  Seq Scan on p3 p_3
+   ->  Hash
+         ->  Append
+               ->  Seq Scan on q1 q_1
+               ->  Seq Scan on q2 q_2
+               ->  Seq Scan on q3 q_3
+(12 rows)
+
+-- can be reduced to p left join q, so both can.
+prepare s8 as select * from p full join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s8('000001', '000002');
+                                      QUERY PLAN                                      
+--------------------------------------------------------------------------------------
+ Nested Loop Left Join
+   Join Filter: ((p.c)::text = (q.c)::text)
+   ->  Append
+         ->  Seq Scan on p1 p_1
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+         ->  Seq Scan on p2 p_2
+               Filter: (((c)::text = '000001'::text) OR ((c)::text = '000002'::text))
+   ->  Append
+         ->  Seq Scan on q1 q_1
+         ->  Seq Scan on q2 q_2
+(10 rows)
+
+deallocate s;
+deallocate s2;
+deallocate s3;
+deallocate s4;
+deallocate s5;
+deallocate s6;
+deallocate s7;
+deallocate s8;
+reset plan_cache_mode;
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 6ccb52ad1d..f15cc3d731 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -1185,3 +1185,113 @@ explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 a
 drop table hp_contradict_test;
 drop operator class part_test_int4_ops2 using hash;
 drop operator ===(int4, int4);
+
+create table p (a int, b int, c character varying(8)) partition by list(c);
+create table p1  partition of p for values in ('000001');
+create table p2  partition of p for values in ('000002');
+create table p3  partition of p for values in ('000003');
+
+create table q (a int, c character varying(8), b int) partition by list(c);
+create table q1  partition of q for values in ('000001');
+create table q2  partition of q for values in ('000002');
+create table q3  partition of q for values in ('000003');
+
+create table o (a int, c character varying(8), b int) partition by list(c);
+create table o1  partition of o for values in ('000001');
+create table o2 partition of o for values in ('000002');
+create table o3  partition of o for values in ('000003');
+
+analyze p;
+analyze q;
+analyze o;
+
+set plan_cache_mode to force_generic_plan ;
+-- inner join
+prepare s as select * from p, q where (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s('000001', '000002');
+
+prepare s2 as select * from p inner join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s2('000001', '000002');
+
+-- left join
+-- q can have the init partititon prune, but p can't since it is the outer join.
+prepare s3 as select * from p left join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s3('000001', '000002');
+
+-- p can't do the init partition prune since it is the outer join. so p can't as well.
+prepare s4 as select * from p left join q on (p.c = $1 or p.c = $2) and p.c = q.c;
+explain (costs off) execute s4('000001', '000002');
+
+-- left join is reduced to inner join, so both p and q can do the pruning.
+prepare s5 as select * from p left join q on p.c = q.c where (q.c = $1 or q.c = $2);
+explain (costs off) execute s5('000001', '000002');
+
+-- left join can't be reduced to inner join, but p.c is not in ON clause so q can.
+-- and q is right table in left join, so it can as well.
+prepare s6 as select * from p left join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s6('000001', '000002');
+
+-- full outer join.
+-- both p & q can't do the init partition prune.
+prepare s7 as select * from p full join q on p.c = q.c and (p.c = $1 or p.c = $2);
+explain (costs off) execute s7('000001', '000002');
+
+-- can be reduced to p left join q, so both can.
+prepare s8 as select * from p full join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s8('000001', '000002');
+
+deallocate s;
+deallocate s2;
+deallocate s3;
+deallocate s4;
+deallocate s5;
+deallocate s6;
+deallocate s7;
+deallocate s8;
+
+set plan_cache_mode to force_custom_plan;
+-- inner join
+prepare s as select * from p, q where (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s('000001', '000002');
+
+prepare s2 as select * from p inner join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s2('000001', '000002');
+
+-- left join
+-- q can have the planning partititon prune, but p can't since it is the outer join.
+prepare s3 as select * from p left join q on (q.c = $1 or q.c = $2) and p.c = q.c;
+explain (costs off) execute s3('000001', '000002');
+
+-- p can't do the planning partition prune since it is the outer join. so p can't as well.
+prepare s4 as select * from p left join q on (p.c = $1 or p.c = $2) and p.c = q.c;
+explain (costs off) execute s4('000001', '000002');
+
+-- left join is reduced to inner join, so both p and q can do the pruning.
+prepare s5 as select * from p left join q on p.c = q.c where (q.c = $1 or q.c = $2);
+explain (costs off) execute s5('000001', '000002');
+
+-- left join can't be reduced to inner join, but p.c is not in ON clause so q can.
+-- and q is right table in left join, so it can as well.
+prepare s6 as select * from p left join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s6('000001', '000002');
+
+-- full outer join.
+-- both p & q can't do the planning partition prune.
+prepare s7 as select * from p full join q on p.c = q.c and (p.c = $1 or p.c = $2);
+explain (costs off) execute s7('000001', '000002');
+
+-- can be reduced to p left join q, so both can.
+prepare s8 as select * from p full join q on p.c = q.c where (p.c = $1 or p.c = $2);
+explain (costs off) execute s8('000001', '000002');
+
+deallocate s;
+deallocate s2;
+deallocate s3;
+deallocate s4;
+deallocate s5;
+deallocate s6;
+deallocate s7;
+deallocate s8;
+
+
+reset plan_cache_mode;
-- 
2.21.0