Parameterized aggregate subquery (was: Pull up aggregate subquery)

diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 681f5f8..039fd7f 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1557,6 +1557,17 @@ _outTidPath(StringInfo str, TidPath *node)
 }
 
 static void
+_outSubqueryPath(StringInfo str, SubqueryPath *node)
+{
+	WRITE_NODE_TYPE("SUBQUERYPATH");
+
+	_outPathInfo(str, (Path *) node);
+	WRITE_NODE_FIELD(subplan);
+	WRITE_NODE_FIELD(subrtable);
+	WRITE_NODE_FIELD(subrowmark);
+}
+
+static void
 _outForeignPath(StringInfo str, ForeignPath *node)
 {
 	WRITE_NODE_TYPE("FOREIGNPATH");
@@ -1738,9 +1749,6 @@ _outRelOptInfo(StringInfo str, RelOptInfo *node)
 	WRITE_NODE_FIELD(indexlist);
 	WRITE_UINT_FIELD(pages);
 	WRITE_FLOAT_FIELD(tuples, "%.0f");
-	WRITE_NODE_FIELD(subplan);
-	WRITE_NODE_FIELD(subrtable);
-	WRITE_NODE_FIELD(subrowmark);
 	WRITE_NODE_FIELD(baserestrictinfo);
 	WRITE_NODE_FIELD(joininfo);
 	WRITE_BOOL_FIELD(has_eclass_joins);
@@ -2948,6 +2956,9 @@ _outNode(StringInfo str, void *obj)
 			case T_TidPath:
 				_outTidPath(str, obj);
 				break;
+			case T_SubqueryPath:
+				_outSubqueryPath(str, obj);
+				break;
 			case T_ForeignPath:
 				_outForeignPath(str, obj);
 				break;
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 47ab08e..37578b9 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -31,6 +31,7 @@
 #include "optimizer/planner.h"
 #include "optimizer/prep.h"
 #include "optimizer/restrictinfo.h"
+#include "optimizer/subselect.h"
 #include "optimizer/var.h"
 #include "parser/parse_clause.h"
 #include "parser/parsetree.h"
@@ -687,7 +688,8 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 	bool	   *differentTypes;
 	double		tuple_fraction;
 	PlannerInfo *subroot;
-	List	   *pathkeys;
+	Plan	    *subplan;
+	List	    *pathkeys;
 
 	/*
 	 * Must copy the Query so that planning doesn't mess up the RTE contents
@@ -749,6 +751,9 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 
 	pfree(differentTypes);
 
+	/* save it for later use in best_inner_subqueryscan */
+	rel->preprocessed_subquery = copyObject(subquery);
+
 	/*
 	 * We can safely pass the outer tuple_fraction down to the subquery if the
 	 * outer level has no joining, aggregation, or sorting to do. Otherwise
@@ -766,27 +771,182 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 		tuple_fraction = root->tuple_fraction;
 
 	/* Generate the plan for the subquery */
-	rel->subplan = subquery_planner(root->glob, subquery,
-									root,
-									false, tuple_fraction,
-									&subroot);
-	rel->subrtable = subroot->parse->rtable;
-	rel->subrowmark = subroot->rowMarks;
+	subplan = subquery_planner(root->glob, subquery,
+							   root,
+							   false, tuple_fraction,
+							   &subroot);
 
 	/* Mark rel with estimated output rows, width, etc */
-	set_subquery_size_estimates(root, rel, subroot);
+	set_subquery_size_estimates(root, rel, subroot, subplan);
 
 	/* Convert subquery pathkeys to outer representation */
-	pathkeys = convert_subquery_pathkeys(root, rel, subroot->query_pathkeys);
+	pathkeys = convert_subquery_pathkeys(root, rel,
+										 subroot->query_pathkeys, subplan);
 
 	/* Generate appropriate path */
-	add_path(rel, create_subqueryscan_path(rel, pathkeys));
+	add_path(rel, create_subqueryscan_path(rel, pathkeys,
+			 subplan, subroot->parse->rtable, subroot->rowMarks));
 
 	/* Select cheapest path (pretty easy in this case...) */
 	set_cheapest(rel);
 }
 
 /*
+ * best_inner_subqueryscan
+ *
+ * As the inner scan relation, try to push down some join clauses to
+ * subquery and re-plan it. If such posibility is found, return the path
+ * in *cheapest_start_path or *cheapest_total_path. See also best_inner_indexscan().
+ *
+ * It re-uses modified subquery (a.k.a. rel->preprocessed_subquery.) The basic
+ * push-down of baserestrictinfo was common among base path (done in
+ * set_subquery_pathlist()) so this routine doesn't care it. But once we find
+ * push-down-able join clause, the parsed subquery will be modified after copied.
+ */
+void
+best_inner_subqueryscan(PlannerInfo *root, RelOptInfo *rel,
+						RelOptInfo *outer_rel, List *join_clause,
+						Path **cheapest_start_path, Path **cheapest_total_path)
+{
+	Query	   *parse = root->parse;
+	Query	   *subquery;
+	Index		rti = rel->relid;
+	RangeTblEntry *rte = planner_rt_fetch(rti, root);
+	bool	   *differentTypes;
+	double		tuple_fraction;
+	PlannerInfo *subroot;
+	Plan	   *subplan;
+	List	   *pathkeys;
+	Path	   *path;
+	bool		query_modified = false;
+
+	/* initialize paths to return immediately anytime */
+	*cheapest_start_path = *cheapest_total_path = NULL;
+
+	/*
+	 * empty join condition doesn't make sense here
+	 */
+	if (join_clause == NIL)
+		return;
+
+	/*
+	 * basic push-down-qual process should have done already.
+	 * see set_subquery_pathlist
+	 */
+	Assert(rel->preprocessed_subquery);
+	/* copying the whole Query is expensive; let's copy-on-write */
+	subquery = rel->preprocessed_subquery;
+
+	/* We need a workspace for keeping track of set-op type coercions */
+	differentTypes = (bool *)
+		palloc0((list_length(subquery->targetList) + 1) * sizeof(bool));
+
+	/*
+	 * Currently we focus on only cases of aggregate subquery.
+	 * I'm not quite sure if there are other useful cases.
+	 */
+	if (subquery->hasAggs &&
+		subquery_is_pushdown_safe(subquery, subquery, differentTypes))
+	{
+		ListCell   *l;
+
+		foreach (l, join_clause)
+		{
+			RestrictInfo   *rinfo = (RestrictInfo *) lfirst(l);
+			Node		   *lexpr, *rexpr;
+			Expr		   *clause = NULL;
+			Param		   *param;
+			OpExpr		   *rclause;
+
+			/*
+			 * For the first step, only Var = Var join clause is targeted.
+			 * This may be improved in the future.
+			 */
+			if (!is_opclause(rinfo->clause))
+				continue;
+			rclause = (OpExpr *) rinfo->clause;
+			lexpr = get_leftop((Expr *) rclause);
+			rexpr = get_rightop((Expr *) rclause);
+			if (!(IsA(lexpr, Var) && IsA(rexpr, Var)))
+				continue;
+
+			/* see which arg is mine and outer's */
+			if (bms_is_member(((Var *) lexpr)->varno, outer_rel->relids))
+			{
+				param = assign_nestloop_param(root, (Var *) lexpr);
+				clause = make_opclause(rclause->opno,
+									   rclause->opresulttype,
+									   false,
+									   (Expr *) param,
+									   (Expr *) copyObject(rexpr),
+									   InvalidOid,
+									   rclause->opcollid);
+			}
+			else if (bms_is_member(((Var *) rexpr)->varno, outer_rel->relids))
+			{
+				param = assign_nestloop_param(root, (Var *) rexpr);
+				clause = make_opclause(rclause->opno,
+									   rclause->opresulttype,
+									   false,
+									   (Expr *) copyObject(lexpr),
+									   (Expr *) param,
+									   InvalidOid,
+									   rclause->opcollid);
+			}
+
+			/* TODO:no check isouter or not? */
+			if (clause)
+			{
+				/* copy on write */
+				if (rel->preprocessed_subquery == subquery)
+					subquery = copyObject(subquery);
+				subquery_push_qual(subquery, rte, rti, (Node *) clause);
+				query_modified = true;
+			}
+		}
+	}
+
+	pfree(differentTypes);
+
+	/* return immediately if such subquery isn't found. Use original one */
+	if (!query_modified)
+		return;
+
+	/*
+	 * We can safely pass the outer tuple_fraction down to the subquery if the
+	 * outer level has no joining, aggregation, or sorting to do. Otherwise
+	 * we'd better tell the subquery to plan for full retrieval. (XXX This
+	 * could probably be made more intelligent ...)
+	 */
+	if (parse->hasAggs ||
+		parse->groupClause ||
+		parse->havingQual ||
+		parse->distinctClause ||
+		parse->sortClause ||
+		has_multiple_baserels(root))
+		tuple_fraction = 0.0;	/* default case */
+	else
+		tuple_fraction = root->tuple_fraction;
+
+	/* Generate the plan for the subquery */
+	subplan = subquery_planner(root->glob, subquery,
+							   root,
+							   false, tuple_fraction,
+							   &subroot);
+
+	/* Convert subquery pathkeys to outer representation */
+	pathkeys = convert_subquery_pathkeys(root, rel,
+										 subroot->query_pathkeys, subplan);
+
+	/* Generate appropriate path */
+	path = create_subqueryscan_path(rel, pathkeys,
+			 subplan, subroot->parse->rtable, subroot->rowMarks);
+
+	/* return the same path for now */
+	*cheapest_start_path = *cheapest_total_path = path;
+}
+
+/*
  * set_function_pathlist
  *		Build the (single) access path for a function RTE
  */
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index c4404b1..e352b4b 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -892,7 +892,7 @@ cost_tidscan(Path *path, PlannerInfo *root,
  *	  Determines and returns the cost of scanning a subquery RTE.
  */
 void
-cost_subqueryscan(Path *path, RelOptInfo *baserel)
+cost_subqueryscan(SubqueryPath *path, RelOptInfo *baserel)
 {
 	Cost		startup_cost;
 	Cost		run_cost;
@@ -907,15 +907,15 @@ cost_subqueryscan(Path *path, RelOptInfo *baserel)
 	 * any restriction clauses that will be attached to the SubqueryScan node,
 	 * plus cpu_tuple_cost to account for selection and projection overhead.
 	 */
-	path->startup_cost = baserel->subplan->startup_cost;
-	path->total_cost = baserel->subplan->total_cost;
+	path->path.startup_cost = path->subplan->startup_cost;
+	path->path.total_cost = path->subplan->total_cost;
 
 	startup_cost = baserel->baserestrictcost.startup;
 	cpu_per_tuple = cpu_tuple_cost + baserel->baserestrictcost.per_tuple;
 	run_cost = cpu_per_tuple * baserel->tuples;
 
-	path->startup_cost += startup_cost;
-	path->total_cost += startup_cost + run_cost;
+	path->path.startup_cost += startup_cost;
+	path->path.total_cost += startup_cost + run_cost;
 }
 
 /*
@@ -3222,7 +3222,7 @@ set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
  */
 void
 set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
-							PlannerInfo *subroot)
+							PlannerInfo *subroot, Plan *subplan)
 {
 	RangeTblEntry *rte;
 	ListCell   *lc;
@@ -3233,7 +3233,7 @@ set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
 	Assert(rte->rtekind == RTE_SUBQUERY);
 
 	/* Copy raw number of output rows from subplan */
-	rel->tuples = rel->subplan->plan_rows;
+	rel->tuples = subplan->plan_rows;
 
 	/*
 	 * Compute per-output-column width estimates by examining the subquery's
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 7d3cf42..c79fd43 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -329,7 +329,9 @@ sort_inner_and_outer(PlannerInfo *root,
  * inner path, one on the same with materialization, one on the
  * cheapest-startup-cost inner path (if different), one on the
  * cheapest-total inner-indexscan path (if any), and one on the
- * cheapest-startup inner-indexscan path (if different).
+ * cheapest-startup inner-indexscan path (if different) or on the
+ * cheapest-total inner-subqueryscan path (if any).
+ * Note inner-indexpaths and inner-subquerypaths are not made at the same time.
  *
  * We also consider mergejoins if mergejoin clauses are available.	We have
  * two ways to generate the inner path for a mergejoin: sort the cheapest
@@ -369,6 +371,8 @@ match_unsorted_outer(PlannerInfo *root,
 	Path	   *matpath = NULL;
 	Path	   *index_cheapest_startup = NULL;
 	Path	   *index_cheapest_total = NULL;
+	Path	   *sub_cheapest_startup = NULL;
+	Path	   *sub_cheapest_total = NULL;
 	ListCell   *l;
 
 	/*
@@ -430,8 +434,8 @@ match_unsorted_outer(PlannerInfo *root,
 				create_material_path(innerrel, inner_cheapest_total);
 
 		/*
-		 * Get the best innerjoin indexpaths (if any) for this outer rel.
-		 * They're the same for all outer paths.
+		 * Get the best innerjoin indexpaths or subquerypaths (if any)
+		 * for this outer rel. They're the same for all outer paths.
 		 */
 		if (innerrel->reloptkind != RELOPT_JOINREL)
 		{
@@ -444,6 +448,10 @@ match_unsorted_outer(PlannerInfo *root,
 				best_inner_indexscan(root, innerrel, outerrel, jointype,
 									 &index_cheapest_startup,
 									 &index_cheapest_total);
+			else if (innerrel->rtekind == RTE_SUBQUERY)
+				best_inner_subqueryscan(root, innerrel, outerrel, restrictlist,
+										&sub_cheapest_startup,
+										&sub_cheapest_total);
 		}
 	}
 
@@ -487,7 +495,7 @@ match_unsorted_outer(PlannerInfo *root,
 			 * cheapest-total-cost inner.  When appropriate, also consider
 			 * using the materialized form of the cheapest inner, the
 			 * cheapest-startup-cost inner path, and the cheapest innerjoin
-			 * indexpaths.
+			 * indexpaths or subquerypaths.
 			 */
 			add_path(joinrel, (Path *)
 					 create_nestloop_path(root,
@@ -539,6 +547,16 @@ match_unsorted_outer(PlannerInfo *root,
 											  index_cheapest_startup,
 											  restrictlist,
 											  merge_pathkeys));
+			if (sub_cheapest_total != NULL)
+				add_path(joinrel, (Path *)
+						 create_nestloop_path(root,
+											  joinrel,
+											  jointype,
+											  sjinfo,
+											  outerpath,
+											  sub_cheapest_startup,
+											  restrictlist,
+											  merge_pathkeys));
 		}
 
 		/* Can't do anything else if outer path needs to be unique'd */
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index 42427d3..e3db45c 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -745,7 +745,6 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
 	return joinrel;
 }
 
-
 /*
  * have_join_order_restriction
  *		Detect whether the two relations should be joined to satisfy
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index e5228a8..cee5c71 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -623,12 +623,12 @@ find_indexkey_var(PlannerInfo *root, RelOptInfo *rel, AttrNumber varattno)
  */
 List *
 convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
-						  List *subquery_pathkeys)
+						  List *subquery_pathkeys, Plan *subplan)
 {
 	List	   *retval = NIL;
 	int			retvallen = 0;
 	int			outer_query_keys = list_length(root->query_pathkeys);
-	List	   *sub_tlist = rel->subplan->targetlist;
+	List	   *sub_tlist = subplan->targetlist;
 	ListCell   *i;
 
 	foreach(i, subquery_pathkeys)
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index ac80511..f1002f3 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -62,8 +62,8 @@ static Plan *create_bitmap_subplan(PlannerInfo *root, Path *bitmapqual,
 					  List **qual, List **indexqual);
 static TidScan *create_tidscan_plan(PlannerInfo *root, TidPath *best_path,
 					List *tlist, List *scan_clauses);
-static SubqueryScan *create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
-						 List *tlist, List *scan_clauses);
+static SubqueryScan *create_subqueryscan_plan(PlannerInfo *root,
+					SubqueryPath *best_path, List *tlist, List *scan_clauses);
 static FunctionScan *create_functionscan_plan(PlannerInfo *root, Path *best_path,
 						 List *tlist, List *scan_clauses);
 static ValuesScan *create_valuesscan_plan(PlannerInfo *root, Path *best_path,
@@ -321,7 +321,7 @@ create_scan_plan(PlannerInfo *root, Path *best_path)
 
 		case T_SubqueryScan:
 			plan = (Plan *) create_subqueryscan_plan(root,
-													 best_path,
+													 (SubqueryPath *) best_path,
 													 tlist,
 													 scan_clauses);
 			break;
@@ -1538,15 +1538,15 @@ create_tidscan_plan(PlannerInfo *root, TidPath *best_path,
  *	 with restriction clauses 'scan_clauses' and targetlist 'tlist'.
  */
 static SubqueryScan *
-create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
+create_subqueryscan_plan(PlannerInfo *root, SubqueryPath *best_path,
 						 List *tlist, List *scan_clauses)
 {
 	SubqueryScan *scan_plan;
-	Index		scan_relid = best_path->parent->relid;
+	Index		scan_relid = best_path->path.parent->relid;
 
 	/* it should be a subquery base rel... */
 	Assert(scan_relid > 0);
-	Assert(best_path->parent->rtekind == RTE_SUBQUERY);
+	Assert(best_path->path.parent->rtekind == RTE_SUBQUERY);
 
 	/* Sort clauses into best execution order */
 	scan_clauses = order_qual_clauses(root, scan_clauses);
@@ -1557,11 +1557,63 @@ create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
 	scan_plan = make_subqueryscan(tlist,
 								  scan_clauses,
 								  scan_relid,
-								  best_path->parent->subplan,
-								  best_path->parent->subrtable,
-								  best_path->parent->subrowmark);
+								  best_path->subplan,
+								  best_path->subrtable,
+								  best_path->subrowmark);
 
-	copy_path_costsize(&scan_plan->scan.plan, best_path);
+	copy_path_costsize(&scan_plan->scan.plan, &best_path->path);
+
+	/*
+	 * If this plan is inner of nestloop and push-down-qual case,
+	 * params are extracted and regsitered for the upper nestloop.
+	 * As Plan has only bitmap extParam field, we cannot take them
+	 * directly; instead take them from PlannerGlobal.
+	 */
+	if (scan_plan->subplan->extParam)
+	{
+		int		i;
+		ListCell   *ppl;
+		PlannerParamItem   *pitem;
+
+		i = 0;
+		foreach(ppl, root->glob->paramlist)
+		{
+			pitem = (PlannerParamItem *) lfirst(ppl);
+			/* take the ones only in this query level */
+			if (pitem->abslevel == root->query_level &&
+				IsA(pitem->item, Var) &&
+				((Var *) pitem->item)->varlevelsup == 0)
+			{
+				Var	   *var = (Var *) pitem->item;
+				NestLoopParam	   *nlp;
+				ListCell   *lc;
+				bool		found;
+
+				/* Is the param already listed in root->curOuterParams? */
+				foreach(lc, root->curOuterParams)
+				{
+					nlp = (NestLoopParam *) lfirst(lc);
+					if (equal(nlp->paramval, var))
+					{
+						/* Present, so we can just skip */
+						found = true;
+						break;
+					}
+				}
+				if (found)
+				{
+					i++;
+					continue;
+				}
+				/* No, so add it */
+				nlp = makeNode(NestLoopParam);
+				nlp->paramno = i;
+				nlp->paramval = var;
+				root->curOuterParams = lappend(root->curOuterParams, nlp);
+			}
+			i++;
+		}
+	}
 
 	return scan_plan;
 }
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 161d5ab..2e711d8 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1331,17 +1331,21 @@ distinct_col_search(int colno, List *colnos, List *opids)
  *	  returning the pathnode.
  */
 Path *
-create_subqueryscan_path(RelOptInfo *rel, List *pathkeys)
+create_subqueryscan_path(RelOptInfo *rel, List *pathkeys, Plan *subplan,
+						 List *subrtable, List *subrowmark)
 {
-	Path	   *pathnode = makeNode(Path);
+	SubqueryPath   *pathnode = makeNode(SubqueryPath);
 
-	pathnode->pathtype = T_SubqueryScan;
-	pathnode->parent = rel;
-	pathnode->pathkeys = pathkeys;
+	pathnode->path.pathtype = T_SubqueryScan;
+	pathnode->path.parent = rel;
+	pathnode->path.pathkeys = pathkeys;
+	pathnode->subplan = subplan;
+	pathnode->subrtable = subrtable;
+	pathnode->subrowmark = subrowmark;
 
 	cost_subqueryscan(pathnode, rel);
 
-	return pathnode;
+	return (Path *) pathnode;
 }
 
 /*
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index b7a5845..ab2338f 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -82,9 +82,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
 	rel->indexlist = NIL;
 	rel->pages = 0;
 	rel->tuples = 0;
-	rel->subplan = NULL;
-	rel->subrtable = NIL;
-	rel->subrowmark = NIL;
+	rel->preprocessed_subquery = NULL;
 	rel->baserestrictinfo = NIL;
 	rel->baserestrictcost.startup = 0;
 	rel->baserestrictcost.per_tuple = 0;
@@ -336,9 +334,7 @@ build_join_rel(PlannerInfo *root,
 	joinrel->indexlist = NIL;
 	joinrel->pages = 0;
 	joinrel->tuples = 0;
-	joinrel->subplan = NULL;
-	joinrel->subrtable = NIL;
-	joinrel->subrowmark = NIL;
+	joinrel->preprocessed_subquery = NULL;
 	joinrel->baserestrictinfo = NIL;
 	joinrel->baserestrictcost.startup = 0;
 	joinrel->baserestrictcost.per_tuple = 0;
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index d8bc6b8..e951fd1 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -220,6 +220,7 @@ typedef enum NodeTag
 	T_MergePath,
 	T_HashPath,
 	T_TidPath,
+	T_SubqueryPath,
 	T_ForeignPath,
 	T_AppendPath,
 	T_MergeAppendPath,
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index f659269..98e2744 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -322,13 +322,6 @@ typedef struct PlannerInfo
  *					(always NIL if it's not a table)
  *		pages - number of disk pages in relation (zero if not a table)
  *		tuples - number of tuples in relation (not considering restrictions)
- *		subplan - plan for subquery (NULL if it's not a subquery)
- *		subrtable - rangetable for subquery (NIL if it's not a subquery)
- *		subrowmark - rowmarks for subquery (NIL if it's not a subquery)
- *
- *		Note: for a subquery, tuples and subplan are not set immediately
- *		upon creation of the RelOptInfo object; they are filled in when
- *		set_base_rel_pathlist processes the object.
  *
  *		For otherrels that are appendrel members, these fields are filled
  *		in just as for a baserel.
@@ -408,9 +401,8 @@ typedef struct RelOptInfo
 	List	   *indexlist;		/* list of IndexOptInfo */
 	BlockNumber pages;
 	double		tuples;
-	struct Plan *subplan;		/* if subquery */
-	List	   *subrtable;		/* if subquery */
-	List	   *subrowmark;		/* if subquery */
+
+	Query	   *preprocessed_subquery; /* if subquery */
 
 	/* used by various scans and joins: */
 	List	   *baserestrictinfo;		/* RestrictInfo structures (if base
@@ -770,6 +762,23 @@ typedef struct TidPath
 } TidPath;
 
 /*
+ * SubqueryPath represents a scan of a subquery scan
+ *
+ * The struct holds subplan, subrtable and subrowmark, which are
+ * the temporary spaces to transport to the final stage of planner.
+ * Note each SubqueryPath may have different plan, depending on
+ * the pushed down qual clauses (as parameterized inner subquery).
+ */
+typedef struct SubqueryPath
+{
+	Path		path;
+	struct Plan *subplan;
+	List	   *subrtable;
+	List	   *subrowmark;
+	double		rows;
+} SubqueryPath;
+
+/*
  * ForeignPath represents a scan of a foreign table
  */
 typedef struct ForeignPath
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 2763863..7ec2c7b 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -75,7 +75,7 @@ extern void cost_bitmap_or_node(BitmapOrPath *path, PlannerInfo *root);
 extern void cost_bitmap_tree_node(Path *path, Cost *cost, Selectivity *selec);
 extern void cost_tidscan(Path *path, PlannerInfo *root,
 			 RelOptInfo *baserel, List *tidquals);
-extern void cost_subqueryscan(Path *path, RelOptInfo *baserel);
+extern void cost_subqueryscan(SubqueryPath *path, RelOptInfo *baserel);
 extern void cost_functionscan(Path *path, PlannerInfo *root,
 				  RelOptInfo *baserel);
 extern void cost_valuesscan(Path *path, PlannerInfo *root,
@@ -122,7 +122,7 @@ extern void set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
 						   SpecialJoinInfo *sjinfo,
 						   List *restrictlist);
 extern void set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
-							PlannerInfo *subroot);
+							PlannerInfo *subroot, Plan *subplan);
 extern void set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel);
 extern void set_values_size_estimates(PlannerInfo *root, RelOptInfo *rel);
 extern void set_cte_size_estimates(PlannerInfo *root, RelOptInfo *rel,
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 1da2131..6de2816 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -14,6 +14,7 @@
 #ifndef PATHNODE_H
 #define PATHNODE_H
 
+#include "nodes/plannodes.h"
 #include "nodes/relation.h"
 
 
@@ -56,7 +57,8 @@ extern ResultPath *create_result_path(List *quals);
 extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath);
 extern UniquePath *create_unique_path(PlannerInfo *root, RelOptInfo *rel,
 				   Path *subpath, SpecialJoinInfo *sjinfo);
