Function scan FDW pushdown
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=10 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 ProfessionalFrom 6b5ea4c62a1fcd3dad586d4f461cb142834ac266 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov
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 = (DefEl
Re: Function scan FDW pushdown
Ashutosh Bapat писал 2021-06-15 16:15:
Hi Alexander,
Hi.
The current version of the patch is based on asymetric partition-wise
join.
Currently it is applied after
v19-0001-Asymmetric-partitionwise-join.patch from
on
https://www.postgresql.org/message-id/792d60f4-37bc-e6ad-68ca-c2af5cbb2...@postgrespro.ru
.
So far I don't know how to visualize actual function expression used
in
function RTE, as in postgresExplainForeignScan() es->rtable comes from
queryDesc->plannedstmt->rtable, and rte->functions is already 0.
The actual function expression will be part of the Remote SQL of
ForeignScan node so no need to visualize it separately.
We still need to create tuple description for functions in
get_tupdesc_for_join_scan_tuples(),
so I had to remove setting newrte->functions to NIL in
add_rte_to_flat_rtable().
With rte->functions in place, there's no issues for explain.
The patch will have problems when there are multiple foreign tables
all on different servers or use different FDWs. In such a case the
function scan's RelOptInfo will get the fpinfo based on the first
foreign table the function scan is paired with during join planning.
But that may not be the best foreign table to join. We should be able
to plan all the possible joins. Current infra to add one fpinfo per
RelOptInfo won't help there. We need something better.
I suppose attached version of the patch is more mature.
The patch targets only postgres FDW, how do you see this working with
other FDWs?
Not now. We introduce necessary APIs for other FDWs, but implementing
TryShippableJoinPaths()
doesn't seem straightforward.
If we come up with the right approach we could use it for 1. pushing
down queries with IN () clause 2. joining a small local table with a
large foreign table by sending the local table rows down to the
foreign server.
--
Best regards,
Alexander Pyhalov,
Postgres ProfessionalFrom d997c313daf0031b812d3fca59d338be1a4f2196 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov
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)
+appendStringInfoStr
Re: Function scan FDW pushdown
Hi Alexander, On Thu, May 20, 2021 at 11:13 PM Alexander Pyhalov wrote: > > 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; > It will be good to provide some practical examples where this is useful. > more efficient. > > with patch: > > > 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. The patch will have problems when there are multiple foreign tables all on different servers or use different FDWs. In such a case the function scan's RelOptInfo will get the fpinfo based on the first foreign table the function scan is paired with during join planning. But that may not be the best foreign table to join. We should be able to plan all the possible joins. Current infra to add one fpinfo per RelOptInfo won't help there. We need something better. The patch targets only postgres FDW, how do you see this working with other FDWs? If we come up with the right approach we could use it for 1. pushing down queries with IN () clause 2. joining a small local table with a large foreign table by sending the local table rows down to the foreign server. -- Best Wishes, Ashutosh Bapat
