public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Pyhalov <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Function scan FDW pushdown
Date: Mon, 04 Oct 2021 10:42:56 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAExHW5sR0FzzwevwHXF-WcuZMJ0K=TDqhu3K_VDnrdamaPhwjA@mail.gmail.com>
References: <[email protected]>
<CAExHW5sR0FzzwevwHXF-WcuZMJ0K=TDqhu3K_VDnrdamaPhwjA@mail.gmail.com>
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/[email protected]
.
>> 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
Attachments:
[text/x-diff] 1-0001-Push-join-with-function-scan-to-remote-server.patch (103.6K, ../[email protected]/2-1-0001-Push-join-with-function-scan-to-remote-server.patch)
download | inline diff:
From d997c313daf0031b812d3fca59d338be1a4f2196 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <[email protected]>
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
view thread (12+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Function scan FDW pushdown
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox