pgsql_fdw_pushdown_v9.patch
text/plain
Filename: pgsql_fdw_pushdown_v9.patch
Type: text/plain
Part: 2
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
Series: patch v9
| File | + | − |
|---|---|---|
| contrib/pgsql_fdw/deparse.c | 0 | 0 |
| contrib/pgsql_fdw/expected/pgsql_fdw.out | 0 | 0 |
| contrib/pgsql_fdw/pgsql_fdw.c | 0 | 0 |
| contrib/pgsql_fdw/pgsql_fdw.h | 0 | 0 |
| doc/src/sgml/pgsql-fdw.sgml | 0 | 0 |
diff --git a/contrib/pgsql_fdw/deparse.c b/contrib/pgsql_fdw/deparse.c
index de49211..a023f44 100644
*** a/contrib/pgsql_fdw/deparse.c
--- b/contrib/pgsql_fdw/deparse.c
*************** typedef struct foreign_executable_cxt
*** 36,50 ****
RelOptInfo *foreignrel;
} foreign_executable_cxt;
/*
* Deparse query representation into SQL statement which suits for remote
! * PostgreSQL server.
*/
char *
deparseSql(Oid relid,
PlannerInfo *root,
RelOptInfo *baserel,
! ForeignTable *table)
{
StringInfoData foreign_relname;
StringInfoData sql; /* builder for SQL statement */
--- 36,58 ----
RelOptInfo *foreignrel;
} foreign_executable_cxt;
+ static bool is_foreign_expr(PlannerInfo *root, RelOptInfo *baserel, Expr *expr);
+ static bool foreign_expr_walker(Node *node, foreign_executable_cxt *context);
+ static bool is_builtin(Oid procid);
+ static bool contain_ext_param(Node *clause);
+ static bool contain_ext_param_walker(Node *node, void *context);
+
/*
* Deparse query representation into SQL statement which suits for remote
! * PostgreSQL server. Also some of quals in WHERE clause will be pushed down
! * If they are safe to be evaluated on the remote side.
*/
char *
deparseSql(Oid relid,
PlannerInfo *root,
RelOptInfo *baserel,
! ForeignTable *table,
! bool *has_param)
{
StringInfoData foreign_relname;
StringInfoData sql; /* builder for SQL statement */
*************** deparseSql(Oid relid,
*** 56,61 ****
--- 64,70 ----
const char *relname = NULL; /* plain relation name */
const char *q_nspname; /* quoted namespace name */
const char *q_relname; /* quoted relation name */
+ List *foreign_expr = NIL; /* list of Expr* evaluated on remote */
int i;
List *rtable = NIL;
List *context = NIL;
*************** deparseSql(Oid relid,
*** 64,78 ****
initStringInfo(&foreign_relname);
/*
! * First of all, determine which column should be retrieved for this scan.
*
* We do this before deparsing SELECT clause because attributes which are
* not used in neither reltargetlist nor baserel->baserestrictinfo, quals
* evaluated on local, can be replaced with literal "NULL" in the SELECT
* clause to reduce overhead of tuple handling tuple and data transfer.
*/
if (baserel->baserestrictinfo != NIL)
{
ListCell *lc;
foreach (lc, baserel->baserestrictinfo)
--- 73,97 ----
initStringInfo(&foreign_relname);
/*
! * First of all, determine which qual can be pushed down.
! *
! * The expressions which satisfy is_foreign_expr() are deparsed into WHERE
! * clause of result SQL string, and they could be removed from PlanState
! * to avoid duplicate evaluation at ExecScan().
! *
! * We never change the quals in the Plan node, because this execution might
! * be for a PREPAREd statement, thus the quals in the Plan node might be
! * reused to construct another PlanState for subsequent EXECUTE statement.
*
* We do this before deparsing SELECT clause because attributes which are
* not used in neither reltargetlist nor baserel->baserestrictinfo, quals
* evaluated on local, can be replaced with literal "NULL" in the SELECT
* clause to reduce overhead of tuple handling tuple and data transfer.
*/
+ *has_param = false;
if (baserel->baserestrictinfo != NIL)
{
+ List *local_qual = NIL;
ListCell *lc;
foreach (lc, baserel->baserestrictinfo)
*************** deparseSql(Oid relid,
*** 81,86 ****
--- 100,119 ----
List *attrs;
/*
+ * Determine whether the qual can be pushed down or not. If a qual
+ * can be pushed down and it contains external param, tell that to
+ * caller in order to fall back to fixed costs.
+ */
+ if (is_foreign_expr(root, baserel, ri->clause))
+ {
+ foreign_expr = lappend(foreign_expr, ri->clause);
+ if (contain_ext_param((Node*) ri->clause))
+ *has_param = true;
+ }
+ else
+ local_qual = lappend(local_qual, ri);
+
+ /*
* We need to know which attributes are used in qual evaluated
* on the local server, because they should be listed in the
* SELECT clause of remote query. We can ignore attributes
*************** deparseSql(Oid relid,
*** 92,97 ****
--- 125,136 ----
PVC_RECURSE_PLACEHOLDERS);
attr_used = list_union(attr_used, attrs);
}
+
+ /*
+ * Remove pushed down qualifiers from baserestrictinfo to avoid
+ * redundant evaluation.
+ */
+ baserel->baserestrictinfo = local_qual;
}
/*
*************** deparseSql(Oid relid,
*** 227,233 ****
--- 266,497 ----
*/
appendStringInfo(&sql, "FROM %s", foreign_relname.data);
+ /*
+ * deparse WHERE clause
+ */
+ if (foreign_expr != NIL)
+ {
+ Node *node;
+
+ node = (Node *) make_ands_explicit(foreign_expr);
+ appendStringInfo(&sql, " WHERE %s ",
+ deparse_expression(node, context, false, false));
+ list_free(foreign_expr);
+ foreign_expr = NIL;
+ }
+
elog(DEBUG3, "Remote SQL: %s", sql.data);
return sql.data;
}
+ /*
+ * Returns true if expr is safe to be evaluated on the foreign server.
+ */
+ static bool
+ is_foreign_expr(PlannerInfo *root, RelOptInfo *baserel, Expr *expr)
+ {
+ foreign_executable_cxt context;
+ context.root = root;
+ context.foreignrel = baserel;
+
+ /*
+ * An expression which includes any mutable function can't be pushed down
+ * because it's result is not stable. For example, pushing now() down to
+ * remote side would cause confusion from the clock offset.
+ * If we have routine mapping infrastructure in future release, we will be
+ * able to choose function to be pushed down in finer granularity.
+ */
+ if (contain_mutable_functions((Node *) expr))
+ return false;
+
+ /*
+ * Check that the expression consists of nodes which are known as safe to
+ * be pushed down.
+ */
+ if (foreign_expr_walker((Node *) expr, &context))
+ return false;
+
+ return true;
+ }
+
+ /*
+ * Return true if node includes any node which is not known as safe to be
+ * pushed down.
+ */
+ static bool
+ foreign_expr_walker(Node *node, foreign_executable_cxt *context)
+ {
+ if (node == NULL)
+ return false;
+
+ /*
+ * If given expression has valid collation, it can't be pushed down because
+ * it might has incompatible semantics on remote side.
+ */
+ if (exprCollation(node) != InvalidOid ||
+ exprInputCollation(node) != InvalidOid)
+ return true;
+
+ /*
+ * If return type of given expression is not built-in, it can't be pushed
+ * down because it might has incompatible semantics on remote side.
+ */
+ if (!is_builtin(exprType(node)))
+ return true;
+
+ switch (nodeTag(node))
+ {
+ case T_Const:
+ case T_BoolExpr:
+ case T_NullTest:
+ case T_DistinctExpr:
+ case T_RelabelType:
+ /*
+ * These type of nodes are known as safe to be pushed down.
+ * Of course the subtree of the node, if any, should be checked
+ * continuously at the tail of this function.
+ */
+ break;
+ /*
+ * If function used by the expression is not built-in, it can't be
+ * pushed down because it might has incompatible semantics on remote
+ * side.
+ */
+ case T_FuncExpr:
+ {
+ FuncExpr *fe = (FuncExpr *) node;
+ if (!is_builtin(fe->funcid))
+ return true;
+ }
+ break;
+ case T_Param:
+ /*
+ * Only external parameters can be pushed down.:
+ */
+ {
+ if (((Param *) node)->paramkind != PARAM_EXTERN)
+ return true;
+ }
+ break;
+ case T_ScalarArrayOpExpr:
+ /*
+ * Only built-in operators can be pushed down. In addition,
+ * underlying function must be built-in and immutable, but we don't
+ * check volatility here; such check must be done already with
+ * contain_mutable_functions.
+ */
+ {
+ ScalarArrayOpExpr *oe = (ScalarArrayOpExpr *) node;
+
+ if (!is_builtin(oe->opno) || !is_builtin(oe->opfuncid))
+ return true;
+
+ /* operands are checked later */
+ }
+ break;
+ case T_OpExpr:
+ /*
+ * Only built-in operators can be pushed down. In addition,
+ * underlying function must be built-in and immutable, but we don't
+ * check volatility here; such check must be done already with
+ * contain_mutable_functions.
+ */
+ {
+ OpExpr *oe = (OpExpr *) node;
+
+ if (!is_builtin(oe->opno) || !is_builtin(oe->opfuncid))
+ return true;
+
+ /* operands are checked later */
+ }
+ break;
+ case T_Var:
+ /*
+ * Var can be pushed down if it is in the foreign table.
+ * XXX Var of other relation can be here?
+ */
+ {
+ Var *var = (Var *) node;
+ foreign_executable_cxt *f_context;
+
+ f_context = (foreign_executable_cxt *) context;
+ if (var->varno != f_context->foreignrel->relid ||
+ var->varlevelsup != 0)
+ return true;
+ }
+ break;
+ case T_ArrayRef:
+ /*
+ * ArrayRef which holds non-built-in typed elements can't be pushed
+ * down.
+ */
+ {
+ if (!is_builtin(((ArrayRef *) node)->refelemtype))
+ return true;
+ }
+ break;
+ case T_ArrayExpr:
+ /*
+ * ArrayExpr which holds non-built-in typed elements can't be pushed
+ * down.
+ */
+ {
+ if (!is_builtin(((ArrayExpr *) node)->element_typeid))
+ return true;
+ }
+ break;
+ default:
+ {
+ ereport(DEBUG3,
+ (errmsg("expression is too complex"),
+ errdetail("%s", nodeToString(node))));
+ return true;
+ }
+ break;
+ }
+
+ return expression_tree_walker(node, foreign_expr_walker, context);
+ }
+
+ /*
+ * Return true if given object is one of built-in objects.
+ */
+ static bool
+ is_builtin(Oid oid)
+ {
+ return (oid < FirstNormalObjectId);
+ }
+
+ /*
+ * contain_ext_param
+ * Recursively search for Param nodes within a clause.
+ *
+ * Returns true if any parameter reference node with relkind PARAM_EXTERN
+ * found.
+ *
+ * This does not descend into subqueries, and so should be used only after
+ * reduction of sublinks to subplans, or in contexts where it's known there
+ * are no subqueries. There mustn't be outer-aggregate references either.
+ *
+ * XXX: These functions could be in core, src/backend/optimizer/util/clauses.c.
+ */
+ static bool
+ contain_ext_param(Node *clause)
+ {
+ return contain_ext_param_walker(clause, NULL);
+ }
+
+ static bool
+ contain_ext_param_walker(Node *node, void *context)
+ {
+ if (node == NULL)
+ return false;
+ if (IsA(node, Param))
+ {
+ Param *param = (Param *) node;
+
+ if (param->paramkind == PARAM_EXTERN)
+ return true; /* abort the tree traversal and return true */
+ }
+ return expression_tree_walker(node, contain_ext_param_walker, context);
+ }
diff --git a/contrib/pgsql_fdw/expected/pgsql_fdw.out b/contrib/pgsql_fdw/expected/pgsql_fdw.out
index c1e2e82..51b347e 100644
*** a/contrib/pgsql_fdw/expected/pgsql_fdw.out
--- b/contrib/pgsql_fdw/expected/pgsql_fdw.out
*************** SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.
*** 230,241 ****
-- with WHERE clause
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7
! Filter: ((t1.c1 = 101) AND ((t1.c6)::text = '1'::text) AND (t1.c7 = '1'::bpchar))
! Remote SQL: DECLARE pgsql_fdw_cursor_4 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
(4 rows)
SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
--- 230,241 ----
-- with WHERE clause
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7
! Filter: (((t1.c6)::text = '1'::text) AND (t1.c7 = '1'::bpchar))
! Remote SQL: DECLARE pgsql_fdw_cursor_4 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ("C 1" = 101)
(4 rows)
SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 = '1';
*************** EXPLAIN (COSTS false) SELECT * FROM ft1
*** 343,362 ****
(3 rows)
EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = abs(c2))
! Remote SQL: DECLARE pgsql_fdw_cursor_20 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = c2)
! Remote SQL: DECLARE pgsql_fdw_cursor_21 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
-- ===================================================================
-- parameterized queries
--- 343,360 ----
(3 rows)
EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: DECLARE pgsql_fdw_cursor_20 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ("C 1" = abs(c2))
! (2 rows)
EXPLAIN (COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: DECLARE pgsql_fdw_cursor_21 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ("C 1" = c2)
! (2 rows)
-- ===================================================================
-- parameterized queries
*************** EXPLAIN (COSTS false) SELECT * FROM ft1
*** 364,380 ****
-- simple join
PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
EXPLAIN (COSTS false) EXECUTE st1(1, 2);
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------
Nested Loop
-> Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: DECLARE pgsql_fdw_cursor_22 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, NULL, NULL, NULL FROM "S 1"."T 1"
! -> Materialize
! -> Foreign Scan on ft2 t2
! Filter: (c1 = 2)
! Remote SQL: DECLARE pgsql_fdw_cursor_23 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, NULL, NULL, NULL FROM "S 1"."T 1"
! (8 rows)
EXECUTE st1(1, 1);
c3 | c3
--- 362,375 ----
-- simple join
PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
EXPLAIN (COSTS false) EXECUTE st1(1, 2);
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop
-> Foreign Scan on ft1 t1
! Remote SQL: DECLARE pgsql_fdw_cursor_22 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE ("C 1" = 1)
! -> Foreign Scan on ft2 t2
! Remote SQL: DECLARE pgsql_fdw_cursor_23 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE ("C 1" = 2)
! (5 rows)
EXECUTE st1(1, 1);
c3 | c3
*************** EXECUTE st1(101, 101);
*** 391,411 ****
-- subquery using stable function (can't be pushed down)
PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c4) = 6) ORDER BY c1;
EXPLAIN (COSTS false) EXECUTE st2(10, 20);
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------------------------------
Sort
Sort Key: t1.c1
-> Hash Join
Hash Cond: (t1.c3 = t2.c3)
-> Foreign Scan on ft1 t1
! Filter: (c1 < 20)
! Remote SQL: DECLARE pgsql_fdw_cursor_28 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
-> Hash
-> HashAggregate
-> Foreign Scan on ft2 t2
! Filter: ((c1 > 10) AND (date_part('dow'::text, c4) = 6::double precision))
! Remote SQL: DECLARE pgsql_fdw_cursor_29 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, c4, NULL, NULL, NULL FROM "S 1"."T 1"
! (12 rows)
EXECUTE st2(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7
--- 386,405 ----
-- subquery using stable function (can't be pushed down)
PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c4) = 6) ORDER BY c1;
EXPLAIN (COSTS false) EXECUTE st2(10, 20);
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort
Sort Key: t1.c1
-> Hash Join
Hash Cond: (t1.c3 = t2.c3)
-> Foreign Scan on ft1 t1
! Remote SQL: DECLARE pgsql_fdw_cursor_28 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ("C 1" < 20)
-> Hash
-> HashAggregate
-> Foreign Scan on ft2 t2
! Filter: (date_part('dow'::text, c4) = 6::double precision)
! Remote SQL: DECLARE pgsql_fdw_cursor_29 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, c4, NULL, NULL, NULL FROM "S 1"."T 1" WHERE ("C 1" > 10)
! (11 rows)
EXECUTE st2(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7
*************** EXECUTE st1(101, 101);
*** 422,442 ****
-- subquery using immutable function (can be pushed down)
PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c5) = 6) ORDER BY c1;
EXPLAIN (COSTS false) EXECUTE st3(10, 20);
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------------------------------
Sort
Sort Key: t1.c1
-> Hash Join
Hash Cond: (t1.c3 = t2.c3)
-> Foreign Scan on ft1 t1
! Filter: (c1 < 20)
! Remote SQL: DECLARE pgsql_fdw_cursor_34 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
-> Hash
-> HashAggregate
-> Foreign Scan on ft2 t2
! Filter: ((c1 > 10) AND (date_part('dow'::text, c5) = 6::double precision))
! Remote SQL: DECLARE pgsql_fdw_cursor_35 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, c5, NULL, NULL FROM "S 1"."T 1"
! (12 rows)
EXECUTE st3(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7
--- 416,435 ----
-- subquery using immutable function (can be pushed down)
PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND EXTRACT(dow FROM c5) = 6) ORDER BY c1;
EXPLAIN (COSTS false) EXECUTE st3(10, 20);
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort
Sort Key: t1.c1
-> Hash Join
Hash Cond: (t1.c3 = t2.c3)
-> Foreign Scan on ft1 t1
! Remote SQL: DECLARE pgsql_fdw_cursor_34 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ("C 1" < 20)
-> Hash
-> HashAggregate
-> Foreign Scan on ft2 t2
! Filter: (date_part('dow'::text, c5) = 6::double precision)
! Remote SQL: DECLARE pgsql_fdw_cursor_35 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, c5, NULL, NULL FROM "S 1"."T 1" WHERE ("C 1" > 10)
! (11 rows)
EXECUTE st3(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7
*************** EXECUTE st3(20, 30);
*** 453,504 ****
-- custom plan should be chosen
PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: DECLARE pgsql_fdw_cursor_40 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: DECLARE pgsql_fdw_cursor_41 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: DECLARE pgsql_fdw_cursor_42 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: DECLARE pgsql_fdw_cursor_43 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = 1)
! Remote SQL: DECLARE pgsql_fdw_cursor_44 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Filter: (c1 = $1)
! Remote SQL: DECLARE pgsql_fdw_cursor_45 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
! (3 rows)
-- cleanup
DEALLOCATE st1;
--- 446,491 ----
-- custom plan should be chosen
PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: DECLARE pgsql_fdw_cursor_40 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ("C 1" = 1)
! (2 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: DECLARE pgsql_fdw_cursor_41 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ("C 1" = 1)
! (2 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: DECLARE pgsql_fdw_cursor_42 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ("C 1" = 1)
! (2 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: DECLARE pgsql_fdw_cursor_43 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ("C 1" = 1)
! (2 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: DECLARE pgsql_fdw_cursor_44 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ("C 1" = 1)
! (2 rows)
EXPLAIN (COSTS false) EXECUTE st4(1);
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on ft1 t1
! Remote SQL: DECLARE pgsql_fdw_cursor_46 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE ("C 1" = 1)
! (2 rows)
-- cleanup
DEALLOCATE st1;
diff --git a/contrib/pgsql_fdw/pgsql_fdw.c b/contrib/pgsql_fdw/pgsql_fdw.c
index d0d6767..7c39307 100644
*** a/contrib/pgsql_fdw/pgsql_fdw.c
--- b/contrib/pgsql_fdw/pgsql_fdw.c
*************** pgsqlPlanForeignScan(Oid foreigntableid,
*** 196,210 ****
List *fdw_private = NIL;
ForeignTable *table;
ForeignServer *server;
/*
* Generate remote query string, and estimate cost of this scan.
*/
table = GetForeignTable(foreigntableid);
! server = GetForeignServer(table->serverid);
! sql = deparseSql(foreigntableid, root, baserel, table);
! estimate_costs(root, baserel, sql, server->serverid,
! &startup_cost, &total_cost);
/*
* Store plain SELECT statement in private area of FdwPlan. This will be
--- 196,227 ----
List *fdw_private = NIL;
ForeignTable *table;
ForeignServer *server;
+ bool has_param;
/*
* Generate remote query string, and estimate cost of this scan.
+ *
+ * If the baserestrictinfo contains any Param node with paramkind
+ * PARAM_EXTERNAL as part of push-down-able condition, we need to
+ * groundless fixed costs because we can't get actual parameter values
+ * here, so we use fixed and large costs as second best so that planner
+ * tend to choose custom plan.
+ *
+ * See comments in plancache.c for details of custom plan.
*/
table = GetForeignTable(foreigntableid);
! sql = deparseSql(foreigntableid, root, baserel, table, &has_param);
! if (has_param)
! {
! startup_cost = CONNECTION_COSTS;
! total_cost = 10000; /* groundless large costs */
! }
! else
! {
! server = GetForeignServer(table->serverid);
! estimate_costs(root, baserel, sql, server->serverid,
! &startup_cost, &total_cost);
! }
/*
* Store plain SELECT statement in private area of FdwPlan. This will be
diff --git a/contrib/pgsql_fdw/pgsql_fdw.h b/contrib/pgsql_fdw/pgsql_fdw.h
index 78a6abf..020d282 100644
*** a/contrib/pgsql_fdw/pgsql_fdw.h
--- b/contrib/pgsql_fdw/pgsql_fdw.h
*************** int ExtractConnectionOptions(List *defel
*** 27,32 ****
char *deparseSql(Oid relid,
PlannerInfo *root,
RelOptInfo *baserel,
! ForeignTable *table);
#endif /* PGSQL_FDW_H */
--- 27,33 ----
char *deparseSql(Oid relid,
PlannerInfo *root,
RelOptInfo *baserel,
! ForeignTable *table,
! bool *has_param);
#endif /* PGSQL_FDW_H */
diff --git a/doc/src/sgml/pgsql-fdw.sgml b/doc/src/sgml/pgsql-fdw.sgml
index 30fa7f5..2375d8f 100644
*** a/doc/src/sgml/pgsql-fdw.sgml
--- b/doc/src/sgml/pgsql-fdw.sgml
*************** postgres=# SELECT pgsql_fdw_disconnect(s
*** 242,253 ****
</para>
<synopsis>
postgres=# EXPLAIN SELECT aid FROM pgbench_accounts WHERE abalance < 0;
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------------------
! Foreign Scan on pgbench_accounts (cost=100.00..8105.13 rows=302613 width=8)
! Filter: (abalance < 0)
! Remote SQL: DECLARE pgsql_fdw_cursor_3 SCROLL CURSOR FOR SELECT aid, NULL, abalance, NULL FROM public.pgbench_accounts
! (3 rows)
</synopsis>
</sect2>
--- 242,252 ----
</para>
<synopsis>
postgres=# EXPLAIN SELECT aid FROM pgbench_accounts WHERE abalance < 0;
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------------------------------------------------
! Foreign Scan on pgbench_accounts (cost=100.00..8861.66 rows=518 width=8)
! Remote SQL: DECLARE pgsql_fdw_cursor_1 SCROLL CURSOR FOR SELECT aid, NULL, abalance, NULL FROM public.pgbench_accounts WHERE (abalance < 0)
! (2 rows)
</synopsis>
</sect2>