From a59b04e33147a075fdd4c6cef155440689850b83 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 The patch allows pushing joins with function RTEs to PostgreSQL data sources in general and postgres_fdw specifically. Co-authored-by: Gleb Kashkin --- contrib/postgres_fdw/deparse.c | 187 ++++++-- .../postgres_fdw/expected/postgres_fdw.out | 368 +++++++++++++++ contrib/postgres_fdw/postgres_fdw.c | 423 +++++++++++++++--- contrib/postgres_fdw/postgres_fdw.h | 6 + contrib/postgres_fdw/sql/postgres_fdw.sql | 148 ++++++ src/backend/optimizer/path/joinpath.c | 11 + src/backend/optimizer/plan/setrefs.c | 1 - src/include/foreign/fdwapi.h | 1 + src/test/regress/expected/create_view.out | 6 +- 9 files changed, 1046 insertions(+), 105 deletions(-) diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 4680d51733..17228dec47 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -153,6 +153,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 bool isPlainForeignVar(Expr *node, deparse_expr_cxt *context); static void deparseOperatorName(StringInfo buf, Form_pg_operator opform); @@ -1977,13 +1978,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 @@ -1991,9 +2033,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; } } @@ -2717,23 +2793,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 @@ -2748,16 +2807,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 { @@ -2772,29 +2858,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 f2bcd6aa98..686ce1ac61 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -12345,3 +12345,371 @@ SELECT CASE WHEN closed IS NOT false THEN 1 ELSE 0 END -- Clean up \set VERBOSITY default RESET debug_discard_caches; +-- =================================================================== +-- 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 (((r2.a = r1.n)))) +(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) + +CREATE OR REPLACE FUNCTION get_constant_texts() +RETURNS TABLE (text_value text) AS $$ +BEGIN + RETURN QUERY VALUES + ('text1'), + ('text4'); +END; +$$ LANGUAGE plpgsql IMMUTABLE; +ALTER EXTENSION postgres_fdw ADD FUNCTION get_constant_texts(); +ALTER SERVER loopback OPTIONS (extensions 'postgres_fdw'); +EXPLAIN (VERBOSE, COSTS OFF) +SELECT r.*, t.n from remote_tbl1 r, ROWS FROM (unnest(array[3,4]), get_constant_texts()) t (n, 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(), public.get_constant_texts()) t) + Remote SQL: SELECT r1.c, r1.d, r2.n FROM (public.base_tbl1 r1 INNER JOIN ROWS FROM (unnest('{3,4}'::integer[]), public.get_constant_texts()) r2 (n,txt) ON (((r1.c = r2.n)) AND ((r2.txt = r1.d)))) +(4 rows) + +SELECT r.*, t.txt from remote_tbl1 r, ROWS FROM (unnest(array[3,4]), get_constant_texts()) t (n, 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) + +-- 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 that a function that returns a record is not pushed down +CREATE OR REPLACE FUNCTION f_ret_record() RETURNS record AS $$ SELECT (1,2)::record $$ language SQL IMMUTABLE; +ALTER EXTENSION postgres_fdw ADD function f_ret_record(); +EXPLAIN (VERBOSE, COSTS OFF) +SELECT s FROM remote_tbl rt, f_ret_record() AS s(a int, b int) +WHERE s.a = rt.a; + QUERY PLAN +----------------------------------------------------------------------------- + Nested Loop + Output: s.* + -> Function Scan on public.f_ret_record s + Output: s.*, s.a + Function Call: f_ret_record() + -> Foreign Scan on public.remote_tbl rt + Output: rt.a, rt.b + Remote SQL: SELECT a FROM public.base_tbl WHERE ((a = $1::integer)) +(8 rows) + +SELECT s FROM remote_tbl rt, f_ret_record() AS s(a int, b int) +WHERE s.a = rt.a; + s +------- + (1,2) +(1 row) + +DROP FUNCTION f(INTEGER); +ALTER EXTENSION postgres_fdw DROP FUNCTION get_constant_texts(); +ALTER EXTENSION postgres_fdw DROP FUNCTION f_ret_record(); +DROP FUNCTION get_constant_texts(); +DROP FUNCTION f_ret_record(); +DROP TABLE base_tbl, base_tbl1; +DROP FOREIGN TABLE remote_tbl, remote_tbl1; +-- Test that function WITH ORDINALITY is not pushed down +CREATE TABLE base_tbl (a int, b int); +CREATE FOREIGN TABLE remote_tbl (a int, b int) SERVER loopback OPTIONS (table_name 'base_tbl'); +INSERT INTO remote_tbl VALUES (1, 2), (2, 3), (3, 4), (5, 6); +ANALYZE remote_tbl; +SET enable_mergejoin TO false; +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM remote_tbl, unnest(ARRAY[1, 2]) WITH ORDINALITY +WHERE a=unnest; + QUERY PLAN +------------------------------------------------------------------------ + Hash Join + Output: remote_tbl.a, remote_tbl.b, unnest.unnest, unnest.ordinality + Hash Cond: (remote_tbl.a = unnest.unnest) + -> Foreign Scan on public.remote_tbl + Output: remote_tbl.a, remote_tbl.b + Remote SQL: SELECT a, b FROM public.base_tbl + -> Hash + Output: unnest.unnest, unnest.ordinality + -> Function Scan on pg_catalog.unnest + Output: unnest.unnest, unnest.ordinality + Function Call: unnest('{1,2}'::integer[]) +(11 rows) + +SELECT * FROM remote_tbl, unnest(ARRAY[1, 2]) WITH ORDINALITY +WHERE a=unnest; + a | b | unnest | ordinality +---+---+--------+------------ + 1 | 2 | 1 | 1 + 2 | 3 | 2 | 2 +(2 rows) + +DROP TABLE base_tbl; +DROP FOREIGN TABLE remote_tbl; +RESET enable_mergejoin; diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 53733d642d..5d0a339ca0 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -36,6 +36,7 @@ #include "optimizer/prep.h" #include "optimizer/restrictinfo.h" #include "optimizer/tlist.h" +#include "optimizer/clauses.h" #include "parser/parsetree.h" #include "postgres_fdw.h" #include "storage/latch.h" @@ -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, @@ -476,7 +485,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); @@ -542,6 +551,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 init_fpinfo(PlannerInfo *root, + RelOptInfo *baserel, + Oid foreigntableid, + PgFdwRelationInfo *existing_fpinfo); /* * Foreign-data wrapper handler function: return a struct with pointers @@ -597,6 +611,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; @@ -621,10 +636,32 @@ 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; + Assert(existing_fpinfo || foreigntableid != InvalidOid); + Assert(existing_fpinfo == NULL || foreigntableid == InvalidOid); + /* * We use PgFdwRelationInfo to pass various information to subsequent * functions. @@ -635,40 +672,64 @@ 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; - - apply_server_options(fpinfo); - apply_table_options(fpinfo); + 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); + } - /* - * 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 ExecCheckPermissions() does. If we fail due to lack - * of permissions, the query would have failed at runtime anyway. - */ - if (fpinfo->use_remote_estimate) + if (existing_fpinfo) { - Oid userid; + merge_fdw_options(fpinfo, existing_fpinfo, NULL); + fpinfo->user = existing_fpinfo->user; - userid = OidIsValid(baserel->userid) ? baserel->userid : GetUserId(); - fpinfo->user = GetUserMapping(userid, fpinfo->server->serverid); + /* + * Don't try to execute anything on remote server for + * non-relation-based query + */ + fpinfo->use_remote_estimate = false; } else - fpinfo->user = NULL; + { + /* + * 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 ExecCheckPermissions() does. If we fail due to lack + * of permissions, the query would have failed at runtime anyway. + */ + if (fpinfo->use_remote_estimate) + { + Oid userid; + + userid = OidIsValid(baserel->userid) ? baserel->userid : GetUserId(); + fpinfo->user = GetUserMapping(userid, fpinfo->server->serverid); + } + else + fpinfo->user = NULL; + } /* * Identify which baserestrictinfo clauses can be sent to the remote @@ -783,6 +844,9 @@ postgresGetForeignRelSize(PlannerInfo *root, fpinfo->hidden_subquery_rels = NULL; /* Set the relation index. */ fpinfo->relation_index = baserel->relid; + if (existing_fpinfo) + /* Mark fpinfo generated */ + fpinfo->is_generated = true; } /* @@ -1476,13 +1540,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; } @@ -1518,14 +1641,26 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags) /* * Identify which user to do the remote access as. This should match what - * ExecCheckPermissions() does. + * ExecCheckPermissions() does. In case of a join or aggregate, scan RTEs + * until RTE_RELATION is found. We would get the same result from any. */ userid = OidIsValid(fsplan->checkAsUser) ? fsplan->checkAsUser : GetUserId(); if (fsplan->scan.scanrelid > 0) + { rtindex = fsplan->scan.scanrelid; + rte = exec_rt_fetch(rtindex, estate); + } else - rtindex = bms_next_member(fsplan->fs_base_relids, -1); - rte = exec_rt_fetch(rtindex, estate); + { + rtindex = -1; + while ((rtindex = bms_next_member(fsplan->fs_base_relids, rtindex)) >= 0) + { + rte = exec_rt_fetch(rtindex, estate); + if (rte && rte->rtekind == RTE_RELATION) + break; + } + Assert(rte && rte->rtekind == RTE_RELATION); + } /* Get info about foreign table. */ table = GetForeignTable(rte->relid); @@ -2566,8 +2701,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); + } } /* @@ -2889,21 +3046,66 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es) rti += rtoffset; Assert(bms_is_member(rti, plan->fs_base_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)); + // Note: relname may be uninitialized. + relname = get_rel_name(rte->relid); + if (es->verbose) + { + char *namespace; + + namespace = get_namespace_name_or_temp(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; @@ -4427,7 +4629,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; @@ -4436,6 +4638,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); /* @@ -4727,11 +4942,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; } @@ -5745,6 +5962,63 @@ 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; + TupleDesc tupdesc; + Oid funcrettype; + + rte = planner_rt_fetch(foreignrel->relid, root); + + if (!rte) + return false; + + Assert(foreignrel->fdw_private); + + if (rte->rtekind == RTE_FUNCTION) + { + ListCell *lc; + + /* + * WITH ORDINALITY pushdown is not implemented yet. + */ + if (rte->funcordinality) + return false; + + Assert(list_length(rte->functions) >= 1); + foreach(lc, rte->functions) + { + rtfunc = (RangeTblFunction *) lfirst(lc); + + get_expr_result_type(rtfunc->funcexpr, &funcrettype, &tupdesc); + + /* + * Remote server requires a well defined return type for a + * function pushdown. + */ + if (!OidIsValid(funcrettype) || funcrettype == RECORDOID || funcrettype == VOIDOID) + return false; + + 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 semi-join. Reltarget is not * safe, if it contains references to inner rel relids, which do not belong to @@ -6477,6 +6751,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 diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index 9e501660d1..772144d611 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 372fe6dad1..6c717b58b8 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -4278,3 +4278,151 @@ SELECT CASE WHEN closed IS NOT false THEN 1 ELSE 0 END -- Clean up \set VERBOSITY default RESET debug_discard_caches; + +-- =================================================================== +-- 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; + +CREATE OR REPLACE FUNCTION get_constant_texts() +RETURNS TABLE (text_value text) AS $$ +BEGIN + RETURN QUERY VALUES + ('text1'), + ('text4'); +END; +$$ LANGUAGE plpgsql IMMUTABLE; + +ALTER EXTENSION postgres_fdw ADD FUNCTION get_constant_texts(); +ALTER SERVER loopback OPTIONS (extensions 'postgres_fdw'); + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT r.*, t.n from remote_tbl1 r, ROWS FROM (unnest(array[3,4]), get_constant_texts()) t (n, 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]), get_constant_texts()) t (n, 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; + +-- 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 that a function that returns a record is not pushed down +CREATE OR REPLACE FUNCTION f_ret_record() RETURNS record AS $$ SELECT (1,2)::record $$ language SQL IMMUTABLE; +ALTER EXTENSION postgres_fdw ADD function f_ret_record(); + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT s FROM remote_tbl rt, f_ret_record() AS s(a int, b int) +WHERE s.a = rt.a; + +SELECT s FROM remote_tbl rt, f_ret_record() AS s(a int, b int) +WHERE s.a = rt.a; + +DROP FUNCTION f(INTEGER); +ALTER EXTENSION postgres_fdw DROP FUNCTION get_constant_texts(); +ALTER EXTENSION postgres_fdw DROP FUNCTION f_ret_record(); +DROP FUNCTION get_constant_texts(); +DROP FUNCTION f_ret_record(); +DROP TABLE base_tbl, base_tbl1; +DROP FOREIGN TABLE remote_tbl, remote_tbl1; + +-- Test that function WITH ORDINALITY is not pushed down +CREATE TABLE base_tbl (a int, b int); +CREATE FOREIGN TABLE remote_tbl (a int, b int) SERVER loopback OPTIONS (table_name 'base_tbl'); +INSERT INTO remote_tbl VALUES (1, 2), (2, 3), (3, 4), (5, 6); +ANALYZE remote_tbl; +SET enable_mergejoin TO false; + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT * FROM remote_tbl, unnest(ARRAY[1, 2]) WITH ORDINALITY +WHERE a=unnest; + +SELECT * FROM remote_tbl, unnest(ARRAY[1, 2]) WITH ORDINALITY +WHERE a=unnest; + +DROP TABLE base_tbl; +DROP FOREIGN TABLE remote_tbl; +RESET enable_mergejoin; diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c index 3971138480..57430a8165 100644 --- a/src/backend/optimizer/path/joinpath.c +++ b/src/backend/optimizer/path/joinpath.c @@ -333,6 +333,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. They * could add new paths (such as CustomPaths) by calling add_path(), or diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 91c7c4fe2f..088845c00f 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -552,7 +552,6 @@ add_rte_to_flat_rtable(PlannerGlobal *glob, List *rteperminfos, 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/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h index fcde3876b2..0ccd6776a8 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; diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index f551624afb..3fda68bf7f 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -1681,10 +1681,10 @@ select pg_get_viewdef('tt14v', true); -- ... and you can even EXPLAIN it ... explain (verbose, costs off) select * from tt14v; - QUERY PLAN ----------------------------------------- + QUERY PLAN +-------------------------------------------- Function Scan on testviewschm2.tt14f t - Output: t.f1, t.f3, t.f4 + Output: t.f1, t."?dropped?column?", t.f4 Function Call: tt14f() (3 rows) -- 2.43.0