Ashutosh Bapat писал 2021-06-15 16:15:
Hi Alexander,
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/792d60f4-37bc-e6ad-68ca-c2af5cbb2...@postgrespro.ru
.
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.
The actual function expression will be part of the Remote SQL of
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.
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.
I suppose attached version of the patch is more mature.
The patch targets only postgres FDW, how do you see this working with
other FDWs?
Not now. We introduce necessary APIs for other FDWs, but implementing
TryShippableJoinPaths()
doesn't seem straightforward.
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 regards,
Alexander Pyhalov,
Postgres Professional
From d997c313daf0031b812d3fca59d338be1a4f2196 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
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);
- /*
- * 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;
+ 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");
- table_close(rel, NoLock);
- bms_free(attrs_used);
}
else
{
@@ -2462,29 +2548,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)
+ {
+ colname = get_rte_attribute_name(rte, varattno);
+
+ if (qualify_col)
+ ADD_REL_QUALIFIER(buf, varno);
+ appendStringInfoString(buf, quote_identifier(colname));
+ }
}
}
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)
/*
* 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. */
@@ -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