-extern Path *create_subqueryscan_path(RelOptInfo *rel, List *pathkeys);
+extern Path *create_subqueryscan_path(RelOptInfo *rel, List *pathkeys,
+					Plan *subplan, List *subrtable, List *subrowmark);
 extern Path *create_functionscan_path(PlannerInfo *root, RelOptInfo *rel);
 extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel);
 extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel);
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 7f1353a..54d0b01 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -33,6 +33,9 @@ extern PGDLLIMPORT join_search_hook_type join_search_hook;
 extern RelOptInfo *make_one_rel(PlannerInfo *root, List *joinlist);
 extern RelOptInfo *standard_join_search(PlannerInfo *root, int levels_needed,
 					 List *initial_rels);
+extern void best_inner_subqueryscan(PlannerInfo *root, RelOptInfo *rel,
+						RelOptInfo *outer_rel, List *join_clause,
+						Path **cheapest_start_path, Path **cheapest_total_path);
 
 #ifdef OPTIMIZER_DEBUG
 extern void debug_print_rel(PlannerInfo *root, RelOptInfo *rel);
@@ -154,7 +157,7 @@ extern Path *get_cheapest_fractional_path_for_pathkeys(List *paths,
 extern List *build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index,
 					 ScanDirection scandir);
 extern List *convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
-						  List *subquery_pathkeys);
+						  List *subquery_pathkeys, Plan *subplan);
 extern List *build_join_pathkeys(PlannerInfo *root,
 					RelOptInfo *joinrel,
 					JoinType jointype,

diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 681f5f8..039fd7f 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1557,6 +1557,17 @@ _outTidPath(StringInfo str, TidPath *node)
 }
 
 static void
