v34-0001-Speed-up-searches-for-child-EquivalenceMembers.patch

From 8a88942c01e73976ad93f4e99ee782839a6b4650 Mon Sep 17 00:00:00 2001
From: Yuya Watari <watari.yuya@gmail.com>
Date: Fri, 25 Aug 2023 10:43:36 +0900
Subject: [PATCH v34 1/4] Speed up searches for child EquivalenceMembers

Traditionally, child EquivalenceMembers were in the
EquivalenceClass->ec_members. When we wanted to find some child members
matching a request, we had to perform a linear search. This search
became heavy when tables had many partitions, leading to much planning
time.

After this commit, child EquivalenceMembers no longer exist in
ec_members. Instead, RelOptInfos have them. This change demonstrates a
significant performance improvement in planning time.
---
 contrib/postgres_fdw/postgres_fdw.c     |  15 +-
 src/backend/optimizer/path/equivclass.c | 449 +++++++++++++++++++-----
 src/backend/optimizer/path/indxpath.c   |  15 +-
 src/backend/optimizer/path/pathkeys.c   |   9 +-
 src/backend/optimizer/plan/createplan.c |  61 ++--
 src/backend/optimizer/util/relnode.c    |  16 +
 src/include/nodes/pathnodes.h           |  79 +++++
 src/include/optimizer/paths.h           |   9 +-
 src/tools/pgindent/typedefs.list        |   2 +
 9 files changed, 525 insertions(+), 130 deletions(-)

diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 1131a8bf77e..2eb0f16b0f6 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -7829,14 +7829,13 @@ conversion_error_callback(void *arg)
 EquivalenceMember *
 find_em_for_rel(PlannerInfo *root, EquivalenceClass *ec, RelOptInfo *rel)
 {
-	ListCell   *lc;
-
 	PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private;
+	EquivalenceAllMemberIterator it;
+	EquivalenceMember *em;
 
-	foreach(lc, ec->ec_members)
+	setup_eclass_all_member_iterator_for_relids(&it, root, ec, rel->relids);
+	while ((em = eclass_all_member_iterator_for_relids_next(&it)) != NULL)
 	{
-		EquivalenceMember *em = (EquivalenceMember *) lfirst(lc);
-
 		/*
 		 * Note we require !bms_is_empty, else we'd accept constant
 		 * expressions which are not suitable for the purpose.
@@ -7847,6 +7846,7 @@ find_em_for_rel(PlannerInfo *root, EquivalenceClass *ec, RelOptInfo *rel)
 			is_foreign_expr(root, rel, em->em_expr))
 			return em;
 	}
+	dispose_eclass_all_member_iterator_for_relids(&it);
 
 	return NULL;
 }
@@ -7900,9 +7900,8 @@ find_em_for_rel_target(PlannerInfo *root, EquivalenceClass *ec,
 			if (em->em_is_const)
 				continue;
 
-			/* Ignore child members */
-			if (em->em_is_child)
-				continue;
+			/* Child members should not exist in ec_members */
+			Assert(!em->em_is_child);
 
 			/* Match if same expression (after stripping relabel) */
 			em_expr = em->em_expr;
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 0f9ecf5ee8b..9ac15cca8fa 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -33,11 +33,15 @@
 #include "utils/lsyscache.h"
 
 
-static EquivalenceMember *add_eq_member(EquivalenceClass *ec,
-										Expr *expr, Relids relids,
-										JoinDomain *jdomain,
-										EquivalenceMember *parent,
-										Oid datatype);
+static EquivalenceMember *make_eq_member(EquivalenceClass *ec,
+										 Expr *expr, Relids relids,
+										 JoinDomain *jdomain,
+										 EquivalenceMember *parent,
+										 Oid datatype);
+static EquivalenceMember *add_parent_eq_member(EquivalenceClass *ec,
+											   Expr *expr, Relids relids,
+											   JoinDomain *jdomain,
+											   Oid datatype);
 static void generate_base_implied_equalities_const(PlannerInfo *root,
 												   EquivalenceClass *ec);
 static void generate_base_implied_equalities_no_const(PlannerInfo *root,
@@ -68,6 +72,10 @@ static bool reconsider_outer_join_clause(PlannerInfo *root,
 static bool reconsider_full_join_clause(PlannerInfo *root,
 										OuterJoinClauseInfo *ojcinfo);
 static JoinDomain *find_join_domain(PlannerInfo *root, Relids relids);
+static void add_eclass_child_members_to_iterator(EquivalenceAllMemberIterator *it,
+												 PlannerInfo *root,
+												 EquivalenceClass *ec,
+												 RelOptInfo *child_rel);
 static Bitmapset *get_eclass_indexes_for_relids(PlannerInfo *root,
 												Relids relids);
 static Bitmapset *get_common_eclass_indexes(PlannerInfo *root, Relids relids1,
@@ -372,8 +380,8 @@ process_equivalence(PlannerInfo *root,
 	else if (ec1)
 	{
 		/* Case 3: add item2 to ec1 */
-		em2 = add_eq_member(ec1, item2, item2_relids,
-							jdomain, NULL, item2_type);
+		em2 = add_parent_eq_member(ec1, item2, item2_relids,
+								   jdomain, item2_type);
 		ec1->ec_sources = lappend(ec1->ec_sources, restrictinfo);
 		ec1->ec_min_security = Min(ec1->ec_min_security,
 								   restrictinfo->security_level);
@@ -389,8 +397,8 @@ process_equivalence(PlannerInfo *root,
 	else if (ec2)
 	{
 		/* Case 3: add item1 to ec2 */
-		em1 = add_eq_member(ec2, item1, item1_relids,
-							jdomain, NULL, item1_type);
+		em1 = add_parent_eq_member(ec2, item1, item1_relids,
+								   jdomain, item1_type);
 		ec2->ec_sources = lappend(ec2->ec_sources, restrictinfo);
 		ec2->ec_min_security = Min(ec2->ec_min_security,
 								   restrictinfo->security_level);
@@ -421,10 +429,10 @@ process_equivalence(PlannerInfo *root,
 		ec->ec_min_security = restrictinfo->security_level;
 		ec->ec_max_security = restrictinfo->security_level;
 		ec->ec_merged = NULL;
-		em1 = add_eq_member(ec, item1, item1_relids,
-							jdomain, NULL, item1_type);
-		em2 = add_eq_member(ec, item2, item2_relids,
-							jdomain, NULL, item2_type);
+		em1 = add_parent_eq_member(ec, item1, item1_relids,
+								   jdomain, item1_type);
+		em2 = add_parent_eq_member(ec, item2, item2_relids,
+								   jdomain, item2_type);
 
 		root->eq_classes = lappend(root->eq_classes, ec);
 
@@ -510,11 +518,16 @@ canonicalize_ec_expression(Expr *expr, Oid req_type, Oid req_collation)
 }
 
 /*
- * add_eq_member - build a new EquivalenceMember and add it to an EC
+ * make_eq_member
+ *
+ * Build a new EquivalenceMember without adding it to an EC. If 'parent'
+ * parameter is NULL, the result will be a parent member, otherwise a child
+ * member. Note that child EquivalenceMembers should not be added to its
+ * parent EquivalenceClass.
  */
 static EquivalenceMember *
-add_eq_member(EquivalenceClass *ec, Expr *expr, Relids relids,
-			  JoinDomain *jdomain, EquivalenceMember *parent, Oid datatype)
+make_eq_member(EquivalenceClass *ec, Expr *expr, Relids relids,
+			   JoinDomain *jdomain, EquivalenceMember *parent, Oid datatype)
 {
 	EquivalenceMember *em = makeNode(EquivalenceMember);
 
@@ -525,6 +538,7 @@ add_eq_member(EquivalenceClass *ec, Expr *expr, Relids relids,
 	em->em_datatype = datatype;
 	em->em_jdomain = jdomain;
 	em->em_parent = parent;
+	em->em_ec = ec;
 
 	if (bms_is_empty(relids))
 	{
@@ -545,11 +559,30 @@ add_eq_member(EquivalenceClass *ec, Expr *expr, Relids relids,
 	{
 		ec->ec_relids = bms_add_members(ec->ec_relids, relids);
 	}
-	ec->ec_members = lappend(ec->ec_members, em);
 
 	return em;
 }
 
+/*
+ * add_parent_eq_member - build a new parent EquivalenceMember and add it to an EC
+ *
+ * Note: We don't have a function to add a child member like
+ * add_child_eq_member() because how to do it depends on the relations they
+ * are translated from. See add_child_rel_equivalences(),
+ * add_child_join_rel_equivalences() and add_setop_child_rel_equivalences()
+ * to see how to add child members.
+ */
+static EquivalenceMember *
+add_parent_eq_member(EquivalenceClass *ec, Expr *expr, Relids relids,
+					 JoinDomain *jdomain, Oid datatype)
+{
+	EquivalenceMember *em = make_eq_member(ec, expr, relids, jdomain,
+										   NULL, datatype);
+
+	ec->ec_members = lappend(ec->ec_members, em);
+	return em;
+}
+
 
 /*
  * get_eclass_for_sort_expr
@@ -616,7 +649,8 @@ get_eclass_for_sort_expr(PlannerInfo *root,
 	foreach(lc1, root->eq_classes)
 	{
 		EquivalenceClass *cur_ec = (EquivalenceClass *) lfirst(lc1);
-		ListCell   *lc2;
+		EquivalenceAllMemberIterator it;
+		EquivalenceMember *cur_em;
 
 		/*
 		 * Never match to a volatile EC, except when we are looking at another
@@ -631,10 +665,9 @@ get_eclass_for_sort_expr(PlannerInfo *root,
 		if (!equal(opfamilies, cur_ec->ec_opfamilies))
 			continue;
 
-		foreach(lc2, cur_ec->ec_members)
+		setup_eclass_all_member_iterator_for_relids(&it, root, cur_ec, rel);
+		while ((cur_em = eclass_all_member_iterator_for_relids_next(&it)) != NULL)
 		{
-			EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc2);
-
 			/*
 			 * Ignore child members unless they match the request.
 			 */
@@ -653,6 +686,7 @@ get_eclass_for_sort_expr(PlannerInfo *root,
 				equal(expr, cur_em->em_expr))
 				return cur_ec;	/* Match! */
 		}
+		dispose_eclass_all_member_iterator_for_relids(&it);
 	}
 
 	/* No match; does caller want a NULL result? */
@@ -689,14 +723,14 @@ get_eclass_for_sort_expr(PlannerInfo *root,
 	 */
 	expr_relids = pull_varnos(root, (Node *) expr);
 
-	newem = add_eq_member(newec, copyObject(expr), expr_relids,
-						  jdomain, NULL, opcintype);
+	newem = add_parent_eq_member(newec, copyObject(expr), expr_relids,
+								 jdomain, opcintype);
 
 	/*
-	 * add_eq_member doesn't check for volatile functions, set-returning
-	 * functions, aggregates, or window functions, but such could appear in
-	 * sort expressions; so we have to check whether its const-marking was
-	 * correct.
+	 * add_parent_eq_member doesn't check for volatile functions,
+	 * set-returning functions, aggregates, or window functions, but such
+	 * could appear in sort expressions; so we have to check whether its
+	 * const-marking was correct.
 	 */
 	if (newec->ec_has_const)
 	{
@@ -760,19 +794,20 @@ get_eclass_for_sort_expr(PlannerInfo *root,
  * Child EC members are ignored unless they belong to given 'relids'.
  */
 EquivalenceMember *
-find_ec_member_matching_expr(EquivalenceClass *ec,
+find_ec_member_matching_expr(PlannerInfo *root, EquivalenceClass *ec,
 							 Expr *expr,
 							 Relids relids)
 {
-	ListCell   *lc;
+	EquivalenceAllMemberIterator it;
+	EquivalenceMember *em;
 
 	/* We ignore binary-compatible relabeling on both ends */
 	while (expr && IsA(expr, RelabelType))
 		expr = ((RelabelType *) expr)->arg;
 
-	foreach(lc, ec->ec_members)
+	setup_eclass_all_member_iterator_for_relids(&it, root, ec, relids);
+	while ((em = eclass_all_member_iterator_for_relids_next(&it)) != NULL)
 	{
-		EquivalenceMember *em = (EquivalenceMember *) lfirst(lc);
 		Expr	   *emexpr;
 
 		/*
@@ -799,6 +834,7 @@ find_ec_member_matching_expr(EquivalenceClass *ec,
 		if (equal(emexpr, expr))
 			return em;
 	}
+	dispose_eclass_all_member_iterator_for_relids(&it);
 
 	return NULL;
 }
@@ -841,7 +877,8 @@ find_computable_ec_member(PlannerInfo *root,
 						  bool require_parallel_safe)
 {
 	List	   *exprvars;
-	ListCell   *lc;
+	EquivalenceAllMemberIterator it;
+	EquivalenceMember *em;
 
 	/*
 	 * Pull out the Vars and quasi-Vars present in "exprs".  In the typical
@@ -855,9 +892,9 @@ find_computable_ec_member(PlannerInfo *root,
 							   PVC_INCLUDE_PLACEHOLDERS |
 							   PVC_INCLUDE_CONVERTROWTYPES);
 
-	foreach(lc, ec->ec_members)
+	setup_eclass_all_member_iterator_for_relids(&it, root, ec, relids);
+	while ((em = eclass_all_member_iterator_for_relids_next(&it)) != NULL)
 	{
-		EquivalenceMember *em = (EquivalenceMember *) lfirst(lc);
 		List	   *emvars;
 		ListCell   *lc2;
 
@@ -901,6 +938,7 @@ find_computable_ec_member(PlannerInfo *root,
 
 		return em;				/* found usable expression */
 	}
+	dispose_eclass_all_member_iterator_for_relids(&it);
 
 	return NULL;
 }
@@ -939,7 +977,7 @@ relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
 	{
 		Expr	   *targetexpr = (Expr *) lfirst(lc);
 
-		em = find_ec_member_matching_expr(ec, targetexpr, rel->relids);
+		em = find_ec_member_matching_expr(root, ec, targetexpr, rel->relids);
 		if (!em)
 			continue;
 
@@ -1564,7 +1602,8 @@ generate_join_implied_equalities_normal(PlannerInfo *root,
 	List	   *new_members = NIL;
 	List	   *outer_members = NIL;
 	List	   *inner_members = NIL;
-	ListCell   *lc1;
+	EquivalenceAllMemberIterator it;
+	EquivalenceMember *cur_em;
 
 	/*
 	 * First, scan the EC to identify member values that are computable at the
@@ -1575,10 +1614,9 @@ generate_join_implied_equalities_normal(PlannerInfo *root,
 	 * as well as to at least one input member, plus enforce at least one
 	 * outer-rel member equal to at least one inner-rel member.
 	 */
-	foreach(lc1, ec->ec_members)
+	setup_eclass_all_member_iterator_for_relids(&it, root, ec, join_relids);
+	while ((cur_em = eclass_all_member_iterator_for_relids_next(&it)) != NULL)
 	{
-		EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc1);
-
 		/*
 		 * We don't need to check explicitly for child EC members.  This test
 		 * against join_relids will cause them to be ignored except when
@@ -1594,6 +1632,7 @@ generate_join_implied_equalities_normal(PlannerInfo *root,
 		else
 			new_members = lappend(new_members, cur_em);
 	}
+	dispose_eclass_all_member_iterator_for_relids(&it);
 
 	/*
 	 * First, select the joinclause if needed.  We can equate any one outer
@@ -1611,6 +1650,7 @@ generate_join_implied_equalities_normal(PlannerInfo *root,
 		Oid			best_eq_op = InvalidOid;
 		int			best_score = -1;
 		RestrictInfo *rinfo;
+		ListCell   *lc1;
 
 		foreach(lc1, outer_members)
 		{
@@ -1685,6 +1725,7 @@ generate_join_implied_equalities_normal(PlannerInfo *root,
 		List	   *old_members = list_concat(outer_members, inner_members);
 		EquivalenceMember *prev_em = NULL;
 		RestrictInfo *rinfo;
+		ListCell   *lc1;
 
 		/* For now, arbitrarily take the first old_member as the one to use */
 		if (old_members)
@@ -1692,7 +1733,7 @@ generate_join_implied_equalities_normal(PlannerInfo *root,
 
 		foreach(lc1, new_members)
 		{
-			EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc1);
+			cur_em = (EquivalenceMember *) lfirst(lc1);
 
 			if (prev_em != NULL)
 			{
@@ -2405,6 +2446,7 @@ reconsider_full_join_clause(PlannerInfo *root, OuterJoinClauseInfo *ojcinfo)
 		if (matchleft && matchright)
 		{
 			cur_ec->ec_members = list_delete_nth_cell(cur_ec->ec_members, coal_idx);
+			Assert(!bms_is_empty(coal_em->em_relids));
 			return true;
 		}
 
@@ -2526,8 +2568,8 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node *item2, Oid opfamily)
 		{
 			EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2);
 
-			if (em->em_is_child)
-				continue;		/* ignore children here */
+			/* Child members should not exist in ec_members */
+			Assert(!em->em_is_child);
 			if (equal(item1, em->em_expr))
 				item1member = true;
 			else if (equal(item2, em->em_expr))
@@ -2598,8 +2640,8 @@ match_eclasses_to_foreign_key_col(PlannerInfo *root,
 			EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2);
 			Var		   *var;
 
-			if (em->em_is_child)
-				continue;		/* ignore children here */
+			/* Child members should not exist in ec_members */
+			Assert(!em->em_is_child);
 
 			/* EM must be a Var, possibly with RelabelType */
 			var = (Var *) em->em_expr;
@@ -2696,6 +2738,7 @@ add_child_rel_equivalences(PlannerInfo *root,
 	Relids		top_parent_relids = child_rel->top_parent_relids;
 	Relids		child_relids = child_rel->relids;
 	int			i;
+	ListCell   *lc;
 
 	/*
 	 * EC merging should be complete already, so we can use the parent rel's
@@ -2708,7 +2751,6 @@ add_child_rel_equivalences(PlannerInfo *root,
 	while ((i = bms_next_member(parent_rel->eclass_indexes, i)) >= 0)
 	{
 		EquivalenceClass *cur_ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
-		int			num_members;
 
 		/*
 		 * If this EC contains a volatile expression, then generating child
@@ -2721,15 +2763,9 @@ add_child_rel_equivalences(PlannerInfo *root,
 		/* Sanity check eclass_indexes only contain ECs for parent_rel */
 		Assert(bms_is_subset(top_parent_relids, cur_ec->ec_relids));
 
-		/*
-		 * We don't use foreach() here because there's no point in scanning
-		 * newly-added child members, so we can stop after the last
-		 * pre-existing EC member.
-		 */
-		num_members = list_length(cur_ec->ec_members);
-		for (int pos = 0; pos < num_members; pos++)
+		foreach(lc, cur_ec->ec_members)
 		{
-			EquivalenceMember *cur_em = (EquivalenceMember *) list_nth(cur_ec->ec_members, pos);
+			EquivalenceMember *cur_em = lfirst_node(EquivalenceMember, lc);
 
 			if (cur_em->em_is_const)
 				continue;		/* ignore consts here */
@@ -2742,8 +2778,8 @@ add_child_rel_equivalences(PlannerInfo *root,
 			 * combinations of children.  (But add_child_join_rel_equivalences
 			 * may add targeted combinations for partitionwise-join purposes.)
 			 */
-			if (cur_em->em_is_child)
-				continue;		/* ignore children here */
+			Assert(!cur_em->em_is_child);	/* Child members should not exist
+											 * in ec_members */
 
 			/*
 			 * Consider only members that reference and can be computed at
@@ -2759,6 +2795,7 @@ add_child_rel_equivalences(PlannerInfo *root,
 				/* OK, generate transformed child version */
 				Expr	   *child_expr;
 				Relids		new_relids;
+				EquivalenceMember *child_em;
 
 				if (parent_rel->reloptkind == RELOPT_BASEREL)
 				{
@@ -2788,9 +2825,11 @@ add_child_rel_equivalences(PlannerInfo *root,
 											top_parent_relids);
 				new_relids = bms_add_members(new_relids, child_relids);
 
-				(void) add_eq_member(cur_ec, child_expr, new_relids,
-									 cur_em->em_jdomain,
-									 cur_em, cur_em->em_datatype);
+				child_em = make_eq_member(cur_ec, child_expr, new_relids,
+										  cur_em->em_jdomain,
+										  cur_em, cur_em->em_datatype);
+				child_rel->eclass_child_members =
+					lappend(child_rel->eclass_child_members, child_em);
 
 				/* Record this EC index for the child rel */
 				child_rel->eclass_indexes = bms_add_member(child_rel->eclass_indexes, i);
@@ -2819,6 +2858,7 @@ add_child_join_rel_equivalences(PlannerInfo *root,
 	Relids		child_relids = child_joinrel->relids;
 	Bitmapset  *matching_ecs;
 	MemoryContext oldcontext;
+	ListCell   *lc;
 	int			i;
 
 	Assert(IS_JOIN_REL(child_joinrel) && IS_JOIN_REL(parent_joinrel));
@@ -2840,7 +2880,6 @@ add_child_join_rel_equivalences(PlannerInfo *root,
 	while ((i = bms_next_member(matching_ecs, i)) >= 0)
 	{
 		EquivalenceClass *cur_ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
-		int			num_members;
 
 		/*
 		 * If this EC contains a volatile expression, then generating child
@@ -2853,15 +2892,9 @@ add_child_join_rel_equivalences(PlannerInfo *root,
 		/* Sanity check on get_eclass_indexes_for_relids result */
 		Assert(bms_overlap(top_parent_relids, cur_ec->ec_relids));
 
-		/*
-		 * We don't use foreach() here because there's no point in scanning
-		 * newly-added child members, so we can stop after the last
-		 * pre-existing EC member.
-		 */
-		num_members = list_length(cur_ec->ec_members);
-		for (int pos = 0; pos < num_members; pos++)
+		foreach(lc, cur_ec->ec_members)
 		{
-			EquivalenceMember *cur_em = (EquivalenceMember *) list_nth(cur_ec->ec_members, pos);
+			EquivalenceMember *cur_em = lfirst_node(EquivalenceMember, lc);
 
 			if (cur_em->em_is_const)
 				continue;		/* ignore consts here */
@@ -2870,8 +2903,8 @@ add_child_join_rel_equivalences(PlannerInfo *root,
 			 * We consider only original EC members here, not
 			 * already-transformed child members.
 			 */
-			if (cur_em->em_is_child)
-				continue;		/* ignore children here */
+			Assert(!cur_em->em_is_child);	/* Child members should not exist
+											 * in ec_members */
 
 			/*
 			 * We may ignore expressions that reference a single baserel,
@@ -2886,6 +2919,8 @@ add_child_join_rel_equivalences(PlannerInfo *root,
 				/* Yes, generate transformed child version */
 				Expr	   *child_expr;
 				Relids		new_relids;
+				EquivalenceMember *child_em;
+				int			j;
 
 				if (parent_joinrel->reloptkind == RELOPT_JOINREL)
 				{
@@ -2916,9 +2951,38 @@ add_child_join_rel_equivalences(PlannerInfo *root,
 											top_parent_relids);
 				new_relids = bms_add_members(new_relids, child_relids);
 
-				(void) add_eq_member(cur_ec, child_expr, new_relids,
-									 cur_em->em_jdomain,
-									 cur_em, cur_em->em_datatype);
+				child_em = make_eq_member(cur_ec, child_expr, new_relids,
+										  cur_em->em_jdomain,
+										  cur_em, cur_em->em_datatype);
+				child_joinrel->eclass_child_members =
+					lappend(child_joinrel->eclass_child_members, child_em);
+
+				/*
+				 * Update the corresponding inverted indexes.
+				 */
+				j = -1;
+				while ((j = bms_next_member(child_joinrel->relids, j)) >= 0)
+				{
+					EquivalenceClassIndexes *indexes =
+						&root->eclass_indexes_array[j];
+
+					/*
+					 * We do not need to update the inverted index of the top
+					 * parent relations. This is because EquivalenceMembers
+					 * that have only such parent relations as em_relids are
+					 * already present in the ec_members, and so cannot be
+					 * candidates for additional iteration by
+					 * EquivalenceAllMemberIterator. Since the iterator needs
+					 * EquivalenceMembers whose em_relids has child relations,
+					 * skipping the update of this inverted index allows for
+					 * faster iteration.
+					 */
+					if (root->append_rel_array[j] == NULL)
+						continue;
+					indexes->joinrel_indexes =
+						bms_add_member(indexes->joinrel_indexes,
+									   child_joinrel->join_rel_list_index);
+				}
 			}
 		}
 	}
@@ -2967,12 +3031,12 @@ add_setop_child_rel_equivalences(PlannerInfo *root, RelOptInfo *child_rel,
 		 * likewise, the JoinDomain can be that of the initial member of the
 		 * Pathkey's EquivalenceClass.
 		 */
-		add_eq_member(pk->pk_eclass,
-					  tle->expr,
-					  child_rel->relids,
-					  parent_em->em_jdomain,
-					  parent_em,
-					  exprType((Node *) tle->expr));
+		add_parent_eq_member(pk->pk_eclass,
+							 tle->expr,
+							 child_rel->relids,
+							 parent_em->em_jdomain,
+							 parent_em,
+							 exprType((Node *) tle->expr));
 
 		lc2 = lnext(setop_pathkeys, lc2);
 	}
@@ -2987,6 +3051,219 @@ add_setop_child_rel_equivalences(PlannerInfo *root, RelOptInfo *child_rel,
 											  list_length(root->eq_classes) - 1);
 }
 
+/*
+ * setup_eclass_all_member_iterator_for_relids
+ *	  Setup an EquivalenceAllMemberIterator 'it' to iterate over all parent
+ *	  EquivalenceMembers and child members associated with the given 'ec' that
+ *	  are relevant to the specified 'relids'.
+ *
+ * This iterator returns:
+ *	- All parent members stored directly in ec->ec_members.
+ *	- The child members whose em_relids is a subset of the given 'relids'.
+ *
+ * Note:
+ *	- The iterator may return false positives, i.e., child members whose
+ *	  em_relids is not a subset. So the caller must check that they satisfy
+ *	  the desired condition.
+ *	- Once used, the caller should dispose of the iterator by calling
+ *	  dispose_eclass_all_member_iterator_for_relids().
+ *
+ * Parameters:
+ *	root - The PlannerInfo context.
+ *	ec - The EquivalenceClass from which to iterate members.
+ *	relids - The Relids used to filter for relevant child members.
+ */
+void
+setup_eclass_all_member_iterator_for_relids(EquivalenceAllMemberIterator *it,
+											PlannerInfo *root,
+											EquivalenceClass *ec,
+											Relids relids)
+{
+	Bitmapset  *matching_indexes;
+	int			i;
+
+	/*
+	 * Initialize the iterator.
+	 */
+	it->index = -1;
+	it->modified = false;
+	it->ec_members = ec->ec_members;
+
+	/*
+	 * If there are no child relations, there is nothing to do. This
+	 * effectively avoids regression for non-partitioned cases.
+	 */
+	if (root->append_rel_array == NULL)
+		return;
+
+	/*
+	 * EquivalenceClass->ec_members has only parent EquivalenceMembers. Child
+	 * members are translated using child RelOptInfos and stored in them. This
+	 * is done in add_child_rel_equivalences(),
+	 * add_child_join_rel_equivalences(), and
+	 * add_setop_child_rel_equivalences(). To retrieve child
+	 * EquivalenceMembers of some parent, we need to know which RelOptInfos
+	 * have such child members. We can know this information using indexes
+	 * like EquivalenceClassIndexes->joinrel_indexes.
+	 *
+	 * We use an inverted index mechanism to quickly iterate over the members
+	 * whose em_relids is a subset of the given 'child_relids'. The inverted
+	 * indexes store RelOptInfo indices that have EquivalenceMembers
+	 * mentioning them. Taking the union of these indexes allows to find which
+	 * RelOptInfos have the EquivalenceMember we are looking for. With this
+	 * method, the em_relids of the newly iterated ones overlap the given
+	 * 'child_relids', but may not be subsets, so the caller must check that
+	 * they satisfy the desired condition.
+	 *
+	 * The above comments are about joinrels, and for simple rels, this
+	 * mechanism is simpler. It is sufficient to simply add the child
+	 * EquivalenceMembers of RelOptInfo to the iterator.
+	 *
+	 * We need to perform these steps for each of the two types of relations.
+	 */
+
+	/*
+	 * Iterate over the given relids, adding child members for simple rels and
+	 * taking union indexes for join rels.
+	 */
+	i = -1;
+	matching_indexes = NULL;
+	while ((i = bms_next_member(relids, i)) >= 0)
+	{
+		RelOptInfo *child_rel;
+		EquivalenceClassIndexes *indexes;
+
+		/*
+		 * If this relation is a parent, we don't have to do anything.
+		 */
+		if (root->append_rel_array[i] == NULL)
+			continue;
+
+		/*
+		 * Add child members that mention this relation to the iterator.
+		 */
+		child_rel = root->simple_rel_array[i];
+		if (child_rel != NULL)
+			add_eclass_child_members_to_iterator(it, root, ec, child_rel);
+
+		/*
+		 * Union indexes for join rels.
+		 */
+		indexes = &root->eclass_indexes_array[i];
+		matching_indexes =
+			bms_add_members(matching_indexes, indexes->joinrel_indexes);
+	}
+
+	/*
+	 * For join rels, add child members using 'matching_indexes'.
+	 */
+	i = -1;
+	while ((i = bms_next_member(matching_indexes, i)) >= 0)
+	{
+		RelOptInfo *child_joinrel =
+			list_nth_node(RelOptInfo, root->join_rel_list, i);
+
+		Assert(child_joinrel != NULL);
+
+		/*
+		 * If this joinrel's Relids is not a subset of the given one, then the
+		 * child EquivalenceMembers it holds should never be a subset either.
+		 */
+		if (bms_is_subset(child_joinrel->relids, relids))
+			add_eclass_child_members_to_iterator(it, root, ec, child_joinrel);
+#ifdef USE_ASSERT_CHECKING
+		else
+		{
+			/*
+			 * Verify that the above comment is correct.
+			 *
+			 * NOTE: We may remove this assertion after the beta process.
+			 */
+
+			ListCell   *lc;
+
+			foreach(lc, child_joinrel->eclass_child_members)
+			{
+				EquivalenceMember *child_em = lfirst_node(EquivalenceMember, lc);
+
+				if (child_em->em_ec != ec)
+					continue;
+				Assert(!bms_is_subset(child_em->em_relids, relids));
+			}
+		}
+#endif
+	}
+	bms_free(matching_indexes);
+}
+
+/*
+ * add_eclass_child_members_to_iterator
+ *	  Add a child EquivalenceMember referencing the given child_rel to
+ *	  the iterator from RelOptInfo.
+ *
+ * This function is expected to be called only from
+ * setup_eclass_all_member_iterator_for_relids().
+ */
+static void
+add_eclass_child_members_to_iterator(EquivalenceAllMemberIterator *it,
+									 PlannerInfo *root,
+									 EquivalenceClass *ec,
+									 RelOptInfo *child_rel)
+{
+	ListCell   *lc;
+
+	foreach(lc, child_rel->eclass_child_members)
+	{
+		EquivalenceMember *child_em = lfirst_node(EquivalenceMember, lc);
+
+		/* Skip unwanted EquivalenceMembers */
+		if (child_em->em_ec != ec)
+			continue;
+
+		/*
+		 * If this is the first time the iterator's list has been modified, we
+		 * need to make a copy of it.
+		 */
+		if (!it->modified)
+		{
+			it->ec_members = list_copy(it->ec_members);
+			it->modified = true;
+		}
+
+		/* Add this child EquivalenceMember to the list */
+		it->ec_members = lappend(it->ec_members, child_em);
+	}
+}
+
+/*
+ * eclass_all_member_iterator_for_relids_next
+ *	  Get a next EquivalenceMember from an EquivalenceAllMemberIterator 'it'
+ *	  that was setup by setup_eclass_all_member_iterator_for_relids(). NULL is
+ *	  returned if there are no members left.
+ */
+EquivalenceMember *
+eclass_all_member_iterator_for_relids_next(EquivalenceAllMemberIterator *it)
+{
+	if (++it->index < list_length(it->ec_members))
+		return list_nth_node(EquivalenceMember, it->ec_members, it->index);
+	return NULL;
+}
+
+/*
+ * dispose_eclass_all_member_iterator_for_relids
+ *	  Free any memory allocated by the iterator.
+ */
+void
+dispose_eclass_all_member_iterator_for_relids(EquivalenceAllMemberIterator *it)
+{
+	/*
+	 * XXX Should we use list_free()? I decided to use this style to take
+	 * advantage of speculative execution.
+	 */
+	if (unlikely(it->modified))
+		pfree(it->ec_members);
+}
+
 
 /*
  * generate_implied_equalities_for_column
@@ -3041,6 +3318,7 @@ generate_implied_equalities_for_column(PlannerInfo *root,
 		EquivalenceClass *cur_ec = (EquivalenceClass *) list_nth(root->eq_classes, i);
 		EquivalenceMember *cur_em;
 		ListCell   *lc2;
+		EquivalenceAllMemberIterator it;
 
 		/* Sanity check eclass_indexes only contain ECs for rel */
 		Assert(is_child_rel || bms_is_subset(rel->relids, cur_ec->ec_relids));
@@ -3062,15 +3340,14 @@ generate_implied_equalities_for_column(PlannerInfo *root,
 		 * corner cases, so for now we live with just reporting the first
 		 * match.  See also get_eclass_for_sort_expr.)
 		 */
-		cur_em = NULL;
-		foreach(lc2, cur_ec->ec_members)
+		setup_eclass_all_member_iterator_for_relids(&it, root, cur_ec, rel->relids);
+		while ((cur_em = eclass_all_member_iterator_for_relids_next(&it)) != NULL)
 		{
-			cur_em = (EquivalenceMember *) lfirst(lc2);
 			if (bms_equal(cur_em->em_relids, rel->relids) &&
 				callback(root, rel, cur_ec, cur_em, callback_arg))
 				break;
-			cur_em = NULL;
 		}
+		dispose_eclass_all_member_iterator_for_relids(&it);
 
 		if (!cur_em)
 			continue;
@@ -3085,8 +3362,8 @@ generate_implied_equalities_for_column(PlannerInfo *root,
 			Oid			eq_op;
 			RestrictInfo *rinfo;
 
-			if (other_em->em_is_child)
-				continue;		/* ignore children here */
+			/* Child members should not exist in ec_members */
+			Assert(!other_em->em_is_child);
 
 			/* Make sure it'll be a join to a different rel */
 			if (other_em == cur_em ||
@@ -3304,8 +3581,8 @@ eclass_useful_for_merging(PlannerInfo *root,
 	{
 		EquivalenceMember *cur_em = (EquivalenceMember *) lfirst(lc);
 
-		if (cur_em->em_is_child)
-			continue;			/* ignore children here */
+		/* Child members should not exist in ec_members */
+		Assert(!cur_em->em_is_child);
 
 		if (!bms_overlap(cur_em->em_relids, relids))
 			return true;
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index a43ca16d683..38f7322caf9 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -190,7 +190,7 @@ static IndexClause *expand_indexqual_rowcompare(PlannerInfo *root,
 												IndexOptInfo *index,
 												Oid expr_op,
 												bool var_on_left);
-static void match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys,
+static void match_pathkeys_to_index(PlannerInfo *root, IndexOptInfo *index, List *pathkeys,
 									List **orderby_clauses_p,
 									List **clause_columns_p);
 static Expr *match_clause_to_ordering_op(IndexOptInfo *index,
@@ -934,7 +934,7 @@ build_index_paths(PlannerInfo *root, RelOptInfo *rel,
 		 * query_pathkeys will allow an incremental sort to be considered on
 		 * the index's partially sorted results.
 		 */
-		match_pathkeys_to_index(index, root->query_pathkeys,
+		match_pathkeys_to_index(root, index, root->query_pathkeys,
 								&orderbyclauses,
 								&orderbyclausecols);
 		if (list_length(root->query_pathkeys) == list_length(orderbyclauses))
@@ -3739,7 +3739,7 @@ expand_indexqual_rowcompare(PlannerInfo *root,
  * item in the given 'pathkeys' list.
  */
 static void
-match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys,
+match_pathkeys_to_index(PlannerInfo *root, IndexOptInfo *index, List *pathkeys,
 						List **orderby_clauses_p,
 						List **clause_columns_p)
 {
@@ -3756,7 +3756,8 @@ match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys,
 	{
 		PathKey    *pathkey = (PathKey *) lfirst(lc1);
 		bool		found = false;
-		ListCell   *lc2;
+		EquivalenceAllMemberIterator it;
+		EquivalenceMember *member;
 
 
 		/* Pathkey must request default sort order for the target opfamily */
@@ -3776,9 +3777,10 @@ match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys,
 		 * be considered to match more than one pathkey list, which is OK
 		 * here.  See also get_eclass_for_sort_expr.)
 		 */
-		foreach(lc2, pathkey->pk_eclass->ec_members)
+		setup_eclass_all_member_iterator_for_relids(&it, root, pathkey->pk_eclass,
+													index->rel->relids);
+		while ((member = eclass_all_member_iterator_for_relids_next(&it)) != NULL)
 		{
-			EquivalenceMember *member = (EquivalenceMember *) lfirst(lc2);
 			int			indexcol;
 
 			/* No possibility of match if it references other relations */
@@ -3813,6 +3815,7 @@ match_pathkeys_to_index(IndexOptInfo *index, List *pathkeys,
 			if (found)			/* don't want to look at remaining members */
 				break;
 		}
+		dispose_eclass_all_member_iterator_for_relids(&it);
 
 		/*
 		 * Return the matches found so far when this pathkey couldn't be
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 154eb505d75..a9419d37e2f 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -1151,8 +1151,8 @@ convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
 				Oid			sub_expr_coll = sub_eclass->ec_collation;
 				ListCell   *k;
 
-				if (sub_member->em_is_child)
-					continue;	/* ignore children here */
+				/* Child members should not exist in ec_members */
+				Assert(!sub_member->em_is_child);
 
 				foreach(k, subquery_tlist)
 				{
@@ -1709,8 +1709,11 @@ select_outer_pathkeys_for_merge(PlannerInfo *root,
 		{
 			EquivalenceMember *em = (EquivalenceMember *) lfirst(lc2);
 
+			/* Child members should not exist in ec_members */
+			Assert(!em->em_is_child);
+
 			/* Potential future join partner? */
-			if (!em->em_is_const && !em->em_is_child &&
+			if (!em->em_is_const &&
 				!bms_overlap(em->em_relids, joinrel->relids))
 				score++;
 		}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 75e2b0b9036..eff5d93aa3a 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -264,7 +264,9 @@ static IncrementalSort *make_incrementalsort(Plan *lefttree,
 											 int numCols, int nPresortedCols,
 											 AttrNumber *sortColIdx, Oid *sortOperators,
 											 Oid *collations, bool *nullsFirst);
-static Plan *prepare_sort_from_pathkeys(Plan *lefttree, List *pathkeys,
+static Plan *prepare_sort_from_pathkeys(PlannerInfo *root,
+										Plan *lefttree,
+										List *pathkeys,
 										Relids relids,
 										const AttrNumber *reqColIdx,
 										bool adjust_tlist_in_place,
@@ -273,9 +275,11 @@ static Plan *prepare_sort_from_pathkeys(Plan *lefttree, List *pathkeys,
 										Oid **p_sortOperators,
 										Oid **p_collations,
 										bool **p_nullsFirst);
-static Sort *make_sort_from_pathkeys(Plan *lefttree, List *pathkeys,
+static Sort *make_sort_from_pathkeys(PlannerInfo *root,
+									 Plan *lefttree,
+									 List *pathkeys,
 									 Relids relids);
-static IncrementalSort *make_incrementalsort_from_pathkeys(Plan *lefttree,
+static IncrementalSort *make_incrementalsort_from_pathkeys(PlannerInfo *root, Plan *lefttree,
 														   List *pathkeys, Relids relids, int nPresortedCols);
 static Sort *make_sort_from_groupcols(List *groupcls,
 									  AttrNumber *grpColIdx,
@@ -294,7 +298,7 @@ static Group *make_group(List *tlist, List *qual, int numGroupCols,
 						 AttrNumber *grpColIdx, Oid *grpOperators, Oid *grpCollations,
 						 Plan *lefttree);
 static Unique *make_unique_from_sortclauses(Plan *lefttree, List *distinctList);
-static Unique *make_unique_from_pathkeys(Plan *lefttree,
+static Unique *make_unique_from_pathkeys(PlannerInfo *root, Plan *lefttree,
 										 List *pathkeys, int numCols);
 static Gather *make_gather(List *qptlist, List *qpqual,
 						   int nworkers, int rescan_param, bool single_copy, Plan *subplan);
@@ -1281,7 +1285,7 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path, int flags)
 		 * function result; it must be the same plan node.  However, we then
 		 * need to detect whether any tlist entries were added.
 		 */
-		(void) prepare_sort_from_pathkeys((Plan *) plan, pathkeys,
+		(void) prepare_sort_from_pathkeys(root, (Plan *) plan, pathkeys,
 										  best_path->path.parent->relids,
 										  NULL,
 										  true,
@@ -1325,7 +1329,7 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path, int flags)
 			 * don't need an explicit sort, to make sure they are returning
 			 * the same sort key columns the Append expects.
 			 */
-			subplan = prepare_sort_from_pathkeys(subplan, pathkeys,
+			subplan = prepare_sort_from_pathkeys(root, subplan, pathkeys,
 												 subpath->parent->relids,
 												 nodeSortColIdx,
 												 false,
@@ -1466,7 +1470,7 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path,
 	 * function result; it must be the same plan node.  However, we then need
 	 * to detect whether any tlist entries were added.
 	 */
-	(void) prepare_sort_from_pathkeys(plan, pathkeys,
+	(void) prepare_sort_from_pathkeys(root, plan, pathkeys,
 									  best_path->path.parent->relids,
 									  NULL,
 									  true,
@@ -1497,7 +1501,7 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path,
 		subplan = create_plan_recurse(root, subpath, CP_EXACT_TLIST);
 
 		/* Compute sort column info, and adjust subplan's tlist as needed */
-		subplan = prepare_sort_from_pathkeys(subplan, pathkeys,
+		subplan = prepare_sort_from_pathkeys(root, subplan, pathkeys,
 											 subpath->parent->relids,
 											 node->sortColIdx,
 											 false,
@@ -1979,7 +1983,7 @@ create_gather_merge_plan(PlannerInfo *root, GatherMergePath *best_path)
 	Assert(pathkeys != NIL);
 
 	/* Compute sort column info, and adjust subplan's tlist as needed */
-	subplan = prepare_sort_from_pathkeys(subplan, pathkeys,
+	subplan = prepare_sort_from_pathkeys(root, subplan, pathkeys,
 										 best_path->subpath->parent->relids,
 										 gm_plan->sortColIdx,
 										 false,
@@ -2193,7 +2197,7 @@ create_sort_plan(PlannerInfo *root, SortPath *best_path, int flags)
 	 * relids. Thus, if this sort path is based on a child relation, we must
 	 * pass its relids.
 	 */
-	plan = make_sort_from_pathkeys(subplan, best_path->path.pathkeys,
+	plan = make_sort_from_pathkeys(root, subplan, best_path->path.pathkeys,
 								   IS_OTHER_REL(best_path->subpath->parent) ?
 								   best_path->path.parent->relids : NULL);
 
@@ -2217,7 +2221,7 @@ create_incrementalsort_plan(PlannerInfo *root, IncrementalSortPath *best_path,
 	/* See comments in create_sort_plan() above */
 	subplan = create_plan_recurse(root, best_path->spath.subpath,
 								  flags | CP_SMALL_TLIST);
-	plan = make_incrementalsort_from_pathkeys(subplan,
+	plan = make_incrementalsort_from_pathkeys(root, subplan,
 											  best_path->spath.path.pathkeys,
 											  IS_OTHER_REL(best_path->spath.subpath->parent) ?
 											  best_path->spath.path.parent->relids : NULL,
@@ -2286,7 +2290,7 @@ create_upper_unique_plan(PlannerInfo *root, UpperUniquePath *best_path, int flag
 	subplan = create_plan_recurse(root, best_path->subpath,
 								  flags | CP_LABEL_TLIST);
 
-	plan = make_unique_from_pathkeys(subplan,
+	plan = make_unique_from_pathkeys(root, subplan,
 									 best_path->path.pathkeys,
 									 best_path->numkeys);
 
@@ -4544,7 +4548,8 @@ create_mergejoin_plan(PlannerInfo *root,
 		if (!use_incremental_sort)
 		{
 			sort_plan = (Plan *)
-				make_sort_from_pathkeys(outer_plan,
+				make_sort_from_pathkeys(root,
+										outer_plan,
 										best_path->outersortkeys,
 										outer_relids);
 
@@ -4553,7 +4558,8 @@ create_mergejoin_plan(PlannerInfo *root,
 		else
 		{
 			sort_plan = (Plan *)
-				make_incrementalsort_from_pathkeys(outer_plan,
+				make_incrementalsort_from_pathkeys(root,
+												   outer_plan,
 												   best_path->outersortkeys,
 												   outer_relids,
 												   presorted_keys);
@@ -4578,7 +4584,7 @@ create_mergejoin_plan(PlannerInfo *root,
 		 */
 
 		Relids		inner_relids = inner_path->parent->relids;
-		Sort	   *sort = make_sort_from_pathkeys(inner_plan,
+		Sort	   *sort = make_sort_from_pathkeys(root, inner_plan,
 												   best_path->innersortkeys,
 												   inner_relids);
 
@@ -6228,7 +6234,7 @@ make_incrementalsort(Plan *lefttree, int numCols, int nPresortedCols,
  * or a Result stacked atop lefttree).
  */
 static Plan *
-prepare_sort_from_pathkeys(Plan *lefttree, List *pathkeys,
+prepare_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
 						   Relids relids,
 						   const AttrNumber *reqColIdx,
 						   bool adjust_tlist_in_place,
@@ -6295,7 +6301,7 @@ prepare_sort_from_pathkeys(Plan *lefttree, List *pathkeys,
 			tle = get_tle_by_resno(tlist, reqColIdx[numsortkeys]);
 			if (tle)
 			{
-				em = find_ec_member_matching_expr(ec, tle->expr, relids);
+				em = find_ec_member_matching_expr(root, ec, tle->expr, relids);
 				if (em)
 				{
 					/* found expr at right place in tlist */
@@ -6323,7 +6329,7 @@ prepare_sort_from_pathkeys(Plan *lefttree, List *pathkeys,
 			foreach(j, tlist)
 			{
 				tle = (TargetEntry *) lfirst(j);
-				em = find_ec_member_matching_expr(ec, tle->expr, relids);
+				em = find_ec_member_matching_expr(root, ec, tle->expr, relids);
 				if (em)
 				{
 					/* found expr already in tlist */
@@ -6339,7 +6345,7 @@ prepare_sort_from_pathkeys(Plan *lefttree, List *pathkeys,
 			/*
 			 * No matching tlist item; look for a computable expression.
 			 */
-			em = find_computable_ec_member(NULL, ec, tlist, relids, false);
+			em = find_computable_ec_member(root, ec, tlist, relids, false);
 			if (!em)
 				elog(ERROR, "could not find pathkey item to sort");
 			pk_datatype = em->em_datatype;
@@ -6410,7 +6416,8 @@ prepare_sort_from_pathkeys(Plan *lefttree, List *pathkeys,
  *	  'relids' is the set of relations required by prepare_sort_from_pathkeys()
  */
 static Sort *
-make_sort_from_pathkeys(Plan *lefttree, List *pathkeys, Relids relids)
+make_sort_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
+						Relids relids)
 {
 	int			numsortkeys;
 	AttrNumber *sortColIdx;
@@ -6419,7 +6426,7 @@ make_sort_from_pathkeys(Plan *lefttree, List *pathkeys, Relids relids)
 	bool	   *nullsFirst;
 
 	/* Compute sort column info, and adjust lefttree as needed */
-	lefttree = prepare_sort_from_pathkeys(lefttree, pathkeys,
+	lefttree = prepare_sort_from_pathkeys(root, lefttree, pathkeys,
 										  relids,
 										  NULL,
 										  false,
@@ -6445,8 +6452,9 @@ make_sort_from_pathkeys(Plan *lefttree, List *pathkeys, Relids relids)
  *	  'nPresortedCols' is the number of presorted columns in input tuples
  */
 static IncrementalSort *
-make_incrementalsort_from_pathkeys(Plan *lefttree, List *pathkeys,
-								   Relids relids, int nPresortedCols)
+make_incrementalsort_from_pathkeys(PlannerInfo *root, Plan *lefttree,
+								   List *pathkeys, Relids relids,
+								   int nPresortedCols)
 {
 	int			numsortkeys;
 	AttrNumber *sortColIdx;
@@ -6455,7 +6463,7 @@ make_incrementalsort_from_pathkeys(Plan *lefttree, List *pathkeys,
 	bool	   *nullsFirst;
 
 	/* Compute sort column info, and adjust lefttree as needed */
-	lefttree = prepare_sort_from_pathkeys(lefttree, pathkeys,
+	lefttree = prepare_sort_from_pathkeys(root, lefttree, pathkeys,
 										  relids,
 										  NULL,
 										  false,
@@ -6812,7 +6820,8 @@ make_unique_from_sortclauses(Plan *lefttree, List *distinctList)
  * as above, but use pathkeys to identify the sort columns and semantics
  */
 static Unique *
-make_unique_from_pathkeys(Plan *lefttree, List *pathkeys, int numCols)
+make_unique_from_pathkeys(PlannerInfo *root, Plan *lefttree, List *pathkeys,
+						  int numCols)
 {
 	Unique	   *node = makeNode(Unique);
 	Plan	   *plan = &node->plan;
@@ -6875,7 +6884,7 @@ make_unique_from_pathkeys(Plan *lefttree, List *pathkeys, int numCols)
 			foreach(j, plan->targetlist)
 			{
 				tle = (TargetEntry *) lfirst(j);
-				em = find_ec_member_matching_expr(ec, tle->expr, NULL);
+				em = find_ec_member_matching_expr(root, ec, tle->expr, NULL);
 				if (em)
 				{
 					/* found expr already in tlist */
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index ff507331a06..8dc51353791 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -119,6 +119,9 @@ setup_simple_rel_arrays(PlannerInfo *root)
 		root->simple_rte_array[rti++] = rte;
 	}
 
+	root->eclass_indexes_array = (EquivalenceClassIndexes *)
+		palloc0(size * sizeof(EquivalenceClassIndexes));
+
 	/* append_rel_array is not needed if there are no AppendRelInfos */
 	if (root->append_rel_list == NIL)
 	{
@@ -181,6 +184,9 @@ expand_planner_arrays(PlannerInfo *root, int add_size)
 		root->append_rel_array =
 			palloc0_array(AppendRelInfo *, new_size);
 
+	root->eclass_indexes_array =
+		repalloc0_array(root->eclass_indexes_array, EquivalenceClassIndexes, root->simple_rel_array_size, new_size);
+
 	root->simple_rel_array_size = new_size;
 }
 
@@ -234,6 +240,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
 	rel->subplan_params = NIL;
 	rel->rel_parallel_workers = -1; /* set up in get_relation_info */
 	rel->amflags = 0;
+	rel->eclass_child_members = NIL;
 	rel->serverid = InvalidOid;
 	if (rte->rtekind == RTE_RELATION)
 	{
@@ -629,6 +636,12 @@ add_join_rel(PlannerInfo *root, RelOptInfo *joinrel)
 	/* GEQO requires us to append the new joinrel to the end of the list! */
 	root->join_rel_list = lappend(root->join_rel_list, joinrel);
 
+	/*
+	 * Store the index of this joinrel to use in
+	 * add_child_join_rel_equivalences().
+	 */
+	joinrel->join_rel_list_index = list_length(root->join_rel_list) - 1;
+
 	/* store it into the auxiliary hashtable if there is one. */
 	if (root->join_rel_hash)
 	{
@@ -741,6 +754,7 @@ build_join_rel(PlannerInfo *root,
 	joinrel->subplan_params = NIL;
 	joinrel->rel_parallel_workers = -1;
 	joinrel->amflags = 0;
+	joinrel->eclass_child_members = NIL;
 	joinrel->serverid = InvalidOid;
 	joinrel->userid = InvalidOid;
 	joinrel->useridiscurrent = false;
@@ -928,6 +942,7 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 	joinrel->subroot = NULL;
 	joinrel->subplan_params = NIL;
 	joinrel->amflags = 0;
+	joinrel->eclass_child_members = NIL;
 	joinrel->serverid = InvalidOid;
 	joinrel->userid = InvalidOid;
 	joinrel->useridiscurrent = false;
@@ -1490,6 +1505,7 @@ fetch_upper_rel(PlannerInfo *root, UpperRelationKind kind, Relids relids)
 	upperrel->cheapest_total_path = NULL;
 	upperrel->cheapest_unique_path = NULL;
 	upperrel->cheapest_parameterized_paths = NIL;
+	upperrel->eclass_child_members = NIL;	/* XXX Is this required? */
 
 	root->upper_rels[kind] = lappend(root->upper_rels[kind], upperrel);
 
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index fbf05322c75..9651f52d405 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -213,6 +213,8 @@ typedef struct PlannerInfo PlannerInfo;
 #define HAVE_PLANNERINFO_TYPEDEF 1
 #endif
 
+struct EquivalenceClassIndexes;
+
 struct PlannerInfo
 {
 	pg_node_attr(no_copy_equal, no_read, no_query_jumble)
@@ -269,6 +271,13 @@ struct PlannerInfo
 	 */
 	struct AppendRelInfo **append_rel_array pg_node_attr(read_write_ignore);
 
+	/*
+	 * eclass_indexes_array is the same length as simple_rel_array and holds
+	 * the indexes of the corresponding rels for faster lookups of
+	 * RestrictInfo. See the EquivalenceClass comment for more details.
+	 */
+	struct EquivalenceClassIndexes *eclass_indexes_array pg_node_attr(read_write_ignore);
+
 	/*
 	 * all_baserels is a Relids set of all base relids (but not joins or
 	 * "other" rels) in the query.  This is computed in deconstruct_jointree.
@@ -981,6 +990,17 @@ typedef struct RelOptInfo
 	/* Bitmask of optional features supported by the table AM */
 	uint32		amflags;
 
+	/*
+	 * information about a join rel
+	 */
+	/* index in root->join_rel_list of this rel */
+	Index		join_rel_list_index;
+
+	/*
+	 * EquivalenceMembers referencing this rel
+	 */
+	List	   *eclass_child_members;
+
 	/*
 	 * Information about foreign tables and foreign joins
 	 */
@@ -1400,6 +1420,12 @@ typedef struct JoinDomain
  * entry: consider SELECT random() AS a, random() AS b ... ORDER BY b,a.
  * So we record the SortGroupRef of the originating sort clause.
  *
+ * EquivalenceClass->ec_members can only have parent members, and child members
+ * are stored in RelOptInfos, from which those child members are translated. To
+ * lookup child EquivalenceMembers, we use EquivalenceAllMemberIterator. See
+ * its comment for usage. The approach to lookup child members quickly is
+ * described as setup_eclass_all_member_iterator_for_relids() comment.
+ *
  * NB: if ec_merged isn't NULL, this class has been merged into another, and
  * should be ignored in favor of using the pointed-to class.
  *
@@ -1474,8 +1500,61 @@ typedef struct EquivalenceMember
 	JoinDomain *em_jdomain;		/* join domain containing the source clause */
 	/* if em_is_child is true, this links to corresponding EM for top parent */
 	struct EquivalenceMember *em_parent pg_node_attr(read_write_ignore);
+	EquivalenceClass *em_ec;	/* EquivalenceClass which has this member */
 } EquivalenceMember;
 
+/*
+ * EquivalenceAllMemberIterator
+ *
+ * EquivalenceAllMemberIterator is designed to iterate over all parent
+ * EquivalenceMembers and child members associated with the given 'ec' that
+ * are relevant to the specified 'relids'. In particular, it iterates over:
+ *	- All parent members stored directly in ec->ec_members.
+ *	- The child members whose em_relids is a subset of the given 'relids'.
+ *
+ * Note:
+ *	- The iterator may return false positives, i.e., child members whose
+ *	  em_relids is not a subset. So the caller must check that they satisfy
+ *	  the desired condition.
+ *
+ * The most common way to use this iterator is as follows:
+ * -----
+ * PlannerInfo					   *root = given;
+ * EquivalenceClass				   *ec = given;
+ * Relids							rel = given;
+ * EquivalenceAllMemberIterator		it;
+ * EquivalenceMember			   *em;
+ *
+ * setup_eclass_all_member_iterator_for_relids(&it, root, ec, rel);
+ * while ((em = eclass_all_member_iterator_for_relids_next(&it)) != NULL)
+ * {
+ *     use em ...;
+ * }
+ * dispose_eclass_all_member_iterator_for_relids(&it);
+ * -----
+ */
+typedef struct
+{
+	int			index;			/* current index within 'ec_members'. */
+	bool		modified;		/* is 'ec_members' a newly allocated one? */
+	List	   *ec_members;		/* parent and child members */
+} EquivalenceAllMemberIterator;
+
+/*
+ * EquivalenceClassIndexes
+ *
+ * As mentioned in the EquivalenceClass comment, we introduce a
+ * bitmapset-based indexing mechanism for faster lookups of child
+ * EquivalenceMembers. This struct exists for each relation and holds the
+ * corresponding indexes.
+ */
+typedef struct EquivalenceClassIndexes
+{
+	Bitmapset  *joinrel_indexes;	/* Indexes in PlannerInfo's join_rel_list
+									 * list for RelOptInfos that mention this
+									 * relation */
+} EquivalenceClassIndexes;
+
 /*
  * PathKeys
  *
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index bc5dfd7db41..954ab6e1955 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -140,7 +140,8 @@ extern EquivalenceClass *get_eclass_for_sort_expr(PlannerInfo *root,
 												  Index sortref,
 												  Relids rel,
 												  bool create_it);
-extern EquivalenceMember *find_ec_member_matching_expr(EquivalenceClass *ec,
+extern EquivalenceMember *find_ec_member_matching_expr(PlannerInfo *root,
+													   EquivalenceClass *ec,
 													   Expr *expr,
 													   Relids relids);
 extern EquivalenceMember *find_computable_ec_member(PlannerInfo *root,
@@ -182,6 +183,12 @@ extern void add_setop_child_rel_equivalences(PlannerInfo *root,
 											 RelOptInfo *child_rel,
 											 List *child_tlist,
 											 List *setop_pathkeys);
+extern void setup_eclass_all_member_iterator_for_relids(EquivalenceAllMemberIterator *it,
+														PlannerInfo *root,
+														EquivalenceClass *ec,
+														Relids relids);
+extern EquivalenceMember *eclass_all_member_iterator_for_relids_next(EquivalenceAllMemberIterator *it);
+extern void dispose_eclass_all_member_iterator_for_relids(EquivalenceAllMemberIterator *it);
 extern List *generate_implied_equalities_for_column(PlannerInfo *root,
 													RelOptInfo *rel,
 													ec_matches_callback_type callback,
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 93339ef3c58..31ffc643916 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -696,7 +696,9 @@ EphemeralNamedRelation
 EphemeralNamedRelationData
 EphemeralNamedRelationMetadata
 EphemeralNamedRelationMetadataData
+EquivalenceAllMemberIterator
 EquivalenceClass
+EquivalenceClassIndexes
 EquivalenceMember
 ErrorContextCallback
 ErrorData
-- 
2.45.2.windows.1