Subject: Function scan FDW pushdown

From: Alexander Pyhalov <[email-masked]>
Date: Thu, 20 May 2021 20:43:42 +0300

Hi.

The attached patch allows pushing joins with function RTEs to PostgreSQL 
data sources.
This makes executing queries like this

create foreign table f_pgbench_accounts (aid int, bid int, abalance int, 
filler char(84)) SERVER local_srv OPTIONS (table_name 
'pgbench_accounts');
select * from f_pgbench_accounts join unnest(array[1,2,3]) ON unnest = 
aid;

more efficient.

with patch:

# explain analyze select * from f_pgbench_accounts join 
unnest(array[1,2,3,4,5,6]) ON unnest = aid;
                                            QUERY PLAN
------------------------------------------------------------------------------------------------
  Foreign Scan  (cost=100.00..116.95 rows=7 width=356) (actual 
time=2.282..2.287 rows=6 loops=1)
    Relations: (f_pgbench_accounts) INNER JOIN (FUNCTION RTE unnest)
  Planning Time: 0.487 ms
  Execution Time: 3.336 ms

without patch:

# explain analyze select * from f_pgbench_accounts join 
unnest(array[1,2,3,4,5,6]) ON unnest = aid;
                                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
  Hash Join  (cost=100.14..158.76 rows=7 width=356) (actual 
time=2.263..1268.607 rows=6 loops=1)
    Hash Cond: (f_pgbench_accounts.aid = unnest.unnest)
    ->  Foreign Scan on f_pgbench_accounts  (cost=100.00..157.74 rows=217 
width=352) (actual time=2.190..1205.938 rows=100000 loops=1)
    ->  Hash  (cost=0.06..0.06 rows=6 width=4) (actual time=0.041..0.043 
rows=6 loops=1)
          Buckets: 1024  Batches: 1  Memory Usage: 9kB
          ->  Function Scan on unnest  (cost=0.00..0.06 rows=6 width=4) 
(actual time=0.025..0.028 rows=6 loops=1)
  Planning Time: 0.389 ms
  Execution Time: 1269.627 ms

So far I don't know how to visualize actual function expression used in 
function RTE, as in postgresExplainForeignScan() es->rtable comes from  
queryDesc->plannedstmt->rtable, and rte->functions is already 0.

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

From 6b5ea4c62a1fcd3dad586d4f461cb142834ac266 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <[email-masked]>
Date: Mon, 17 May 2021 19:19:31 +0300
Subject: [PATCH] Function scan FDW pushdown

---
 contrib/postgres_fdw/deparse.c                |  82 ++++--
 .../postgres_fdw/expected/postgres_fdw.out    | 123 ++++++++
 contrib/postgres_fdw/postgres_fdw.c           | 273 ++++++++++++++++--
 contrib/postgres_fdw/sql/postgres_fdw.sql     |  60 ++++
 src/backend/optimizer/util/relnode.c          |  58 +++-
 5 files changed, 547 insertions(+), 49 deletions(-)

diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 31919fda8c6..292ba52ea14 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -1613,13 +1613,36 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
 	{
 		RangeTblEntry *rte = planner_rt_fetch(foreignrel->relid, root);
 
-		/*
-		 * Core code already has some lock on each rel being planned, so we
-		 * can use NoLock here.
-		 */
-		Relation	rel = table_open(rte->relid, NoLock);
+		Assert(rte->rtekind == RTE_RELATION || rte->rtekind == RTE_FUNCTION);
+		if (rte->rtekind == RTE_RELATION)
+		{
+			/*
+			 * Core code already has some lock on each rel being planned, so
+			 * we can use NoLock here.
+			 */
+			Relation	rel = table_open(rte->relid, NoLock);
 
-		deparseRelation(buf, rel);
+			deparseRelation(buf, rel);
+
+			table_close(rel, NoLock);
+		}
+		else if (rte->rtekind == RTE_FUNCTION)
+		{
+			RangeTblFunction *rtfunc;
+			deparse_expr_cxt context;
+
+			Assert(list_length(rte->functions) == 1);
+
+			rtfunc = (RangeTblFunction *) linitial(rte->functions);
+
+			context.root = root;
+			context.foreignrel = foreignrel;
+			context.scanrel = foreignrel;
+			context.buf = buf;
+			context.params_list = params_list;
+
+			deparseExpr((Expr *) rtfunc->funcexpr, &context);
+		}
 
 		/*
 		 * Add a unique alias to avoid any conflict in relation names due to
@@ -1628,8 +1651,6 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
 		 */
 		if (use_alias)
 			appendStringInfo(buf, " %s%d", REL_ALIAS_PREFIX, foreignrel->relid);
-
-		table_close(rel, NoLock);
 	}
 }
 
@@ -2309,29 +2330,40 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte,
 		 * If it's a column of a foreign table, and it has the column_name FDW
 		 * option, use that value.
 		 */
-		options = GetForeignColumnOptions(rte->relid, varattno);
-		foreach(lc, options)
+		if (rte->rtekind == RTE_RELATION)
 		{
-			DefElem    *def = (DefElem *) lfirst(lc);
-
-			if (strcmp(def->defname, "column_name") == 0)
+			options = GetForeignColumnOptions(rte->relid, varattno);
+			foreach(lc, options)
 			{
-				colname = defGetString(def);
-				break;
+				DefElem    *def = (DefElem *) lfirst(lc);
+
+				if (strcmp(def->defname, "column_name") == 0)
+				{
+					colname = defGetString(def);
+					break;
+				}
 			}
-		}
 
-		/*
-		 * If it's a column of a regular table or it doesn't have column_name
-		 * FDW option, use attribute name.
-		 */
-		if (colname == NULL)
-			colname = get_attname(rte->relid, varattno, false);
+			/*
+			 * If it's a column of a regular table or it doesn't have
+			 * column_name FDW option, use attribute name.
+			 */
+			if (colname == NULL)
+				colname = get_attname(rte->relid, varattno, false);
 
-		if (qualify_col)
-			ADD_REL_QUALIFIER(buf, varno);
+			if (qualify_col)
+				ADD_REL_QUALIFIER(buf, varno);
 
-		appendStringInfoString(buf, quote_identifier(colname));
+			appendStringInfoString(buf, quote_identifier(colname));
+		}
+		else if (rte->rtekind == RTE_FUNCTION)
+		{
+			/*
+			 * If it's a column of a function rte, use not column name, but
+			 * RTE alias
+			 */
+			appendStringInfo((buf), "%s%d", REL_ALIAS_PREFIX, (varno));
+		}
 	}
 }
 
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 7df30010f25..da3abd453c2 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10231,3 +10231,126 @@ DROP TABLE result_tbl;
 DROP TABLE join_tbl;
 ALTER SERVER loopback OPTIONS (DROP async_capable);
 ALTER SERVER loopback2 OPTIONS (DROP async_capable);