+_outSubqueryPath(StringInfo str, SubqueryPath *node)
+{
+	WRITE_NODE_TYPE("SUBQUERYPATH");
+
+	_outPathInfo(str, (Path *) node);
+	WRITE_NODE_FIELD(subplan);
+	WRITE_NODE_FIELD(subrtable);
+	WRITE_NODE_FIELD(subrowmark);
+}
+
+static void
 _outForeignPath(StringInfo str, ForeignPath *node)
 {
 	WRITE_NODE_TYPE("FOREIGNPATH");
@@ -1738,9 +1749,6 @@ _outRelOptInfo(StringInfo str, RelOptInfo *node)
 	WRITE_NODE_FIELD(indexlist);
 	WRITE_UINT_FIELD(pages);
 	WRITE_FLOAT_FIELD(tuples, "%.0f");
-	WRITE_NODE_FIELD(subplan);
-	WRITE_NODE_FIELD(subrtable);
-	WRITE_NODE_FIELD(subrowmark);
 	WRITE_NODE_FIELD(baserestrictinfo);
 	WRITE_NODE_FIELD(joininfo);
 	WRITE_BOOL_FIELD(has_eclass_joins);
@@ -2948,6 +2956,9 @@ _outNode(StringInfo str, void *obj)
 			case T_TidPath:
 				_outTidPath(str, obj);
 				break;
+			case T_SubqueryPath:
+				_outSubqueryPath(str, obj);
+				break;
 			case T_ForeignPath:
 				_outForeignPath(str, obj);
 				break;
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 47ab08e..354a3e5 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -31,6 +31,7 @@
 #include "optimizer/planner.h"
 #include "optimizer/prep.h"
 #include "optimizer/restrictinfo.h"
+#include "optimizer/subselect.h"
 #include "optimizer/var.h"
 #include "parser/parse_clause.h"
 #include "parser/parsetree.h"
@@ -677,6 +678,12 @@ has_multiple_baserels(PlannerInfo *root)
 /*
  * set_subquery_pathlist
  *		Build the (single) access path for a subquery RTE
+ *
+ * Although we build only one access path for the subquery,
+ * join search process may find another path by pushing down
+ * the nestloop param into subquery. The finding process will
+ * be done much later than here, but some common operation like
+ * preprocessing subquery are shared.
  */
 static void
 set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
@@ -687,7 +694,9 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 	bool	   *differentTypes;
 	double		tuple_fraction;
 	PlannerInfo *subroot;
-	List	   *pathkeys;
+	Plan	    *subplan;
+	List	    *pathkeys;
+	bool		issafe = false;
 
 	/*
 	 * Must copy the Query so that planning doesn't mess up the RTE contents
@@ -721,7 +730,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 	 * push down a pushable qual, because it'd result in a worse plan?
 	 */
 	if (rel->baserestrictinfo != NIL &&
-		subquery_is_pushdown_safe(subquery, subquery, differentTypes))
+		(issafe = subquery_is_pushdown_safe(subquery, subquery, differentTypes)))
 	{
 		/* OK to consider pushing down individual quals */
 		List	   *upperrestrictlist = NIL;
@@ -749,6 +758,10 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 
 	pfree(differentTypes);
 
+	/* save it for later use in best_inner_subqueryscan */
+	if (issafe)
+		rel->preprocessed_subquery = copyObject(subquery);
+
 	/*
 	 * We can safely pass the outer tuple_fraction down to the subquery if the
 	 * outer level has no joining, aggregation, or sorting to do. Otherwise
@@ -766,27 +779,187 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 		tuple_fraction = root->tuple_fraction;
 
 	/* Generate the plan for the subquery */
-	rel->subplan = subquery_planner(root->glob, subquery,
-									root,
-									false, tuple_fraction,
-									&subroot);
-	rel->subrtable = subroot->parse->rtable;
-	rel->subrowmark = subroot->rowMarks;
+	subplan = subquery_planner(root->glob, subquery,
+							   root,
+							   false, tuple_fraction,
+							   &subroot);
 
 	/* Mark rel with estimated output rows, width, etc */
-	set_subquery_size_estimates(root, rel, subroot);
+	set_subquery_size_estimates(root, rel, subroot, subplan);
 
 	/* Convert subquery pathkeys to outer representation */
-	pathkeys = convert_subquery_pathkeys(root, rel, subroot->query_pathkeys);
+	pathkeys = convert_subquery_pathkeys(root, rel,
+										 subroot->query_pathkeys, subplan);
 
 	/* Generate appropriate path */
-	add_path(rel, create_subqueryscan_path(rel, pathkeys));
+	add_path(rel, create_subqueryscan_path(rel, pathkeys,
+			 subplan, subroot->parse->rtable, subroot->rowMarks));
 
 	/* Select cheapest path (pretty easy in this case...) */
 	set_cheapest(rel);
 }
 
 /*
+ * best_inner_subqueryscan
+ *
+ * As the inner scan relation, try to find another subquer path which may
+ * be better by pushing down some join clauses. If such possibility is found,
+ * return the path in *cheapest_start_path or *cheapest_total_path.
+ * Currently both paths will be resulted in the same, whereas further
+ * improvement might find cases they'd be different.
+ *
+ * It re-uses modified subquery (a.k.a. rel->preprocessed_subquery.) The basic
+ * push-down of baserestrictinfo was common among base path (done in
+ * set_subquery_pathlist()) so this routine doesn't care it. But once we find
+ * push-down-able join clause, the parsed subquery will be modified after copied.
+ */
+void
+best_inner_subqueryscan(PlannerInfo *root, RelOptInfo *rel,
+						RelOptInfo *outer_rel, List *join_clause,
+						Path **cheapest_start_path, Path **cheapest_total_path)
+{
+	Query	   *parse = root->parse;
+	Query	   *subquery;
+	Index		rti = rel->relid;
+	RangeTblEntry *rte = planner_rt_fetch(rti, root);
+	bool	   *differentTypes;
+	double		tuple_fraction;
+	PlannerInfo *subroot;
+	Plan	   *subplan;
+	List	   *pathkeys;
+	Path	   *path;
+	bool		query_modified = false;
+
+	/* initialize paths to return immediately anytime */
+	*cheapest_start_path = *cheapest_total_path = NULL;
+
+	/*
+	 * empty join condition doesn't make sense here
+	 */
+	if (join_clause == NIL)
+		return;
+
+	/*
+	 * If the subquery is not safe for pushdown, preprocessed
+	 * subquery was not saved. Simply skip this process.
+	 */
+	if (!rel->preprocessed_subquery)
+		return;
+
+	/* copying the whole Query is expensive; let's copy-on-write */
+	subquery = rel->preprocessed_subquery;
+
+	/* We need a workspace for keeping track of set-op type coercions */
+	differentTypes = (bool *)
+		palloc0((list_length(subquery->targetList) + 1) * sizeof(bool));
+
+	/*
+	 * Currently we focus on only cases of aggregate subquery.
+	 * I'm not quite sure if there are other useful cases.
+	 */
+	if (subquery->hasAggs)
+	{
+		ListCell   *l;
+
+		foreach (l, join_clause)
+		{
+			RestrictInfo   *rinfo = (RestrictInfo *) lfirst(l);
+			Node		   *lexpr, *rexpr;
+			Expr		   *clause = NULL;
+			Param		   *param;
+			OpExpr		   *rclause;
+
+			/*
+			 * For the first step, only Var = Var join clause is targeted.
+			 * This may be improved in the future.
+			 */
+			if (!is_opclause(rinfo->clause))
+				continue;
+			rclause = (OpExpr *) rinfo->clause;
+			lexpr = get_leftop((Expr *) rclause);
+			rexpr = get_rightop((Expr *) rclause);
+			if (!(IsA(lexpr, Var) && IsA(rexpr, Var)))
+				continue;
+
+			/* see which arg is mine and outer's */
+			if (bms_is_member(((Var *) lexpr)->varno, outer_rel->relids))
+			{
+				param = assign_nestloop_param(root, (Var *) lexpr);
+				clause = make_opclause(rclause->opno,
+									   rclause->opresulttype,
+									   false,
+									   (Expr *) param,
+									   (Expr *) copyObject(rexpr),
+									   InvalidOid,
+									   rclause->opcollid);
+			}
+			else if (bms_is_member(((Var *) rexpr)->varno, outer_rel->relids))
+			{
+				param = assign_nestloop_param(root, (Var *) rexpr);
+				clause = make_opclause(rclause->opno,
+									   rclause->opresulttype,
+									   false,
+									   (Expr *) copyObject(lexpr),
+									   (Expr *) param,
+									   InvalidOid,
+									   rclause->opcollid);
+			}
+
+			/* TODO:no check isouter or not? */
+			if (clause &&
+				qual_is_pushdown_safe(subquery, rti, (Node *) clause,
+									  differentTypes))
+			{
+				/* copy on write */
+				if (rel->preprocessed_subquery == subquery)
+					subquery = copyObject(subquery);
+				subquery_push_qual(subquery, rte, rti, (Node *) clause);
+				query_modified = true;
+			}
+		}
+	}
+
+	pfree(differentTypes);
+
+	/* return immediately if such subquery isn't found. Use original one */
+	if (!query_modified)
+		return;
+
+	/*
+	 * We can safely pass the outer tuple_fraction down to the subquery if the
+	 * outer level has no joining, aggregation, or sorting to do. Otherwise
+	 * we'd better tell the subquery to plan for full retrieval. (XXX This
+	 * could probably be made more intelligent ...)
+	 */
+	if (parse->hasAggs ||
+		parse->groupClause ||
+		parse->havingQual ||
+		parse->distinctClause ||
+		parse->sortClause ||
+		has_multiple_baserels(root))
+		tuple_fraction = 0.0;	/* default case */
+	else
+		tuple_fraction = root->tuple_fraction;
+
+	/* Generate the plan for the subquery */
+	subplan = subquery_planner(root->glob, subquery,
+							   root,
+							   false, tuple_fraction,
+							   &subroot);
+
+	/* Convert subquery pathkeys to outer representation */
+	pathkeys = convert_subquery_pathkeys(root, rel,
+										 subroot->query_pathkeys, subplan);
+
+	/* Generate appropriate path */
+	path = create_subqueryscan_path(rel, pathkeys,
+			 subplan, subroot->parse->rtable, subroot->rowMarks);
+
+	/* return the same path for now */
+	*cheapest_start_path = *cheapest_total_path = path;
+}
+
+/*
  * set_function_pathlist
  *		Build the (single) access path for a function RTE
  */
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index bb38768..b7271c3 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -892,7 +892,7 @@ cost_tidscan(Path *path, PlannerInfo *root,
  *	  Determines and returns the cost of scanning a subquery RTE.
  */
 void
-cost_subqueryscan(Path *path, RelOptInfo *baserel)
+cost_subqueryscan(SubqueryPath *path, RelOptInfo *baserel)
 {
 	Cost		startup_cost;
 	Cost		run_cost;
@@ -907,15 +907,15 @@ cost_subqueryscan(Path *path, RelOptInfo *baserel)
 	 * any restriction clauses that will be attached to the SubqueryScan node,
 	 * plus cpu_tuple_cost to account for selection and projection overhead.
 	 */
-	path->startup_cost = baserel->subplan->startup_cost;
-	path->total_cost = baserel->subplan->total_cost;
+	path->path.startup_cost = path->subplan->startup_cost;
+	path->path.total_cost = path->subplan->total_cost;
 
 	startup_cost = baserel->baserestrictcost.startup;
 	cpu_per_tuple = cpu_tuple_cost + baserel->baserestrictcost.per_tuple;
 	run_cost = cpu_per_tuple * baserel->tuples;
 
-	path->startup_cost += startup_cost;
-	path->total_cost += startup_cost + run_cost;
+	path->path.startup_cost += startup_cost;
+	path->path.total_cost += startup_cost + run_cost;
 }
 
 /*
@@ -3222,7 +3222,7 @@ set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
  */
 void
 set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
-							PlannerInfo *subroot)
+							PlannerInfo *subroot, Plan *subplan)
 {
 	RangeTblEntry *rte;
 	ListCell   *lc;
@@ -3233,7 +3233,7 @@ set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
 	Assert(rte->rtekind == RTE_SUBQUERY);
 
 	/* Copy raw number of output rows from subplan */
-	rel->tuples = rel->subplan->plan_rows;
+	rel->tuples = subplan->plan_rows;
 
 	/*
 	 * Compute per-output-column width estimates by examining the subquery's
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 7d3cf42..c79fd43 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -329,7 +329,9 @@ sort_inner_and_outer(PlannerInfo *root,
  * inner path, one on the same with materialization, one on the
  * cheapest-startup-cost inner path (if different), one on the
  * cheapest-total inner-indexscan path (if any), and one on the
- * cheapest-startup inner-indexscan path (if different).
+ * cheapest-startup inner-indexscan path (if different) or on the
+ * cheapest-total inner-subqueryscan path (if any).
+ * Note inner-indexpaths and inner-subquerypaths are not made at the same time.
  *
  * We also consider mergejoins if mergejoin clauses are available.	We have
  * two ways to generate the inner path for a mergejoin: sort the cheapest
@@ -369,6 +371,8 @@ match_unsorted_outer(PlannerInfo *root,
 	Path	   *matpath = NULL;
 	Path	   *index_cheapest_startup = NULL;
 	Path	   *index_cheapest_total = NULL;
+	Path	   *sub_cheapest_startup = NULL;
+	Path	   *sub_cheapest_total = NULL;
 	ListCell   *l;
 
 	/*
@@ -430,8 +434,8 @@ match_unsorted_outer(PlannerInfo *root,
 				create_material_path(innerrel, inner_cheapest_total);
 
 		/*
-		 * Get the best innerjoin indexpaths (if any) for this outer rel.
-		 * They're the same for all outer paths.
+		 * Get the best innerjoin indexpaths or subquerypaths (if any)
+		 * for this outer rel. They're the same for all outer paths.
 		 */
 		if (innerrel->reloptkind != RELOPT_JOINREL)
 		{
@@ -444,6 +448,10 @@ match_unsorted_outer(PlannerInfo *root,
 				best_inner_indexscan(root, innerrel, outerrel, jointype,
 									 &index_cheapest_startup,
 									 &index_cheapest_total);
+			else if (innerrel->rtekind == RTE_SUBQUERY)
+				best_inner_subqueryscan(root, innerrel, outerrel, restrictlist,
+										&sub_cheapest_startup,
+										&sub_cheapest_total);
 		}
 	}
 
@@ -487,7 +495,7 @@ match_unsorted_outer(PlannerInfo *root,
 			 * cheapest-total-cost inner.  When appropriate, also consider
 			 * using the materialized form of the cheapest inner, the
 			 * cheapest-startup-cost inner path, and the cheapest innerjoin
-			 * indexpaths.
+			 * indexpaths or subquerypaths.
 			 */
 			add_path(joinrel, (Path *)
 					 create_nestloop_path(root,
@@ -539,6 +547,16 @@ match_unsorted_outer(PlannerInfo *root,
 											  index_cheapest_startup,
 											  restrictlist,
 											  merge_pathkeys));
+			if (sub_cheapest_total != NULL)
+				add_path(joinrel, (Path *)
+						 create_nestloop_path(root,
+											  joinrel,
+											  jointype,
+											  sjinfo,
+											  outerpath,
+											  sub_cheapest_startup,
+											  restrictlist,
+											  merge_pathkeys));
 		}
 
 		/* Can't do anything else if outer path needs to be unique'd */
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index 24e4e59..c3f7ca0 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -745,7 +745,6 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
 	return joinrel;
 }
 
-
 /*
  * have_join_order_restriction
  *		Detect whether the two relations should be joined to satisfy
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index e5228a8..cee5c71 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -623,12 +623,12 @@ find_indexkey_var(PlannerInfo *root, RelOptInfo *rel, AttrNumber varattno)
  */
 List *
 convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
-						  List *subquery_pathkeys)
+						  List *subquery_pathkeys, Plan *subplan)
 {
 	List	   *retval = NIL;
 	int			retvallen = 0;
 	int			outer_query_keys = list_length(root->query_pathkeys);
-	List	   *sub_tlist = rel->subplan->targetlist;
+	List	   *sub_tlist = subplan->targetlist;
 	ListCell   *i;
 
 	foreach(i, subquery_pathkeys)
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index e4ccf5c..417c9e3 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -62,8 +62,8 @@ static Plan *create_bitmap_subplan(PlannerInfo *root, Path *bitmapqual,
 					  List **qual, List **indexqual);
 static TidScan *create_tidscan_plan(PlannerInfo *root, TidPath *best_path,
 					List *tlist, List *scan_clauses);
-static SubqueryScan *create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
-						 List *tlist, List *scan_clauses);
+static SubqueryScan *create_subqueryscan_plan(PlannerInfo *root,
+					SubqueryPath *best_path, List *tlist, List *scan_clauses);
 static FunctionScan *create_functionscan_plan(PlannerInfo *root, Path *best_path,
 						 List *tlist, List *scan_clauses);
 static ValuesScan *create_valuesscan_plan(PlannerInfo *root, Path *best_path,
@@ -321,7 +321,7 @@ create_scan_plan(PlannerInfo *root, Path *best_path)
 
 		case T_SubqueryScan:
 			plan = (Plan *) create_subqueryscan_plan(root,
-													 best_path,
+													 (SubqueryPath *) best_path,
 													 tlist,
 													 scan_clauses);
 			break;
@@ -1538,15 +1538,15 @@ create_tidscan_plan(PlannerInfo *root, TidPath *best_path,
  *	 with restriction clauses 'scan_clauses' and targetlist 'tlist'.
  */
 static SubqueryScan *
-create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
+create_subqueryscan_plan(PlannerInfo *root, SubqueryPath *best_path,
 						 List *tlist, List *scan_clauses)
 {
 	SubqueryScan *scan_plan;
-	Index		scan_relid = best_path->parent->relid;
+	Index		scan_relid = best_path->path.parent->relid;
 
 	/* it should be a subquery base rel... */
 	Assert(scan_relid > 0);
-	Assert(best_path->parent->rtekind == RTE_SUBQUERY);
+	Assert(best_path->path.parent->rtekind == RTE_SUBQUERY);
 
 	/* Sort clauses into best execution order */
 	scan_clauses = order_qual_clauses(root, scan_clauses);
@@ -1557,11 +1557,63 @@ create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
 	scan_plan = make_subqueryscan(tlist,
 								  scan_clauses,
 								  scan_relid,
-								  best_path->parent->subplan,
-								  best_path->parent->subrtable,
-								  best_path->parent->subrowmark);
+								  best_path->subplan,
+								  best_path->subrtable,
+								  best_path->subrowmark);
 
-	copy_path_costsize(&scan_plan->scan.plan, best_path);
+	copy_path_costsize(&scan_plan->scan.plan, &best_path->path);
+
+	/*
+	 * If this plan is inner of nestloop and push-down-qual case,
+	 * params are extracted and registered for the upper nestloop.
+	 * As Plan has only bitmap extParam field, we cannot take them
+	 * directly; instead take them from PlannerGlobal.
+	 */
+	if (scan_plan->subplan->extParam)
+	{
+		int		i;
+		ListCell   *ppl;
+		PlannerParamItem   *pitem;
+
+		i = 0;
+		foreach(ppl, root->glob->paramlist)
+		{
+			pitem = (PlannerParamItem *) lfirst(ppl);
+			/* take the ones only in this query level */
+			if (pitem->abslevel == root->query_level &&
+				IsA(pitem->item, Var) &&
+				((Var *) pitem->item)->varlevelsup == 0)
+			{
+				Var	   *var = (Var *) pitem->item;
+				NestLoopParam	   *nlp;
+				ListCell   *lc;
+				bool		found = false;
+
+				/* Is the param already listed in root->curOuterParams? */
+				foreach(lc, root->curOuterParams)
+				{
+					nlp = (NestLoopParam *) lfirst(lc);
+					if (equal(nlp->paramval, var))
+					{
+						/* Present, so we can just skip */
+						found = true;
+						break;
+					}
+				}
+				if (found)
+				{
+					i++;
+					continue;
+				}
+				/* No, so add it */
+				nlp = makeNode(NestLoopParam);
+				nlp->paramno = i;
+				nlp->paramval = var;
+				root->curOuterParams = lappend(root->curOuterParams, nlp);
+			}
+			i++;
+		}
+	}
 
 	return scan_plan;
 }
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 161d5ab..2e711d8 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1331,17 +1331,21 @@ distinct_col_search(int colno, List *colnos, List *opids)
  *	  returning the pathnode.
  */
 Path *
-create_subqueryscan_path(RelOptInfo *rel, List *pathkeys)
+create_subqueryscan_path(RelOptInfo *rel, List *pathkeys, Plan *subplan,
+						 List *subrtable, List *subrowmark)
 {
-	Path	   *pathnode = makeNode(Path);
+	SubqueryPath   *pathnode = makeNode(SubqueryPath);
 
-	pathnode->pathtype = T_SubqueryScan;
-	pathnode->parent = rel;
-	pathnode->pathkeys = pathkeys;
+	pathnode->path.pathtype = T_SubqueryScan;
+	pathnode->path.parent = rel;
+	pathnode->path.pathkeys = pathkeys;
+	pathnode->subplan = subplan;
+	pathnode->subrtable = subrtable;
+	pathnode->subrowmark = subrowmark;
 
 	cost_subqueryscan(pathnode, rel);
 
-	return pathnode;
+	return (Path *) pathnode;
 }
 
 /*
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index b7a5845..ab2338f 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -82,9 +82,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
 	rel->indexlist = NIL;
 	rel->pages = 0;
 	rel->tuples = 0;
-	rel->subplan = NULL;
-	rel->subrtable = NIL;
-	rel->subrowmark = NIL;
+	rel->preprocessed_subquery = NULL;
 	rel->baserestrictinfo = NIL;
 	rel->baserestrictcost.startup = 0;
 	rel->baserestrictcost.per_tuple = 0;
@@ -336,9 +334,7 @@ build_join_rel(PlannerInfo *root,
 	joinrel->indexlist = NIL;
 	joinrel->pages = 0;
 	joinrel->tuples = 0;
-	joinrel->subplan = NULL;
-	joinrel->subrtable = NIL;
-	joinrel->subrowmark = NIL;
+	joinrel->preprocessed_subquery = NULL;
 	joinrel->baserestrictinfo = NIL;
 	joinrel->baserestrictcost.startup = 0;
 	joinrel->baserestrictcost.per_tuple = 0;
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index d8bc6b8..e951fd1 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -220,6 +220,7 @@ typedef enum NodeTag
 	T_MergePath,
 	T_HashPath,
 	T_TidPath,
+	T_SubqueryPath,
 	T_ForeignPath,
 	T_AppendPath,
 	T_MergeAppendPath,
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index f659269..98e2744 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -322,13 +322,6 @@ typedef struct PlannerInfo
  *					(always NIL if it's not a table)
  *		pages - number of disk pages in relation (zero if not a table)
  *		tuples - number of tuples in relation (not considering restrictions)
- *		subplan - plan for subquery (NULL if it's not a subquery)
- *		subrtable - rangetable for subquery (NIL if it's not a subquery)
- *		subrowmark - rowmarks for subquery (NIL if it's not a subquery)
- *
- *		Note: for a subquery, tuples and subplan are not set immediately
- *		upon creation of the RelOptInfo object; they are filled in when
- *		set_base_rel_pathlist processes the object.
  *
  *		For otherrels that are appendrel members, these fields are filled
  *		in just as for a baserel.
@@ -408,9 +401,8 @@ typedef struct RelOptInfo
 	List	   *indexlist;		/* list of IndexOptInfo */
 	BlockNumber pages;
 	double		tuples;
-	struct Plan *subplan;		/* if subquery */
-	List	   *subrtable;		/* if subquery */
-	List	   *subrowmark;		/* if subquery */
+
+	Query	   *preprocessed_subquery; /* if subquery */
 
 	/* used by various scans and joins: */
 	List	   *baserestrictinfo;		/* RestrictInfo structures (if base
@@ -770,6 +762,23 @@ typedef struct TidPath
 } TidPath;
 
 /*
+ * SubqueryPath represents a scan of a subquery scan
+ *
+ * The struct holds subplan, subrtable and subrowmark, which are
+ * the temporary spaces to transport to the final stage of planner.
+ * Note each SubqueryPath may have different plan, depending on
+ * the pushed down qual clauses (as parameterized inner subquery).
+ */
+typedef struct SubqueryPath
+{
+	Path		path;
+	struct Plan *subplan;
+	List	   *subrtable;
+	List	   *subrowmark;
+	double		rows;
+} SubqueryPath;
+
+/*
  * ForeignPath represents a scan of a foreign table
  */
 typedef struct ForeignPath
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 2763863..7ec2c7b 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -75,7 +75,7 @@ extern void cost_bitmap_or_node(BitmapOrPath *path, PlannerInfo *root);
 extern void cost_bitmap_tree_node(Path *path, Cost *cost, Selectivity *selec);
 extern void cost_tidscan(Path *path, PlannerInfo *root,
 			 RelOptInfo *baserel, List *tidquals);
-extern void cost_subqueryscan(Path *path, RelOptInfo *baserel);
+extern void cost_subqueryscan(SubqueryPath *path, RelOptInfo *baserel);
 extern void cost_functionscan(Path *path, PlannerInfo *root,
 				  RelOptInfo *baserel);
 extern void cost_valuesscan(Path *path, PlannerInfo *root,
@@ -122,7 +122,7 @@ extern void set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
 						   SpecialJoinInfo *sjinfo,
 						   List *restrictlist);
 extern void set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
-							PlannerInfo *subroot);
+							PlannerInfo *subroot, Plan *subplan);
 extern void set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel);
 extern void set_values_size_estimates(PlannerInfo *root, RelOptInfo *rel);
 extern void set_cte_size_estimates(PlannerInfo *root, RelOptInfo *rel,
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 1da2131..6de2816 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -14,6 +14,7 @@
 #ifndef PATHNODE_H
 #define PATHNODE_H
 
+#include "nodes/plannodes.h"
 #include "nodes/relation.h"
 
 
@@ -56,7 +57,8 @@ extern ResultPath *create_result_path(List *quals);
 extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath);
 extern UniquePath *create_unique_path(PlannerInfo *root, RelOptInfo *rel,
 				   Path *subpath, SpecialJoinInfo *sjinfo);
