v4-0001-postgres_fdw-push-down-FUNCTION-RTE-into-foreign-.patch
application/octet-stream
Filename: v4-0001-postgres_fdw-push-down-FUNCTION-RTE-into-foreign-.patch
Type: application/octet-stream
Part: 0
Message:
Re: Function scan FDW pushdown
From c111e195bd683b335565e6a62caf37b3ca1be961 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Sun, 10 May 2026 17:15:21 +0300
Subject: [PATCH v4] postgres_fdw: push down FUNCTION RTE into foreign joins
A foreign join planning hook now considers a (foreign-table x function-RTE)
INNER join as a push-down candidate when the function expression is
IMMUTABLE and otherwise shippable. The remote query absorbs the function
call as a FROM-list item (e.g. unnest(...) AS f<rti>(c1, c2, ...)), so the
foreign side returns only rows that match the function-produced set and the
join executes entirely on the remote.
An IMMUTABLE function gives the same result on any server, so the same
function RTE can be a push-down candidate for several distinct foreign
servers without semantic risk. To keep the planner state consistent
across those independent attempts, the per-call stub fpinfo for the
function side lives on the joinrel's PgFdwRelationInfo (new
outer_func_fpinfo / inner_func_fpinfo), never on the function rel itself,
and the function side is detected via rtekind rather than fdw_private.
set_foreign_rel_properties() propagates fdwroutine onto a joinrel that
pairs a foreign rel with an RTE_FUNCTION rel so GetForeignJoinPaths gets
called; the FDW retains full control over whether to actually generate a
path. deparseRangeTblRef and deparseColumnRef gain a FUNCTION-RTE branch
that emits the function expression and resolves Vars to the generated
column aliases.
---
contrib/postgres_fdw/deparse.c | 77 +++++++-
.../postgres_fdw/expected/postgres_fdw.out | 168 ++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 136 +++++++++++++-
contrib/postgres_fdw/postgres_fdw.h | 10 ++
contrib/postgres_fdw/sql/postgres_fdw.sql | 57 ++++++
src/backend/optimizer/util/relnode.c | 24 +++
6 files changed, 462 insertions(+), 10 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 2dcc6c8af1b..e8449569ef8 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -112,6 +112,7 @@ typedef struct deparse_expr_cxt
appendStringInfo((buf), "%s%d.", REL_ALIAS_PREFIX, (varno))
#define SUBQUERY_REL_ALIAS_PREFIX "s"
#define SUBQUERY_COL_ALIAS_PREFIX "c"
+#define FUNCTION_REL_ALIAS_PREFIX "f"
/*
* Functions to determine whether an expression can be evaluated safely on
@@ -2030,6 +2031,51 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
}
}
+/*
+ * Deparse a FUNCTION RTE that is being absorbed into a foreign join. The
+ * function expression is emitted as the FROM-list item, with a generated
+ * alias and column-name list.
+ */
+static void
+deparseFunctionRangeTblRef(StringInfo buf, PlannerInfo *root,
+ RelOptInfo *foreignrel, RelOptInfo *scanrel,
+ List **params_list)
+{
+ RangeTblEntry *rte = planner_rt_fetch(foreignrel->relid, root);
+ RangeTblFunction *rtfunc;
+ deparse_expr_cxt context;
+ int i;
+
+ Assert(rte->rtekind == RTE_FUNCTION);
+ Assert(list_length(rte->functions) == 1);
+ Assert(!rte->funcordinality);
+
+ rtfunc = (RangeTblFunction *) linitial(rte->functions);
+
+ context.buf = buf;
+ context.root = root;
+ context.foreignrel = scanrel;
+ context.scanrel = scanrel;
+ context.params_list = params_list;
+
+ /* Emit the function expression itself. */
+ deparseExpr((Expr *) rtfunc->funcexpr, &context);
+
+ /* Emit alias and generated column names. */
+ appendStringInfo(buf, " %s%d", FUNCTION_REL_ALIAS_PREFIX, foreignrel->relid);
+ if (rtfunc->funccolcount > 0)
+ {
+ appendStringInfoChar(buf, '(');
+ for (i = 1; i <= rtfunc->funccolcount; i++)
+ {
+ if (i > 1)
+ appendStringInfoString(buf, ", ");
+ appendStringInfo(buf, "%s%d", SUBQUERY_COL_ALIAS_PREFIX, i);
+ }
+ appendStringInfoChar(buf, ')');
+ }
+}
+
/*
* Append FROM clause entry for the given relation into buf.
* Conditions from lower-level SEMI-JOINs are appended to additional_conds
@@ -2040,7 +2086,22 @@ deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
bool make_subquery, Index ignore_rel, List **ignore_conds,
List **additional_conds, List **params_list)
{
- PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ PgFdwRelationInfo *fpinfo;
+
+ /*
+ * For a function RTE absorbed into a foreign join, deparse the function
+ * expression as a FROM-list item and return. The stub fpinfo set up by
+ * foreign_join_ok() may or may not be present here.
+ */
+ if (foreignrel->rtekind == RTE_FUNCTION)
+ {
+ Assert(!make_subquery);
+ deparseFunctionRangeTblRef(buf, root, foreignrel, foreignrel,
+ params_list);
+ return;
+ }
+
+ fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
/* Should only be called in these cases. */
Assert(IS_SIMPLE_REL(foreignrel) || IS_JOIN_REL(foreignrel));
@@ -2712,6 +2773,20 @@ static void
deparseColumnRef(StringInfo buf, int varno, int varattno, RangeTblEntry *rte,
bool qualify_col)
{
+ /*
+ * Function RTE columns: emit as f<varno>.c<varattno>, matching the
+ * aliases generated by deparseFunctionRangeTblRef(). Note that ctid
+ * and other system attributes are not meaningful for function RTEs.
+ */
+ if (rte->rtekind == RTE_FUNCTION)
+ {
+ Assert(varattno > 0);
+ if (qualify_col)
+ appendStringInfo(buf, "%s%d.", FUNCTION_REL_ALIAS_PREFIX, varno);
+ appendStringInfo(buf, "%s%d", SUBQUERY_COL_ALIAS_PREFIX, varattno);
+ return;
+ }
+
/* We support fetching the remote side's CTID and OID. */
if (varattno == SelfItemPointerAttributeNumber)
{
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index aaffcf31271..db8d0103340 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2885,6 +2885,174 @@ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c
(10 rows)
ALTER VIEW v4 OWNER TO regress_view_owner;
+-- ===================================================================
+-- Foreign-join with FUNCTION RTE pushdown (IMMUTABLE functions only)
+-- ===================================================================
+-- IMMUTABLE function: unnest of constant array can be pushed down
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1, t1.c3 FROM ft1 t1, unnest(ARRAY[1, 5, 10, 100]::int[]) AS u(id)
+WHERE t1.c1 = u.id ORDER BY t1.c1;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: t1.c1, t1.c3
+ Sort Key: t1.c1
+ -> Foreign Scan
+ Output: t1.c1, t1.c3
+ Relations: (public.ft1 t1) INNER JOIN (Function u)
+ Remote SQL: SELECT r1."C 1", r1.c3 FROM ("S 1"."T 1" r1 INNER JOIN unnest('{1,5,10,100}'::integer[]) f2(c1) ON (((r1."C 1" = f2.c1))))
+(7 rows)
+
+SELECT t1.c1, t1.c3 FROM ft1 t1, unnest(ARRAY[1, 5, 10, 100]::int[]) AS u(id)
+WHERE t1.c1 = u.id ORDER BY t1.c1;
+ c1 | c3
+-----+-------
+ 1 | 00001
+ 5 | 00005
+ 10 | 00010
+ 100 | 00100
+(4 rows)
+
+-- IMMUTABLE function: generate_series with constant args
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1 FROM ft1 t1, generate_series(1, 4) AS g(id)
+WHERE t1.c1 = g.id ORDER BY t1.c1;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: t1.c1
+ Sort Key: t1.c1
+ -> Foreign Scan
+ Output: t1.c1
+ Relations: (public.ft1 t1) INNER JOIN (Function g)
+ Remote SQL: SELECT r1."C 1" FROM ("S 1"."T 1" r1 INNER JOIN generate_series(1, 4) f2(c1) ON (((r1."C 1" = f2.c1))))
+(7 rows)
+
+SELECT t1.c1 FROM ft1 t1, generate_series(1, 4) AS g(id)
+WHERE t1.c1 = g.id ORDER BY t1.c1;
+ c1
+----
+ 1
+ 2
+ 3
+ 4
+(4 rows)
+
+-- VOLATILE function (random) must NOT be pushed down
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1 FROM ft1 t1, generate_series(1, 4 + (random() * 0)::int) AS g(id)
+WHERE t1.c1 = g.id;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Hash Join
+ Output: t1.c1
+ Hash Cond: (g.id = t1.c1)
+ -> Function Scan on pg_catalog.generate_series g
+ Output: g.id
+ Function Call: generate_series(1, (4 + ((random() * '0'::double precision))::integer))
+ -> Hash
+ Output: t1.c1
+ -> Foreign Scan on public.ft1 t1
+ Output: t1.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
+(11 rows)
+
+-- WITH ORDINALITY must NOT be pushed down (limitation of this implementation)
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1, u.ord
+FROM ft1 t1, unnest(ARRAY[1, 5, 10]::int[]) WITH ORDINALITY AS u(id, ord)
+WHERE t1.c1 = u.id;
+ QUERY PLAN
+------------------------------------------------------------
+ Hash Join
+ Output: t1.c1, u.ord
+ Hash Cond: (t1.c1 = u.id)
+ -> Foreign Scan on public.ft1 t1
+ Output: t1.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
+ -> Hash
+ Output: u.ord, u.id
+ -> Function Scan on pg_catalog.unnest u
+ Output: u.ord, u.id
+ Function Call: unnest('{1,5,10}'::integer[])
+(11 rows)
+
+-- Same function RTE joined with two different foreign servers: planner picks
+-- one absorption + a local join with the second foreign server. The fact
+-- that the function is IMMUTABLE makes this safe even if both sides chose to
+-- absorb it independently.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1, t2.c1
+FROM ft1 t1, ft6 t2, unnest(ARRAY[1, 5, 10, 100]::int[]) AS u(id)
+WHERE t1.c1 = u.id AND t2.c1 = u.id ORDER BY t1.c1;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------
+ Merge Join
+ Output: t1.c1, t2.c1
+ Merge Cond: (t1.c1 = u.id)
+ -> Foreign Scan on public.ft1 t1
+ Output: t1.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
+ -> Sort
+ Output: t2.c1, u.id
+ Sort Key: t2.c1
+ -> Foreign Scan
+ Output: t2.c1, u.id
+ Relations: (public.ft6 t2) INNER JOIN (Function u)
+ Remote SQL: SELECT r2.c1, f3.c1 FROM ("S 1"."T 4" r2 INNER JOIN unnest('{1,5,10,100}'::integer[]) f3(c1) ON (((r2.c1 = f3.c1))))
+(13 rows)
+
+-- Cost-based selection between two foreign servers: ft1 ("S 1"."T 1") has
+-- 1000 rows, ft6 ("S 1"."T 4") has ~33 rows. The same query shape gets a
+-- different push-down target depending on a predicate that changes the
+-- effective cardinality of one side -- the function "jumps" to whichever
+-- foreign scan benefits more from being pre-filtered.
+ANALYZE ft1;
+ANALYZE ft6;
+-- No extra predicate: ft1 is the bigger side, function absorbed there.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1, t2.c1
+FROM ft1 t1, ft6 t2, unnest(ARRAY[3, 6, 9, 12, 15, 18]::int[]) AS u(id)
+WHERE t1.c1 = u.id AND t2.c1 = u.id;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Hash Join
+ Output: t1.c1, t2.c1
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Foreign Scan
+ Output: t1.c1, u.id
+ Relations: (public.ft1 t1) INNER JOIN (Function u)
+ Remote SQL: SELECT r1."C 1", f3.c1 FROM ("S 1"."T 1" r1 INNER JOIN unnest('{3,6,9,12,15,18}'::integer[]) f3(c1) ON (((r1."C 1" = f3.c1))))
+ -> Hash
+ Output: t2.c1
+ -> Foreign Scan on public.ft6 t2
+ Output: t2.c1
+ Remote SQL: SELECT c1 FROM "S 1"."T 4"
+(12 rows)
+
+-- Selective predicate on ft1.c3 (not in the eqclass) shrinks ft1 to a
+-- handful of remote rows; now ft6 is effectively the bigger side and the
+-- function is absorbed into ft6 instead.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1, t2.c1
+FROM ft1 t1, ft6 t2, unnest(ARRAY[3, 6, 9, 12, 15, 18]::int[]) AS u(id)
+WHERE t1.c1 = u.id AND t2.c1 = u.id AND t1.c3 < '00010';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: t1.c1, t2.c1
+ Join Filter: (t1.c1 = u.id)
+ -> Foreign Scan on public.ft1 t1
+ Output: t1.c1
+ Remote SQL: SELECT "C 1" FROM "S 1"."T 1" WHERE ((c3 < '00010'))
+ -> Materialize
+ Output: t2.c1, u.id
+ -> Foreign Scan
+ Output: t2.c1, u.id
+ Relations: (public.ft6 t2) INNER JOIN (Function u)
+ Remote SQL: SELECT r2.c1, f3.c1 FROM ("S 1"."T 4" r2 INNER JOIN unnest('{3,6,9,12,15,18}'::integer[]) f3(c1) ON (((r2.c1 = f3.c1))))
+(12 rows)
+
-- ====================================================================
-- Check that userid to use when querying the remote table is correctly
-- propagated into foreign rels present in subqueries under an UNION ALL
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 0ff4ec23164..57708bf0baa 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3104,6 +3104,21 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
rti += rtoffset;
Assert(bms_is_member(rti, plan->fs_base_relids));
rte = rt_fetch(rti, es->rtable);
+
+ /*
+ * If a function RTE was absorbed into the foreign join, render
+ * it as "Function <alias>" since we have no foreign relid.
+ */
+ if (rte->rtekind == RTE_FUNCTION)
+ {
+ refname = (char *) list_nth(es->rtable_names, rti - 1);
+ if (refname == NULL)
+ refname = rte->eref->aliasname;
+ appendStringInfo(&relations, "Function %s",
+ quote_identifier(refname));
+ continue;
+ }
+
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
@@ -3479,8 +3494,17 @@ estimate_path_cost_size(PlannerInfo *root,
/* For join we expect inner and outer relations set */
Assert(fpinfo->innerrel && fpinfo->outerrel);
- fpinfo_i = (PgFdwRelationInfo *) fpinfo->innerrel->fdw_private;
- fpinfo_o = (PgFdwRelationInfo *) fpinfo->outerrel->fdw_private;
+ /*
+ * For a FUNCTION RTE absorbed into the join, use the stub
+ * fpinfo we built in foreign_join_ok(), since the function
+ * rel itself has no fdw_private.
+ */
+ fpinfo_i = fpinfo->inner_func_fpinfo ?
+ fpinfo->inner_func_fpinfo :
+ (PgFdwRelationInfo *) fpinfo->innerrel->fdw_private;
+ fpinfo_o = fpinfo->outer_func_fpinfo ?
+ fpinfo->outer_func_fpinfo :
+ (PgFdwRelationInfo *) fpinfo->outerrel->fdw_private;
/* Estimate of number of rows in cross product */
nrows = fpinfo_i->rows * fpinfo_o->rows;
@@ -6619,6 +6643,42 @@ semijoin_target_ok(PlannerInfo *root, RelOptInfo *joinrel, RelOptInfo *outerrel,
return ok;
}
+/*
+ * Check if a relation is a FUNCTION RTE that can be absorbed into a remote
+ * join. Sets *funcexpr_p to the function expression on success. We only
+ * accept function RTEs with a single function and no WITH ORDINALITY, and we
+ * leave the volatility/shippability check to is_foreign_expr().
+ */
+static bool
+function_rte_pushdown_ok(PlannerInfo *root, RelOptInfo *rel, RelOptInfo *fdwrel,
+ Expr **funcexpr_p)
+{
+ RangeTblEntry *rte;
+ RangeTblFunction *rtfunc;
+
+ if (rel->rtekind != RTE_FUNCTION)
+ return false;
+ rte = planner_rt_fetch(rel->relid, root);
+ if (rte->rtekind != RTE_FUNCTION)
+ return false;
+ if (list_length(rte->functions) != 1)
+ return false;
+ if (rte->funcordinality)
+ return false;
+
+ rtfunc = (RangeTblFunction *) linitial(rte->functions);
+
+ /*
+ * is_foreign_expr() rejects volatile expressions and checks shippability
+ * of every node, so it suffices for the IMMUTABLE-and-shippable test.
+ */
+ if (!is_foreign_expr(root, fdwrel, (Expr *) rtfunc->funcexpr))
+ return false;
+
+ *funcexpr_p = (Expr *) rtfunc->funcexpr;
+ return true;
+}
+
/*
* 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
@@ -6634,6 +6694,9 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
PgFdwRelationInfo *fpinfo_i;
ListCell *lc;
List *joinclauses;
+ Expr *funcexpr = NULL;
+ bool outer_is_function = false;
+ bool inner_is_function = false;
/*
* We support pushing down INNER, LEFT, RIGHT, FULL OUTER and SEMI joins.
@@ -6652,15 +6715,70 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
return false;
/*
- * If either of the joining relations is marked as unsafe to pushdown, the
- * join can not be pushed down.
+ * Detect mixed (foreign x function-RTE) cases. Only INNER joins are
+ * supported initially. We dispatch on rtekind here so that the same
+ * function RTE can be absorbed into joins on multiple foreign servers
+ * (each call gets its own stub fpinfo and rechecks shippability for the
+ * specific server).
*/
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;
+ if (jointype == JOIN_INNER && innerrel->rtekind == RTE_FUNCTION &&
+ (fpinfo_o = (PgFdwRelationInfo *) outerrel->fdw_private) &&
+ fpinfo_o->pushdown_safe &&
+ function_rte_pushdown_ok(root, innerrel, outerrel, &funcexpr))
+ {
+ inner_is_function = true;
+ }
+ else if (jointype == JOIN_INNER && outerrel->rtekind == RTE_FUNCTION &&
+ (fpinfo_i = (PgFdwRelationInfo *) innerrel->fdw_private) &&
+ fpinfo_i->pushdown_safe &&
+ function_rte_pushdown_ok(root, outerrel, innerrel, &funcexpr))
+ {
+ outer_is_function = true;
+ }
+ else
+ {
+ 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;
+ }
+
+ /*
+ * If one side is a function RTE, allocate a stub fpinfo so the rest of
+ * this function and the cost estimator can treat it uniformly. We hand
+ * the stub to the joinrel's deparser via the same path the foreign side
+ * uses, but we never permanently attach it to the function rel's
+ * fdw_private (different joinrels may pair the same function RTE with
+ * different foreign servers).
+ */
+ if (inner_is_function)
+ {
+ fpinfo_i = palloc0_object(PgFdwRelationInfo);
+ fpinfo_i->pushdown_safe = true;
+ fpinfo_i->server = fpinfo_o->server;
+ fpinfo_i->relation_name = psprintf("%u", innerrel->relid);
+ fpinfo_i->rows = innerrel->rows;
+ fpinfo_i->width = innerrel->reltarget->width;
+ fpinfo_i->retrieved_rows = innerrel->rows;
+ fpinfo_i->rel_startup_cost = 0;
+ fpinfo_i->rel_total_cost = 0;
+ fpinfo->inner_func_fpinfo = fpinfo_i;
+ }
+ else if (outer_is_function)
+ {
+ fpinfo_o = palloc0_object(PgFdwRelationInfo);
+ fpinfo_o->pushdown_safe = true;
+ fpinfo_o->server = fpinfo_i->server;
+ fpinfo_o->relation_name = psprintf("%u", outerrel->relid);
+ fpinfo_o->rows = outerrel->rows;
+ fpinfo_o->width = outerrel->reltarget->width;
+ fpinfo_o->retrieved_rows = outerrel->rows;
+ fpinfo_o->rel_startup_cost = 0;
+ fpinfo_o->rel_total_cost = 0;
+ fpinfo->outer_func_fpinfo = fpinfo_o;
+ }
/*
* If joining relations have local conditions, those conditions are
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index a2bb1ff352c..b3f1dc0d2b2 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -106,6 +106,16 @@ typedef struct PgFdwRelationInfo
/* joinclauses contains only JOIN/ON conditions for an outer join */
List *joinclauses; /* List of RestrictInfo */
+ /*
+ * If a FUNCTION RTE was absorbed into this join, these point at the
+ * stub PgFdwRelationInfo for the function side (paired with the
+ * outerrel/innerrel), so the cost estimator and deparser can find it
+ * without consulting the function rel's fdw_private. At most one of
+ * outer_func_fpinfo/inner_func_fpinfo is set.
+ */
+ struct PgFdwRelationInfo *outer_func_fpinfo;
+ struct PgFdwRelationInfo *inner_func_fpinfo;
+
/* Upper relation information */
UpperRelationKind stage;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 267d3c1a7e7..a468b564b72 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -790,6 +790,63 @@ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c
SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 10 LIMIT 10;
ALTER VIEW v4 OWNER TO regress_view_owner;
+-- ===================================================================
+-- Foreign-join with FUNCTION RTE pushdown (IMMUTABLE functions only)
+-- ===================================================================
+-- IMMUTABLE function: unnest of constant array can be pushed down
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1, t1.c3 FROM ft1 t1, unnest(ARRAY[1, 5, 10, 100]::int[]) AS u(id)
+WHERE t1.c1 = u.id ORDER BY t1.c1;
+SELECT t1.c1, t1.c3 FROM ft1 t1, unnest(ARRAY[1, 5, 10, 100]::int[]) AS u(id)
+WHERE t1.c1 = u.id ORDER BY t1.c1;
+
+-- IMMUTABLE function: generate_series with constant args
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1 FROM ft1 t1, generate_series(1, 4) AS g(id)
+WHERE t1.c1 = g.id ORDER BY t1.c1;
+SELECT t1.c1 FROM ft1 t1, generate_series(1, 4) AS g(id)
+WHERE t1.c1 = g.id ORDER BY t1.c1;
+
+-- VOLATILE function (random) must NOT be pushed down
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1 FROM ft1 t1, generate_series(1, 4 + (random() * 0)::int) AS g(id)
+WHERE t1.c1 = g.id;
+
+-- WITH ORDINALITY must NOT be pushed down (limitation of this implementation)
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1, u.ord
+FROM ft1 t1, unnest(ARRAY[1, 5, 10]::int[]) WITH ORDINALITY AS u(id, ord)
+WHERE t1.c1 = u.id;
+
+-- Same function RTE joined with two different foreign servers: planner picks
+-- one absorption + a local join with the second foreign server. The fact
+-- that the function is IMMUTABLE makes this safe even if both sides chose to
+-- absorb it independently.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1, t2.c1
+FROM ft1 t1, ft6 t2, unnest(ARRAY[1, 5, 10, 100]::int[]) AS u(id)
+WHERE t1.c1 = u.id AND t2.c1 = u.id ORDER BY t1.c1;
+
+-- Cost-based selection between two foreign servers: ft1 ("S 1"."T 1") has
+-- 1000 rows, ft6 ("S 1"."T 4") has ~33 rows. The same query shape gets a
+-- different push-down target depending on a predicate that changes the
+-- effective cardinality of one side -- the function "jumps" to whichever
+-- foreign scan benefits more from being pre-filtered.
+ANALYZE ft1;
+ANALYZE ft6;
+-- No extra predicate: ft1 is the bigger side, function absorbed there.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1, t2.c1
+FROM ft1 t1, ft6 t2, unnest(ARRAY[3, 6, 9, 12, 15, 18]::int[]) AS u(id)
+WHERE t1.c1 = u.id AND t2.c1 = u.id;
+-- Selective predicate on ft1.c3 (not in the eqclass) shrinks ft1 to a
+-- handful of remote rows; now ft6 is effectively the bigger side and the
+-- function is absorbed into ft6 instead.
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.c1, t2.c1
+FROM ft1 t1, ft6 t2, unnest(ARRAY[3, 6, 9, 12, 15, 18]::int[]) AS u(id)
+WHERE t1.c1 = u.id AND t2.c1 = u.id AND t1.c3 < '00010';
+
-- ====================================================================
-- Check that userid to use when querying the remote table is correctly
-- propagated into foreign rels present in subqueries under an UNION ALL
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 3fc2c2f71d0..f21ae1baeb2 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -746,6 +746,30 @@ set_foreign_rel_properties(RelOptInfo *joinrel, RelOptInfo *outer_rel,
joinrel->fdwroutine = outer_rel->fdwroutine;
}
}
+ else if (OidIsValid(outer_rel->serverid) &&
+ inner_rel->rtekind == RTE_FUNCTION)
+ {
+ /*
+ * One side is a foreign relation, the other side is a function RTE.
+ * If the function is IMMUTABLE, the FDW can absorb the function call
+ * into the remote query (the result is identical regardless of which
+ * server evaluates it). Let the FDW decide whether the join is
+ * actually shippable; here we just propagate the FDW routine so the
+ * FDW gets a chance.
+ */
+ joinrel->serverid = outer_rel->serverid;
+ joinrel->userid = outer_rel->userid;
+ joinrel->useridiscurrent = outer_rel->useridiscurrent;
+ joinrel->fdwroutine = outer_rel->fdwroutine;
+ }
+ else if (OidIsValid(inner_rel->serverid) &&
+ outer_rel->rtekind == RTE_FUNCTION)
+ {
+ joinrel->serverid = inner_rel->serverid;
+ joinrel->userid = inner_rel->userid;
+ joinrel->useridiscurrent = inner_rel->useridiscurrent;
+ joinrel->fdwroutine = inner_rel->fdwroutine;
+ }
}
/*
--
2.39.5 (Apple Git-154)