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