-extern Path *create_subqueryscan_path(RelOptInfo *rel, List *pathkeys);
+extern Path *create_subqueryscan_path(RelOptInfo *rel, List *pathkeys,
+					Plan *subplan, List *subrtable, List *subrowmark);
 extern Path *create_functionscan_path(PlannerInfo *root, RelOptInfo *rel);
 extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel);
 extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel);
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 7f1353a..54d0b01 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -33,6 +33,9 @@ extern PGDLLIMPORT join_search_hook_type join_search_hook;
 extern RelOptInfo *make_one_rel(PlannerInfo *root, List *joinlist);
 extern RelOptInfo *standard_join_search(PlannerInfo *root, int levels_needed,
 					 List *initial_rels);
+extern void best_inner_subqueryscan(PlannerInfo *root, RelOptInfo *rel,
+						RelOptInfo *outer_rel, List *join_clause,
+						Path **cheapest_start_path, Path **cheapest_total_path);
 
 #ifdef OPTIMIZER_DEBUG
 extern void debug_print_rel(PlannerInfo *root, RelOptInfo *rel);
@@ -154,7 +157,7 @@ extern Path *get_cheapest_fractional_path_for_pathkeys(List *paths,
 extern List *build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index,
 					 ScanDirection scandir);
 extern List *convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
-						  List *subquery_pathkeys);
+						  List *subquery_pathkeys, Plan *subplan);
 extern List *build_join_pathkeys(PlannerInfo *root,
 					RelOptInfo *joinrel,
 					JoinType jointype,

diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 681f5f8..039fd7f 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1557,6 +1557,17 @@ _outTidPath(StringInfo str, TidPath *node)
 }
 
 static void