+-- ===================================================================
+-- test function scan pushdown
+-- ===================================================================
+CREATE TABLE base_tbl (a int, b int);
+CREATE FOREIGN TABLE remote_tbl (a int, b int)
+  SERVER loopback OPTIONS (table_name 'base_tbl');
+ALTER FOREIGN TABLE remote_tbl OPTIONS (use_remote_estimate 'true');
+CREATE TABLE base_tbl1 (c int, d text);
+CREATE FOREIGN TABLE remote_tbl1 (c int, d text)
+  SERVER loopback OPTIONS (table_name 'base_tbl1');
+ALTER FOREIGN TABLE remote_tbl1 OPTIONS (use_remote_estimate 'true');
+INSERT INTO remote_tbl SELECT g, g*2 from  generate_series(1,1000) g;
+INSERT INTO remote_tbl1 SELECT g, 'text'|| g from  generate_series(1,500) g;
+ANALYZE base_tbl;
+ANALYZE base_tbl1;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n;
+                                                         QUERY PLAN                                                          
+-----------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: r.a, r.b, n.n
+   Relations: (public.remote_tbl r) INNER JOIN (FUNCTION RTE n)
+   Remote SQL: SELECT r1.a, r1.b, r2 FROM (public.base_tbl r1 INNER JOIN unnest('{2,3,4}'::integer[]) r2 ON (((r1.a = r2))))
+(4 rows)
+
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n
+ORDER BY r.a;
+ a | b | n 
+---+---+---
+ 2 | 4 | 2
+ 3 | 6 | 3
+ 4 | 8 | 4
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n;
+                                                         QUERY PLAN                                                          
+-----------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: n.n, r.a, r.b
+   Relations: (FUNCTION RTE n) INNER JOIN (public.remote_tbl r)
+   Remote SQL: SELECT r1, r2.a, r2.b FROM (unnest('{2,3,4}'::integer[]) r1 INNER JOIN public.base_tbl r2 ON (((r1 = r2.a))))
+(4 rows)
+
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n
+ORDER BY r.a;
+ n | a | b 
+---+---+---
+ 2 | 2 | 4
+ 3 | 3 | 6
+ 4 | 4 | 8
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a;
+                                                                                         QUERY PLAN                                                                                          
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: r.a, r.b, r1.c, r1.d, n.n
+   Relations: ((public.remote_tbl r) INNER JOIN (public.remote_tbl1 r1)) INNER JOIN (FUNCTION RTE n)
+   Remote SQL: SELECT r1.a, r1.b, r2.c, r2.d, r3 FROM ((public.base_tbl r1 INNER JOIN public.base_tbl1 r2 ON (((r1.a = r2.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 ON (((r1.a = r3))))
+(4 rows)
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = r1.c AND r1.c = n
+ORDER BY r.a;
+ a | b | c |   d   | n 
+---+---+---+-------+---
+ 3 | 6 | 3 | text3 | 3
+ 4 | 8 | 4 | text4 | 4
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT r.*,n from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+                                                                                           QUERY PLAN                                                                                           
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: r.a, r.b, n.n
+   Relations: ((public.remote_tbl r) INNER JOIN (public.remote_tbl1 r1)) INNER JOIN (FUNCTION RTE n)
+   Remote SQL: SELECT r1.a, r1.b, r3 FROM ((public.base_tbl r1 INNER JOIN public.base_tbl1 r2 ON (((r1.a = r2.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 ON (((r1.a = r3)) AND ((r3 > 3))))
+(4 rows)
+
+SELECT * from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+ a | b | c |   d   | n 
+---+---+---+-------+---
+ 4 | 8 | 4 | text4 | 4
+(1 row)
+
+-- Test that local functions are not pushed down
+CREATE OR REPLACE FUNCTION f(INTEGER)
+RETURNS SETOF INTEGER
+LANGUAGE sql AS 'select generate_series(1,$1);' IMMUTABLE;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, f(10) n
+WHERE r.a = n;
+                      QUERY PLAN                      
+------------------------------------------------------
+ Hash Join
+   Output: r.a, r.b, (generate_series(1, 10))
+   Hash Cond: (r.a = (generate_series(1, 10)))
+   ->  Foreign Scan on public.remote_tbl r
+         Output: r.a, r.b
+         Remote SQL: SELECT a, b FROM public.base_tbl
+   ->  Hash
+         Output: (generate_series(1, 10))
+         ->  ProjectSet
+               Output: generate_series(1, 10)
+               ->  Result
+(11 rows)
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a AND n > 3;
+ a | b | c |   d   | n 
+---+---+---+-------+---
+ 4 | 8 | 4 | text4 | 4
+(1 row)
+
+DROP FUNCTION f(INTEGER);
+DROP TABLE base_tbl, base_tbl1;
+DROP FOREIGN TABLE remote_tbl, remote_tbl1;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index c48a421e88b..cf2db2379ed 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -547,6 +547,11 @@ static void merge_fdw_options(PgFdwRelationInfo *fpinfo,
 							  const PgFdwRelationInfo *fpinfo_i);
 static int	get_batch_size_option(Relation rel);
 
+static bool is_nonrel_relinfo_ok(PlannerInfo *root, RelOptInfo *foreignrel);
+static void initialize_nonrel_fpinfo(PlannerInfo *root,
+									 RelOptInfo *baserel,
+									 PgFdwRelationInfo *existing_fpinfo);
+
 
 /*
  * Foreign-data wrapper handler function: return a struct with pointers
@@ -787,6 +792,142 @@ postgresGetForeignRelSize(PlannerInfo *root,
 	fpinfo->relation_index = baserel->relid;
 }
 
+/*
+ * initialize_nonrel_fpinfo
+ *		Initialize fpinfo for non-refering tables queries
+ */
+static void
+initialize_nonrel_fpinfo(PlannerInfo *root,
+						 RelOptInfo *baserel,
+						 PgFdwRelationInfo *existing_fpinfo)
+{
+	PgFdwRelationInfo *fpinfo;
+	ListCell   *lc;
+
+	Assert(existing_fpinfo);
+
+	/*
+	 * We use PgFdwRelationInfo to pass various information to subsequent
+	 * functions.
+	 */
+	fpinfo = (PgFdwRelationInfo *) palloc0(sizeof(PgFdwRelationInfo));
+	baserel->fdw_private = (void *) fpinfo;
+
+	/* Base foreign tables need to be pushed down always. */
+	fpinfo->pushdown_safe = true;
+
+	/* We don't have any table, related to query */
+	fpinfo->table = NULL;
+	fpinfo->server = GetForeignServer(existing_fpinfo->server->serverid);
+
+	/*
+	 * Extract user-settable option values.  Note that per-table settings of
+	 * use_remote_estimate, fetch_size and async_capable override per-server
+	 * settings of them, respectively.
+	 */
+	merge_fdw_options(fpinfo, existing_fpinfo, NULL);
+
+	/*
+	 * If the table or the server is configured to use remote estimates,
+	 * identify which user to do remote access as during planning.  This
+	 * should match what ExecCheckRTEPerms() does.  If we fail due to lack of
+	 * permissions, the query would have failed at runtime anyway.
+	 */
+	fpinfo->user = existing_fpinfo->user;
+
+	/*
+	 * Identify which baserestrictinfo clauses can be sent to the remote
+	 * server and which can't.
+	 */
+	classifyConditions(root, baserel, baserel->baserestrictinfo,
+					   &fpinfo->remote_conds, &fpinfo->local_conds);
+
+	/*
+	 * Identify which attributes will need to be retrieved from the remote
+	 * server.  These include all attrs needed for joins or final output, plus
+	 * all attrs used in the local_conds.  (Note: if we end up using a
+	 * parameterized scan, it's possible that some of the join clauses will be
+	 * sent to the remote and thus we wouldn't really need to retrieve the
+	 * columns used in them.  Doesn't seem worth detecting that case though.)
+	 */
+	fpinfo->attrs_used = NULL;
+	pull_varattnos((Node *) baserel->reltarget->exprs, baserel->relid,
+				   &fpinfo->attrs_used);
+	foreach(lc, fpinfo->local_conds)
+	{
+		RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+
+		pull_varattnos((Node *) rinfo->clause, baserel->relid,
+					   &fpinfo->attrs_used);
+	}
+
+	/*
+	 * Compute the selectivity and cost of the local_conds, so we don't have
+	 * to do it over again for each path.  The best we can do for these
+	 * conditions is to estimate selectivity on the basis of local statistics.
+	 */
+	fpinfo->local_conds_sel = clauselist_selectivity(root,
+													 fpinfo->local_conds,
+													 baserel->relid,
+													 JOIN_INNER,
+													 NULL);
+
+	cost_qual_eval(&fpinfo->local_conds_cost, fpinfo->local_conds, root);
+
+	/*
+	 * Set # of retrieved rows and cached relation costs to some negative
+	 * value, so that we can detect when they are set to some sensible values,
+	 * during one (usually the first) of the calls to estimate_path_cost_size.
+	 */
+	fpinfo->retrieved_rows = -1;
+	fpinfo->rel_startup_cost = -1;
+	fpinfo->rel_total_cost = -1;
+
+	/*
+	 * Don't try to execute anything on remote server for non-relation-based
+	 * query
+	 */
+	fpinfo->use_remote_estimate = false;
+
+	/*
+	 * If the foreign table has never been ANALYZEd, it will have reltuples <
+	 * 0, meaning "unknown".  We can't do much if we're not allowed to consult
+	 * the remote server, but we can use a hack similar to plancat.c's
+	 * treatment of empty relations: use a minimum size estimate of 10 pages,
+	 * and divide by the column-datatype-based width estimate to get the
+	 * corresponding number of tuples.
+	 */
+	if (baserel->tuples < 0)
+	{
+		baserel->pages = 10;
+		baserel->tuples =
+			(10 * BLCKSZ) / (baserel->reltarget->width +
+							 MAXALIGN(SizeofHeapTupleHeader));
+	}
+
+	/* Estimate baserel size as best we can with local statistics. */
+	set_baserel_size_estimates(root, baserel);
+
+	/* Fill in basically-bogus cost estimates for use later. */
+	estimate_path_cost_size(root, baserel, NIL, NIL, NULL,
+							&fpinfo->rows, &fpinfo->width,
+							&fpinfo->startup_cost, &fpinfo->total_cost);
+
+	/*
+	 * fpinfo->relation_name gets the numeric rangetable index of the foreign
+	 * table RTE.  (If this query gets EXPLAIN'd, we'll convert that to a
+	 * human-readable string at that time.)
+	 */
+	fpinfo->relation_name = psprintf("%u", baserel->relid);
+
+	/* No outer and inner relations. */
+	fpinfo->make_outerrel_subquery = false;
+	fpinfo->make_innerrel_subquery = false;
+	fpinfo->lower_subquery_rels = NULL;
+	/* Set the relation index. */
+	fpinfo->relation_index = baserel->relid;
+}
+
 /*
  * get_useful_ecs_for_relation
  *		Determine which EquivalenceClasses might be involved in useful
@@ -1470,15 +1611,25 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
 
 	/*
 	 * Identify which user to do the remote access as.  This should match what
-	 * ExecCheckRTEPerms() does.  In case of a join or aggregate, use the
-	 * lowest-numbered member RTE as a representative; we would get the same
-	 * result from any.
+	 * ExecCheckRTEPerms() does.  In case of a join or aggregate, scan RTEs
+	 * until RTE_RELATION is found. We would get the same result from any.
 	 */
 	if (fsplan->scan.scanrelid > 0)
+	{
 		rtindex = fsplan->scan.scanrelid;
+		rte = exec_rt_fetch(rtindex, estate);
+	}
 	else
-		rtindex = bms_next_member(fsplan->fs_relids, -1);
-	rte = exec_rt_fetch(rtindex, estate);
+	{
+		rtindex = -1;
+		while ((rtindex = bms_next_member(fsplan->fs_relids, rtindex)) >= 0)
+		{
+			rte = exec_rt_fetch(rtindex, estate);
+			if (rte && rte->rtekind == RTE_RELATION)
+				break;
+		}
+		Assert(rte && rte->rtekind == RTE_RELATION);
+	}
 	userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
 
 	/* Get info about foreign table. */
@@ -2795,21 +2946,57 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
 				rti += rtoffset;
 				Assert(bms_is_member(rti, plan->fs_relids));
 				rte = rt_fetch(rti, es->rtable);
-				Assert(rte->rtekind == RTE_RELATION);
 				/* This logic should agree with explain.c's ExplainTargetRel */
-				relname = get_rel_name(rte->relid);
-				if (es->verbose)
+				if (rte->rtekind == RTE_RELATION)
 				{
-					char	   *namespace;
-
-					namespace = get_namespace_name(get_rel_namespace(rte->relid));
-					appendStringInfo(relations, "%s.%s",
-									 quote_identifier(namespace),
-									 quote_identifier(relname));
+					relname = get_rel_name(rte->relid);
+					if (es->verbose)
+					{
+						char	   *namespace;
+
+						namespace = get_namespace_name(get_rel_namespace(rte->relid));
+						appendStringInfo(relations, "%s.%s",
+										 quote_identifier(namespace),
+										 quote_identifier(relname));
+					}
+					else
+						appendStringInfoString(relations,
+											   quote_identifier(relname));
+				}
+				else if (rte->rtekind == RTE_FUNCTION)
+				{
+					appendStringInfoString(relations, "FUNCTION RTE");
+#if 0
+
+					/*
+					 * TODO: rte->functions is always 0, how should we proceed
+					 * here ?
+					 */
+					if (list_length(rte->functions) == 1)
+					{
+						RangeTblFunction *rtfunc = (RangeTblFunction *) linitial(rte->functions);
+
+						if (IsA(rtfunc->funcexpr, FuncExpr))
+						{
+							FuncExpr   *funcexpr = (FuncExpr *) rtfunc->funcexpr;
+							Oid			funcid = funcexpr->funcid;
+
+							relname = get_func_name(funcid);
+							if (es->verbose)
+							{
+								char	   *namespace;
+
+								namespace = get_namespace_name(get_func_namespace(funcid));
+								appendStringInfo(relations, "%s.%s()",
+												 quote_identifier(namespace),
+												 quote_identifier(relname));
+							}
+							else
+								appendStringInfo(relations, "%s()", quote_identifier(relname));
+						}
+					}
+#endif
 				}
-				else
-					appendStringInfoString(relations,
-										   quote_identifier(relname));
 				refname = (char *) list_nth(es->rtable_names, rti - 1);
 				if (refname == NULL)
 					refname = rte->eref->aliasname;
@@ -5405,6 +5592,27 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
 	return commands;
 }
 
+/*
+ * Determine if foreignrel, not backed by foreign
+ * table, is fine to push down.
+ */
+static bool
+is_nonrel_relinfo_ok(PlannerInfo *root, RelOptInfo *foreignrel)
+{
+	RangeTblEntry *rte;
+	RangeTblFunction *rtfunc;
+
+	rte = planner_rt_fetch(foreignrel->relid, root);
+
+	/* For now only this RTE type can come without fpinfo */
+	Assert(rte && rte->rtekind == RTE_FUNCTION);
+	Assert(list_length(rte->functions) == 1);
+
+	rtfunc = (RangeTblFunction *) linitial(rte->functions);
+
+	return is_foreign_expr(root, foreignrel, (Expr *) rtfunc->funcexpr);
+}
+
 /*
  * Assess whether the join between inner and outer relations can be pushed down
  * to the foreign server. As a side effect, save information we obtain in this
@@ -5430,13 +5638,37 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
 		jointype != JOIN_RIGHT && jointype != JOIN_FULL)
 		return false;
 
+	fpinfo = (PgFdwRelationInfo *) joinrel->fdw_private;
+	fpinfo_o = (PgFdwRelationInfo *) outerrel->fdw_private;
+	fpinfo_i = (PgFdwRelationInfo *) innerrel->fdw_private;
+
+	if (!fpinfo_o && !fpinfo_i)
+		return false;
+
+	if (!fpinfo_o)
+	{
+
+		initialize_nonrel_fpinfo(root, outerrel, fpinfo_i);
+
+		if (!is_nonrel_relinfo_ok(root, outerrel))
+			return false;
+
+		fpinfo_o = (PgFdwRelationInfo *) outerrel->fdw_private;
+	}
+	if (!fpinfo_i)
+	{
+		initialize_nonrel_fpinfo(root, innerrel, fpinfo_o);
+
+		if (!is_nonrel_relinfo_ok(root, innerrel))
+			return false;
+
+		fpinfo_i = (PgFdwRelationInfo *) innerrel->fdw_private;
+	}
+
 	/*
 	 * If either of the joining relations is marked as unsafe to pushdown, the
 	 * join can not be pushed down.
 	 */
-	fpinfo = (PgFdwRelationInfo *) joinrel->fdw_private;
-	fpinfo_o = (PgFdwRelationInfo *) outerrel->fdw_private;
-	fpinfo_i = (PgFdwRelationInfo *) innerrel->fdw_private;
 	if (!fpinfo_o || !fpinfo_o->pushdown_safe ||
 		!fpinfo_i || !fpinfo_i->pushdown_safe)
 		return false;
@@ -5578,6 +5810,7 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
 		case JOIN_RIGHT:
 			fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
 											  fpinfo_o->remote_conds);
+
 			fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
 											   fpinfo_i->remote_conds);
 			break;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 78379bdea5b..da25c535dd5 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3262,3 +3262,63 @@ DROP TABLE join_tbl;
 
 ALTER SERVER loopback OPTIONS (DROP async_capable);
 ALTER SERVER loopback2 OPTIONS (DROP async_capable);
+
+-- ===================================================================
+-- test function scan pushdown
+-- ===================================================================
+CREATE TABLE base_tbl (a int, b int);
+CREATE FOREIGN TABLE remote_tbl (a int, b int)
+  SERVER loopback OPTIONS (table_name 'base_tbl');
+ALTER FOREIGN TABLE remote_tbl OPTIONS (use_remote_estimate 'true');
+CREATE TABLE base_tbl1 (c int, d text);
+CREATE FOREIGN TABLE remote_tbl1 (c int, d text)
+  SERVER loopback OPTIONS (table_name 'base_tbl1');
+ALTER FOREIGN TABLE remote_tbl1 OPTIONS (use_remote_estimate 'true');
+
+INSERT INTO remote_tbl SELECT g, g*2 from  generate_series(1,1000) g;
+INSERT INTO remote_tbl1 SELECT g, 'text'|| g from  generate_series(1,500) g;
+ANALYZE base_tbl;
+ANALYZE base_tbl1;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n;
+
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n
+ORDER BY r.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n;
+
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n
+ORDER BY r.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a;
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = r1.c AND r1.c = n
+ORDER BY r.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT r.*,n from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+
+SELECT * from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+
+-- Test that local functions are not pushed down
+CREATE OR REPLACE FUNCTION f(INTEGER)
+RETURNS SETOF INTEGER
+LANGUAGE sql AS 'select generate_series(1,$1);' IMMUTABLE;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, f(10) n
+WHERE r.a = n;
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a AND n > 3;
+
+DROP FUNCTION f(INTEGER);
+DROP TABLE base_tbl, base_tbl1;
+DROP FOREIGN TABLE remote_tbl, remote_tbl1;
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index e105a4d5f1d..80f29482ef8 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -22,12 +22,14 @@
 #include "optimizer/clauses.h"
 #include "optimizer/cost.h"
 #include "optimizer/inherit.h"
+#include "optimizer/optimizer.h"
 #include "optimizer/pathnode.h"
 #include "optimizer/paths.h"
 #include "optimizer/placeholder.h"
 #include "optimizer/plancat.h"
 #include "optimizer/restrictinfo.h"
 #include "optimizer/tlist.h"
+#include "parser/parsetree.h"
 #include "utils/hsearch.h"
 #include "utils/lsyscache.h"
 
@@ -53,7 +55,8 @@ static List *subbuild_joinrel_restrictlist(RelOptInfo *joinrel,
 static List *subbuild_joinrel_joinlist(RelOptInfo *joinrel,
 									   List *joininfo_list,
 									   List *new_joininfo);
-static void set_foreign_rel_properties(RelOptInfo *joinrel,
+static void set_foreign_rel_properties(PlannerInfo *root,
+									   RelOptInfo *joinrel,
 									   RelOptInfo *outer_rel, RelOptInfo *inner_rel);
 static void add_join_rel(PlannerInfo *root, RelOptInfo *joinrel);
 static void build_joinrel_partition_info(RelOptInfo *joinrel,
@@ -498,7 +501,7 @@ find_join_rel(PlannerInfo *root, Relids relids)
  *
  */
 static void
-set_foreign_rel_properties(RelOptInfo *joinrel, RelOptInfo *outer_rel,
+set_foreign_rel_properties(PlannerInfo *root, RelOptInfo *joinrel, RelOptInfo *outer_rel,
 						   RelOptInfo *inner_rel)
 {
 	if (OidIsValid(outer_rel->serverid) &&
@@ -528,6 +531,53 @@ set_foreign_rel_properties(RelOptInfo *joinrel, RelOptInfo *outer_rel,
 			joinrel->fdwroutine = outer_rel->fdwroutine;
 		}
 	}
+	else if ((OidIsValid(outer_rel->serverid) &&
+			  inner_rel->serverid == InvalidOid) ||
+			 (OidIsValid(inner_rel->serverid) &&
+			  outer_rel->serverid == InvalidOid))
+	{
+		RelOptInfo *foreign_rel;
+		RelOptInfo *local_rel;
+		RangeTblEntry *rte;
+
+		foreign_rel = OidIsValid(outer_rel->serverid) ? outer_rel : inner_rel;
+		local_rel = OidIsValid(outer_rel->serverid) ? inner_rel : outer_rel;
+		rte = planner_rt_fetch(local_rel->relid, root);
+
+		if (!rte)
+			return;
+
+		switch (rte->rtekind)
+		{
+			case RTE_FUNCTION:
+				{
+					ListCell   *lc;
+
+					/* For now support only one function */
+					if (list_length(rte->functions) > 1)
+						return;
+
+					foreach(lc, rte->functions)
+					{
+						RangeTblFunction *rtfunc = (RangeTblFunction *) lfirst(lc);
+
+						if (contain_var_clause(rtfunc->funcexpr) ||
+							contain_mutable_functions(rtfunc->funcexpr) ||
+							contain_subplans(rtfunc->funcexpr))
+							return;
+					}
+					break;
+				}
+			default:
+				/* Avoid pushing unsupported RelOptInfo */
+				return;
+		}
+
+		joinrel->serverid = foreign_rel->serverid;
+		joinrel->userid = foreign_rel->userid;
+		joinrel->useridiscurrent = foreign_rel->useridiscurrent;
+		joinrel->fdwroutine = foreign_rel->fdwroutine;
+	}
 }
 
 /*
@@ -674,7 +724,7 @@ build_join_rel(PlannerInfo *root,
 	joinrel->nullable_partexprs = NULL;
 
 	/* Compute information relevant to the foreign relations. */
-	set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
+	set_foreign_rel_properties(root, joinrel, outer_rel, inner_rel);
 
 	/*
 	 * Create a new tlist containing just the vars that need to be output from
@@ -855,7 +905,7 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
 										   inner_rel->top_parent_relids);
 
 	/* Compute information relevant to foreign relations. */
-	set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
+	set_foreign_rel_properties(root, joinrel, outer_rel, inner_rel);
 
 	/* Compute information needed for mapping Vars to the child rel */
 	appinfos = find_appinfos_by_relids(root, joinrel->relids, &nappinfos);
-- 
2.25.1

---

From: Ashutosh Bapat <[email-masked]>
Date: Tue, 15 Jun 2021 18:45:24 +0530

Hi Alexander,

> select * from f_pgbench_accounts join unnest(array[1,2,3]) ON unnest =

It will be good to provide some practical examples where this is useful.

> queryDesc->plannedstmt->rtable, and rte->functions is already 0.

The actual function expression will be part of the Remote SQL of
ForeignScan node so no need to visualize it separately.

The patch will have problems when there are multiple foreign tables
all on different servers or use different FDWs. In such a case the
function scan's RelOptInfo will get the fpinfo based on the first
foreign table the function scan is paired with during join planning.
But that may not be the best foreign table to join. We should be able
to plan all the possible joins. Current infra to add one fpinfo per
RelOptInfo won't help there. We need something better.

The patch targets only postgres FDW, how do you see this working with
other FDWs?

If we come up with the right approach we could use it for 1. pushing
down queries with IN () clause 2. joining a small local table with a
large foreign table by sending the local table rows down to the
foreign server.

-- 
Best Wishes,
Ashutosh Bapat

---

From: Alexander Pyhalov <[email-masked]>
Date: Mon, 04 Oct 2021 10:42:56 +0300

Hi.

The current version of the patch is based on asymetric partition-wise 
join.
Currently it is applied after 
v19-0001-Asymmetric-partitionwise-join.patch from
on 
https://www.postgresql.org/message-id/[email-masked] 
.

> ForeignScan node so no need to visualize it separately.

We still need to create tuple description for functions in 
get_tupdesc_for_join_scan_tuples(),
so I had to remove setting newrte->functions to NIL in 
add_rte_to_flat_rtable().
With rte->functions in place, there's no issues for explain.

> RelOptInfo won't help there. We need something better.

I suppose attached version of the patch is more mature.

> The patch targets only postgres FDW, how do you see this working with

Not now. We introduce necessary APIs for other FDWs, but implementing 
TryShippableJoinPaths()
doesn't seem straightforward.

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

From d997c313daf0031b812d3fca59d338be1a4f2196 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <[email-masked]>
Date: Mon, 17 May 2021 19:19:31 +0300
Subject: [PATCH] Push join with function scan to remote server

---
 contrib/postgres_fdw/deparse.c                |  199 ++-
 .../postgres_fdw/expected/postgres_fdw.out    | 1095 +++++++++++++++++
 contrib/postgres_fdw/postgres_fdw.c           |  497 +++++++-
 contrib/postgres_fdw/postgres_fdw.h           |    6 +
 contrib/postgres_fdw/sql/postgres_fdw.sql     |  336 +++++
 src/backend/optimizer/path/joinpath.c         |   11 +
 src/backend/optimizer/plan/setrefs.c          |    1 -
 src/backend/optimizer/util/relnode.c          |    2 +
 src/include/foreign/fdwapi.h                  |    1 +
 9 files changed, 2035 insertions(+), 113 deletions(-)

diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index d98bd666818..7f08575ef60 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -151,6 +151,7 @@ static void deparseConst(Const *node, deparse_expr_cxt *context, int showtype);
 static void deparseParam(Param *node, deparse_expr_cxt *context);
 static void deparseSubscriptingRef(SubscriptingRef *node, deparse_expr_cxt *context);
 static void deparseFuncExpr(FuncExpr *node, deparse_expr_cxt *context);
+static void deparseFuncColnames(StringInfo buf, int varno, RangeTblEntry *rte, bool qualify_col);
 static void deparseOpExpr(OpExpr *node, deparse_expr_cxt *context);
 static void deparseOperatorName(StringInfo buf, Form_pg_operator opform);
 static void deparseDistinctExpr(DistinctExpr *node, deparse_expr_cxt *context);
@@ -1740,13 +1741,54 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
 	{
 		RangeTblEntry *rte = planner_rt_fetch(foreignrel->relid, root);
 
-		/*
 
-		deparseRelation(buf, rel);
+			deparseRelation(buf, rel);
+
+			table_close(rel, NoLock);
+		}
+		else if (rte->rtekind == RTE_FUNCTION)
+		{
+			RangeTblFunction *rtfunc;
+			deparse_expr_cxt context;
+			ListCell   *lc;
+			bool		first = true;
+			int			n;
+
+			n = list_length(rte->functions);
+			Assert(n >= 1);
+
+			if (n > 1)
+				appendStringInfoString(buf, "ROWS FROM (");
+
+			foreach(lc, rte->functions)
+			{
+				if (!first)
+					appendStringInfoString(buf, ", ");
+				else
+					first = false;
+
+				rtfunc = (RangeTblFunction *) lfirst(lc);
+
+				context.root = root;
+				context.foreignrel = foreignrel;
+				context.scanrel = foreignrel;
+				context.buf = buf;
+				context.params_list = params_list;
+
+				deparseExpr((Expr *) rtfunc->funcexpr, &context);
+			}
+
+			if (n > 1)
+				appendStringInfoString(buf, ")");
+		}
 
 		/*
 		 * Add a unique alias to avoid any conflict in relation names due to
@@ -1754,9 +1796,43 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
 		 * join.
 		 */
 		if (use_alias)
+		{
 			appendStringInfo(buf, " %s%d", REL_ALIAS_PREFIX, foreignrel->relid);
+			if (rte->rtekind == RTE_FUNCTION)
+			{
+				appendStringInfo(buf, " (");
+				deparseFuncColnames(buf, 0, rte, false);
+				appendStringInfo(buf, ") ");
+			}
+		}
+	}
+}
 
-		table_close(rel, NoLock);
+/*
+ * Deparse function columns alias list
+ */
+static void
+deparseFuncColnames(StringInfo buf, int varno, RangeTblEntry *rte, bool qualify_col)
+{
+	bool		first = true;
+	ListCell   *lc;
+
+	Assert(rte);
+	Assert(rte->rtekind == RTE_FUNCTION);
+	Assert(rte->eref);
+
+	foreach(lc, rte->eref->colnames)
+	{
+		char	   *colname = strVal(lfirst(lc));
+
+		if (colname[0] == '\0')
+			continue;
+		if (!first)
+			appendStringInfoString(buf, ",");
+		if (qualify_col)
+			ADD_REL_QUALIFIER(buf, varno);
+		appendStringInfoString(buf, quote_identifier(colname));
+		first = false;
 	}
 }
 
@@ -2057,7 +2133,7 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
 
 	appendStringInfoString(buf, "UPDATE ");
 	deparseRelation(buf, rel);
-	if (foreignrel->reloptkind == RELOPT_JOINREL)
+	if (IS_JOIN_REL(foreignrel))
 		appendStringInfo(buf, " %s%d", REL_ALIAS_PREFIX, rtindex);
 	appendStringInfoString(buf, " SET ");
 
@@ -2084,7 +2160,7 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
 
 	reset_transmission_modes(nestlevel);
 
-	if (foreignrel->reloptkind == RELOPT_JOINREL)
+	if (IS_JOIN_REL(foreignrel))
 	{
 		List	   *ignore_conds = NIL;
 
@@ -2100,7 +2176,7 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
 		appendConditions(remote_conds, &context);
 	}
 
-	if (foreignrel->reloptkind == RELOPT_JOINREL)
+	if (IS_JOIN_REL(foreignrel))
 		deparseExplicitTargetList(returningList, true, retrieved_attrs,
 								  &context);
 	else
@@ -2164,10 +2240,10 @@ deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
 
 	appendStringInfoString(buf, "DELETE FROM ");
 	deparseRelation(buf, rel);
-	if (foreignrel->reloptkind == RELOPT_JOINREL)
+	if (IS_JOIN_REL(foreignrel))
 		appendStringInfo(buf, " %s%d", REL_ALIAS_PREFIX, rtindex);
 
-	if (foreignrel->reloptkind == RELOPT_JOINREL)
+	if (IS_JOIN_REL(foreignrel))
 	{
 		List	   *ignore_conds = NIL;
 
@@ -2183,7 +2259,7 @@ deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
 		appendConditions(remote_conds, &context);
 	}
 
-	if (foreignrel->reloptkind == RELOPT_JOINREL)
+	if (IS_JOIN_REL(foreignrel))
 		deparseExplicitTargetList(returningList, true, retrieved_attrs,
 								  &context);
 	else
@@ -2407,23 +2483,6 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte,
 		/* Required only to be passed down to deparseTargetList(). */
 		List	   *retrieved_attrs;
 
-		/*
-		 * The lock on the relation will be held by upper callers, so it's
-		 * fine to open it with no lock here.
-		 */
-		rel = table_open(rte->relid, NoLock);
-
-		/*
-		 * The local name of the foreign table can not be recognized by the
-		 * foreign server and the table it references on foreign server might
-		 * have different column ordering or different columns than those
-		 * declared locally. Hence we have to deparse whole-row reference as
-		 * ROW(columns referenced locally). Construct this by deparsing a
-		 * "whole row" attribute.
-		 */
-		attrs_used = bms_add_member(NULL,
-									0 - FirstLowInvalidHeapAttributeNumber);
-
 		/*
 		 * In case the whole-row reference is under an outer join then it has
 		 * to go NULL whenever the rest of the row goes NULL. Deparsing a join
@@ -2438,16 +2497,43 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte,
 		}
 
 		appendStringInfoString(buf, "ROW(");
-		deparseTargetList(buf, rte, varno, rel, false, attrs_used, qualify_col,
-						  &retrieved_attrs);
+		if (rte->rtekind == RTE_RELATION)
+		{
+			/*
+			 * The local name of the foreign table can not be recognized by
+			 * the foreign server and the table it references on foreign
+			 * server might have different column ordering or different
+			 * columns than those declared locally. Hence we have to deparse
+			 * whole-row reference as ROW(columns referenced locally).
+			 * Construct this by deparsing a "whole row" attribute.
+			 */
+			attrs_used = bms_add_member(NULL,
+										0 - FirstLowInvalidHeapAttributeNumber);
+
+			/*
+			 * The lock on the relation will be held by upper callers, so it's
+			 * fine to open it with no lock here.
+			 */
+			rel = table_open(rte->relid, NoLock);
+			deparseTargetList(buf, rte, varno, rel, false, attrs_used, qualify_col,
+							  &retrieved_attrs);
+			table_close(rel, NoLock);
+			bms_free(attrs_used);
+		}
+		else if (rte->rtekind == RTE_FUNCTION)
+		{
+			/*
+			 * Function call is translated as-is, function returns the same
+			 * columns in the same order as on local server
+			 */
+			deparseFuncColnames(buf, varno, rte, qualify_col);
+		}
 		appendStringInfoChar(buf, ')');
 
 		/* Complete the CASE WHEN statement started above. */
 		if (qualify_col)
 			appendStringInfoString(buf, " END");
 
-		bms_free(attrs_used);
@@ -2462,29 +2548,40 @@ deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte,
 
-		/*
 
-		if (qualify_col)
 
+			colname = get_rte_attribute_name(rte, varattno);
 
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index c7b7db80650..bc896914fd1 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10761,3 +10761,1098 @@ ERROR:  invalid value for integer option "fetch_size": 100$%$#$#
 CREATE FOREIGN TABLE inv_bsz (c1 int )
 	SERVER loopback OPTIONS (batch_size '100$%$#$#');
 ERROR:  invalid value for integer option "batch_size": 100$%$#$#
+-- ===================================================================
+-- test function scan pushdown
+-- ===================================================================
+CREATE TABLE base_tbl (a int, b int);
+CREATE FOREIGN TABLE remote_tbl (a int, b int)
+  SERVER loopback OPTIONS (table_name 'base_tbl');
+ALTER FOREIGN TABLE remote_tbl OPTIONS (use_remote_estimate 'true');
+CREATE TABLE base_tbl1 (c int, d text);
+CREATE FOREIGN TABLE remote_tbl1 (c int, d text)
+  SERVER loopback OPTIONS (table_name 'base_tbl1');
+ALTER FOREIGN TABLE remote_tbl1 OPTIONS (use_remote_estimate 'true');
+INSERT INTO remote_tbl SELECT g, g*2 from  generate_series(1,1000) g;
+INSERT INTO remote_tbl1 SELECT g, 'text'|| g from  generate_series(1,500) g;
+ANALYZE base_tbl;
+ANALYZE base_tbl1;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n;
+                                                              QUERY PLAN                                                              
+--------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: r.a, r.b, n.n
+   Relations: (public.remote_tbl r) INNER JOIN (pg_catalog.unnest() n)
+   Remote SQL: SELECT r1.a, r1.b, r2.n FROM (public.base_tbl r1 INNER JOIN unnest('{2,3,4}'::integer[]) r2 (n)  ON (((r1.a = r2.n))))
+(4 rows)
+
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n
+ORDER BY r.a;
+ a | b | n 
+---+---+---
+ 2 | 4 | 2
+ 3 | 6 | 3
+ 4 | 8 | 4
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n;
+                                                              QUERY PLAN                                                              
+--------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: n.n, r.a, r.b
+   Relations: (public.remote_tbl r) INNER JOIN (pg_catalog.unnest() n)
+   Remote SQL: SELECT r1.n, r2.a, r2.b FROM (public.base_tbl r2 INNER JOIN unnest('{2,3,4}'::integer[]) r1 (n)  ON (((r1.n = r2.a))))
+(4 rows)
+
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n
+ORDER BY r.a;
+ n | a | b 
+---+---+---
+ 2 | 2 | 4
+ 3 | 3 | 6
+ 4 | 4 | 8
+(3 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a;
+                                                                                              QUERY PLAN                                                                                              
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: r.a, r.b, r1.c, r1.d, n.n
+   Relations: ((public.remote_tbl r) INNER JOIN (public.remote_tbl1 r1)) INNER JOIN (pg_catalog.unnest() n)
+   Remote SQL: SELECT r1.a, r1.b, r2.c, r2.d, r3.n FROM ((public.base_tbl r1 INNER JOIN public.base_tbl1 r2 ON (((r1.a = r2.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 (n)  ON (((r1.a = r3.n))))
+(4 rows)
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a
+ORDER BY r.a;
+ a | b | c |   d   | n 
+---+---+---+-------+---
+ 3 | 6 | 3 | text3 | 3
+ 4 | 8 | 4 | text4 | 4
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT r.*,n from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+                                                                                                QUERY PLAN                                                                                                 
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: r.a, r.b, n.n
+   Relations: ((public.remote_tbl r) INNER JOIN (public.remote_tbl1 r1)) INNER JOIN (pg_catalog.unnest() n)
+   Remote SQL: SELECT r1.a, r1.b, r3.n FROM ((public.base_tbl r1 INNER JOIN public.base_tbl1 r2 ON (((r1.a = r2.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 (n)  ON (((r1.a = r3.n)) AND ((r3.n > 3))))
+(4 rows)
+
+SELECT * from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+ a | b | c |   d   | n 
+---+---+---+-------+---
+ 4 | 8 | 4 | text4 | 4
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT r.*, t.n from remote_tbl1 r, ROWS FROM (unnest(array[3,4]), json_each_text('{"a":"text1", "c":"text4"}')) t (n, k, txt)
+WHERE r.c =  t.n AND r.d = t.txt;
+                                                                                                           QUERY PLAN                                                                                                            
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: r.c, r.d, t.n
+   Relations: (public.remote_tbl1 r) INNER JOIN (ROWS FROM(pg_catalog.unnest(), pg_catalog.json_each_text()) t)
+   Remote SQL: SELECT r1.c, r1.d, r2.n FROM (public.base_tbl1 r1 INNER JOIN ROWS FROM (unnest('{3,4}'::integer[]), json_each_text('{"a":"text1", "c":"text4"}'::json)) r2 (n,k,txt)  ON (((r1.c = r2.n)) AND ((r1.d = r2.txt))))
+(4 rows)
+
+SELECT r.*, t.txt from remote_tbl1 r, ROWS FROM (unnest(array[3,4]), json_each_text('{"a":"text1", "c":"text4"}')) t (n, k, txt)
+WHERE r.c =  t.n AND r.d = t.txt;
+ c |   d   |  txt  
+---+-------+-------
+ 4 | text4 | text4
+(1 row)
+
+-- complex types
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r JOIN UNNEST(array[box '((2,3),(-2,-3))']) as t(bx) ON a = area(bx);
+                                                                    QUERY PLAN                                                                     
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: r.a, r.b, t.bx
+   Relations: (public.remote_tbl r) INNER JOIN (pg_catalog.unnest() t)
+   Remote SQL: SELECT r1.a, r1.b, r2.bx FROM (public.base_tbl r1 INNER JOIN unnest('{(2,3),(-2,-3)}'::box[]) r2 (bx)  ON (((r1.a = area(r2.bx)))))
+(4 rows)
+
+SELECT * FROM remote_tbl r JOIN UNNEST(array[box '((2,3),(-2,-3))']) as t(bx) ON a = area(bx)
+ORDER BY r.a;
+ a  | b  |      bx       
+----+----+---------------
+ 24 | 48 | (2,3),(-2,-3)
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl1 r1 JOIN json_each_text('{"a":"text1", "b":2, "c":"text14"}') ON d = value;
+                                                                                         QUERY PLAN                                                                                          
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: r1.c, r1.d, json_each_text.key, json_each_text.value
+   Relations: (public.remote_tbl1 r1) INNER JOIN (pg_catalog.json_each_text())
+   Remote SQL: SELECT r1.c, r1.d, r2.key, r2.value FROM (public.base_tbl1 r1 INNER JOIN json_each_text('{"a":"text1", "b":2, "c":"text14"}'::json) r2 (key,value)  ON (((r1.d = r2.value))))
+(4 rows)
+
+SELECT * FROM remote_tbl1 r1 JOIN json_each_text('{"a":"text1", "b":2, "c":"text14"}') ON d = value
+ORDER BY r1.c;
+ c  |   d    | key | value  
+----+--------+-----+--------
+  1 | text1  | a   | text1
+ 14 | text14 | c   | text14
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl1 r1 JOIN json_each_text('{"a":"text1", "b":2, "c":"text14"}') AS t(u,v) ON d = v;
+                                                                                 QUERY PLAN                                                                                  
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+   Output: r1.c, r1.d, t.u, t.v
+   Relations: (public.remote_tbl1 r1) INNER JOIN (pg_catalog.json_each_text() t)
+   Remote SQL: SELECT r1.c, r1.d, r2.u, r2.v FROM (public.base_tbl1 r1 INNER JOIN json_each_text('{"a":"text1", "b":2, "c":"text14"}'::json) r2 (u,v)  ON (((r1.d = r2.v))))
+(4 rows)
+
+SELECT * FROM remote_tbl1 r1 JOIN json_each_text('{"a":"text1", "b":2, "c":"text14"}') AS t(u,v) ON d = v
+ORDER BY r1.c;
+ c  |   d    | u |   v    
+----+--------+---+--------
+  1 | text1  | a | text1
+ 14 | text14 | c | text14
+(2 rows)
+
+-- DML
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+                                                                        QUERY PLAN                                                                        
+----------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.remote_tbl r
+   Output: r.a, r.b
+   ->  Foreign Update
+         Remote SQL: UPDATE public.base_tbl r1 SET b = 5 FROM unnest('{(2,3),(-2,-3)}'::box[]) r2 (bx)  WHERE ((r1.a = area(r2.bx))) RETURNING r1.a, r1.b
+(4 rows)
+
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+ a  | b 
+----+---
+ 24 | 5
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+                                                                                                                                     QUERY PLAN                                                                                                                                      
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.remote_tbl r
+   Output: r.a, r.b
+   Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
+   ->  Foreign Scan
+         Output: CASE WHEN (random() >= '0'::double precision) THEN 5 ELSE 0 END, r.ctid, r.*, t.*
+         Relations: (public.remote_tbl r) INNER JOIN (pg_catalog.unnest() t)
+         Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1.a, r1.b) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.bx) END FROM (public.base_tbl r1 INNER JOIN unnest('{(2,3),(-2,-3)}'::box[]) r2 (bx)  ON (((r1.a = area(r2.bx))))) FOR UPDATE OF r1
+         ->  Hash Join
+               Output: r.ctid, r.*, t.*
+               Hash Cond: ((r.a)::double precision = area(t.bx))
+               ->  Foreign Scan on public.remote_tbl r
+                     Output: r.ctid, r.*, r.a
+                     Remote SQL: SELECT a, b, ctid FROM public.base_tbl FOR UPDATE
+               ->  Hash
+                     Output: t.*, t.bx
+                     ->  Function Scan on pg_catalog.unnest t
+                           Output: t.*, t.bx
+                           Function Call: unnest('{(2,3),(-2,-3)}'::box[])
+(18 rows)
+
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+ a  | b 
+----+---
+ 24 | 5
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+                                                                                                                QUERY PLAN                                                                                                                
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.remote_tbl r
+   Output: r.a, r.b
+   ->  Foreign Update
+         Remote SQL: UPDATE public.base_tbl r1 SET b = 5 FROM ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx)  WHERE ((r1.a >= r2.l)) AND ((r1.a <= area(r2.bx))) RETURNING r1.a, r1.b
+(4 rows)
+
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+ a  | b 
+----+---
+ 10 | 5
+ 11 | 5
+ 12 | 5
+ 13 | 5
+ 14 | 5
+ 15 | 5
+ 16 | 5
+ 17 | 5
+ 18 | 5
+ 19 | 5
+ 20 | 5
+ 21 | 5
+ 22 | 5
+ 23 | 5
+ 25 | 5
+ 26 | 5
+ 27 | 5
+ 28 | 5
+ 24 | 5
+(19 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+                                                                                                                                                                                QUERY PLAN                                                                                                                                                                                
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.remote_tbl r
+   Output: r.a, r.b
+   Remote SQL: UPDATE public.base_tbl SET b = $2 WHERE ctid = $1 RETURNING a, b
+   ->  Foreign Scan
+         Output: CASE WHEN (random() >= '0'::double precision) THEN 5 ELSE 0 END, r.ctid, r.*, t.*
+         Relations: (public.remote_tbl r) INNER JOIN (ROWS FROM(pg_catalog.unnest(), pg_catalog.unnest()) t)
+         Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1.a, r1.b) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.l,r2.bx) END FROM (public.base_tbl r1 INNER JOIN ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx)  ON (((r1.a >= r2.l)) AND ((r1.a <= area(r2.bx))))) FOR UPDATE OF r1
+         ->  Nested Loop
+               Output: r.ctid, r.*, t.*
+               Join Filter: ((r.a >= t.l) AND ((r.a)::double precision <= area(t.bx)))
+               ->  Foreign Scan on public.remote_tbl r
+                     Output: r.ctid, r.*, r.a
+                     Remote SQL: SELECT a, b, ctid FROM public.base_tbl FOR UPDATE
+               ->  Function Scan on t
+                     Output: t.*, t.l, t.bx
+                     Function Call: unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])
+(16 rows)
+
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+ a  | b 
+----+---
+ 10 | 5
+ 11 | 5
+ 12 | 5
+ 13 | 5
+ 14 | 5
+ 15 | 5
+ 16 | 5
+ 17 | 5
+ 18 | 5
+ 19 | 5
+ 20 | 5
+ 21 | 5
+ 22 | 5
+ 23 | 5
+ 25 | 5
+ 26 | 5
+ 27 | 5
+ 28 | 5
+ 24 | 5
+(19 rows)
+
+-- Test that local functions are not pushed down
+CREATE OR REPLACE FUNCTION f(INTEGER)
+RETURNS SETOF INTEGER
+LANGUAGE sql AS 'select generate_series(1,$1);' IMMUTABLE;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, f(10) n
+WHERE r.a = n;
+                      QUERY PLAN                      
+------------------------------------------------------
+ Hash Join
+   Output: r.a, r.b, (generate_series(1, 10))
+   Hash Cond: (r.a = (generate_series(1, 10)))
+   ->  Foreign Scan on public.remote_tbl r
+         Output: r.a, r.b
+         Remote SQL: SELECT a, b FROM public.base_tbl
+   ->  Hash
+         Output: (generate_series(1, 10))
+         ->  ProjectSet
+               Output: generate_series(1, 10)
+               ->  Result
+(11 rows)
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a AND n > 3;
+ a | b | c |   d   | n 
+---+---+---+-------+---
+ 4 | 8 | 4 | text4 | 4
+(1 row)
+
+-- Test joins with append relations
+SET enable_partitionwise_join=on;
+-- Partitioned tables and function scan pushdown
+CREATE TABLE distr1(a int, b int) PARTITION BY HASH(a);
+CREATE TABLE distr1_part_1 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 4, REMAINDER 0);
+CREATE TABLE distr1_base_2 (a int, b int);
+CREATE FOREIGN TABLE distr1_part_2 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 4, REMAINDER 1)
+	SERVER loopback OPTIONS (table_name 'distr1_base_2', use_remote_estimate 'true');
+CREATE TABLE distr1_base_3 (a int, b int);
+CREATE FOREIGN TABLE distr1_part_3 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 4, REMAINDER 2)
+	SERVER loopback2 OPTIONS (table_name 'distr1_base_3', use_remote_estimate 'true');
+CREATE TABLE distr1_base_4 (a int, b int);
+CREATE FOREIGN TABLE distr1_part_4 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 4, REMAINDER 3)
+	SERVER loopback OPTIONS (table_name 'distr1_base_4', use_remote_estimate 'true');
+CREATE TABLE distr2(c int, d text) PARTITION BY HASH(c);
+CREATE TABLE distr2_part_1 PARTITION OF distr2 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
+CREATE TABLE distr2_base_2 (c int, d text);
+CREATE FOREIGN TABLE distr2_part_2 PARTITION OF distr2 FOR VALUES WITH ( MODULUS 4, REMAINDER 1)
+	SERVER loopback OPTIONS (table_name 'distr2_base_2', use_remote_estimate 'true');
+CREATE TABLE distr2_base_3 (c int, d text);
+CREATE FOREIGN TABLE distr2_part_3 PARTITION OF distr2 FOR VALUES WITH ( MODULUS 4, REMAINDER 2)
+	SERVER loopback2 OPTIONS (table_name 'distr2_base_3', use_remote_estimate 'true');
+CREATE TABLE distr2_base_4 (c int, d text);
+CREATE FOREIGN TABLE distr2_part_4 PARTITION OF distr2 FOR VALUES WITH ( MODULUS 4, REMAINDER 3)
+	SERVER loopback OPTIONS (table_name 'distr2_base_4', use_remote_estimate 'true');
+INSERT INTO distr1 SELECT g, g*2 from  generate_series(1,1000) g;
+INSERT INTO distr2 SELECT g, 'text'|| g from  generate_series(1,500) g;
+ANALYZE distr1;
+ANALYZE distr1_part_1;
+ANALYZE distr1_base_2;
+ANALYZE distr1_base_3;
+ANALYZE distr1_base_4;
+ANALYZE distr2;
+ANALYZE distr2_base_2;
+ANALYZE distr2_base_3;
+ANALYZE distr2_base_4;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM distr1 d, unnest(array[2,3,4,5]) n WHERE d.a = n;
+                                                                    QUERY PLAN                                                                     
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ Append
+   ->  Hash Join
+         Output: d_1.a, d_1.b, n.n
+         Hash Cond: (d_1.a = n.n)
+         ->  Seq Scan on public.distr1_part_1 d_1
+               Output: d_1.a, d_1.b
+         ->  Hash
+               Output: n.n
+               ->  Function Scan on pg_catalog.unnest n
+                     Output: n.n
+                     Function Call: unnest('{2,3,4,5}'::integer[])
+   ->  Foreign Scan
+         Output: d_2.a, d_2.b, n.n
+         Relations: (public.distr1_part_2 d_2) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r4.a, r4.b, r2.n FROM (public.distr1_base_2 r4 INNER JOIN unnest('{2,3,4,5}'::integer[]) r2 (n)  ON (((r4.a = r2.n))))
+   ->  Foreign Scan
+         Output: d_3.a, d_3.b, n.n
+         Relations: (public.distr1_part_3 d_3) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r5.a, r5.b, r2.n FROM (public.distr1_base_3 r5 INNER JOIN unnest('{2,3,4,5}'::integer[]) r2 (n)  ON (((r5.a = r2.n))))
+   ->  Foreign Scan
+         Output: d_4.a, d_4.b, n.n
+         Relations: (public.distr1_part_4 d_4) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r6.a, r6.b, r2.n FROM (public.distr1_base_4 r6 INNER JOIN unnest('{2,3,4,5}'::integer[]) r2 (n)  ON (((r6.a = r2.n))))
+(23 rows)
+
+SELECT * FROM distr1 d, unnest(array[2,3,4,5]) n WHERE d.a = n
+ORDER BY d.a;
+ a | b  | n 
+---+----+---
+ 2 |  4 | 2
+ 3 |  6 | 3
+ 4 |  8 | 4
+ 5 | 10 | 5
+(4 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM unnest(array[2,3,4,5]) n, distr1 d WHERE d.a = n;
+                                                                    QUERY PLAN                                                                     
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ Append
+   ->  Hash Join
+         Output: n.n, d_1.a, d_1.b
+         Hash Cond: (d_1.a = n.n)
+         ->  Seq Scan on public.distr1_part_1 d_1
+               Output: d_1.a, d_1.b
+         ->  Hash
+               Output: n.n
+               ->  Function Scan on pg_catalog.unnest n
+                     Output: n.n
+                     Function Call: unnest('{2,3,4,5}'::integer[])
+   ->  Foreign Scan
+         Output: n.n, d_2.a, d_2.b
+         Relations: (public.distr1_part_2 d_2) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r1.n, r4.a, r4.b FROM (public.distr1_base_2 r4 INNER JOIN unnest('{2,3,4,5}'::integer[]) r1 (n)  ON (((r1.n = r4.a))))
+   ->  Foreign Scan
+         Output: n.n, d_3.a, d_3.b
+         Relations: (public.distr1_part_3 d_3) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r1.n, r5.a, r5.b FROM (public.distr1_base_3 r5 INNER JOIN unnest('{2,3,4,5}'::integer[]) r1 (n)  ON (((r1.n = r5.a))))
+   ->  Foreign Scan
+         Output: n.n, d_4.a, d_4.b
+         Relations: (public.distr1_part_4 d_4) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r1.n, r6.a, r6.b FROM (public.distr1_base_4 r6 INNER JOIN unnest('{2,3,4,5}'::integer[]) r1 (n)  ON (((r1.n = r6.a))))
+(23 rows)
+
+SELECT * FROM unnest(array[2,3,4,5]) n, distr1 d WHERE d.a = n
+ORDER BY d.a;
+ n | a | b  
+---+---+----
+ 2 | 2 |  4
+ 3 | 3 |  6
+ 4 | 4 |  8
+ 5 | 5 | 10
+(4 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM distr1 d1, distr2 d2, unnest(array[3,4]) n
+WHERE d1.a = n AND d2.c = d1.a;
+                                                                                                       QUERY PLAN                                                                                                        
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Append
+   ->  Hash Join
+         Output: d1_1.a, d1_1.b, d2_1.c, d2_1.d, n.n
+         Hash Cond: (d1_1.a = n.n)
+         ->  Hash Join
+               Output: d1_1.a, d1_1.b, d2_1.c, d2_1.d
+               Hash Cond: (d1_1.a = d2_1.c)
+               ->  Seq Scan on public.distr1_part_1 d1_1
+                     Output: d1_1.a, d1_1.b
+               ->  Hash
+                     Output: d2_1.c, d2_1.d
+                     ->  Seq Scan on public.distr2_part_1 d2_1
+                           Output: d2_1.c, d2_1.d
+         ->  Hash
+               Output: n.n
+               ->  Function Scan on pg_catalog.unnest n
+                     Output: n.n
+                     Function Call: unnest('{3,4}'::integer[])
+   ->  Foreign Scan
+         Output: d1_2.a, d1_2.b, d2_2.c, d2_2.d, n.n
+         Relations: ((public.distr1_part_2 d1_2) INNER JOIN (public.distr2_part_2 d2_2)) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r5.a, r5.b, r9.c, r9.d, r3.n FROM ((public.distr1_base_2 r5 INNER JOIN public.distr2_base_2 r9 ON (((r5.a = r9.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 (n)  ON (((r5.a = r3.n))))
+   ->  Foreign Scan
+         Output: d1_3.a, d1_3.b, d2_3.c, d2_3.d, n.n
+         Relations: ((public.distr1_part_3 d1_3) INNER JOIN (public.distr2_part_3 d2_3)) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r6.a, r6.b, r10.c, r10.d, r3.n FROM ((public.distr1_base_3 r6 INNER JOIN public.distr2_base_3 r10 ON (((r6.a = r10.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 (n)  ON (((r6.a = r3.n))))
+   ->  Foreign Scan
+         Output: d1_4.a, d1_4.b, d2_4.c, d2_4.d, n.n
+         Relations: ((public.distr1_part_4 d1_4) INNER JOIN (public.distr2_part_4 d2_4)) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r7.a, r7.b, r11.c, r11.d, r3.n FROM ((public.distr1_base_4 r7 INNER JOIN public.distr2_base_4 r11 ON (((r7.a = r11.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 (n)  ON (((r7.a = r3.n))))
+(30 rows)
+
+SELECT * FROM distr1 d1, distr2 d2, unnest(array[3,4]) n
+WHERE d1.a = n AND d2.c = d1.a
+ORDER BY d1.a;
+ a | b | c |   d   | n 
+---+---+---+-------+---
+ 3 | 6 | 3 | text3 | 3
+ 4 | 8 | 4 | text4 | 4
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * from distr1 d1, distr2 d2, unnest(array[3,4]) n
+WHERE d1.a = n AND d2.c = d1.a and n > 3;
+                                                                                                                QUERY PLAN                                                                                                                
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Append
+   ->  Hash Join
+         Output: d1_1.a, d1_1.b, d2_1.c, d2_1.d, n.n
+         Hash Cond: (d1_1.a = n.n)
+         ->  Hash Join
+               Output: d1_1.a, d1_1.b, d2_1.c, d2_1.d
+               Hash Cond: (d1_1.a = d2_1.c)
+               ->  Seq Scan on public.distr1_part_1 d1_1
+                     Output: d1_1.a, d1_1.b
+               ->  Hash
+                     Output: d2_1.c, d2_1.d
+                     ->  Seq Scan on public.distr2_part_1 d2_1
+                           Output: d2_1.c, d2_1.d
+         ->  Hash
+               Output: n.n
+               ->  Function Scan on pg_catalog.unnest n
+                     Output: n.n
+                     Function Call: unnest('{3,4}'::integer[])
+                     Filter: (n.n > 3)
+   ->  Foreign Scan
+         Output: d1_2.a, d1_2.b, d2_2.c, d2_2.d, n.n
+         Relations: ((public.distr1_part_2 d1_2) INNER JOIN (public.distr2_part_2 d2_2)) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r5.a, r5.b, r9.c, r9.d, r3.n FROM ((public.distr1_base_2 r5 INNER JOIN public.distr2_base_2 r9 ON (((r5.a = r9.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 (n)  ON (((r5.a = r3.n)) AND ((r3.n > 3))))
+   ->  Foreign Scan
+         Output: d1_3.a, d1_3.b, d2_3.c, d2_3.d, n.n
+         Relations: ((public.distr1_part_3 d1_3) INNER JOIN (public.distr2_part_3 d2_3)) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r6.a, r6.b, r10.c, r10.d, r3.n FROM ((public.distr1_base_3 r6 INNER JOIN public.distr2_base_3 r10 ON (((r6.a = r10.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 (n)  ON (((r6.a = r3.n)) AND ((r3.n > 3))))
+   ->  Foreign Scan
+         Output: d1_4.a, d1_4.b, d2_4.c, d2_4.d, n.n
+         Relations: ((public.distr1_part_4 d1_4) INNER JOIN (public.distr2_part_4 d2_4)) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r7.a, r7.b, r11.c, r11.d, r3.n FROM ((public.distr1_base_4 r7 INNER JOIN public.distr2_base_4 r11 ON (((r7.a = r11.c)))) INNER JOIN unnest('{3,4}'::integer[]) r3 (n)  ON (((r7.a = r3.n)) AND ((r3.n > 3))))
+(31 rows)
+
+SELECT * from distr1 d1, distr2 d2, unnest(array[3,4]) n
+WHERE d1.a = n AND d2.c = d1.a and n > 3
+ORDER BY d1.a;
+ a | b | c |   d   | n 
+---+---+---+-------+---
+ 4 | 8 | 4 | text4 | 4
+(1 row)
+
+-- Direct update with returning
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE distr1 d1 SET b=t.l FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b;
+                                                                                                                                                   QUERY PLAN                                                                                                                                                    
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.distr1 d1
+   Output: d1_1.a, d1_1.b
+   Update on public.distr1_part_1 d1_1
+   Foreign Update on public.distr1_part_2 d1_2
+   Foreign Update on public.distr1_part_3 d1_3
+   Foreign Update on public.distr1_part_4 d1_4
+   ->  Result
+         Output: t.l, t.*, d1.tableoid, d1.ctid, (NULL::record)
+         ->  Append
+               ->  Nested Loop
+                     Output: d1_1.tableoid, d1_1.ctid, NULL::record, t.l, t.*
+                     Join Filter: ((d1_1.a >= t.l) AND ((d1_1.a)::double precision <= area(t.bx)))
+                     ->  Function Scan on t
+                           Output: t.l, t.*, t.bx
+                           Function Call: unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])
+                     ->  Materialize
+                           Output: d1_1.a, d1_1.tableoid, d1_1.ctid, NULL::record
+                           ->  Seq Scan on public.distr1_part_1 d1_1
+                                 Output: d1_1.a, d1_1.tableoid, d1_1.ctid, NULL::record
+               ->  Foreign Update
+                     Remote SQL: UPDATE public.distr1_base_2 r4 SET b = r2.l FROM ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx)  WHERE ((r4.a >= r2.l)) AND ((r4.a <= area(r2.bx))) RETURNING CASE WHEN (r4.*)::text IS NOT NULL THEN 17254 END, r4.a, r4.b
+               ->  Foreign Update
+                     Remote SQL: UPDATE public.distr1_base_3 r5 SET b = r2.l FROM ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx)  WHERE ((r5.a >= r2.l)) AND ((r5.a <= area(r2.bx))) RETURNING CASE WHEN (r5.*)::text IS NOT NULL THEN 17260 END, r5.a, r5.b
+               ->  Foreign Update
+                     Remote SQL: UPDATE public.distr1_base_4 r6 SET b = r2.l FROM ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx)  WHERE ((r6.a >= r2.l)) AND ((r6.a <= area(r2.bx))) RETURNING CASE WHEN (r6.*)::text IS NOT NULL THEN 17266 END, r6.a, r6.b
+(25 rows)
+
+UPDATE distr1 d1 SET b=t.l FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b;
+ a  | b  
+----+----
+ 12 | 10
+ 14 | 10
+ 16 | 10
+ 17 | 10
+ 26 | 10
+ 28 | 10
+ 11 | 10
+ 19 | 10
+ 20 | 10
+ 21 | 10
+ 13 | 10
+ 18 | 10
+ 23 | 10
+ 25 | 10
+ 27 | 10
+ 10 | 10
+ 15 | 10
+ 22 | 10
+ 24 | 10
+(19 rows)
+
+-- Direct update with returning tableoid
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE distr1 d1 SET b=t.l FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b,tableoid;
+                                                                                                                                                   QUERY PLAN                                                                                                                                                    
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.distr1 d1
+   Output: d1_1.a, d1_1.b, d1_1.tableoid
+   Update on public.distr1_part_1 d1_1
+   Foreign Update on public.distr1_part_2 d1_2
+   Foreign Update on public.distr1_part_3 d1_3
+   Foreign Update on public.distr1_part_4 d1_4
+   ->  Result
+         Output: t.l, t.*, d1.tableoid, d1.ctid, (NULL::record)
+         ->  Append
+               ->  Nested Loop
+                     Output: d1_1.tableoid, d1_1.ctid, NULL::record, t.l, t.*
+                     Join Filter: ((d1_1.a >= t.l) AND ((d1_1.a)::double precision <= area(t.bx)))
+                     ->  Function Scan on t
+                           Output: t.l, t.*, t.bx
+                           Function Call: unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])
+                     ->  Materialize
+                           Output: d1_1.a, d1_1.tableoid, d1_1.ctid, NULL::record
+                           ->  Seq Scan on public.distr1_part_1 d1_1
+                                 Output: d1_1.a, d1_1.tableoid, d1_1.ctid, NULL::record
+               ->  Foreign Update
+                     Remote SQL: UPDATE public.distr1_base_2 r4 SET b = r2.l FROM ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx)  WHERE ((r4.a >= r2.l)) AND ((r4.a <= area(r2.bx))) RETURNING CASE WHEN (r4.*)::text IS NOT NULL THEN 17254 END, r4.a, r4.b
+               ->  Foreign Update
+                     Remote SQL: UPDATE public.distr1_base_3 r5 SET b = r2.l FROM ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx)  WHERE ((r5.a >= r2.l)) AND ((r5.a <= area(r2.bx))) RETURNING CASE WHEN (r5.*)::text IS NOT NULL THEN 17260 END, r5.a, r5.b
+               ->  Foreign Update
+                     Remote SQL: UPDATE public.distr1_base_4 r6 SET b = r2.l FROM ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx)  WHERE ((r6.a >= r2.l)) AND ((r6.a <= area(r2.bx))) RETURNING CASE WHEN (r6.*)::text IS NOT NULL THEN 17266 END, r6.a, r6.b
+(25 rows)
+
+UPDATE distr1 d1 SET b=t.l FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b,tableoid;
+ a  | b  | tableoid 
+----+----+----------
+ 12 | 10 |    17248
+ 14 | 10 |    17248
+ 16 | 10 |    17248
+ 17 | 10 |    17248
+ 26 | 10 |    17248
+ 28 | 10 |    17248
+ 11 | 10 |    17254
+ 19 | 10 |    17254
+ 20 | 10 |    17254
+ 21 | 10 |    17254
+ 13 | 10 |    17260
+ 18 | 10 |    17260
+ 23 | 10 |    17260
+ 25 | 10 |    17260
+ 27 | 10 |    17260
+ 10 | 10 |    17266
+ 15 | 10 |    17266
+ 22 | 10 |    17266
+ 24 | 10 |    17266
+(19 rows)
+
+-- Indirect update with returning
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE distr1 d1 SET b=CASE WHEN random()>=0 THEN t.l ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b;
+                                                                                                                                                                                                                     QUERY PLAN                                                                                                                                                                                                                     
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Update on public.distr1 d1
+   Output: d1_1.a, d1_1.b
+   Update on public.distr1_part_1 d1_1
+   Foreign Update on public.distr1_part_2 d1_2
+     Remote SQL: UPDATE public.distr1_base_2 SET b = $2 WHERE ctid = $1 RETURNING a, b
+   Foreign Update on public.distr1_part_3 d1_3
+     Remote SQL: UPDATE public.distr1_base_3 SET b = $2 WHERE ctid = $1 RETURNING a, b
+   Foreign Update on public.distr1_part_4 d1_4
+     Remote SQL: UPDATE public.distr1_base_4 SET b = $2 WHERE ctid = $1 RETURNING a, b
+   ->  Result
+         Output: CASE WHEN (random() >= '0'::double precision) THEN t.l ELSE 0 END, t.*, d1.tableoid, d1.ctid, (NULL::record)
+         ->  Append
+               ->  Nested Loop
+                     Output: d1_1.tableoid, d1_1.ctid, NULL::record, t.l, t.*
+                     Join Filter: ((d1_1.a >= t.l) AND ((d1_1.a)::double precision <= area(t.bx)))
+                     ->  Function Scan on t
+                           Output: t.l, t.*, t.bx
+                           Function Call: unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])
+                     ->  Materialize
+                           Output: d1_1.a, d1_1.tableoid, d1_1.ctid, NULL::record
+                           ->  Seq Scan on public.distr1_part_1 d1_1
+                                 Output: d1_1.a, d1_1.tableoid, d1_1.ctid, NULL::record
+               ->  Foreign Scan
+                     Output: d1_2.tableoid, d1_2.ctid, d1_2.*, t.l, t.*
+                     Relations: (public.distr1_part_2 d1_2) INNER JOIN (ROWS FROM(pg_catalog.unnest(), pg_catalog.unnest()) t)
+                     Remote SQL: SELECT CASE WHEN (r4.*)::text IS NOT NULL THEN 17254 END, r4.ctid, CASE WHEN (r4.*)::text IS NOT NULL THEN ROW(r4.a, r4.b) END, r2.l, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.l,r2.bx) END FROM (public.distr1_base_2 r4 INNER JOIN ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx)  ON (((r4.a >= r2.l)) AND ((r4.a <= area(r2.bx))))) FOR UPDATE OF r4
+                     ->  Nested Loop
+                           Output: d1_2.tableoid, d1_2.ctid, d1_2.*, t.l, t.*
+                           Join Filter: ((d1_2.a >= t.l) AND ((d1_2.a)::double precision <= area(t.bx)))
+                           ->  Foreign Scan on public.distr1_part_2 d1_2
+                                 Output: d1_2.a, d1_2.tableoid, d1_2.ctid, d1_2.*
+                                 Remote SQL: SELECT a, b, ctid FROM public.distr1_base_2 FOR UPDATE
+                           ->  Function Scan on t
+                                 Output: t.l, t.*, t.bx
+                                 Function Call: unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])
+               ->  Foreign Scan
+                     Output: d1_3.tableoid, d1_3.ctid, d1_3.*, t.l, t.*
+                     Relations: (public.distr1_part_3 d1_3) INNER JOIN (ROWS FROM(pg_catalog.unnest(), pg_catalog.unnest()) t)
+                     Remote SQL: SELECT CASE WHEN (r5.*)::text IS NOT NULL THEN 17260 END, r5.ctid, CASE WHEN (r5.*)::text IS NOT NULL THEN ROW(r5.a, r5.b) END, r2.l, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.l,r2.bx) END FROM (public.distr1_base_3 r5 INNER JOIN ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx)  ON (((r5.a >= r2.l)) AND ((r5.a <= area(r2.bx))))) FOR UPDATE OF r5
+                     ->  Nested Loop
+                           Output: d1_3.tableoid, d1_3.ctid, d1_3.*, t.l, t.*
+                           Join Filter: ((d1_3.a >= t.l) AND ((d1_3.a)::double precision <= area(t.bx)))
+                           ->  Foreign Scan on public.distr1_part_3 d1_3
+                                 Output: d1_3.a, d1_3.tableoid, d1_3.ctid, d1_3.*
+                                 Remote SQL: SELECT a, b, ctid FROM public.distr1_base_3 FOR UPDATE
+                           ->  Function Scan on t
+                                 Output: t.l, t.*, t.bx
+                                 Function Call: unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])
+               ->  Foreign Scan
+                     Output: d1_4.tableoid, d1_4.ctid, d1_4.*, t.l, t.*
+                     Relations: (public.distr1_part_4 d1_4) INNER JOIN (ROWS FROM(pg_catalog.unnest(), pg_catalog.unnest()) t)
+                     Remote SQL: SELECT CASE WHEN (r6.*)::text IS NOT NULL THEN 17266 END, r6.ctid, CASE WHEN (r6.*)::text IS NOT NULL THEN ROW(r6.a, r6.b) END, r2.l, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.l,r2.bx) END FROM (public.distr1_base_4 r6 INNER JOIN ROWS FROM (unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])) r2 (l,bx)  ON (((r6.a >= r2.l)) AND ((r6.a <= area(r2.bx))))) FOR UPDATE OF r6
+                     ->  Nested Loop
+                           Output: d1_4.tableoid, d1_4.ctid, d1_4.*, t.l, t.*
+                           Join Filter: ((d1_4.a >= t.l) AND ((d1_4.a)::double precision <= area(t.bx)))
+                           ->  Foreign Scan on public.distr1_part_4 d1_4
+                                 Output: d1_4.a, d1_4.tableoid, d1_4.ctid, d1_4.*
+                                 Remote SQL: SELECT a, b, ctid FROM public.distr1_base_4 FOR UPDATE
+                           ->  Function Scan on t
+                                 Output: t.l, t.*, t.bx
+                                 Function Call: unnest('{10,20}'::integer[]), unnest('{(2,3),(-2,-4);(1,2),(-2,-3)}'::box[])
+(61 rows)
+
+UPDATE distr1 d1 SET b=CASE WHEN random()>=0 THEN t.l ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b;
+ a  | b  
+----+----
+ 12 | 10
+ 14 | 10
+ 16 | 10
+ 17 | 10
+ 26 | 10
+ 28 | 10
+ 11 | 10
+ 19 | 10
+ 20 | 10
+ 21 | 10
+ 13 | 10
+ 18 | 10
+ 23 | 10
+ 25 | 10
+ 27 | 10
+ 10 | 10
+ 15 | 10
+ 22 | 10
+ 24 | 10
+(19 rows)
+
+DROP TABLE distr1, distr2, distr1_base_2, distr2_base_2, distr1_base_3, distr2_base_3, distr1_base_4, distr2_base_4;
+-- Test pushdown of several function scans
+CREATE TABLE distr1(a int, b int) PARTITION BY HASH(a);
+CREATE TABLE distr1_part_1 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 2, REMAINDER 0);
+CREATE TABLE distr1_base_2 (a int, b int);
+CREATE FOREIGN TABLE distr1_part_2 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 2, REMAINDER 1)
+	SERVER loopback OPTIONS (table_name 'distr1_base_2', use_remote_estimate 'true');
+CREATE TABLE distr2(c int, d text) PARTITION BY HASH(c);
+CREATE TABLE distr2_part_1 PARTITION OF distr2 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
+CREATE TABLE distr2_base_2 (c int, d text);
+CREATE FOREIGN TABLE distr2_part_2 PARTITION OF distr2 FOR VALUES WITH ( MODULUS 2, REMAINDER 1)
+	SERVER loopback OPTIONS (table_name 'distr2_base_2', use_remote_estimate 'true');
+INSERT INTO distr1 SELECT g, g*2 from  generate_series(1,1000) g;
+INSERT INTO distr2 SELECT g, 'text'|| g from  generate_series(1,500) g;
+ANALYZE;
+-- Make local function scan not so attractive
+ALTER SERVER loopback OPTIONS (ADD fdw_tuple_cost '1000');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * from distr1 d1, distr1 d2, unnest(array[3,4]) n, unnest(array[3,4]) g
+WHERE d1.a = n AND d2.a = d1.a and g = n;
+                                                                                                                                           QUERY PLAN                                                                                                                                           
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Append
+   ->  Hash Join
+         Output: d1_1.a, d1_1.b, d2_1.a, d2_1.b, n.n, g.g
+         Hash Cond: (d1_1.a = g.g)
+         ->  Hash Join
+               Output: d1_1.a, d1_1.b, d2_1.a, d2_1.b, n.n
+               Hash Cond: (d1_1.a = n.n)
+               ->  Hash Join
+                     Output: d1_1.a, d1_1.b, d2_1.a, d2_1.b
+                     Hash Cond: (d1_1.a = d2_1.a)
+                     ->  Seq Scan on public.distr1_part_1 d1_1
+                           Output: d1_1.a, d1_1.b
+                     ->  Hash
+                           Output: d2_1.a, d2_1.b
+                           ->  Seq Scan on public.distr1_part_1 d2_1
+                                 Output: d2_1.a, d2_1.b
+               ->  Hash
+                     Output: n.n
+                     ->  Function Scan on pg_catalog.unnest n
+                           Output: n.n
+                           Function Call: unnest('{3,4}'::integer[])
+         ->  Hash
+               Output: g.g
+               ->  Function Scan on pg_catalog.unnest g
+                     Output: g.g
+                     Function Call: unnest('{3,4}'::integer[])
+   ->  Foreign Scan
+         Output: d1_2.a, d1_2.b, d2_2.a, d2_2.b, n.n, g.g
+         Relations: (((public.distr1_part_2 d1_2) INNER JOIN (public.distr1_part_2 d2_2)) INNER JOIN (pg_catalog.unnest() n)) INNER JOIN (pg_catalog.unnest() g)
+         Remote SQL: SELECT r6.a, r6.b, r8.a, r8.b, r3.n, r4.g FROM (((public.distr1_base_2 r6 INNER JOIN public.distr1_base_2 r8 ON (((r6.a = r8.a)))) INNER JOIN unnest('{3,4}'::integer[]) r3 (n)  ON (((r6.a = r3.n)))) INNER JOIN unnest('{3,4}'::integer[]) r4 (g)  ON (((r6.a = r4.g))))
+(30 rows)
+
+SELECT * from distr1 d1, distr2 d2, unnest(array[3,4]) n, generate_series(1,10000) g
+WHERE d1.a = n AND d2.c = d1.a and g = d1.a
+ORDER BY d1.a;
+ a | b | c |   d   | n | g 
+---+---+---+-------+---+---
+ 3 | 6 | 3 | text3 | 3 | 3
+ 4 | 8 | 4 | text4 | 4 | 4
+(2 rows)
+
+ALTER SERVER loopback OPTIONS (DROP fdw_tuple_cost);
+DROP TABLE distr1, distr2, distr1_base_2, distr2_base_2;
+-- Test inheritance chain and function scan pushdown
+CREATE TABLE distr1(a int, b int);
+CREATE TABLE distr1_loc (a int, b int) INHERITS (distr1);
+NOTICE:  merging column "a" with inherited definition
+NOTICE:  merging column "b" with inherited definition
+CREATE TABLE distr1_base (a int, b int, c text);
+CREATE FOREIGN TABLE distr1_remote (a int, b int, c text) INHERITS (distr1)
+	SERVER loopback OPTIONS (table_name 'distr1_base', use_remote_estimate 'true');
+NOTICE:  merging column "a" with inherited definition
+NOTICE:  merging column "b" with inherited definition
+CREATE TABLE distr2(c int, d text);
+CREATE TABLE distr2_loc (c int, d text) INHERITS (distr2);
+NOTICE:  merging column "c" with inherited definition
+NOTICE:  merging column "d" with inherited definition
+CREATE TABLE distr2_base (c int, d text, e int);
+CREATE FOREIGN TABLE distr2_remote (c int, d text, e int) INHERITS (distr2)
+	SERVER loopback OPTIONS (table_name 'distr2_base', use_remote_estimate 'true');
+NOTICE:  merging column "c" with inherited definition
+NOTICE:  merging column "d" with inherited definition
+INSERT INTO distr1_loc SELECT g, g*2 from  generate_series(1,100) g;
+INSERT INTO distr1_base SELECT g, g*2, 'text'|| g from  generate_series(200,20000) g;
+INSERT INTO distr2_loc SELECT g, 'text'|| g from generate_series(1,100) g;
+INSERT INTO distr2_base SELECT g, 'text'|| g, g*2 from generate_series(200,20000) g;
+ANALYZE distr1, distr1_loc, distr1_base;
+ANALYZE distr2, distr2_loc, distr2_base;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM distr1 d, unnest(array[2,3,4,5]) n WHERE d.a = n;
+                                                                   QUERY PLAN                                                                    
+-------------------------------------------------------------------------------------------------------------------------------------------------
+ Append
+   ->  Hash Join
+         Output: d_1.a, d_1.b, n.n
+         Hash Cond: (n.n = d_1.a)
+         ->  Function Scan on pg_catalog.unnest n
+               Output: n.n
+               Function Call: unnest('{2,3,4,5}'::integer[])
+         ->  Hash
+               Output: d_1.a, d_1.b
+               ->  Seq Scan on public.distr1 d_1
+                     Output: d_1.a, d_1.b
+   ->  Hash Join
+         Output: d_2.a, d_2.b, n.n
+         Hash Cond: (d_2.a = n.n)
+         ->  Seq Scan on public.distr1_loc d_2
+               Output: d_2.a, d_2.b
+         ->  Hash
+               Output: n.n
+               ->  Function Scan on pg_catalog.unnest n
+                     Output: n.n
+                     Function Call: unnest('{2,3,4,5}'::integer[])
+   ->  Foreign Scan
+         Output: d_3.a, d_3.b, n.n
+         Relations: (public.distr1_remote d_3) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r5.a, r5.b, r2.n FROM (public.distr1_base r5 INNER JOIN unnest('{2,3,4,5}'::integer[]) r2 (n)  ON (((r5.a = r2.n))))
+(25 rows)
+
+SELECT * FROM distr1 d, unnest(array[2,3,4,5]) n WHERE d.a = n
+ORDER BY d.a;
+ a | b  | n 
+---+----+---
+ 2 |  4 | 2
+ 3 |  6 | 3
+ 4 |  8 | 4
+ 5 | 10 | 5
+(4 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM unnest(array[2,3,4,5]) n, distr1 d WHERE d.a = n;
+                                                                   QUERY PLAN                                                                    
+-------------------------------------------------------------------------------------------------------------------------------------------------
+ Append
+   ->  Hash Join
+         Output: n.n, d_1.a, d_1.b
+         Hash Cond: (n.n = d_1.a)
+         ->  Function Scan on pg_catalog.unnest n
+               Output: n.n
+               Function Call: unnest('{2,3,4,5}'::integer[])
+         ->  Hash
+               Output: d_1.a, d_1.b
+               ->  Seq Scan on public.distr1 d_1
+                     Output: d_1.a, d_1.b
+   ->  Hash Join
+         Output: n.n, d_2.a, d_2.b
+         Hash Cond: (d_2.a = n.n)
+         ->  Seq Scan on public.distr1_loc d_2
+               Output: d_2.a, d_2.b
+         ->  Hash
+               Output: n.n
+               ->  Function Scan on pg_catalog.unnest n
+                     Output: n.n
+                     Function Call: unnest('{2,3,4,5}'::integer[])
+   ->  Foreign Scan
+         Output: n.n, d_3.a, d_3.b
+         Relations: (public.distr1_remote d_3) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r1.n, r5.a, r5.b FROM (public.distr1_base r5 INNER JOIN unnest('{2,3,4,5}'::integer[]) r1 (n)  ON (((r1.n = r5.a))))
+(25 rows)
+
+SELECT * FROM unnest(array[2,3,4,5]) n, distr1 d WHERE d.a = n
+ORDER BY d.a;
+ n | a | b  
+---+---+----
+ 2 | 2 |  4
+ 3 | 3 |  6
+ 4 | 4 |  8
+ 5 | 5 | 10
+(4 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM distr1 d1, distr2 d2, unnest(array[300,400]) n
+WHERE d1.a = n AND d2.c = d1.a;
+                                                                         QUERY PLAN                                                                          
+-------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Hash Join
+   Output: d1.a, d1.b, d2.c, d2.d, n.n
+   Hash Cond: (d2.c = d1.a)
+   ->  Append
+         ->  Seq Scan on public.distr2 d2_1
+               Output: d2_1.c, d2_1.d
+         ->  Seq Scan on public.distr2_loc d2_2
+               Output: d2_2.c, d2_2.d
+         ->  Foreign Scan on public.distr2_remote d2_3
+               Output: d2_3.c, d2_3.d
+               Remote SQL: SELECT c, d FROM public.distr2_base
+   ->  Hash
+         Output: d1.a, d1.b, n.n
+         ->  Append
+               ->  Nested Loop
+                     Output: d1_1.a, d1_1.b, n.n
+                     Join Filter: (d1_1.a = n.n)
+                     ->  Seq Scan on public.distr1 d1_1
+                           Output: d1_1.a, d1_1.b
+                     ->  Function Scan on pg_catalog.unnest n
+                           Output: n.n
+                           Function Call: unnest('{300,400}'::integer[])
+               ->  Hash Join
+                     Output: d1_2.a, d1_2.b, n.n
+                     Hash Cond: (d1_2.a = n.n)
+                     ->  Seq Scan on public.distr1_loc d1_2
+                           Output: d1_2.a, d1_2.b
+                     ->  Hash
+                           Output: n.n
+                           ->  Function Scan on pg_catalog.unnest n
+                                 Output: n.n
+                                 Function Call: unnest('{300,400}'::integer[])
+               ->  Foreign Scan
+                     Output: d1_3.a, d1_3.b, n.n
+                     Relations: (public.distr1_remote d1_3) INNER JOIN (pg_catalog.unnest() n)
+                     Remote SQL: SELECT r6.a, r6.b, r3.n FROM (public.distr1_base r6 INNER JOIN unnest('{300,400}'::integer[]) r3 (n)  ON (((r6.a = r3.n))))
+(36 rows)
+
+SELECT * FROM distr1 d1, distr2 d2, unnest(array[300,400]) n
+WHERE d1.a = n AND d2.c = d1.a
+ORDER BY d1.a;
+  a  |  b  |  c  |    d    |  n  
+-----+-----+-----+---------+-----
+ 300 | 600 | 300 | text300 | 300
+ 400 | 800 | 400 | text400 | 400
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * from distr1 d1, distr2 d2, unnest(array[300,400]) n
+WHERE d1.a = n AND d2.c = d1.a and n > 3;
+                                          QUERY PLAN                                           
+-----------------------------------------------------------------------------------------------
+ Nested Loop
+   Output: d1.a, d1.b, d2.c, d2.d, n.n
+   ->  Nested Loop
+         Output: d1.a, d1.b, n.n
+         ->  Function Scan on pg_catalog.unnest n
+               Output: n.n
+               Function Call: unnest('{300,400}'::integer[])
+               Filter: (n.n > 3)
+         ->  Append
+               ->  Seq Scan on public.distr1 d1_1
+                     Output: d1_1.a, d1_1.b
+                     Filter: (n.n = d1_1.a)
+               ->  Seq Scan on public.distr1_loc d1_2
+                     Output: d1_2.a, d1_2.b
+                     Filter: (n.n = d1_2.a)
+               ->  Foreign Scan on public.distr1_remote d1_3
+                     Output: d1_3.a, d1_3.b
+                     Remote SQL: SELECT a, b FROM public.distr1_base WHERE (($1::integer = a))
+   ->  Append
+         ->  Seq Scan on public.distr2 d2_1
+               Output: d2_1.c, d2_1.d
+               Filter: (d1.a = d2_1.c)
+         ->  Seq Scan on public.distr2_loc d2_2
+               Output: d2_2.c, d2_2.d
+               Filter: (d1.a = d2_2.c)
+         ->  Foreign Scan on public.distr2_remote d2_3
+               Output: d2_3.c, d2_3.d
+               Remote SQL: SELECT c, d FROM public.distr2_base WHERE (($1::integer = c))
+(28 rows)
+
+SELECT * from distr1 d1, distr2 d2, unnest(array[300,400]) n
+WHERE d1.a = n AND d2.c = d1.a and n > 3
+ORDER BY d1.a;
+  a  |  b  |  c  |    d    |  n  
+-----+-----+-----+---------+-----
+ 300 | 600 | 300 | text300 | 300
+ 400 | 800 | 400 | text400 | 400
+(2 rows)
+
+DROP FOREIGN TABLE distr1_remote, distr2_remote;
+DROP TABLE distr1, distr1_loc, distr1_base, distr2, distr2_loc, distr2_base;
+-- Test UNION and function scan pushdown
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM
+(SELECT a FROM remote_tbl
+UNION
+SELECT c FROM remote_tbl1) u
+JOIN unnest(array[3,4]) n
+ON u.a = n;
+                           QUERY PLAN                           
+----------------------------------------------------------------
+ Hash Join
+   Output: remote_tbl.a, n.n
+   Hash Cond: (remote_tbl.a = n.n)
+   ->  HashAggregate
+         Output: remote_tbl.a
+         Group Key: remote_tbl.a
+         ->  Append
+               ->  Foreign Scan on public.remote_tbl
+                     Output: remote_tbl.a
+                     Remote SQL: SELECT a FROM public.base_tbl
+               ->  Foreign Scan on public.remote_tbl1
+                     Output: remote_tbl1.c
+                     Remote SQL: SELECT c FROM public.base_tbl1
+   ->  Hash
+         Output: n.n
+         ->  Function Scan on pg_catalog.unnest n
+               Output: n.n
+               Function Call: unnest('{3,4}'::integer[])
+(18 rows)
+
+SELECT * FROM
+(SELECT a FROM remote_tbl
+UNION
+SELECT c FROM remote_tbl1) u
+JOIN unnest(array[3,4]) n
+ON u.a = n
+ORDER BY n;
+ a | n 
+---+---
+ 3 | 3
+ 4 | 4
+(2 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM
+(SELECT a FROM remote_tbl
+UNION ALL
+SELECT c FROM remote_tbl1) u
+JOIN unnest(array[3,4]) n
+ON u.a = n;
+                                                             QUERY PLAN                                                              
+-------------------------------------------------------------------------------------------------------------------------------------
+ Append
+   ->  Foreign Scan
+         Output: remote_tbl.a, n.n
+         Relations: (public.remote_tbl) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r6.a, r2.n FROM (public.base_tbl r6 INNER JOIN unnest('{3,4}'::integer[]) r2 (n)  ON (((r6.a = r2.n))))
+   ->  Foreign Scan
+         Output: remote_tbl1.c, n.n
+         Relations: (public.remote_tbl1) INNER JOIN (pg_catalog.unnest() n)
+         Remote SQL: SELECT r7.c, r2.n FROM (public.base_tbl1 r7 INNER JOIN unnest('{3,4}'::integer[]) r2 (n)  ON (((r7.c = r2.n))))
+(9 rows)
+
+SELECT * FROM
+(SELECT a FROM remote_tbl
+UNION ALL
+SELECT c FROM remote_tbl1) u
+JOIN unnest(array[3,4]) n
+ON u.a = n
+ORDER BY n;
+ a | n 
+---+---
+ 3 | 3
+ 3 | 3
+ 4 | 4
+ 4 | 4
+(4 rows)
+
+RESET enable_partitionwise_join;
+DROP FUNCTION f(INTEGER);
+DROP TABLE base_tbl, base_tbl1;
+DROP FOREIGN TABLE remote_tbl, remote_tbl1;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 76d4fea21c4..bd6e974ebdb 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -48,6 +48,7 @@
 #include "utils/rel.h"
 #include "utils/sampling.h"
 #include "utils/selfuncs.h"
+#include "utils/typcache.h"
 
 PG_MODULE_MAGIC;
 
@@ -406,6 +407,14 @@ static void postgresGetForeignJoinPaths(PlannerInfo *root,
 										RelOptInfo *innerrel,
 										JoinType jointype,
 										JoinPathExtraData *extra);
+
+static void postgresTryShippableJoinPaths(PlannerInfo *root,
+										  RelOptInfo *joinrel,
+										  RelOptInfo *outerrel,
+										  RelOptInfo *innerrel,
+										  JoinType jointype,
+										  JoinPathExtraData *extra);
+
 static bool postgresRecheckForeignScan(ForeignScanState *node,
 									   TupleTableSlot *slot);
 static void postgresGetForeignUpperPaths(PlannerInfo *root,
@@ -474,7 +483,7 @@ static void store_returning_result(PgFdwModifyState *fmstate,
 static void finish_foreign_modify(PgFdwModifyState *fmstate);
 static void deallocate_query(PgFdwModifyState *fmstate);
 static List *build_remote_returning(Index rtindex, Relation rel,
-									List *returningList);
+									List *returningList, Var *tid);
 static void rebuild_fdw_scan_tlist(ForeignScan *fscan, List *tlist);
 static void execute_dml_stmt(ForeignScanState *node);
 static TupleTableSlot *get_returning_data(ForeignScanState *node);
@@ -540,6 +549,12 @@ static void merge_fdw_options(PgFdwRelationInfo *fpinfo,
 							  const PgFdwRelationInfo *fpinfo_i);
 static int	get_batch_size_option(Relation rel);
 
+static bool is_nonrel_relinfo_ok(PlannerInfo *root, RelOptInfo *foreignrel);
+static void init_fpinfo(PlannerInfo *root,
+						RelOptInfo *baserel,
+						Oid foreigntableid,
+						PgFdwRelationInfo *existing_fpinfo);
+
 
 /*
  * Foreign-data wrapper handler function: return a struct with pointers
@@ -595,6 +610,7 @@ postgres_fdw_handler(PG_FUNCTION_ARGS)
 
 	/* Support functions for join push-down */
 	routine->GetForeignJoinPaths = postgresGetForeignJoinPaths;
+	routine->TryShippableJoinPaths = postgresTryShippableJoinPaths;
 
 	/* Support functions for upper relation push-down */
 	routine->GetForeignUpperPaths = postgresGetForeignUpperPaths;
@@ -619,10 +635,31 @@ static void
 postgresGetForeignRelSize(PlannerInfo *root,
 						  RelOptInfo *baserel,
 						  Oid foreigntableid)
+{
+	init_fpinfo(root, baserel, foreigntableid, NULL);
+}
+
+/*
+ * init_fpinfo
+ *
+ * Either initialize fpinfo based on foreign table or generate one, based on
+ * existing fpinfo.
+ * Also estimate # of rows and width of the result of the scan.
+ *
+ * We should consider the effect of all baserestrictinfo clauses here, but
+ * not any join clauses.
+ */
+static void
+init_fpinfo(PlannerInfo *root,
+			RelOptInfo *baserel,
+			Oid foreigntableid,
+			PgFdwRelationInfo *existing_fpinfo)
 {
 	PgFdwRelationInfo *fpinfo;
 	ListCell   *lc;
-	RangeTblEntry *rte = planner_rt_fetch(baserel->relid, root);
+
+	Assert(existing_fpinfo || foreigntableid != InvalidOid);
+	Assert(existing_fpinfo == NULL || foreigntableid == InvalidOid);
 
 	/*
 	 * We use PgFdwRelationInfo to pass various information to subsequent
@@ -634,39 +671,59 @@ postgresGetForeignRelSize(PlannerInfo *root,
 	/* Base foreign tables need to be pushed down always. */
 	fpinfo->pushdown_safe = true;
 
-	/* Look up foreign-table catalog info. */
-	fpinfo->table = GetForeignTable(foreigntableid);
-	fpinfo->server = GetForeignServer(fpinfo->table->serverid);
-
-	/*
-	 * Extract user-settable option values.  Note that per-table settings of
-	 * use_remote_estimate, fetch_size and async_capable override per-server
-	 * settings of them, respectively.
-	 */
-	fpinfo->use_remote_estimate = false;
-	fpinfo->fdw_startup_cost = DEFAULT_FDW_STARTUP_COST;
-	fpinfo->fdw_tuple_cost = DEFAULT_FDW_TUPLE_COST;
-	fpinfo->shippable_extensions = NIL;
-	fpinfo->fetch_size = 100;
-	fpinfo->async_capable = false;
+	if (existing_fpinfo)
+	{
+		/* We don't have any table, related to query */
+		fpinfo->table = NULL;
+		fpinfo->server = existing_fpinfo->server;
+	}
+	else
+	{
+		/* Look up foreign-table catalog info. */
+		fpinfo->table = GetForeignTable(foreigntableid);
+		fpinfo->server = GetForeignServer(fpinfo->table->serverid);
+	}
 
-	apply_server_options(fpinfo);
-	apply_table_options(fpinfo);
+	if (existing_fpinfo)
+	{
+		merge_fdw_options(fpinfo, existing_fpinfo, NULL);
+		fpinfo->user = existing_fpinfo->user;
 
-	/*
-	 * If the table or the server is configured to use remote estimates,
-	 * identify which user to do remote access as during planning.  This
-	 * should match what ExecCheckRTEPerms() does.  If we fail due to lack of
-	 * permissions, the query would have failed at runtime anyway.
-	 */
-	if (fpinfo->use_remote_estimate)
+		/*
+		 * Don't try to execute anything on remote server for
+		 * non-relation-based query
+		 */
+		fpinfo->use_remote_estimate = false;
+	}
+	else
 	{
+		RangeTblEntry *rte = planner_rt_fetch(baserel->relid, root);
 		Oid			userid = rte->checkAsUser ? rte->checkAsUser : GetUserId();
 
+		/*
+		 * Extract user-settable option values.  Note that per-table settings
+		 * of use_remote_estimate, fetch_size and async_capable override
+		 * per-server settings of them, respectively.
+		 */
+		fpinfo->use_remote_estimate = false;
+		fpinfo->fdw_startup_cost = DEFAULT_FDW_STARTUP_COST;
+		fpinfo->fdw_tuple_cost = DEFAULT_FDW_TUPLE_COST;
+		fpinfo->shippable_extensions = NIL;
+		fpinfo->fetch_size = 100;
+		fpinfo->async_capable = false;
+		fpinfo->is_generated = false;
+
+		apply_server_options(fpinfo);
+		apply_table_options(fpinfo);
+
+		/*
+		 * If the table or the server is configured to use remote estimates,
+		 * identify which user to do remote access as during planning.  This
+		 * should match what ExecCheckRTEPerms() does.  If we fail due to lack
+		 * of permissions, the query would have failed at runtime anyway.
+		 */
 		fpinfo->user = GetUserMapping(userid, fpinfo->server->serverid);
 	}
-	else
-		fpinfo->user = NULL;
 
 	/*
 	 * Identify which baserestrictinfo clauses can be sent to the remote
@@ -778,6 +835,9 @@ postgresGetForeignRelSize(PlannerInfo *root,
 	fpinfo->lower_subquery_rels = NULL;
 	/* Set the relation index. */
 	fpinfo->relation_index = baserel->relid;
+	if (existing_fpinfo)
+		/* Mark fpinfo generated */
+		fpinfo->is_generated = true;
 }
 
 /*
@@ -1472,13 +1532,72 @@ get_tupdesc_for_join_scan_tuples(ForeignScanState *node)
 		if (!IsA(var, Var) || var->varattno != 0)
 			continue;
 		rte = list_nth(estate->es_range_table, var->varno - 1);
-		if (rte->rtekind != RTE_RELATION)
-			continue;
-		reltype = get_rel_type_id(rte->relid);
-		if (!OidIsValid(reltype))
-			continue;
-		att->atttypid = reltype;
-		/* shouldn't need to change anything else */
+		if (rte->rtekind == RTE_RELATION)
+		{
+			reltype = get_rel_type_id(rte->relid);
+			if (!OidIsValid(reltype))
+				continue;
+			att->atttypid = reltype;
+			/* shouldn't need to change anything else */
+		}
+		else if (rte->rtekind == RTE_FUNCTION)
+		{
+			RangeTblFunction *rtfunc;
+			TupleDesc	td;
+			Oid			funcrettype;
+			int			num_funcs,
+						attnum;
+			ListCell   *lc,
+					   *lctype,
+					   *lcname;
+			bool		functype_OK = true;
+			List	   *functypes = NIL;
+
+			if (rte->funcordinality)
+				continue;
+
+			num_funcs = list_length(rte->functions);
+			Assert(num_funcs >= 0);
+
+			foreach(lc, rte->functions)
+			{
+				rtfunc = (RangeTblFunction *) lfirst(lc);
+				get_expr_result_type(rtfunc->funcexpr, &funcrettype, NULL);
+				if (!OidIsValid(funcrettype) || funcrettype == RECORDOID)
+				{
+					functype_OK = false;
+					break;
+				}
+				functypes = lappend_oid(functypes, funcrettype);
+			}
+			if (!functype_OK)
+				continue;
+			td = CreateTemplateTupleDesc(num_funcs);
+
+			/*
+			 * funcrettype != RECORD, so we have only one return attribute per
+			 * function
+			 */
+			Assert(list_length(rte->eref->colnames) == num_funcs);
+			attnum = 1;
+			forthree(lc, rte->functions, lctype, functypes, lcname, rte->eref->colnames)
+			{
+				char	   *colname;
+
+				rtfunc = (RangeTblFunction *) lfirst(lc);
+				funcrettype = lfirst_oid(lctype);
+				colname = strVal(lfirst(lcname));
+
+				TupleDescInitEntry(td, (AttrNumber) attnum, colname,
+								   funcrettype, -1, 0);
+				TupleDescInitEntryCollation(td, (AttrNumber) attnum,
+											exprCollation(rtfunc->funcexpr));
+				attnum++;
+			}
+
+			assign_record_type_typmod(td);
+			att->atttypmod = td->tdtypmod;
+		}
 	}
 	return tupdesc;
 }
@@ -1514,15 +1633,25 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
 
 	/*
 
 	/* Get info about foreign table. */
@@ -2522,8 +2651,30 @@ postgresPlanDirectModify(PlannerInfo *root,
 		 * node below.
 		 */
 		if (fscan->scan.scanrelid == 0)
+		{
+			ListCell   *lc;
+			Var		   *tid_var = NULL;
+
+			/*
+			 * We should explicitly add tableoid to returning list if it's
+			 * requested
+			 */
+			foreach(lc, processed_tlist)
+			{
+				TargetEntry *tle = lfirst_node(TargetEntry, lc);
+				Var		   *var = (Var *) tle->expr;
+
+				if (IsA(var, Var) && (var->varattno == TableOidAttributeNumber) && (strcmp(tle->resname, "tableoid") == 0))
+				{
+					tid_var = var;
+					break;
+				}
+
+			}
+
 			returningList = build_remote_returning(resultRelation, rel,
-												   returningList);
+												   returningList, tid_var);
+		}
 	}
 
 	/*
@@ -2848,21 +2999,65 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
 				rti += rtoffset;
 				Assert(bms_is_member(rti, plan->fs_relids));
 				rte = rt_fetch(rti, es->rtable);
-				Assert(rte->rtekind == RTE_RELATION);
 				/* This logic should agree with explain.c's ExplainTargetRel */
-				relname = get_rel_name(rte->relid);
-				if (es->verbose)
+				if (rte->rtekind == RTE_RELATION)
 				{
-					char	   *namespace;
-
-					namespace = get_namespace_name_or_temp(get_rel_namespace(rte->relid));
-					appendStringInfo(relations, "%s.%s",
-									 quote_identifier(namespace),
-									 quote_identifier(relname));
+					relname = get_rel_name(rte->relid);
+					if (es->verbose)
+					{
+						char	   *namespace;
+
+						namespace = get_namespace_name(get_rel_namespace(rte->relid));
+						appendStringInfo(relations, "%s.%s",
+										 quote_identifier(namespace),
+										 quote_identifier(relname));
+					}
+					else
+						appendStringInfoString(relations,
+											   quote_identifier(relname));
+				}
+				else if (rte->rtekind == RTE_FUNCTION)
+				{
+					ListCell   *lc;
+					int			n;
+					bool		first = true;
+
+
+					n = list_length(rte->functions);
+
+					if (n > 1)
+						appendStringInfo(relations, "ROWS FROM(");
+					foreach(lc, rte->functions)
+					{
+						RangeTblFunction *rtfunc = (RangeTblFunction *) lfirst(lc);
+
+						if (!first)
+							appendStringInfoString(relations, ", ");
+						else
+							first = false;
+
+						if (IsA(rtfunc->funcexpr, FuncExpr))
+						{
+							FuncExpr   *funcexpr = (FuncExpr *) rtfunc->funcexpr;
+							Oid			funcid = funcexpr->funcid;
+
+							relname = get_func_name(funcid);
+							if (es->verbose)
+							{
+								char	   *namespace;
+
+								namespace = get_namespace_name(get_func_namespace(funcid));
+								appendStringInfo(relations, "%s.%s()",
+												 quote_identifier(namespace),
+												 quote_identifier(relname));
+							}
+							else
+								appendStringInfo(relations, "%s()", quote_identifier(relname));
+						}
+					}
+					if (n > 1)
+						appendStringInfo(relations, ")");
 				}
-				else
-					appendStringInfoString(relations,
-										   quote_identifier(relname));
 				refname = (char *) list_nth(es->rtable_names, rti - 1);
 				if (refname == NULL)
 					refname = rte->eref->aliasname;
@@ -3201,7 +3396,7 @@ estimate_path_cost_size(PlannerInfo *root,
 				/* Shouldn't get here unless we have LIMIT */
 				Assert(fpextra->has_limit);
 				Assert(foreignrel->reloptkind == RELOPT_BASEREL ||
-					   foreignrel->reloptkind == RELOPT_JOINREL);
+					   IS_JOIN_REL(foreignrel));
 				startup_cost += foreignrel->reltarget->cost.startup;
 				run_cost += foreignrel->reltarget->cost.per_tuple * rows;
 			}
@@ -4378,7 +4573,7 @@ deallocate_query(PgFdwModifyState *fmstate)
  *		UPDATE/DELETE .. RETURNING on a join directly
  */
 static List *
-build_remote_returning(Index rtindex, Relation rel, List *returningList)
+build_remote_returning(Index rtindex, Relation rel, List *returningList, Var *tid)
 {
 	bool		have_wholerow = false;
 	List	   *tlist = NIL;
@@ -4387,6 +4582,19 @@ build_remote_returning(Index rtindex, Relation rel, List *returningList)
 
 	Assert(returningList);
 
+	/*
+	 * If tid is requested, add it to the returning list
+	 */
+	if (tid)
+	{
+		tlist = lappend(tlist,
+						makeTargetEntry((Expr *) tid,
+										list_length(tlist) + 1,
+										NULL,
+										false));
+
+	}
+
 	vars = pull_var_clause((Node *) returningList, PVC_INCLUDE_PLACEHOLDERS);
 
 	/*
@@ -4679,11 +4887,13 @@ init_returning_filter(PgFdwDirectModifyState *dmstate,
 			if (attrno < 0)
 			{
 				/*
-				 * We don't retrieve system columns other than ctid and oid.
+				 * We don't retrieve system columns other than ctid and oid,
+				 * but locally-generated tableoid can appear in returning
+				 * list.
 				 */
 				if (attrno == SelfItemPointerAttributeNumber)
 					dmstate->ctidAttno = i;
-				else
+				else if (attrno != TableOidAttributeNumber)
 					Assert(false);
 				dmstate->hasSystemCols = true;
 			}
@@ -5487,6 +5697,128 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
 	return commands;
 }
 
+/*
+ * Determine if foreignrel, not backed by foreign
+ * table, is fine to push down.
+ */
+static bool
+is_nonrel_relinfo_ok(PlannerInfo *root, RelOptInfo *foreignrel)
+{
+	RangeTblEntry *rte;
+	RangeTblFunction *rtfunc;
+
+	rte = planner_rt_fetch(foreignrel->relid, root);
+
+	if (!rte)
+		return false;
+
+	Assert(foreignrel->fdw_private);
+
+	if (rte->rtekind == RTE_FUNCTION)
+	{
+		ListCell   *lc;
+
+		Assert(list_length(rte->functions) >= 1);
+		foreach(lc, rte->functions)
+		{
+			rtfunc = (RangeTblFunction *) lfirst(lc);
+
+			if (contain_var_clause(rtfunc->funcexpr) ||
+				contain_mutable_functions(rtfunc->funcexpr) ||
+				contain_subplans(rtfunc->funcexpr))
+				return false;
+			if (!is_foreign_expr(root, foreignrel, (Expr *) rtfunc->funcexpr))
+				return false;
+		}
+
+		return true;
+	}
+
+	return false;
+}
+
+/*
+ * Check if reltarget is safe enough to push down such join
+ */
+static bool
+joinrel_target_ok(PlannerInfo *root, RelOptInfo *joinrel)
+{
+	List	   *vars;
+	ListCell   *lc;
+	bool		ok = true;
+
+	Assert(joinrel->reltarget);
+
+	/* TODO: is flag correct ? */
+	vars = pull_var_clause((Node *) joinrel->reltarget->exprs, PVC_INCLUDE_PLACEHOLDERS);
+
+	foreach(lc, vars)
+	{
+		Var		   *var = (Var *) lfirst(lc);
+
+		/*
+		 * We can't return abstract records, forbid such foreign joins, except
+		 * cases when we can derive its type
+		 */
+		if (IsA(var, Var) &&
+			var->vartype == RECORDOID &&
+			var->vartypmod < 0)
+		{
+			ok = false;
+
+			if (var->varattno == InvalidAttrNumber)
+			{
+				RangeTblEntry *rte = planner_rt_fetch(var->varno, root);
+
+				if (rte)
+				{
+					if (rte->rtekind == RTE_RELATION)
+					{
+						Oid			reltype;
+
+						reltype = get_rel_type_id(rte->relid);
+						if (OidIsValid(reltype))
+							ok = true;
+					}
+					else if (rte->rtekind == RTE_FUNCTION && !rte->funcordinality)
+					{
+						ListCell   *lc;
+						int			n;
+
+						n = list_length(rte->functions);
+
+						Assert(n >= 1);
+
+						if (n == list_length(rte->eref->colnames))
+						{
+							ok = true;
+							foreach(lc, rte->functions)
+							{
+								RangeTblFunction *rtfunc;
+								TupleDesc	tupdesc;
+								Oid			funcrettype;
+
+								rtfunc = (RangeTblFunction *) lfirst(lc);
+								get_expr_result_type(rtfunc->funcexpr, &funcrettype, &tupdesc);
+
+								if (!OidIsValid(funcrettype) || funcrettype == RECORDOID || funcrettype == VOIDOID)
+								{
+									ok = false;
+									break;
+								}
+							}
+						}
+					}
+				}
+			}
+
+			if (!ok)
+				break;
+		}
+	}
+	return ok;
+}
+
 /*
  * Assess whether the join between inner and outer relations can be pushed down
  * to the foreign server. As a side effect, save information we obtain in this
@@ -5512,6 +5844,12 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
 		jointype != JOIN_RIGHT && jointype != JOIN_FULL)
 		return false;
 
+	/*
+	 * We can't push down join if its reltarget is not safe
+	 */
+	if (!joinrel_target_ok(root, joinrel))
+		return false;
+
 	/*
 	 * If either of the joining relations is marked as unsafe to pushdown, the
 	 * join can not be pushed down.
@@ -6075,6 +6413,43 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
 	/* XXX Consider parameterized paths for the join relation */
 }
 
+/*
+ * postgresTryShippableJoinPaths
+ *
+ * Try to add foreign join of foreign relation with shippable RTE.
+ */
+static void
+postgresTryShippableJoinPaths(PlannerInfo *root,
+							  RelOptInfo *joinrel,
+							  RelOptInfo *outerrel,
+							  RelOptInfo *innerrel,
+							  JoinType jointype,
+							  JoinPathExtraData *extra)
+{
+	PgFdwRelationInfo *fpinfo_o = (PgFdwRelationInfo *) outerrel->fdw_private;
+	PgFdwRelationInfo *fpinfo_i = (PgFdwRelationInfo *) innerrel->fdw_private;
+
+	if (fpinfo_o == NULL)
+		/* Outer path is not foreign relation or foreign JOIN. */
+		return;
+
+	if (joinrel->fdwroutine != NULL || innerrel->reloptkind != RELOPT_BASEREL)
+		return;
+
+	if (fpinfo_i == NULL || fpinfo_i->is_generated)
+		init_fpinfo(root, innerrel, InvalidOid, fpinfo_o);
+
+	if (!is_nonrel_relinfo_ok(root, innerrel))
+		return;
+
+	joinrel->serverid = outerrel->serverid;
+	joinrel->userid = outerrel->userid;
+	joinrel->useridiscurrent = outerrel->useridiscurrent;
+	joinrel->fdwroutine = outerrel->fdwroutine;
+
+	postgresGetForeignJoinPaths(root, joinrel, outerrel, innerrel, jointype, extra);
+}
+
 /*
  * Assess whether the aggregation, grouping and having operations can be pushed
  * down to the foreign server.  As a side effect, save information we obtain in
@@ -6505,7 +6880,7 @@ add_foreign_ordered_paths(PlannerInfo *root, RelOptInfo *input_rel,
 	 * standard_qp_callback()).
 	 */
 	if (input_rel->reloptkind == RELOPT_BASEREL ||
-		input_rel->reloptkind == RELOPT_JOINREL)
+		IS_JOIN_REL(input_rel))
 	{
 		Assert(root->query_pathkeys == root->sort_pathkeys);
 
@@ -6656,11 +7031,11 @@ add_foreign_final_paths(PlannerInfo *root, RelOptInfo *input_rel,
 		 * join relation.
 		 */
 		Assert(input_rel->reloptkind == RELOPT_BASEREL ||
-			   input_rel->reloptkind == RELOPT_JOINREL ||
+			   IS_JOIN_REL(input_rel) ||
 			   (input_rel->reloptkind == RELOPT_UPPER_REL &&
 				ifpinfo->stage == UPPERREL_ORDERED &&
 				(ifpinfo->outerrel->reloptkind == RELOPT_BASEREL ||
-				 ifpinfo->outerrel->reloptkind == RELOPT_JOINREL)));
+				 IS_JOIN_REL(ifpinfo->outerrel))));
 
 		foreach(lc, input_rel->pathlist)
 		{
@@ -6728,7 +7103,7 @@ add_foreign_final_paths(PlannerInfo *root, RelOptInfo *input_rel,
 
 	/* The input_rel should be a base, join, or grouping relation */
 	Assert(input_rel->reloptkind == RELOPT_BASEREL ||
-		   input_rel->reloptkind == RELOPT_JOINREL ||
+		   IS_JOIN_REL(input_rel) ||
 		   (input_rel->reloptkind == RELOPT_UPPER_REL &&
 			ifpinfo->stage == UPPERREL_GROUP_AGG));
 
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 90b72e9ec55..0f599cdef5a 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -52,6 +52,12 @@ typedef struct PgFdwRelationInfo
 	/* True means that the query_pathkeys is safe to push down */
 	bool		qp_is_pushdown_safe;
 
+	/*
+	 * True means that PgFdwRelationInfo is not extracted from catalogs, but
+	 * generated
+	 */
+	bool		is_generated;
+
 	/* Cost and selectivity of local_conds. */
 	QualCost	local_conds_cost;
 	Selectivity local_conds_sel;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 38f4a7837fe..fb3469efe5a 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3422,3 +3422,339 @@ CREATE FOREIGN TABLE inv_fsz (c1 int )
 -- Invalid batch_size option
 CREATE FOREIGN TABLE inv_bsz (c1 int )
 	SERVER loopback OPTIONS (batch_size '100$%$#$#');
+
+-- ===================================================================
+-- test function scan pushdown
+-- ===================================================================
+CREATE TABLE base_tbl (a int, b int);
+CREATE FOREIGN TABLE remote_tbl (a int, b int)
+  SERVER loopback OPTIONS (table_name 'base_tbl');
+ALTER FOREIGN TABLE remote_tbl OPTIONS (use_remote_estimate 'true');
+CREATE TABLE base_tbl1 (c int, d text);
+CREATE FOREIGN TABLE remote_tbl1 (c int, d text)
+  SERVER loopback OPTIONS (table_name 'base_tbl1');
+ALTER FOREIGN TABLE remote_tbl1 OPTIONS (use_remote_estimate 'true');
+
+INSERT INTO remote_tbl SELECT g, g*2 from  generate_series(1,1000) g;
+INSERT INTO remote_tbl1 SELECT g, 'text'|| g from  generate_series(1,500) g;
+ANALYZE base_tbl;
+ANALYZE base_tbl1;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n;
+
+SELECT * FROM remote_tbl r, unnest(array[2,3,4]) n WHERE r.a = n
+ORDER BY r.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n;
+
+SELECT * FROM unnest(array[2,3,4]) n, remote_tbl r WHERE r.a = n
+ORDER BY r.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a;
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a
+ORDER BY r.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT r.*,n from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+
+SELECT * from remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a and n > 3;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT r.*, t.n from remote_tbl1 r, ROWS FROM (unnest(array[3,4]), json_each_text('{"a":"text1", "c":"text4"}')) t (n, k, txt)
+WHERE r.c =  t.n AND r.d = t.txt;
+
+SELECT r.*, t.txt from remote_tbl1 r, ROWS FROM (unnest(array[3,4]), json_each_text('{"a":"text1", "c":"text4"}')) t (n, k, txt)
+WHERE r.c =  t.n AND r.d = t.txt;
+
+-- complex types
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r JOIN UNNEST(array[box '((2,3),(-2,-3))']) as t(bx) ON a = area(bx);
+
+SELECT * FROM remote_tbl r JOIN UNNEST(array[box '((2,3),(-2,-3))']) as t(bx) ON a = area(bx)
+ORDER BY r.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl1 r1 JOIN json_each_text('{"a":"text1", "b":2, "c":"text14"}') ON d = value;
+
+SELECT * FROM remote_tbl1 r1 JOIN json_each_text('{"a":"text1", "b":2, "c":"text14"}') ON d = value
+ORDER BY r1.c;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl1 r1 JOIN json_each_text('{"a":"text1", "b":2, "c":"text14"}') AS t(u,v) ON d = v;
+
+SELECT * FROM remote_tbl1 r1 JOIN json_each_text('{"a":"text1", "b":2, "c":"text14"}') AS t(u,v) ON d = v
+ORDER BY r1.c;
+
+-- DML
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[box '((2,3),(-2,-3))']) AS t (bx) WHERE r.a = area(t.bx)
+RETURNING a,b;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+
+UPDATE remote_tbl r SET b=5 FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+
+UPDATE remote_tbl r SET b=CASE WHEN random()>=0 THEN 5 ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE r.a between l and area(t.bx)
+RETURNING a,b;
+
+-- Test that local functions are not pushed down
+CREATE OR REPLACE FUNCTION f(INTEGER)
+RETURNS SETOF INTEGER
+LANGUAGE sql AS 'select generate_series(1,$1);' IMMUTABLE;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM remote_tbl r, f(10) n
+WHERE r.a = n;
+
+SELECT * FROM remote_tbl r, remote_tbl1 r1, unnest(array[3,4]) n
+WHERE r.a = n AND r1.c = r.a AND n > 3;
+
+-- Test joins with append relations
+
+SET enable_partitionwise_join=on;
+
+-- Partitioned tables and function scan pushdown
+
+CREATE TABLE distr1(a int, b int) PARTITION BY HASH(a);
+CREATE TABLE distr1_part_1 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 4, REMAINDER 0);
+CREATE TABLE distr1_base_2 (a int, b int);
+CREATE FOREIGN TABLE distr1_part_2 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 4, REMAINDER 1)
+	SERVER loopback OPTIONS (table_name 'distr1_base_2', use_remote_estimate 'true');
+CREATE TABLE distr1_base_3 (a int, b int);
+CREATE FOREIGN TABLE distr1_part_3 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 4, REMAINDER 2)
+	SERVER loopback2 OPTIONS (table_name 'distr1_base_3', use_remote_estimate 'true');
+CREATE TABLE distr1_base_4 (a int, b int);
+CREATE FOREIGN TABLE distr1_part_4 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 4, REMAINDER 3)
+	SERVER loopback OPTIONS (table_name 'distr1_base_4', use_remote_estimate 'true');
+
+CREATE TABLE distr2(c int, d text) PARTITION BY HASH(c);
+CREATE TABLE distr2_part_1 PARTITION OF distr2 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
+CREATE TABLE distr2_base_2 (c int, d text);
+CREATE FOREIGN TABLE distr2_part_2 PARTITION OF distr2 FOR VALUES WITH ( MODULUS 4, REMAINDER 1)
+	SERVER loopback OPTIONS (table_name 'distr2_base_2', use_remote_estimate 'true');
+CREATE TABLE distr2_base_3 (c int, d text);
+CREATE FOREIGN TABLE distr2_part_3 PARTITION OF distr2 FOR VALUES WITH ( MODULUS 4, REMAINDER 2)
+	SERVER loopback2 OPTIONS (table_name 'distr2_base_3', use_remote_estimate 'true');
+CREATE TABLE distr2_base_4 (c int, d text);
+CREATE FOREIGN TABLE distr2_part_4 PARTITION OF distr2 FOR VALUES WITH ( MODULUS 4, REMAINDER 3)
+	SERVER loopback OPTIONS (table_name 'distr2_base_4', use_remote_estimate 'true');
+
+INSERT INTO distr1 SELECT g, g*2 from  generate_series(1,1000) g;
+INSERT INTO distr2 SELECT g, 'text'|| g from  generate_series(1,500) g;
+
+ANALYZE distr1;
+ANALYZE distr1_part_1;
+ANALYZE distr1_base_2;
+ANALYZE distr1_base_3;
+ANALYZE distr1_base_4;
+ANALYZE distr2;
+ANALYZE distr2_base_2;
+ANALYZE distr2_base_3;
+ANALYZE distr2_base_4;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM distr1 d, unnest(array[2,3,4,5]) n WHERE d.a = n;
+
+SELECT * FROM distr1 d, unnest(array[2,3,4,5]) n WHERE d.a = n
+ORDER BY d.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM unnest(array[2,3,4,5]) n, distr1 d WHERE d.a = n;
+
+SELECT * FROM unnest(array[2,3,4,5]) n, distr1 d WHERE d.a = n
+ORDER BY d.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM distr1 d1, distr2 d2, unnest(array[3,4]) n
+WHERE d1.a = n AND d2.c = d1.a;
+
+SELECT * FROM distr1 d1, distr2 d2, unnest(array[3,4]) n
+WHERE d1.a = n AND d2.c = d1.a
+ORDER BY d1.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * from distr1 d1, distr2 d2, unnest(array[3,4]) n
+WHERE d1.a = n AND d2.c = d1.a and n > 3;
+
+SELECT * from distr1 d1, distr2 d2, unnest(array[3,4]) n
+WHERE d1.a = n AND d2.c = d1.a and n > 3
+ORDER BY d1.a;
+
+-- Direct update with returning
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE distr1 d1 SET b=t.l FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b;
+
+UPDATE distr1 d1 SET b=t.l FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b;
+
+-- Direct update with returning tableoid
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE distr1 d1 SET b=t.l FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b,tableoid;
+
+UPDATE distr1 d1 SET b=t.l FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b,tableoid;
+
+-- Indirect update with returning
+EXPLAIN (VERBOSE, COSTS OFF)
+UPDATE distr1 d1 SET b=CASE WHEN random()>=0 THEN t.l ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b;
+
+UPDATE distr1 d1 SET b=CASE WHEN random()>=0 THEN t.l ELSE 0 END FROM UNNEST(array[10,20], array[box '((2,3),(-2,-4))', box '((1,2),(-2,-3))']) AS t (l, bx) WHERE d1.a between l and area(t.bx)
+RETURNING a,b;
+
+DROP TABLE distr1, distr2, distr1_base_2, distr2_base_2, distr1_base_3, distr2_base_3, distr1_base_4, distr2_base_4;
+
+-- Test pushdown of several function scans
+
+CREATE TABLE distr1(a int, b int) PARTITION BY HASH(a);
+CREATE TABLE distr1_part_1 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 2, REMAINDER 0);
+CREATE TABLE distr1_base_2 (a int, b int);
+CREATE FOREIGN TABLE distr1_part_2 PARTITION OF distr1 FOR VALUES WITH ( MODULUS 2, REMAINDER 1)
+	SERVER loopback OPTIONS (table_name 'distr1_base_2', use_remote_estimate 'true');
+
+CREATE TABLE distr2(c int, d text) PARTITION BY HASH(c);
+CREATE TABLE distr2_part_1 PARTITION OF distr2 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
+CREATE TABLE distr2_base_2 (c int, d text);
+CREATE FOREIGN TABLE distr2_part_2 PARTITION OF distr2 FOR VALUES WITH ( MODULUS 2, REMAINDER 1)
+	SERVER loopback OPTIONS (table_name 'distr2_base_2', use_remote_estimate 'true');
+
+INSERT INTO distr1 SELECT g, g*2 from  generate_series(1,1000) g;
+INSERT INTO distr2 SELECT g, 'text'|| g from  generate_series(1,500) g;
+
+ANALYZE;
+
+-- Make local function scan not so attractive
+ALTER SERVER loopback OPTIONS (ADD fdw_tuple_cost '1000');
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * from distr1 d1, distr1 d2, unnest(array[3,4]) n, unnest(array[3,4]) g
+WHERE d1.a = n AND d2.a = d1.a and g = n;
+
+SELECT * from distr1 d1, distr2 d2, unnest(array[3,4]) n, generate_series(1,10000) g
+WHERE d1.a = n AND d2.c = d1.a and g = d1.a
+ORDER BY d1.a;
+
+ALTER SERVER loopback OPTIONS (DROP fdw_tuple_cost);
+
+DROP TABLE distr1, distr2, distr1_base_2, distr2_base_2;
+
+-- Test inheritance chain and function scan pushdown
+
+CREATE TABLE distr1(a int, b int);
+CREATE TABLE distr1_loc (a int, b int) INHERITS (distr1);
+CREATE TABLE distr1_base (a int, b int, c text);
+CREATE FOREIGN TABLE distr1_remote (a int, b int, c text) INHERITS (distr1)
+	SERVER loopback OPTIONS (table_name 'distr1_base', use_remote_estimate 'true');
+
+CREATE TABLE distr2(c int, d text);
+CREATE TABLE distr2_loc (c int, d text) INHERITS (distr2);
+CREATE TABLE distr2_base (c int, d text, e int);
+CREATE FOREIGN TABLE distr2_remote (c int, d text, e int) INHERITS (distr2)
+	SERVER loopback OPTIONS (table_name 'distr2_base', use_remote_estimate 'true');
+
+INSERT INTO distr1_loc SELECT g, g*2 from  generate_series(1,100) g;
+INSERT INTO distr1_base SELECT g, g*2, 'text'|| g from  generate_series(200,20000) g;
+
+INSERT INTO distr2_loc SELECT g, 'text'|| g from generate_series(1,100) g;
+INSERT INTO distr2_base SELECT g, 'text'|| g, g*2 from generate_series(200,20000) g;
+
+ANALYZE distr1, distr1_loc, distr1_base;
+ANALYZE distr2, distr2_loc, distr2_base;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM distr1 d, unnest(array[2,3,4,5]) n WHERE d.a = n;
+
+SELECT * FROM distr1 d, unnest(array[2,3,4,5]) n WHERE d.a = n
+ORDER BY d.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM unnest(array[2,3,4,5]) n, distr1 d WHERE d.a = n;
+
+SELECT * FROM unnest(array[2,3,4,5]) n, distr1 d WHERE d.a = n
+ORDER BY d.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM distr1 d1, distr2 d2, unnest(array[300,400]) n
+WHERE d1.a = n AND d2.c = d1.a;
+
+SELECT * FROM distr1 d1, distr2 d2, unnest(array[300,400]) n
+WHERE d1.a = n AND d2.c = d1.a
+ORDER BY d1.a;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * from distr1 d1, distr2 d2, unnest(array[300,400]) n
+WHERE d1.a = n AND d2.c = d1.a and n > 3;
+
+SELECT * from distr1 d1, distr2 d2, unnest(array[300,400]) n
+WHERE d1.a = n AND d2.c = d1.a and n > 3
+ORDER BY d1.a;
+
+DROP FOREIGN TABLE distr1_remote, distr2_remote;
+DROP TABLE distr1, distr1_loc, distr1_base, distr2, distr2_loc, distr2_base;
+
+-- Test UNION and function scan pushdown
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM
+(SELECT a FROM remote_tbl
+UNION
+SELECT c FROM remote_tbl1) u
+JOIN unnest(array[3,4]) n
+ON u.a = n;
+
+SELECT * FROM
+(SELECT a FROM remote_tbl
+UNION
+SELECT c FROM remote_tbl1) u
+JOIN unnest(array[3,4]) n
+ON u.a = n
+ORDER BY n;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM
+(SELECT a FROM remote_tbl
+UNION ALL
+SELECT c FROM remote_tbl1) u
+JOIN unnest(array[3,4]) n
+ON u.a = n;
+
+SELECT * FROM
+(SELECT a FROM remote_tbl
+UNION ALL
+SELECT c FROM remote_tbl1) u
+JOIN unnest(array[3,4]) n
+ON u.a = n
+ORDER BY n;
+
+RESET enable_partitionwise_join;
+
+DROP FUNCTION f(INTEGER);
+DROP TABLE base_tbl, base_tbl1;
+DROP FOREIGN TABLE remote_tbl, remote_tbl1;
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 32618ebbd51..c8b12c9360d 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -329,6 +329,17 @@ add_paths_to_joinrel(PlannerInfo *root,
 												 outerrel, innerrel,
 												 jointype, &extra);
 
+	/*
+	 * If push down of join is not possible we can try to join foreign
+	 * relation with shippable RTE. In this case we have a chance to push down
+	 * this join yet.
+	 */
+	else if (outerrel->fdwroutine &&
+			 outerrel->fdwroutine->TryShippableJoinPaths)
+		outerrel->fdwroutine->TryShippableJoinPaths(root, joinrel,
+													outerrel, innerrel,
+													jointype, &extra);
+
 	/*
 	 * 6. Finally, give extensions a chance to manipulate the path list.
 	 */
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 02529da562b..172122ed4d9 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -500,7 +500,6 @@ add_rte_to_flat_rtable(PlannerGlobal *glob, RangeTblEntry *rte)
 	newrte->joinleftcols = NIL;
 	newrte->joinrightcols = NIL;
 	newrte->join_using_alias = NULL;
-	newrte->functions = NIL;
 	newrte->tablefunc = NULL;
 	newrte->values_lists = NIL;
 	newrte->coltypes = NIL;
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index ddf0f5a8765..9675aa6b3ab 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -22,12 +22,14 @@
 #include "optimizer/clauses.h"
 #include "optimizer/cost.h"
 #include "optimizer/inherit.h"
+#include "optimizer/optimizer.h"
 #include "optimizer/pathnode.h"
 #include "optimizer/paths.h"
 #include "optimizer/placeholder.h"
 #include "optimizer/plancat.h"
 #include "optimizer/restrictinfo.h"
 #include "optimizer/tlist.h"
+#include "parser/parsetree.h"
 #include "utils/hsearch.h"
 #include "utils/lsyscache.h"
 
diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h
index a801cd30576..58555094da3 100644
--- a/src/include/foreign/fdwapi.h
+++ b/src/include/foreign/fdwapi.h
@@ -221,6 +221,7 @@ typedef struct FdwRoutine
 
 	/* Functions for remote-join planning */
 	GetForeignJoinPaths_function GetForeignJoinPaths;
+	GetForeignJoinPaths_function TryShippableJoinPaths;
 
 	/* Functions for remote upper-relation (post scan/join) planning */
 	GetForeignUpperPaths_function GetForeignUpperPaths;
-- 
2.25.1