+_outSubqueryPath(StringInfo str, SubqueryPath *node)
+{
+	WRITE_NODE_TYPE("SUBQUERYPATH");
+
+	_outPathInfo(str, (Path *) node);
+	WRITE_NODE_FIELD(subplan);
+	WRITE_NODE_FIELD(subrtable);
+	WRITE_NODE_FIELD(subrowmark);
+}
+
+static void
 _outForeignPath(StringInfo str, ForeignPath *node)
 {
 	WRITE_NODE_TYPE("FOREIGNPATH");
@@ -1738,9 +1749,6 @@ _outRelOptInfo(StringInfo str, RelOptInfo *node)
 	WRITE_NODE_FIELD(indexlist);
 	WRITE_UINT_FIELD(pages);
 	WRITE_FLOAT_FIELD(tuples, "%.0f");
-	WRITE_NODE_FIELD(subplan);
-	WRITE_NODE_FIELD(subrtable);
-	WRITE_NODE_FIELD(subrowmark);
 	WRITE_NODE_FIELD(baserestrictinfo);
 	WRITE_NODE_FIELD(joininfo);
 	WRITE_BOOL_FIELD(has_eclass_joins);
@@ -2948,6 +2956,9 @@ _outNode(StringInfo str, void *obj)
 			case T_TidPath:
 				_outTidPath(str, obj);
 				break;
+			case T_SubqueryPath:
+				_outSubqueryPath(str, obj);
+				break;
 			case T_ForeignPath:
 				_outForeignPath(str, obj);
 				break;
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 6b43aee..dbea2f1 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -31,6 +31,7 @@
 #include "optimizer/planner.h"
 #include "optimizer/prep.h"
 #include "optimizer/restrictinfo.h"
+#include "optimizer/subselect.h"
 #include "optimizer/var.h"
 #include "parser/parse_clause.h"
 #include "parser/parsetree.h"
@@ -677,6 +678,12 @@ has_multiple_baserels(PlannerInfo *root)
 /*
  * set_subquery_pathlist
  *		Build the (single) access path for a subquery RTE
+ *
+ * Although we build only one access path for the subquery,
+ * join search process may find another path by pushing down
+ * the nestloop param into subquery. The finding process will
+ * be done much later than here, but some common operation like
+ * preprocessing subquery are shared.
  */
 static void
 set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
@@ -687,7 +694,8 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 	bool	   *differentTypes;
 	double		tuple_fraction;
 	PlannerInfo *subroot;
-	List	   *pathkeys;
+	Plan	    *subplan;
+	List	    *pathkeys;
 
 	/*
 	 * Must copy the Query so that planning doesn't mess up the RTE contents
@@ -749,6 +757,9 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 
 	pfree(differentTypes);
 
+	/* save it for later use in best_inner_subqueryscan */
+	rel->preprocessed_subquery = copyObject(subquery);
+
 	/*
 	 * We can safely pass the outer tuple_fraction down to the subquery if the
 	 * outer level has no joining, aggregation, or sorting to do. Otherwise
@@ -766,27 +777,181 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
 		tuple_fraction = root->tuple_fraction;
 
 	/* Generate the plan for the subquery */
-	rel->subplan = subquery_planner(root->glob, subquery,
-									root,
-									false, tuple_fraction,
-									&subroot);
-	rel->subrtable = subroot->parse->rtable;
-	rel->subrowmark = subroot->rowMarks;
+	subplan = subquery_planner(root->glob, subquery,
+							   root,
+							   false, tuple_fraction,
+							   &subroot);
 
 	/* Mark rel with estimated output rows, width, etc */
-	set_subquery_size_estimates(root, rel, subroot);
+	set_subquery_size_estimates(root, rel, subroot, subplan);
 
 	/* Convert subquery pathkeys to outer representation */
-	pathkeys = convert_subquery_pathkeys(root, rel, subroot->query_pathkeys);
+	pathkeys = convert_subquery_pathkeys(root, rel,
+										 subroot->query_pathkeys, subplan);
 
 	/* Generate appropriate path */
-	add_path(rel, create_subqueryscan_path(rel, pathkeys));
+	add_path(rel, create_subqueryscan_path(rel, pathkeys,
+			 subplan, subroot->parse->rtable, subroot->rowMarks));
 
 	/* Select cheapest path (pretty easy in this case...) */
 	set_cheapest(rel);
 }
 
 /*
+ * best_inner_subqueryscan
+ *
+ * As the inner scan relation, try to find another subquer path which may
+ * be better by pushing down some join clauses. If such possibility is found,
+ * return the path in *cheapest_start_path or *cheapest_total_path.
+ * Currently both paths will be resulted in the same, whereas further
+ * improvement might find cases they'd be different.
+ *
+ * It re-uses modified subquery (a.k.a. rel->preprocessed_subquery.) The basic
+ * push-down of baserestrictinfo was common among base path (done in
+ * set_subquery_pathlist()) so this routine doesn't care it. But once we find
+ * push-down-able join clause, the parsed subquery will be modified after copied.
+ */
+void
+best_inner_subqueryscan(PlannerInfo *root, RelOptInfo *rel,
+						RelOptInfo *outer_rel, List *join_clause,
+						Path **cheapest_start_path, Path **cheapest_total_path)
+{
+	Query	   *parse = root->parse;
+	Query	   *subquery;
+	Index		rti = rel->relid;
+	RangeTblEntry *rte = planner_rt_fetch(rti, root);
+	bool	   *differentTypes;
+	double		tuple_fraction;
+	PlannerInfo *subroot;
+	Plan	   *subplan;
+	List	   *pathkeys;
+	Path	   *path;
+	bool		query_modified = false;
+
+	/* initialize paths to return immediately anytime */
+	*cheapest_start_path = *cheapest_total_path = NULL;
+
+	/*
+	 * empty join condition doesn't make sense here
+	 */
+	if (join_clause == NIL)
+		return;
+
+	/* copying the whole Query is expensive; let's copy-on-write */
+	subquery = rel->preprocessed_subquery;
+
+	/* We need a workspace for keeping track of set-op type coercions */
+	differentTypes = (bool *)
+		palloc0((list_length(subquery->targetList) + 1) * sizeof(bool));
+
+	/*
+	 * Currently we focus on only cases of aggregate subquery.
+	 * I'm not quite sure if there are other useful cases.
+	 */
+	if (subquery->hasAggs &&
+		subquery_is_pushdown_safe(subquery, subquery, differentTypes))
+	{
+		ListCell   *l;
+
+		foreach (l, join_clause)
+		{
+			RestrictInfo   *rinfo = (RestrictInfo *) lfirst(l);
+			Node		   *lexpr, *rexpr;
+			Expr		   *clause = NULL;
+			Param		   *param;
+			OpExpr		   *rclause;
+
+			/*
+			 * For the first step, only Var = Var join clause is targeted.
+			 * This may be improved in the future.
+			 */
+			if (!is_opclause(rinfo->clause))
+				continue;
+			rclause = (OpExpr *) rinfo->clause;
+			lexpr = get_leftop((Expr *) rclause);
+			rexpr = get_rightop((Expr *) rclause);
+			if (!(IsA(lexpr, Var) && IsA(rexpr, Var)))
+				continue;
+
+			/* see which arg is mine and outer's */
+			if (bms_is_member(((Var *) lexpr)->varno, outer_rel->relids))
+			{
+				param = assign_nestloop_param(root, (Var *) lexpr);
+				clause = make_opclause(rclause->opno,
+									   rclause->opresulttype,
+									   false,
+									   (Expr *) param,
+									   (Expr *) copyObject(rexpr),
+									   InvalidOid,
+									   rclause->opcollid);
+			}
+			else if (bms_is_member(((Var *) rexpr)->varno, outer_rel->relids))
+			{
+				param = assign_nestloop_param(root, (Var *) rexpr);
+				clause = make_opclause(rclause->opno,
+									   rclause->opresulttype,
+									   false,
+									   (Expr *) copyObject(lexpr),
+									   (Expr *) param,
+									   InvalidOid,
+									   rclause->opcollid);
+			}
+
+			/* TODO:no check isouter or not? */
+			if (clause &&
+				qual_is_pushdown_safe(subquery, rti, (Node *) clause,
+									  differentTypes))
+			{
+				/* copy on write */
+				if (rel->preprocessed_subquery == subquery)
+					subquery = copyObject(subquery);
+				subquery_push_qual(subquery, rte, rti, (Node *) clause);
+				query_modified = true;
+			}
+		}
+	}
+
+	pfree(differentTypes);
+
+	/* return immediately if such subquery isn't found. Use original one */
+	if (!query_modified)
+		return;
+
+	/*
+	 * We can safely pass the outer tuple_fraction down to the subquery if the
+	 * outer level has no joining, aggregation, or sorting to do. Otherwise
+	 * we'd better tell the subquery to plan for full retrieval. (XXX This
+	 * could probably be made more intelligent ...)
+	 */
+	if (parse->hasAggs ||
+		parse->groupClause ||
+		parse->havingQual ||
+		parse->distinctClause ||
+		parse->sortClause ||
+		has_multiple_baserels(root))
+		tuple_fraction = 0.0;	/* default case */
+	else
+		tuple_fraction = root->tuple_fraction;
+
+	/* Generate the plan for the subquery */
+	subplan = subquery_planner(root->glob, subquery,
+							   root,
+							   false, tuple_fraction,
+							   &subroot);
+
+	/* Convert subquery pathkeys to outer representation */
+	pathkeys = convert_subquery_pathkeys(root, rel,
+										 subroot->query_pathkeys, subplan);
+
+	/* Generate appropriate path */
+	path = create_subqueryscan_path(rel, pathkeys,
+			 subplan, subroot->parse->rtable, subroot->rowMarks);
+
+	/* return the same path for now */
+	*cheapest_start_path = *cheapest_total_path = path;
+}
+
+/*
  * set_function_pathlist
  *		Build the (single) access path for a function RTE
  */
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index bb38768..b7271c3 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -892,7 +892,7 @@ cost_tidscan(Path *path, PlannerInfo *root,
  *	  Determines and returns the cost of scanning a subquery RTE.
  */
 void
-cost_subqueryscan(Path *path, RelOptInfo *baserel)
+cost_subqueryscan(SubqueryPath *path, RelOptInfo *baserel)
 {
 	Cost		startup_cost;
 	Cost		run_cost;
@@ -907,15 +907,15 @@ cost_subqueryscan(Path *path, RelOptInfo *baserel)
 	 * any restriction clauses that will be attached to the SubqueryScan node,
 	 * plus cpu_tuple_cost to account for selection and projection overhead.
 	 */
-	path->startup_cost = baserel->subplan->startup_cost;
-	path->total_cost = baserel->subplan->total_cost;
+	path->path.startup_cost = path->subplan->startup_cost;
+	path->path.total_cost = path->subplan->total_cost;
 
 	startup_cost = baserel->baserestrictcost.startup;
 	cpu_per_tuple = cpu_tuple_cost + baserel->baserestrictcost.per_tuple;
 	run_cost = cpu_per_tuple * baserel->tuples;
 
-	path->startup_cost += startup_cost;
-	path->total_cost += startup_cost + run_cost;
+	path->path.startup_cost += startup_cost;
+	path->path.total_cost += startup_cost + run_cost;
 }
 
 /*
@@ -3222,7 +3222,7 @@ set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
  */
 void
 set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
-							PlannerInfo *subroot)
+							PlannerInfo *subroot, Plan *subplan)
 {
 	RangeTblEntry *rte;
 	ListCell   *lc;
@@ -3233,7 +3233,7 @@ set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
 	Assert(rte->rtekind == RTE_SUBQUERY);
 
 	/* Copy raw number of output rows from subplan */
-	rel->tuples = rel->subplan->plan_rows;
+	rel->tuples = subplan->plan_rows;
 
 	/*
 	 * Compute per-output-column width estimates by examining the subquery's
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 7d3cf42..c79fd43 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -329,7 +329,9 @@ sort_inner_and_outer(PlannerInfo *root,
  * inner path, one on the same with materialization, one on the
  * cheapest-startup-cost inner path (if different), one on the
  * cheapest-total inner-indexscan path (if any), and one on the
- * cheapest-startup inner-indexscan path (if different).
+ * cheapest-startup inner-indexscan path (if different) or on the
+ * cheapest-total inner-subqueryscan path (if any).
+ * Note inner-indexpaths and inner-subquerypaths are not made at the same time.
  *
  * We also consider mergejoins if mergejoin clauses are available.	We have
  * two ways to generate the inner path for a mergejoin: sort the cheapest
@@ -369,6 +371,8 @@ match_unsorted_outer(PlannerInfo *root,
 	Path	   *matpath = NULL;
 	Path	   *index_cheapest_startup = NULL;
 	Path	   *index_cheapest_total = NULL;
+	Path	   *sub_cheapest_startup = NULL;
+	Path	   *sub_cheapest_total = NULL;
 	ListCell   *l;
 
 	/*
@@ -430,8 +434,8 @@ match_unsorted_outer(PlannerInfo *root,
 				create_material_path(innerrel, inner_cheapest_total);
 
 		/*
-		 * Get the best innerjoin indexpaths (if any) for this outer rel.
-		 * They're the same for all outer paths.
+		 * Get the best innerjoin indexpaths or subquerypaths (if any)
+		 * for this outer rel. They're the same for all outer paths.
 		 */
 		if (innerrel->reloptkind != RELOPT_JOINREL)
 		{
@@ -444,6 +448,10 @@ match_unsorted_outer(PlannerInfo *root,
 				best_inner_indexscan(root, innerrel, outerrel, jointype,
 									 &index_cheapest_startup,
 									 &index_cheapest_total);
+			else if (innerrel->rtekind == RTE_SUBQUERY)
+				best_inner_subqueryscan(root, innerrel, outerrel, restrictlist,
+										&sub_cheapest_startup,
+										&sub_cheapest_total);
 		}
 	}
 
@@ -487,7 +495,7 @@ match_unsorted_outer(PlannerInfo *root,
 			 * cheapest-total-cost inner.  When appropriate, also consider
 			 * using the materialized form of the cheapest inner, the
 			 * cheapest-startup-cost inner path, and the cheapest innerjoin
-			 * indexpaths.
+			 * indexpaths or subquerypaths.
 			 */
 			add_path(joinrel, (Path *)
 					 create_nestloop_path(root,
@@ -539,6 +547,16 @@ match_unsorted_outer(PlannerInfo *root,
 											  index_cheapest_startup,
 											  restrictlist,
 											  merge_pathkeys));
+			if (sub_cheapest_total != NULL)
+				add_path(joinrel, (Path *)
+						 create_nestloop_path(root,
+											  joinrel,
+											  jointype,
+											  sjinfo,
+											  outerpath,
+											  sub_cheapest_startup,
+											  restrictlist,
+											  merge_pathkeys));
 		}
 
 		/* Can't do anything else if outer path needs to be unique'd */
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index 24e4e59..c3f7ca0 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -745,7 +745,6 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
 	return joinrel;
 }
 
-
 /*
  * have_join_order_restriction
  *		Detect whether the two relations should be joined to satisfy
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index e5228a8..cee5c71 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -623,12 +623,12 @@ find_indexkey_var(PlannerInfo *root, RelOptInfo *rel, AttrNumber varattno)
  */
 List *
 convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
-						  List *subquery_pathkeys)
+						  List *subquery_pathkeys, Plan *subplan)
 {
 	List	   *retval = NIL;
 	int			retvallen = 0;
 	int			outer_query_keys = list_length(root->query_pathkeys);
-	List	   *sub_tlist = rel->subplan->targetlist;
+	List	   *sub_tlist = subplan->targetlist;
 	ListCell   *i;
 
 	foreach(i, subquery_pathkeys)
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index a3a82ec..f0c1a6c 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -62,8 +62,8 @@ static Plan *create_bitmap_subplan(PlannerInfo *root, Path *bitmapqual,
 					  List **qual, List **indexqual);
 static TidScan *create_tidscan_plan(PlannerInfo *root, TidPath *best_path,
 					List *tlist, List *scan_clauses);
-static SubqueryScan *create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
-						 List *tlist, List *scan_clauses);
+static SubqueryScan *create_subqueryscan_plan(PlannerInfo *root,
+					SubqueryPath *best_path, List *tlist, List *scan_clauses);
 static FunctionScan *create_functionscan_plan(PlannerInfo *root, Path *best_path,
 						 List *tlist, List *scan_clauses);
 static ValuesScan *create_valuesscan_plan(PlannerInfo *root, Path *best_path,
@@ -321,7 +321,7 @@ create_scan_plan(PlannerInfo *root, Path *best_path)
 
 		case T_SubqueryScan:
 			plan = (Plan *) create_subqueryscan_plan(root,
-													 best_path,
+													 (SubqueryPath *) best_path,
 													 tlist,
 													 scan_clauses);
 			break;
@@ -1538,15 +1538,15 @@ create_tidscan_plan(PlannerInfo *root, TidPath *best_path,
  *	 with restriction clauses 'scan_clauses' and targetlist 'tlist'.
  */
 static SubqueryScan *
-create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
+create_subqueryscan_plan(PlannerInfo *root, SubqueryPath *best_path,
 						 List *tlist, List *scan_clauses)
 {
 	SubqueryScan *scan_plan;
-	Index		scan_relid = best_path->parent->relid;
+	Index		scan_relid = best_path->path.parent->relid;
 
 	/* it should be a subquery base rel... */
 	Assert(scan_relid > 0);
-	Assert(best_path->parent->rtekind == RTE_SUBQUERY);
+	Assert(best_path->path.parent->rtekind == RTE_SUBQUERY);
 
 	/* Sort clauses into best execution order */
 	scan_clauses = order_qual_clauses(root, scan_clauses);
@@ -1557,11 +1557,63 @@ create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
 	scan_plan = make_subqueryscan(tlist,
 								  scan_clauses,
 								  scan_relid,
-								  best_path->parent->subplan,
-								  best_path->parent->subrtable,
-								  best_path->parent->subrowmark);
+								  best_path->subplan,
+								  best_path->subrtable,
+								  best_path->subrowmark);
 
-	copy_path_costsize(&scan_plan->scan.plan, best_path);
+	copy_path_costsize(&scan_plan->scan.plan, &best_path->path);
+
+	/*
+	 * If this plan is inner of nestloop and push-down-qual case,
+	 * params are extracted and registered for the upper nestloop.
+	 * As Plan has only bitmap extParam field, we cannot take them
+	 * directly; instead take them from PlannerGlobal.
+	 */
+	if (scan_plan->subplan->extParam)
+	{
+		int		i;
+		ListCell   *ppl;
+		PlannerParamItem   *pitem;
+
+		i = 0;
+		foreach(ppl, root->glob->paramlist)
+		{
+			pitem = (PlannerParamItem *) lfirst(ppl);
+			/* take the ones only in this query level */
+			if (pitem->abslevel == root->query_level &&
+				IsA(pitem->item, Var) &&
+				((Var *) pitem->item)->varlevelsup == 0)
+			{
+				Var	   *var = (Var *) pitem->item;
+				NestLoopParam	   *nlp;
+				ListCell   *lc;
+				bool		found = false;
+
+				/* Is the param already listed in root->curOuterParams? */
+				foreach(lc, root->curOuterParams)
+				{
+					nlp = (NestLoopParam *) lfirst(lc);
+					if (equal(nlp->paramval, var))
+					{
+						/* Present, so we can just skip */
+						found = true;
+						break;
+					}
+				}
+				if (found)
+				{
+					i++;
+					continue;
+				}
+				/* No, so add it */
+				nlp = makeNode(NestLoopParam);
+				nlp->paramno = i;
+				nlp->paramval = var;
+				root->curOuterParams = lappend(root->curOuterParams, nlp);
+			}
+			i++;
+		}
+	}
 
 	return scan_plan;
 }
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 161d5ab..2e711d8 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -1331,17 +1331,21 @@ distinct_col_search(int colno, List *colnos, List *opids)
  *	  returning the pathnode.
  */
 Path *
-create_subqueryscan_path(RelOptInfo *rel, List *pathkeys)
+create_subqueryscan_path(RelOptInfo *rel, List *pathkeys, Plan *subplan,
+						 List *subrtable, List *subrowmark)
 {
-	Path	   *pathnode = makeNode(Path);
+	SubqueryPath   *pathnode = makeNode(SubqueryPath);
 
-	pathnode->pathtype = T_SubqueryScan;
-	pathnode->parent = rel;
-	pathnode->pathkeys = pathkeys;
+	pathnode->path.pathtype = T_SubqueryScan;
+	pathnode->path.parent = rel;
+	pathnode->path.pathkeys = pathkeys;
+	pathnode->subplan = subplan;
+	pathnode->subrtable = subrtable;
+	pathnode->subrowmark = subrowmark;
 
 	cost_subqueryscan(pathnode, rel);
 
-	return pathnode;
+	return (Path *) pathnode;
 }
 
 /*
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index b7a5845..ab2338f 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -82,9 +82,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
 	rel->indexlist = NIL;
 	rel->pages = 0;
 	rel->tuples = 0;
-	rel->subplan = NULL;
-	rel->subrtable = NIL;
-	rel->subrowmark = NIL;
+	rel->preprocessed_subquery = NULL;
 	rel->baserestrictinfo = NIL;
 	rel->baserestrictcost.startup = 0;
 	rel->baserestrictcost.per_tuple = 0;
@@ -336,9 +334,7 @@ build_join_rel(PlannerInfo *root,
 	joinrel->indexlist = NIL;
 	joinrel->pages = 0;
 	joinrel->tuples = 0;
-	joinrel->subplan = NULL;
-	joinrel->subrtable = NIL;
-	joinrel->subrowmark = NIL;
+	joinrel->preprocessed_subquery = NULL;
 	joinrel->baserestrictinfo = NIL;
 	joinrel->baserestrictcost.startup = 0;
 	joinrel->baserestrictcost.per_tuple = 0;
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index d8bc6b8..e951fd1 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -220,6 +220,7 @@ typedef enum NodeTag
 	T_MergePath,
 	T_HashPath,
 	T_TidPath,
+	T_SubqueryPath,
 	T_ForeignPath,
 	T_AppendPath,
 	T_MergeAppendPath,
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index f659269..98e2744 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -322,13 +322,6 @@ typedef struct PlannerInfo
  *					(always NIL if it's not a table)
  *		pages - number of disk pages in relation (zero if not a table)
  *		tuples - number of tuples in relation (not considering restrictions)
- *		subplan - plan for subquery (NULL if it's not a subquery)
- *		subrtable - rangetable for subquery (NIL if it's not a subquery)
- *		subrowmark - rowmarks for subquery (NIL if it's not a subquery)
- *
- *		Note: for a subquery, tuples and subplan are not set immediately
- *		upon creation of the RelOptInfo object; they are filled in when
- *		set_base_rel_pathlist processes the object.
  *
  *		For otherrels that are appendrel members, these fields are filled
  *		in just as for a baserel.
@@ -408,9 +401,8 @@ typedef struct RelOptInfo
 	List	   *indexlist;		/* list of IndexOptInfo */
 	BlockNumber pages;
 	double		tuples;
-	struct Plan *subplan;		/* if subquery */
-	List	   *subrtable;		/* if subquery */
-	List	   *subrowmark;		/* if subquery */
+
+	Query	   *preprocessed_subquery; /* if subquery */
 
 	/* used by various scans and joins: */
 	List	   *baserestrictinfo;		/* RestrictInfo structures (if base
@@ -770,6 +762,23 @@ typedef struct TidPath
 } TidPath;
 
 /*
+ * SubqueryPath represents a scan of a subquery scan
+ *
+ * The struct holds subplan, subrtable and subrowmark, which are
+ * the temporary spaces to transport to the final stage of planner.
+ * Note each SubqueryPath may have different plan, depending on
+ * the pushed down qual clauses (as parameterized inner subquery).
+ */
+typedef struct SubqueryPath
+{
+	Path		path;
+	struct Plan *subplan;
+	List	   *subrtable;
+	List	   *subrowmark;
+	double		rows;
+} SubqueryPath;
+
+/*
  * ForeignPath represents a scan of a foreign table
  */
 typedef struct ForeignPath
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 2763863..7ec2c7b 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -75,7 +75,7 @@ extern void cost_bitmap_or_node(BitmapOrPath *path, PlannerInfo *root);
 extern void cost_bitmap_tree_node(Path *path, Cost *cost, Selectivity *selec);
 extern void cost_tidscan(Path *path, PlannerInfo *root,
 			 RelOptInfo *baserel, List *tidquals);
-extern void cost_subqueryscan(Path *path, RelOptInfo *baserel);
+extern void cost_subqueryscan(SubqueryPath *path, RelOptInfo *baserel);
 extern void cost_functionscan(Path *path, PlannerInfo *root,
 				  RelOptInfo *baserel);
 extern void cost_valuesscan(Path *path, PlannerInfo *root,
@@ -122,7 +122,7 @@ extern void set_joinrel_size_estimates(PlannerInfo *root, RelOptInfo *rel,
 						   SpecialJoinInfo *sjinfo,
 						   List *restrictlist);
 extern void set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
-							PlannerInfo *subroot);
+							PlannerInfo *subroot, Plan *subplan);
 extern void set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel);
 extern void set_values_size_estimates(PlannerInfo *root, RelOptInfo *rel);
 extern void set_cte_size_estimates(PlannerInfo *root, RelOptInfo *rel,
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 1da2131..6de2816 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -14,6 +14,7 @@
 #ifndef PATHNODE_H
 #define PATHNODE_H
 
+#include "nodes/plannodes.h"
 #include "nodes/relation.h"
 
 
@@ -56,7 +57,8 @@ extern ResultPath *create_result_path(List *quals);
 extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath);
 extern UniquePath *create_unique_path(PlannerInfo *root, RelOptInfo *rel,
 				   Path *subpath, SpecialJoinInfo *sjinfo);
-extern Path *create_subqueryscan_path(RelOptInfo *rel, List *pathkeys);
+extern Path *create_subqueryscan_path(RelOptInfo *rel, List *pathkeys,
+					Plan *subplan, List *subrtable, List *subrowmark);
 extern Path *create_functionscan_path(PlannerInfo *root, RelOptInfo *rel);
 extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel);
 extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel);
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 7f1353a..54d0b01 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -33,6 +33,9 @@ extern PGDLLIMPORT join_search_hook_type join_search_hook;
 extern RelOptInfo *make_one_rel(PlannerInfo *root, List *joinlist);
 extern RelOptInfo *standard_join_search(PlannerInfo *root, int levels_needed,
 					 List *initial_rels);
+extern void best_inner_subqueryscan(PlannerInfo *root, RelOptInfo *rel,
+						RelOptInfo *outer_rel, List *join_clause,
+						Path **cheapest_start_path, Path **cheapest_total_path);
 
 #ifdef OPTIMIZER_DEBUG
 extern void debug_print_rel(PlannerInfo *root, RelOptInfo *rel);
@@ -154,7 +157,7 @@ extern Path *get_cheapest_fractional_path_for_pathkeys(List *paths,
 extern List *build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index,
 					 ScanDirection scandir);
 extern List *convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
-						  List *subquery_pathkeys);
+						  List *subquery_pathkeys, Plan *subplan);
 extern List *build_join_pathkeys(PlannerInfo *root,
 					RelOptInfo *joinrel,
 					JoinType jointype,

diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
new file mode 100644
index b5be09a..6596a1c
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
*************** _outTidPath(StringInfo str, TidPath *nod
*** 1557,1562 ****
--- 1557,1573 ----
  }
  
  static void
+ _outSubqueryPath(StringInfo str, SubqueryPath *node)
+ {
+ 	WRITE_NODE_TYPE("SUBQUERYPATH");
+ 
+ 	_outPathInfo(str, (Path *) node);
+ 	WRITE_NODE_FIELD(subplan);
+ 	WRITE_NODE_FIELD(subrtable);
+ 	WRITE_NODE_FIELD(subrowmark);
+ }
+ 
+ static void
  _outForeignPath(StringInfo str, ForeignPath *node)
  {
  	WRITE_NODE_TYPE("FOREIGNPATH");
*************** _outRelOptInfo(StringInfo str, RelOptInf
*** 1738,1746 ****
  	WRITE_NODE_FIELD(indexlist);
  	WRITE_UINT_FIELD(pages);
  	WRITE_FLOAT_FIELD(tuples, "%.0f");
- 	WRITE_NODE_FIELD(subplan);
- 	WRITE_NODE_FIELD(subrtable);
- 	WRITE_NODE_FIELD(subrowmark);
  	WRITE_NODE_FIELD(baserestrictinfo);
  	WRITE_NODE_FIELD(joininfo);
  	WRITE_BOOL_FIELD(has_eclass_joins);
--- 1749,1754 ----
*************** _outNode(StringInfo str, void *obj)
*** 2949,2954 ****
--- 2957,2965 ----
  			case T_TidPath:
  				_outTidPath(str, obj);
  				break;
+ 			case T_SubqueryPath:
+ 				_outSubqueryPath(str, obj);
+ 				break;
  			case T_ForeignPath:
  				_outForeignPath(str, obj);
  				break;
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
new file mode 100644
index 6b43aee..bd3a53f
*** a/src/backend/optimizer/path/allpaths.c
--- b/src/backend/optimizer/path/allpaths.c
***************
*** 31,36 ****
--- 31,37 ----
  #include "optimizer/planner.h"
  #include "optimizer/prep.h"
  #include "optimizer/restrictinfo.h"
+ #include "optimizer/subselect.h"
  #include "optimizer/var.h"
  #include "parser/parse_clause.h"
  #include "parser/parsetree.h"
*************** has_multiple_baserels(PlannerInfo *root)
*** 677,682 ****
--- 678,689 ----
  /*
   * set_subquery_pathlist
   *		Build the (single) access path for a subquery RTE
+  *
+  * Although we build only one access path for the subquery,
+  * join search process may find another path by pushing down
+  * the nestloop param into subquery. The finding process will
+  * be done much later than here, but some common operation like
+  * preprocessing subquery are shared.
   */
  static void
  set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
*************** set_subquery_pathlist(PlannerInfo *root,
*** 687,693 ****
  	bool	   *differentTypes;
  	double		tuple_fraction;
  	PlannerInfo *subroot;
! 	List	   *pathkeys;
  
  	/*
  	 * Must copy the Query so that planning doesn't mess up the RTE contents
--- 694,702 ----
  	bool	   *differentTypes;
  	double		tuple_fraction;
  	PlannerInfo *subroot;
! 	Plan	    *subplan;
! 	List	    *pathkeys;
! 	bool issafe;
  
  	/*
  	 * Must copy the Query so that planning doesn't mess up the RTE contents
*************** set_subquery_pathlist(PlannerInfo *root,
*** 720,727 ****
  	 * XXX Are there any cases where we want to make a policy decision not to
  	 * push down a pushable qual, because it'd result in a worse plan?
  	 */
! 	if (rel->baserestrictinfo != NIL &&
! 		subquery_is_pushdown_safe(subquery, subquery, differentTypes))
  	{
  		/* OK to consider pushing down individual quals */
  		List	   *upperrestrictlist = NIL;
--- 729,738 ----
  	 * XXX Are there any cases where we want to make a policy decision not to
  	 * push down a pushable qual, because it'd result in a worse plan?
  	 */
! 
! 	issafe = subquery_is_pushdown_safe(subquery, subquery, differentTypes);
! 
! 	if (rel->baserestrictinfo != NIL && issafe)
  	{
  		/* OK to consider pushing down individual quals */
  		List	   *upperrestrictlist = NIL;
*************** set_subquery_pathlist(PlannerInfo *root,
*** 749,754 ****
--- 760,769 ----
  
  	pfree(differentTypes);
  
+ 	/* save it for later use in best_inner_subqueryscan */
+ 	if (issafe)
+ 		rel->preprocessed_subquery = copyObject(subquery);
+ 
  	/*
  	 * We can safely pass the outer tuple_fraction down to the subquery if the
  	 * outer level has no joining, aggregation, or sorting to do. Otherwise
*************** set_subquery_pathlist(PlannerInfo *root,
*** 766,792 ****
  		tuple_fraction = root->tuple_fraction;
  
  	/* Generate the plan for the subquery */
! 	rel->subplan = subquery_planner(root->glob, subquery,
! 									root,
! 									false, tuple_fraction,
! 									&subroot);
! 	rel->subrtable = subroot->parse->rtable;
! 	rel->subrowmark = subroot->rowMarks;
  
  	/* Mark rel with estimated output rows, width, etc */
! 	set_subquery_size_estimates(root, rel, subroot);
  
  	/* Convert subquery pathkeys to outer representation */
! 	pathkeys = convert_subquery_pathkeys(root, rel, subroot->query_pathkeys);
  
  	/* Generate appropriate path */
! 	add_path(rel, create_subqueryscan_path(rel, pathkeys));
  
  	/* Select cheapest path (pretty easy in this case...) */
  	set_cheapest(rel);
  }
  
  /*
   * set_function_pathlist
   *		Build the (single) access path for a function RTE
   */
--- 781,959 ----
  		tuple_fraction = root->tuple_fraction;
  
  	/* Generate the plan for the subquery */
! 	subplan = subquery_planner(root->glob, subquery,
! 							   root,
! 							   false, tuple_fraction,
! 							   &subroot);
  
  	/* Mark rel with estimated output rows, width, etc */
! 	set_subquery_size_estimates(root, rel, subroot, subplan);
  
  	/* Convert subquery pathkeys to outer representation */
! 	pathkeys = convert_subquery_pathkeys(root, rel,
! 										 subroot->query_pathkeys, subplan);
  
  	/* Generate appropriate path */
! 	add_path(rel, create_subqueryscan_path(rel, pathkeys,
! 			 subplan, subroot->parse->rtable, subroot->rowMarks));
  
  	/* Select cheapest path (pretty easy in this case...) */
  	set_cheapest(rel);
  }
  
  /*
+  * best_inner_subqueryscan
+  *
+  * As the inner scan relation, try to find another subquer path which may
+  * be better by pushing down some join clauses. If such possibility is found,
+  * return the path in *cheapest_start_path or *cheapest_total_path.
+  * Currently both paths will be resulted in the same, whereas further
+  * improvement might find cases they'd be different.
+  *
+  * It re-uses modified subquery (a.k.a. rel->preprocessed_subquery.) The basic
+  * push-down of baserestrictinfo was common among base path (done in
+  * set_subquery_pathlist()) so this routine doesn't care it. But once we find
+  * push-down-able join clause, the parsed subquery will be modified after copied.
+  */
+ void
+ best_inner_subqueryscan(PlannerInfo *root, RelOptInfo *rel,
+ 						RelOptInfo *outer_rel, List *join_clause,
+ 						Path **cheapest_start_path, Path **cheapest_total_path)
+ {
+ 	Query	   *parse = root->parse;
+ 	Query	   *subquery;
+ 	Index		rti = rel->relid;
+ 	RangeTblEntry *rte = planner_rt_fetch(rti, root);
+ 	bool	   *differentTypes;
+ 	double		tuple_fraction;
+ 	PlannerInfo *subroot;
+ 	Plan	   *subplan;
+ 	List	   *pathkeys;
+ 	Path	   *path;
+ 	bool		query_modified = false;
+ 
+ 	/* initialize paths to return immediately anytime */
+ 	*cheapest_start_path = *cheapest_total_path = NULL;
+ 
+ 	/* empty join condition doesn't make sense here */
+ 	if (join_clause == NIL)
+ 		return;
+ 
+ 	/* rel->preprocessed_subquery was only saved for pushdown save subqueries */
+ 	if ((subquery = rel->preprocessed_subquery) == NULL)
+ 		return;
+ 
+ 	/* We need a workspace for keeping track of set-op type coercions */
+ 	differentTypes = (bool *)
+ 		palloc0((list_length(subquery->targetList) + 1) * sizeof(bool));
+ 
+ 	/*
+ 	 * Currently we focus on only cases of aggregate subquery.
+ 	 * I'm not quite sure if there are other useful cases.
+ 	 */
+ 	if (subquery->hasAggs)
+ 	{
+ 		ListCell   *l;
+ 
+ 		foreach (l, join_clause)
+ 		{
+ 			RestrictInfo   *rinfo = (RestrictInfo *) lfirst(l);
+ 			Node		   *lexpr, *rexpr;
+ 			Expr		   *clause = NULL;
+ 			Param		   *param;
+ 			OpExpr		   *rclause;
+ 
+ 			/*
+ 			 * For the first step, only Var = Var join clause is targeted.
+ 			 * This may be improved in the future.
+ 			 */
+ 			if (!is_opclause(rinfo->clause))
+ 				continue;
+ 			rclause = (OpExpr *) rinfo->clause;
+ 			lexpr = get_leftop((Expr *) rclause);
+ 			rexpr = get_rightop((Expr *) rclause);
+ 			if (!(IsA(lexpr, Var) && IsA(rexpr, Var)))
+ 				continue;
+ 
+ 			/* see which arg is mine and outer's */
+ 			if (bms_is_member(((Var *) lexpr)->varno, outer_rel->relids))
+ 			{
+ 				param = assign_nestloop_param(root, (Var *) lexpr);
+ 				clause = make_opclause(rclause->opno,
+ 									   rclause->opresulttype,
+ 									   false,
+ 									   (Expr *) param,
+ 									   (Expr *) copyObject(rexpr),
+ 									   InvalidOid,
+ 									   rclause->opcollid);
+ 			}
+ 			else if (bms_is_member(((Var *) rexpr)->varno, outer_rel->relids))
+ 			{
+ 				param = assign_nestloop_param(root, (Var *) rexpr);
+ 				clause = make_opclause(rclause->opno,
+ 									   rclause->opresulttype,
+ 									   false,
+ 									   (Expr *) copyObject(lexpr),
+ 									   (Expr *) param,
+ 									   InvalidOid,
+ 									   rclause->opcollid);
+ 			}
+ 
+ 			/* TODO:no check isouter or not? */
+ 			if (clause &&
+ 				qual_is_pushdown_safe(subquery, rti, (Node *) clause,
+ 									  differentTypes))
+ 			{
+ 				/* copying the whole Query is expensive; let's copy-on-write */
+ 				if (rel->preprocessed_subquery == subquery)
+ 					subquery = copyObject(subquery);
+ 				subquery_push_qual(subquery, rte, rti, (Node *) clause);
+ 				query_modified = true;
+ 			}
+ 		}
+ 	}
+ 
+ 	pfree(differentTypes);
+ 
+ 	/* return immediately if such subquery isn't found. Use original one */
+ 	if (!query_modified)
+ 		return;
+ 
+ 	/*
+ 	 * We can safely pass the outer tuple_fraction down to the subquery if the
+ 	 * outer level has no joining, aggregation, or sorting to do. Otherwise
+ 	 * we'd better tell the subquery to plan for full retrieval. (XXX This
+ 	 * could probably be made more intelligent ...)
+ 	 */
+ 	if (parse->hasAggs ||
+ 		parse->groupClause ||
+ 		parse->havingQual ||
+ 		parse->distinctClause ||
+ 		parse->sortClause ||
+ 		has_multiple_baserels(root))
+ 		tuple_fraction = 0.0;	/* default case */
+ 	else
+ 		tuple_fraction = root->tuple_fraction;
+ 
+ 	/* Generate the plan for the subquery */
+ 	subplan = subquery_planner(root->glob, subquery,
+ 							   root,
+ 							   false, tuple_fraction,
+ 							   &subroot);
+ 
+ 	/* Convert subquery pathkeys to outer representation */
+ 	pathkeys = convert_subquery_pathkeys(root, rel,
+ 										 subroot->query_pathkeys, subplan);
+ 
+ 	/* Generate appropriate path */
+ 	path = create_subqueryscan_path(rel, pathkeys,
+ 			 subplan, subroot->parse->rtable, subroot->rowMarks);
+ 
+ 	/* return the same path for now */
+ 	*cheapest_start_path = *cheapest_total_path = path;
+ }
+ 
+ /*
   * set_function_pathlist
   *		Build the (single) access path for a function RTE
   */
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
new file mode 100644
index bb38768..b7271c3
*** a/src/backend/optimizer/path/costsize.c
--- b/src/backend/optimizer/path/costsize.c
*************** cost_tidscan(Path *path, PlannerInfo *ro
*** 892,898 ****
   *	  Determines and returns the cost of scanning a subquery RTE.
   */
  void
! cost_subqueryscan(Path *path, RelOptInfo *baserel)
  {
  	Cost		startup_cost;
  	Cost		run_cost;
--- 892,898 ----
   *	  Determines and returns the cost of scanning a subquery RTE.
   */
  void
! cost_subqueryscan(SubqueryPath *path, RelOptInfo *baserel)
  {
  	Cost		startup_cost;
  	Cost		run_cost;
*************** cost_subqueryscan(Path *path, RelOptInfo
*** 907,921 ****
  	 * any restriction clauses that will be attached to the SubqueryScan node,
  	 * plus cpu_tuple_cost to account for selection and projection overhead.
  	 */
! 	path->startup_cost = baserel->subplan->startup_cost;
! 	path->total_cost = baserel->subplan->total_cost;
  
  	startup_cost = baserel->baserestrictcost.startup;
  	cpu_per_tuple = cpu_tuple_cost + baserel->baserestrictcost.per_tuple;
  	run_cost = cpu_per_tuple * baserel->tuples;
  
! 	path->startup_cost += startup_cost;
! 	path->total_cost += startup_cost + run_cost;
  }
  
  /*
--- 907,921 ----
  	 * any restriction clauses that will be attached to the SubqueryScan node,
  	 * plus cpu_tuple_cost to account for selection and projection overhead.
  	 */
! 	path->path.startup_cost = path->subplan->startup_cost;
! 	path->path.total_cost = path->subplan->total_cost;
  
  	startup_cost = baserel->baserestrictcost.startup;
  	cpu_per_tuple = cpu_tuple_cost + baserel->baserestrictcost.per_tuple;
  	run_cost = cpu_per_tuple * baserel->tuples;
  
! 	path->path.startup_cost += startup_cost;
! 	path->path.total_cost += startup_cost + run_cost;
  }
  
  /*
*************** set_joinrel_size_estimates(PlannerInfo *
*** 3222,3228 ****
   */
  void
  set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
! 							PlannerInfo *subroot)
  {
  	RangeTblEntry *rte;
  	ListCell   *lc;
--- 3222,3228 ----
   */
  void
  set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
! 							PlannerInfo *subroot, Plan *subplan)
  {
  	RangeTblEntry *rte;
  	ListCell   *lc;
*************** set_subquery_size_estimates(PlannerInfo 
*** 3233,3239 ****
  	Assert(rte->rtekind == RTE_SUBQUERY);
  
  	/* Copy raw number of output rows from subplan */
! 	rel->tuples = rel->subplan->plan_rows;
  
  	/*
  	 * Compute per-output-column width estimates by examining the subquery's
--- 3233,3239 ----
  	Assert(rte->rtekind == RTE_SUBQUERY);
  
  	/* Copy raw number of output rows from subplan */
! 	rel->tuples = subplan->plan_rows;
  
  	/*
  	 * Compute per-output-column width estimates by examining the subquery's
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
new file mode 100644
index 7d3cf42..c79fd43
*** a/src/backend/optimizer/path/joinpath.c
--- b/src/backend/optimizer/path/joinpath.c
*************** sort_inner_and_outer(PlannerInfo *root,
*** 329,335 ****
   * inner path, one on the same with materialization, one on the
   * cheapest-startup-cost inner path (if different), one on the
   * cheapest-total inner-indexscan path (if any), and one on the
!  * cheapest-startup inner-indexscan path (if different).
   *
   * We also consider mergejoins if mergejoin clauses are available.	We have
   * two ways to generate the inner path for a mergejoin: sort the cheapest
--- 329,337 ----
   * inner path, one on the same with materialization, one on the
   * cheapest-startup-cost inner path (if different), one on the
   * cheapest-total inner-indexscan path (if any), and one on the
!  * cheapest-startup inner-indexscan path (if different) or on the
!  * cheapest-total inner-subqueryscan path (if any).
!  * Note inner-indexpaths and inner-subquerypaths are not made at the same time.
   *
   * We also consider mergejoins if mergejoin clauses are available.	We have
   * two ways to generate the inner path for a mergejoin: sort the cheapest
*************** match_unsorted_outer(PlannerInfo *root,
*** 369,374 ****
--- 371,378 ----
  	Path	   *matpath = NULL;
  	Path	   *index_cheapest_startup = NULL;
  	Path	   *index_cheapest_total = NULL;
+ 	Path	   *sub_cheapest_startup = NULL;
+ 	Path	   *sub_cheapest_total = NULL;
  	ListCell   *l;
  
  	/*
*************** match_unsorted_outer(PlannerInfo *root,
*** 430,437 ****
  				create_material_path(innerrel, inner_cheapest_total);
  
  		/*
! 		 * Get the best innerjoin indexpaths (if any) for this outer rel.
! 		 * They're the same for all outer paths.
  		 */
  		if (innerrel->reloptkind != RELOPT_JOINREL)
  		{
--- 434,441 ----
  				create_material_path(innerrel, inner_cheapest_total);
  
  		/*
! 		 * Get the best innerjoin indexpaths or subquerypaths (if any)
! 		 * for this outer rel. They're the same for all outer paths.
  		 */
  		if (innerrel->reloptkind != RELOPT_JOINREL)
  		{
*************** match_unsorted_outer(PlannerInfo *root,
*** 444,449 ****
--- 448,457 ----
  				best_inner_indexscan(root, innerrel, outerrel, jointype,
  									 &index_cheapest_startup,
  									 &index_cheapest_total);
+ 			else if (innerrel->rtekind == RTE_SUBQUERY)
+ 				best_inner_subqueryscan(root, innerrel, outerrel, restrictlist,
+ 										&sub_cheapest_startup,
+ 										&sub_cheapest_total);
  		}
  	}
  
*************** match_unsorted_outer(PlannerInfo *root,
*** 487,493 ****
  			 * cheapest-total-cost inner.  When appropriate, also consider
  			 * using the materialized form of the cheapest inner, the
  			 * cheapest-startup-cost inner path, and the cheapest innerjoin
! 			 * indexpaths.
  			 */
  			add_path(joinrel, (Path *)
  					 create_nestloop_path(root,
--- 495,501 ----
  			 * cheapest-total-cost inner.  When appropriate, also consider
  			 * using the materialized form of the cheapest inner, the
  			 * cheapest-startup-cost inner path, and the cheapest innerjoin
! 			 * indexpaths or subquerypaths.
  			 */
  			add_path(joinrel, (Path *)
  					 create_nestloop_path(root,
*************** match_unsorted_outer(PlannerInfo *root,
*** 539,544 ****
--- 547,562 ----
  											  index_cheapest_startup,
  											  restrictlist,
  											  merge_pathkeys));
+ 			if (sub_cheapest_total != NULL)
+ 				add_path(joinrel, (Path *)
+ 						 create_nestloop_path(root,
+ 											  joinrel,
+ 											  jointype,
+ 											  sjinfo,
+ 											  outerpath,
+ 											  sub_cheapest_startup,
+ 											  restrictlist,
+ 											  merge_pathkeys));
  		}
  
  		/* Can't do anything else if outer path needs to be unique'd */
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
new file mode 100644
index 24e4e59..c3f7ca0
*** a/src/backend/optimizer/path/joinrels.c
--- b/src/backend/optimizer/path/joinrels.c
*************** make_join_rel(PlannerInfo *root, RelOptI
*** 745,751 ****
  	return joinrel;
  }
  
- 
  /*
   * have_join_order_restriction
   *		Detect whether the two relations should be joined to satisfy
--- 745,750 ----
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
new file mode 100644
index e5228a8..cee5c71
*** a/src/backend/optimizer/path/pathkeys.c
--- b/src/backend/optimizer/path/pathkeys.c
*************** find_indexkey_var(PlannerInfo *root, Rel
*** 623,634 ****
   */
  List *
  convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
! 						  List *subquery_pathkeys)
  {
  	List	   *retval = NIL;
  	int			retvallen = 0;
  	int			outer_query_keys = list_length(root->query_pathkeys);
! 	List	   *sub_tlist = rel->subplan->targetlist;
  	ListCell   *i;
  
  	foreach(i, subquery_pathkeys)
--- 623,634 ----
   */
  List *
  convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
! 						  List *subquery_pathkeys, Plan *subplan)
  {
  	List	   *retval = NIL;
  	int			retvallen = 0;
  	int			outer_query_keys = list_length(root->query_pathkeys);
! 	List	   *sub_tlist = subplan->targetlist;
  	ListCell   *i;
  
  	foreach(i, subquery_pathkeys)
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
new file mode 100644
index a3a82ec..f0c1a6c
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
*************** static Plan *create_bitmap_subplan(Plann
*** 62,69 ****
  					  List **qual, List **indexqual);
  static TidScan *create_tidscan_plan(PlannerInfo *root, TidPath *best_path,
  					List *tlist, List *scan_clauses);
! static SubqueryScan *create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
! 						 List *tlist, List *scan_clauses);
  static FunctionScan *create_functionscan_plan(PlannerInfo *root, Path *best_path,
  						 List *tlist, List *scan_clauses);
  static ValuesScan *create_valuesscan_plan(PlannerInfo *root, Path *best_path,
--- 62,69 ----
  					  List **qual, List **indexqual);
  static TidScan *create_tidscan_plan(PlannerInfo *root, TidPath *best_path,
  					List *tlist, List *scan_clauses);
! static SubqueryScan *create_subqueryscan_plan(PlannerInfo *root,
! 					SubqueryPath *best_path, List *tlist, List *scan_clauses);
  static FunctionScan *create_functionscan_plan(PlannerInfo *root, Path *best_path,
  						 List *tlist, List *scan_clauses);
  static ValuesScan *create_valuesscan_plan(PlannerInfo *root, Path *best_path,
*************** create_scan_plan(PlannerInfo *root, Path
*** 321,327 ****
  
  		case T_SubqueryScan:
  			plan = (Plan *) create_subqueryscan_plan(root,
! 													 best_path,
  													 tlist,
  													 scan_clauses);
  			break;
--- 321,327 ----
  
  		case T_SubqueryScan:
  			plan = (Plan *) create_subqueryscan_plan(root,
! 													 (SubqueryPath *) best_path,
  													 tlist,
  													 scan_clauses);
  			break;
*************** create_tidscan_plan(PlannerInfo *root, T
*** 1538,1552 ****
   *	 with restriction clauses 'scan_clauses' and targetlist 'tlist'.
   */
  static SubqueryScan *
! create_subqueryscan_plan(PlannerInfo *root, Path *best_path,
  						 List *tlist, List *scan_clauses)
  {
  	SubqueryScan *scan_plan;
! 	Index		scan_relid = best_path->parent->relid;
  
  	/* it should be a subquery base rel... */
  	Assert(scan_relid > 0);
! 	Assert(best_path->parent->rtekind == RTE_SUBQUERY);
  
  	/* Sort clauses into best execution order */
  	scan_clauses = order_qual_clauses(root, scan_clauses);
--- 1538,1552 ----
   *	 with restriction clauses 'scan_clauses' and targetlist 'tlist'.
   */
  static SubqueryScan *
! create_subqueryscan_plan(PlannerInfo *root, SubqueryPath *best_path,
  						 List *tlist, List *scan_clauses)
  {
  	SubqueryScan *scan_plan;
! 	Index		scan_relid = best_path->path.parent->relid;
  
  	/* it should be a subquery base rel... */
  	Assert(scan_relid > 0);
! 	Assert(best_path->path.parent->rtekind == RTE_SUBQUERY);
  
  	/* Sort clauses into best execution order */
  	scan_clauses = order_qual_clauses(root, scan_clauses);
*************** create_subqueryscan_plan(PlannerInfo *ro
*** 1557,1567 ****
  	scan_plan = make_subqueryscan(tlist,
  								  scan_clauses,
  								  scan_relid,
! 								  best_path->parent->subplan,
! 								  best_path->parent->subrtable,
! 								  best_path->parent->subrowmark);
  
! 	copy_path_costsize(&scan_plan->scan.plan, best_path);
  
  	return scan_plan;
  }
--- 1557,1619 ----
  	scan_plan = make_subqueryscan(tlist,
  								  scan_clauses,
  								  scan_relid,
! 								  best_path->subplan,
! 								  best_path->subrtable,
! 								  best_path->subrowmark);
  
! 	copy_path_costsize(&scan_plan->scan.plan, &best_path->path);
! 
! 	/*
! 	 * If this plan is inner of nestloop and push-down-qual case,
! 	 * params are extracted and registered for the upper nestloop.
! 	 * As Plan has only bitmap extParam field, we cannot take them
! 	 * directly; instead take them from PlannerGlobal.
! 	 */
! 	if (scan_plan->subplan->extParam)
! 	{
! 		int		i;
! 		ListCell   *ppl;
! 		PlannerParamItem   *pitem;
! 
! 		i = 0;
! 		foreach(ppl, root->glob->paramlist)
! 		{
! 			pitem = (PlannerParamItem *) lfirst(ppl);
! 			/* take the ones only in this query level */
! 			if (pitem->abslevel == root->query_level &&
! 				IsA(pitem->item, Var) &&
! 				((Var *) pitem->item)->varlevelsup == 0)
! 			{
! 				Var	   *var = (Var *) pitem->item;
! 				NestLoopParam	   *nlp;
! 				ListCell   *lc;
! 				bool		found = false;
! 
! 				/* Is the param already listed in root->curOuterParams? */
! 				foreach(lc, root->curOuterParams)
! 				{
! 					nlp = (NestLoopParam *) lfirst(lc);
! 					if (equal(nlp->paramval, var))
! 					{
! 						/* Present, so we can just skip */
! 						found = true;
! 						break;
! 					}
! 				}
! 				if (found)
! 				{
! 					i++;
! 					continue;
! 				}
! 				/* No, so add it */
! 				nlp = makeNode(NestLoopParam);
! 				nlp->paramno = i;
! 				nlp->paramval = var;
! 				root->curOuterParams = lappend(root->curOuterParams, nlp);
! 			}
! 			i++;
! 		}
! 	}
  
  	return scan_plan;
  }
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
new file mode 100644
index 161d5ab..2e711d8
*** a/src/backend/optimizer/util/pathnode.c
--- b/src/backend/optimizer/util/pathnode.c
*************** distinct_col_search(int colno, List *col
*** 1331,1347 ****
   *	  returning the pathnode.
   */
  Path *
! create_subqueryscan_path(RelOptInfo *rel, List *pathkeys)
  {
! 	Path	   *pathnode = makeNode(Path);
  
! 	pathnode->pathtype = T_SubqueryScan;
! 	pathnode->parent = rel;
! 	pathnode->pathkeys = pathkeys;
  
  	cost_subqueryscan(pathnode, rel);
  
! 	return pathnode;
  }
  
  /*
--- 1331,1351 ----
   *	  returning the pathnode.
   */
  Path *
! create_subqueryscan_path(RelOptInfo *rel, List *pathkeys, Plan *subplan,
! 						 List *subrtable, List *subrowmark)
  {
! 	SubqueryPath   *pathnode = makeNode(SubqueryPath);
  
! 	pathnode->path.pathtype = T_SubqueryScan;
! 	pathnode->path.parent = rel;
! 	pathnode->path.pathkeys = pathkeys;
! 	pathnode->subplan = subplan;
! 	pathnode->subrtable = subrtable;
! 	pathnode->subrowmark = subrowmark;
  
  	cost_subqueryscan(pathnode, rel);
  
! 	return (Path *) pathnode;
  }
  
  /*
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
new file mode 100644
index b7a5845..ab2338f
*** a/src/backend/optimizer/util/relnode.c
--- b/src/backend/optimizer/util/relnode.c
*************** build_simple_rel(PlannerInfo *root, int 
*** 82,90 ****
  	rel->indexlist = NIL;
  	rel->pages = 0;
  	rel->tuples = 0;
! 	rel->subplan = NULL;
! 	rel->subrtable = NIL;
! 	rel->subrowmark = NIL;
  	rel->baserestrictinfo = NIL;
  	rel->baserestrictcost.startup = 0;
  	rel->baserestrictcost.per_tuple = 0;
--- 82,88 ----
  	rel->indexlist = NIL;
  	rel->pages = 0;
  	rel->tuples = 0;
! 	rel->preprocessed_subquery = NULL;
  	rel->baserestrictinfo = NIL;
  	rel->baserestrictcost.startup = 0;
  	rel->baserestrictcost.per_tuple = 0;
*************** build_join_rel(PlannerInfo *root,
*** 336,344 ****
  	joinrel->indexlist = NIL;
  	joinrel->pages = 0;
  	joinrel->tuples = 0;
! 	joinrel->subplan = NULL;
! 	joinrel->subrtable = NIL;
! 	joinrel->subrowmark = NIL;
  	joinrel->baserestrictinfo = NIL;
  	joinrel->baserestrictcost.startup = 0;
  	joinrel->baserestrictcost.per_tuple = 0;
--- 334,340 ----
  	joinrel->indexlist = NIL;
  	joinrel->pages = 0;
  	joinrel->tuples = 0;
! 	joinrel->preprocessed_subquery = NULL;
  	joinrel->baserestrictinfo = NIL;
  	joinrel->baserestrictcost.startup = 0;
  	joinrel->baserestrictcost.per_tuple = 0;
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
new file mode 100644
index d8bc6b8..e951fd1
*** a/src/include/nodes/nodes.h
--- b/src/include/nodes/nodes.h
*************** typedef enum NodeTag
*** 220,225 ****
--- 220,226 ----
  	T_MergePath,
  	T_HashPath,
  	T_TidPath,
+ 	T_SubqueryPath,
  	T_ForeignPath,
  	T_AppendPath,
  	T_MergeAppendPath,
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
new file mode 100644
index f659269..98e2744
*** a/src/include/nodes/relation.h
--- b/src/include/nodes/relation.h
*************** typedef struct PlannerInfo
*** 322,334 ****
   *					(always NIL if it's not a table)
   *		pages - number of disk pages in relation (zero if not a table)
   *		tuples - number of tuples in relation (not considering restrictions)
-  *		subplan - plan for subquery (NULL if it's not a subquery)
-  *		subrtable - rangetable for subquery (NIL if it's not a subquery)
-  *		subrowmark - rowmarks for subquery (NIL if it's not a subquery)
-  *
-  *		Note: for a subquery, tuples and subplan are not set immediately
-  *		upon creation of the RelOptInfo object; they are filled in when
-  *		set_base_rel_pathlist processes the object.
   *
   *		For otherrels that are appendrel members, these fields are filled
   *		in just as for a baserel.
--- 322,327 ----
*************** typedef struct RelOptInfo
*** 408,416 ****
  	List	   *indexlist;		/* list of IndexOptInfo */
  	BlockNumber pages;
  	double		tuples;
! 	struct Plan *subplan;		/* if subquery */
! 	List	   *subrtable;		/* if subquery */
! 	List	   *subrowmark;		/* if subquery */
  
  	/* used by various scans and joins: */
  	List	   *baserestrictinfo;		/* RestrictInfo structures (if base
--- 401,408 ----
  	List	   *indexlist;		/* list of IndexOptInfo */
  	BlockNumber pages;
  	double		tuples;
! 
! 	Query	   *preprocessed_subquery; /* if subquery */
  
  	/* used by various scans and joins: */
  	List	   *baserestrictinfo;		/* RestrictInfo structures (if base
*************** typedef struct TidPath
*** 770,775 ****
--- 762,784 ----
  } TidPath;
  
  /*
+  * SubqueryPath represents a scan of a subquery scan
+  *
+  * The struct holds subplan, subrtable and subrowmark, which are
+  * the temporary spaces to transport to the final stage of planner.
+  * Note each SubqueryPath may have different plan, depending on
+  * the pushed down qual clauses (as parameterized inner subquery).
+  */
+ typedef struct SubqueryPath
+ {
+ 	Path		path;
+ 	struct Plan *subplan;
+ 	List	   *subrtable;
+ 	List	   *subrowmark;
+ 	double		rows;
+ } SubqueryPath;
+ 
+ /*
   * ForeignPath represents a scan of a foreign table
   */
  typedef struct ForeignPath
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
new file mode 100644
index 2763863..7ec2c7b
*** a/src/include/optimizer/cost.h
--- b/src/include/optimizer/cost.h
*************** extern void cost_bitmap_or_node(BitmapOr
*** 75,81 ****
  extern void cost_bitmap_tree_node(Path *path, Cost *cost, Selectivity *selec);
  extern void cost_tidscan(Path *path, PlannerInfo *root,
  			 RelOptInfo *baserel, List *tidquals);
! extern void cost_subqueryscan(Path *path, RelOptInfo *baserel);
  extern void cost_functionscan(Path *path, PlannerInfo *root,
  				  RelOptInfo *baserel);
  extern void cost_valuesscan(Path *path, PlannerInfo *root,
--- 75,81 ----
  extern void cost_bitmap_tree_node(Path *path, Cost *cost, Selectivity *selec);
  extern void cost_tidscan(Path *path, PlannerInfo *root,
  			 RelOptInfo *baserel, List *tidquals);
! extern void cost_subqueryscan(SubqueryPath *path, RelOptInfo *baserel);
  extern void cost_functionscan(Path *path, PlannerInfo *root,
  				  RelOptInfo *baserel);
  extern void cost_valuesscan(Path *path, PlannerInfo *root,
*************** extern void set_joinrel_size_estimates(P
*** 122,128 ****
  						   SpecialJoinInfo *sjinfo,
  						   List *restrictlist);
  extern void set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
! 							PlannerInfo *subroot);
  extern void set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel);
  extern void set_values_size_estimates(PlannerInfo *root, RelOptInfo *rel);
  extern void set_cte_size_estimates(PlannerInfo *root, RelOptInfo *rel,
--- 122,128 ----
  						   SpecialJoinInfo *sjinfo,
  						   List *restrictlist);
  extern void set_subquery_size_estimates(PlannerInfo *root, RelOptInfo *rel,
! 							PlannerInfo *subroot, Plan *subplan);
  extern void set_function_size_estimates(PlannerInfo *root, RelOptInfo *rel);
  extern void set_values_size_estimates(PlannerInfo *root, RelOptInfo *rel);
  extern void set_cte_size_estimates(PlannerInfo *root, RelOptInfo *rel,
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
new file mode 100644
index 1da2131..6de2816
*** a/src/include/optimizer/pathnode.h
--- b/src/include/optimizer/pathnode.h
***************
*** 14,19 ****
--- 14,20 ----
  #ifndef PATHNODE_H
  #define PATHNODE_H
  
+ #include "nodes/plannodes.h"
  #include "nodes/relation.h"
  
  
*************** extern ResultPath *create_result_path(Li
*** 56,62 ****
  extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath);
  extern UniquePath *create_unique_path(PlannerInfo *root, RelOptInfo *rel,
  				   Path *subpath, SpecialJoinInfo *sjinfo);
! extern Path *create_subqueryscan_path(RelOptInfo *rel, List *pathkeys);
  extern Path *create_functionscan_path(PlannerInfo *root, RelOptInfo *rel);
  extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel);
  extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel);
--- 57,64 ----
  extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath);
  extern UniquePath *create_unique_path(PlannerInfo *root, RelOptInfo *rel,
  				   Path *subpath, SpecialJoinInfo *sjinfo);
! extern Path *create_subqueryscan_path(RelOptInfo *rel, List *pathkeys,
! 					Plan *subplan, List *subrtable, List *subrowmark);
  extern Path *create_functionscan_path(PlannerInfo *root, RelOptInfo *rel);
  extern Path *create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel);
  extern Path *create_ctescan_path(PlannerInfo *root, RelOptInfo *rel);
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
new file mode 100644
index 7f1353a..54d0b01
*** a/src/include/optimizer/paths.h
--- b/src/include/optimizer/paths.h
*************** extern PGDLLIMPORT join_search_hook_type
*** 33,38 ****
--- 33,41 ----
  extern RelOptInfo *make_one_rel(PlannerInfo *root, List *joinlist);
  extern RelOptInfo *standard_join_search(PlannerInfo *root, int levels_needed,
  					 List *initial_rels);
+ extern void best_inner_subqueryscan(PlannerInfo *root, RelOptInfo *rel,
+ 						RelOptInfo *outer_rel, List *join_clause,
+ 						Path **cheapest_start_path, Path **cheapest_total_path);
  
  #ifdef OPTIMIZER_DEBUG
  extern void debug_print_rel(PlannerInfo *root, RelOptInfo *rel);
*************** extern Path *get_cheapest_fractional_pat
*** 154,160 ****
  extern List *build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index,
  					 ScanDirection scandir);
  extern List *convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
! 						  List *subquery_pathkeys);
  extern List *build_join_pathkeys(PlannerInfo *root,
  					RelOptInfo *joinrel,
  					JoinType jointype,
--- 157,163 ----
  extern List *build_index_pathkeys(PlannerInfo *root, IndexOptInfo *index,
  					 ScanDirection scandir);
  extern List *convert_subquery_pathkeys(PlannerInfo *root, RelOptInfo *rel,
! 						  List *subquery_pathkeys, Plan *subplan);
  extern List *build_join_pathkeys(PlannerInfo *root,
  					RelOptInfo *joinrel,
  					JoinType jointype,