pgsql_fdw_pushdown_v10.patch
text/plain
Filename: pgsql_fdw_pushdown_v10.patch
Type: text/plain
Part: 1
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 v10
| 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 |
diff --git a/contrib/pgsql_fdw/deparse.c b/contrib/pgsql_fdw/deparse.c
index beb62fe..a9585e9 100644
*** a/contrib/pgsql_fdw/deparse.c
--- b/contrib/pgsql_fdw/deparse.c
***************
*** 14,19 ****
--- 14,21 ----
#include "access/transam.h"
#include "catalog/pg_class.h"
+ #include "catalog/pg_operator.h"
+ #include "catalog/pg_type.h"
#include "commands/defrem.h"
#include "foreign/foreign.h"
#include "lib/stringinfo.h"
***************
*** 22,30 ****
--- 24,34 ----
#include "nodes/makefuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/var.h"
+ #include "parser/parser.h"
#include "parser/parsetree.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
+ #include "utils/syscache.h"
#include "pgsql_fdw.h"
*************** static void deparseRelation(StringInfo b
*** 44,49 ****
--- 48,81 ----
bool need_prefix);
static void deparseVar(StringInfo buf, Var *node, PlannerInfo *root,
RelOptInfo *baserel, bool need_prefix);
+ static void deparseConst(StringInfo buf, Const *node, PlannerInfo *root,
+ RelOptInfo *baserel);
+ static void deparseBoolExpr(StringInfo buf, BoolExpr *node, PlannerInfo *root,
+ RelOptInfo *baserel);
+ static void deparseNullTest(StringInfo buf, NullTest *node, PlannerInfo *root,
+ RelOptInfo *baserel);
+ static void deparseDistinctExpr(StringInfo buf, DistinctExpr *node,
+ PlannerInfo *root, RelOptInfo *baserel);
+ static void deparseRelabelType(StringInfo buf, RelabelType *node,
+ PlannerInfo *root, RelOptInfo *baserel);
+ static void deparseFuncExpr(StringInfo buf, FuncExpr *node, PlannerInfo *root,
+ RelOptInfo *baserel);
+ static void deparseParam(StringInfo buf, Param *node, PlannerInfo *root,
+ RelOptInfo *baserel);
+ static void deparseScalarArrayOpExpr(StringInfo buf, ScalarArrayOpExpr *node,
+ PlannerInfo *root, RelOptInfo *baserel);
+ static void deparseOpExpr(StringInfo buf, OpExpr *node, PlannerInfo *root,
+ RelOptInfo *baserel);
+ static void deparseArrayRef(StringInfo buf, ArrayRef *node, PlannerInfo *root,
+ RelOptInfo *baserel);
+ static void deparseArrayExpr(StringInfo buf, ArrayExpr *node, PlannerInfo *root, RelOptInfo *baserel);
+
+ /*
+ * Determine whether an expression can be evaluated on remote side safely.
+ */
+ 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);
/*
* Deparse query representation into SQL statement which suits for remote
*************** deparseSimpleSql(Oid relid,
*** 152,157 ****
--- 184,268 ----
}
/*
+ * Examine baserestrictinfo of baserel, and extract expressions which can be
+ * evaluated on remote side safely. This function never changes
+ * baserestrictinfo.
+ */
+ List *
+ extractRemoteExprs(Oid relid,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ ListCell *lc;
+ List *exprs = NIL;
+
+ foreach(lc, baserel->baserestrictinfo)
+ {
+ RestrictInfo *ri = (RestrictInfo *) lfirst(lc);
+
+ if (is_foreign_expr(root, baserel, ri->clause))
+ exprs = lappend(exprs, ri->clause);
+ }
+
+ return exprs;
+ }
+
+ void
+ deparseExpr(StringInfo buf, Expr *node, PlannerInfo *root, RelOptInfo *baserel)
+ {
+ /*
+ * This part must be match foreign_expr_walker.
+ */
+ switch (nodeTag(node))
+ {
+ case T_Const:
+ deparseConst(buf, (Const *) node, root, baserel);
+ break;
+ case T_BoolExpr:
+ deparseBoolExpr(buf, (BoolExpr *) node, root, baserel);
+ break;
+ case T_NullTest:
+ deparseNullTest(buf, (NullTest *) node, root, baserel);
+ break;
+ case T_DistinctExpr:
+ deparseDistinctExpr(buf, (DistinctExpr *) node, root, baserel);
+ break;
+ case T_RelabelType:
+ deparseRelabelType(buf, (RelabelType *) node, root, baserel);
+ break;
+ case T_FuncExpr:
+ deparseFuncExpr(buf, (FuncExpr *) node, root, baserel);
+ break;
+ case T_Param:
+ deparseParam(buf, (Param *) node, root, baserel);
+ break;
+ case T_ScalarArrayOpExpr:
+ deparseScalarArrayOpExpr(buf, (ScalarArrayOpExpr *) node, root,
+ baserel);
+ break;
+ case T_OpExpr:
+ deparseOpExpr(buf, (OpExpr *) node, root, baserel);
+ break;
+ case T_Var:
+ deparseVar(buf, (Var *) node, root, baserel, false);
+ break;
+ case T_ArrayRef:
+ deparseArrayRef(buf, (ArrayRef *) node, root, baserel);
+ break;
+ case T_ArrayExpr:
+ deparseArrayExpr(buf, (ArrayExpr *) node, root, baserel);
+ break;
+ default:
+ {
+ ereport(ERROR,
+ (errmsg("unsupported expression for deparse"),
+ errdetail("%s", nodeToString(node))));
+ }
+ break;
+ }
+ }
+
+ /*
* Deparse node into buf, with relation qualifier if need_prefix was true. If
* node is a column of a foreign table, use value of colname FDW option (if any)
* instead of attribute name.
*************** deparseRelation(StringInfo buf,
*** 288,290 ****
--- 399,811 ----
appendStringInfo(buf, "%s.", q_nspname);
appendStringInfo(buf, "%s", q_relname);
}
+
+ static void
+ deparseConst(StringInfo buf,
+ Const *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ Oid typoutput;
+ bool typIsVarlena;
+ char *extval;
+
+ if (node->constisnull)
+ {
+ appendStringInfo(buf, "NULL");
+ return;
+ }
+
+ getTypeOutputInfo(node->consttype,
+ &typoutput, &typIsVarlena);
+ extval = OidOutputFunctionCall(typoutput, node->constvalue);
+
+ switch (node->consttype)
+ {
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case OIDOID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case NUMERICOID:
+ {
+ /*
+ * No need to quote unless they contain special values such as
+ * 'Nan'.
+ */
+ if (strspn(extval, "0123456789+-eE.") == strlen(extval))
+ {
+ if (extval[0] == '+' || extval[0] == '-')
+ appendStringInfo(buf, "(%s)", extval);
+ else
+ appendStringInfoString(buf, extval);
+ }
+ else
+ appendStringInfo(buf, "'%s'", extval);
+ }
+ break;
+ case BITOID:
+ case VARBITOID:
+ appendStringInfo(buf, "B'%s'", extval);
+ break;
+ case BOOLOID:
+ if (strcmp(extval, "t") == 0)
+ appendStringInfoString(buf, "true");
+ else
+ appendStringInfoString(buf, "false");
+ break;
+
+ default:
+ {
+ const char *valptr;
+
+ appendStringInfoChar(buf, '\'');
+ for (valptr = extval; *valptr; valptr++)
+ {
+ char ch = *valptr;
+
+ /*
+ * standard_conforming_strings of remote session should be
+ * set to similar value as local session.
+ */
+ if (SQL_STR_DOUBLE(ch, !standard_conforming_strings))
+ appendStringInfoChar(buf, ch);
+ appendStringInfoChar(buf, ch);
+ }
+ appendStringInfoChar(buf, '\'');
+ }
+ break;
+ }
+ }
+
+ static void
+ deparseBoolExpr(StringInfo buf,
+ BoolExpr *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ ListCell *lc;
+ char *op;
+ bool first;
+
+ switch (node->boolop)
+ {
+ case AND_EXPR:
+ op = "AND";
+ break;
+ case OR_EXPR:
+ op = "OR";
+ break;
+ case NOT_EXPR:
+ appendStringInfo(buf, "(NOT ");
+ deparseExpr(buf, list_nth(node->args, 0), root, baserel);
+ appendStringInfo(buf, ")");
+ return;
+ }
+
+ first = true;
+ appendStringInfo(buf, "(");
+ foreach(lc, node->args)
+ {
+ if (!first)
+ appendStringInfo(buf, " %s ", op);
+ deparseExpr(buf, (Expr *) lfirst(lc), root, baserel);
+ first = false;
+ }
+ appendStringInfo(buf, ")");
+ }
+
+ static void
+ deparseNullTest(StringInfo buf,
+ NullTest *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ elog(ERROR, "NullTest is not supported");
+ }
+
+ static void
+ deparseDistinctExpr(StringInfo buf,
+ DistinctExpr *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ elog(ERROR, "DistinctExpr not supported");
+ }
+
+ static void
+ deparseRelabelType(StringInfo buf,
+ RelabelType *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ elog(ERROR, "RelabelType is not supported");
+ }
+
+ static void
+ deparseFuncExpr(StringInfo buf,
+ FuncExpr *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ elog(ERROR, "FuncExpr is not supported");
+ }
+
+ static void
+ deparseParam(StringInfo buf,
+ Param *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ elog(ERROR, "Param is not supported");
+ }
+
+ static void
+ deparseScalarArrayOpExpr(StringInfo buf,
+ ScalarArrayOpExpr *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ elog(ERROR, "ScalarArrayOpExpr is not supported");
+ }
+
+ static void
+ deparseOpExpr(StringInfo buf,
+ OpExpr *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ HeapTuple tuple;
+ Form_pg_operator form;
+ char *opname;
+ char oprkind;
+ ListCell *arg = list_head(node->args);
+
+ /* Retieve necessary information about the operator from system catalog. */
+ tuple = SearchSysCache1(OPEROID, ObjectIdGetDatum(node->opno));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for operator %u", node->opno);
+ form = (Form_pg_operator) GETSTRUCT(tuple);
+ opname = NameStr(form->oprname);
+ oprkind = form->oprkind;
+ ReleaseSysCache(tuple);
+
+ Assert((oprkind == 'r' && list_length(node->args) == 1) ||
+ (oprkind == 'l' && list_length(node->args) == 1) ||
+ (oprkind == 'b' && list_length(node->args) == 2));
+
+ appendStringInfo(buf, "(");
+ if (oprkind == 'b' || oprkind == 'r')
+ {
+ deparseExpr(buf, lfirst(arg), root, baserel);
+ arg = lnext(arg);
+ }
+
+ appendStringInfo(buf, " %s ", opname);
+
+ if (oprkind == 'b' || oprkind == 'r')
+ deparseExpr(buf, lfirst(arg), root, baserel);
+ appendStringInfo(buf, ")");
+ }
+
+ static void
+ deparseArrayRef(StringInfo buf,
+ ArrayRef *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ elog(ERROR, "ArrayRef is not supported");
+ }
+
+ static void
+ deparseArrayExpr(StringInfo buf,
+ ArrayExpr *node,
+ PlannerInfo *root,
+ RelOptInfo *baserel)
+ {
+ elog(ERROR, "ArrayExpr is not supported");
+ }
+
+ /*
+ * 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:
+ break;
+ #ifdef NOT_SUPPORTED
+ 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;
+ #endif
+ 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;
+ #ifdef NOT_SUPPORTED
+ 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;
+ #endif
+ 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);
+ }
diff --git a/contrib/pgsql_fdw/expected/pgsql_fdw.out b/contrib/pgsql_fdw/expected/pgsql_fdw.out
index 5bac241..d60c768 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.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" 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
*** 351,361 ****
(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_19 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1"
(3 rows)
-- ===================================================================
--- 351,361 ----
(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_19 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" = c2))
(3 rows)
-- ===================================================================
*************** 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_20 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_21 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
--- 364,379 ----
-- 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_20 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
! Filter: (c1 = 2)
! Remote SQL: DECLARE pgsql_fdw_cursor_21 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" = 2))
! (7 rows)
EXECUTE st1(1, 1);
c3 | c3
*************** EXECUTE st1(101, 101);
*** 391,410 ****
-- 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_26 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_27 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, c4, NULL, NULL, NULL FROM "S 1"."T 1"
(12 rows)
EXECUTE st2(10, 20);
--- 390,409 ----
-- 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_26 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: ((c1 > 10) AND (date_part('dow'::text, c4) = 6::double precision))
! Remote SQL: DECLARE pgsql_fdw_cursor_27 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, c4, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" > 10))
(12 rows)
EXECUTE st2(10, 20);
*************** EXECUTE st1(101, 101);
*** 422,441 ****
-- 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_32 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_33 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, c5, NULL, NULL FROM "S 1"."T 1"
(12 rows)
EXECUTE st3(10, 20);
--- 421,440 ----
-- 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_32 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: ((c1 > 10) AND (date_part('dow'::text, c5) = 6::double precision))
! Remote SQL: DECLARE pgsql_fdw_cursor_33 SCROLL CURSOR FOR SELECT "C 1", NULL, c3, NULL, c5, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" > 10))
(12 rows)
EXECUTE st3(10, 20);
*************** EXECUTE st3(20, 30);
*** 453,503 ****
-- 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_38 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_39 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_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)
-- cleanup
--- 452,502 ----
-- 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_38 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" = 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_39 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" = 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_40 SCROLL CURSOR FOR SELECT "C 1", c2, c3, c4, c5, c6, c7 FROM "S 1"."T 1" WHERE (("C 1" = 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" WHERE (("C 1" = 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" WHERE (("C 1" = 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" WHERE (("C 1" = 1))
(3 rows)
-- cleanup
diff --git a/contrib/pgsql_fdw/pgsql_fdw.c b/contrib/pgsql_fdw/pgsql_fdw.c
index 370d240..7746ab9 100644
*** a/contrib/pgsql_fdw/pgsql_fdw.c
--- b/contrib/pgsql_fdw/pgsql_fdw.c
*************** typedef struct PgsqlFdwPlanState {
*** 75,80 ****
--- 75,84 ----
char *sql;
Cost startup_cost;
Cost total_cost;
+
+ ForeignTable *table;
+ ForeignServer *server;
+ UserMapping *user;
} PgsqlFdwPlanState;
/*
*************** typedef struct PgsqlFdwPlanState {
*** 82,87 ****
--- 86,92 ----
* stored in fdw_private list.
*/
enum FdwPrivateIndex {
+ FdwPrivateFdwExprs,
FdwPrivateSelectSql,
FdwPrivateDeclareSql,
FdwPrivateFetchSql,
*************** pgsqlGetForeignRelSize(PlannerInfo *root
*** 268,273 ****
--- 273,281 ----
planstate->sql = sql;
planstate->startup_cost = startup_cost;
planstate->total_cost = total_cost;
+ planstate->table = table;
+ planstate->server = server;
+ planstate->user = user;
baserel->fdw_private = (void *) planstate;
}
*************** pgsqlGetForeignPaths(PlannerInfo *root,
*** 283,288 ****
--- 291,305 ----
PgsqlFdwPlanState *planstate = (PgsqlFdwPlanState *) baserel->fdw_private;
List *fdw_private = NIL;
ForeignPath *path;
+ StringInfoData buf;
+ PGconn *conn;
+ double rows;
+ int width;
+ Cost startup_cost;
+ Cost total_cost;
+ bool first = true;
+ ListCell *lc;
+ List *fdw_exprs;
/*
* Cost estimation should be modified to respect cost of establishing
*************** pgsqlGetForeignPaths(PlannerInfo *root,
*** 297,303 ****
* Create simplest ForeignScan path node, counterpart of SeqScan for
* regular tables, for this scan, and add it to baserel.
*/
! fdw_private = list_make1(makeString(planstate->sql));
path = create_foreignscan_path(root, baserel,
baserel->rows,
planstate->startup_cost,
--- 314,321 ----
* Create simplest ForeignScan path node, counterpart of SeqScan for
* regular tables, for this scan, and add it to baserel.
*/
! fdw_private = lappend(fdw_private, NIL); /* no fdw_exprs */
! fdw_private = lappend(fdw_private, makeString(planstate->sql));
path = create_foreignscan_path(root, baserel,
baserel->rows,
planstate->startup_cost,
*************** pgsqlGetForeignPaths(PlannerInfo *root,
*** 309,314 ****
--- 327,389 ----
add_path(baserel, (Path *) path);
/*
+ * Create a ForeignScan path with WHERE push-down.
+ */
+ fdw_exprs = extractRemoteExprs(foreigntableid, root, baserel);
+ if (fdw_exprs != NIL)
+ {
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "%s WHERE (", planstate->sql);
+
+ foreach(lc, fdw_exprs)
+ {
+ Expr *expr = (Expr *) lfirst(lc);
+
+ /* Connect expressions with "AND". */
+ if (!first)
+ appendStringInfo(&buf, ") AND (");
+
+ deparseExpr(&buf, expr, root, baserel);
+ first = false;
+ }
+ appendStringInfoChar(&buf, ')');
+
+ /*
+ * Cost estimation should be modified to respect cost of establishing
+ * connection and transferring data.
+ */
+ conn = GetConnection(planstate->server, planstate->user, false);
+ get_remote_estimate(buf.data,
+ conn,
+ &rows,
+ &width,
+ &startup_cost,
+ &total_cost);
+ ReleaseConnection(conn);
+ adjust_costs(baserel->rows,
+ baserel->width,
+ &planstate->startup_cost,
+ &planstate->total_cost);
+
+ /*
+ * Create simplest ForeignScan path node, counterpart of SeqScan for
+ * regular tables, for this scan, and add it to baserel.
+ */
+ fdw_private = NIL;
+ fdw_private = lappend(fdw_private, fdw_exprs);
+ fdw_private = lappend(fdw_private, makeString(buf.data));
+ path = create_foreignscan_path(root, baserel,
+ rows,
+ startup_cost,
+ total_cost,
+ NIL, /* no pathkeys */
+ NULL, /* no outer rel either */
+ NIL, /* no param clause */
+ fdw_private); /* pass SQL */
+ add_path(baserel, (Path *) path);
+ }
+
+ /*
* XXX We can consider sorted path here if we know that foreign table is
* indexed on remote end. For this purpose, should we support FOREIGN
* INDEX to represent possible sets of sort keys which are relatively
*************** pgsqlGetForeignPlan(PlannerInfo *root,
*** 338,343 ****
--- 413,419 ----
DefElem *def;
int fetch_count = DEFAULT_FETCH_COUNT;
char *sql;
+ List *fdw_exprs = NIL;
/*
* We have no native ability to evaluate restriction clauses, so we just
*************** pgsqlGetForeignPlan(PlannerInfo *root,
*** 380,396 ****
* boundary between planner and executor. Finally FdwPlan using cursor
* would hold items below:
*
! * 1) plain SELECT statement (already added above)
! * 2) SQL statement used to declare cursor
! * 3) SQL statement used to fetch rows from cursor
! * 4) SQL statement used to reset cursor
! * 5) SQL statement used to close cursor
*
* These items are indexed with the enum FdwPrivateIndex, so an item
* can be accessed directly via list_nth(). For example of FETCH
* statement:
* list_nth(fdw_private, FdwPrivateFetchSql)
*/
sql = strVal(list_nth(fdw_private, FdwPrivateSelectSql));
/* Construct cursor name from sequential value */
--- 456,474 ----
* boundary between planner and executor. Finally FdwPlan using cursor
* would hold items below:
*
! * 1) expressions which are pushed down
! * 2) plain SELECT statement (already added above)
! * 3) SQL statement used to declare cursor
! * 4) SQL statement used to fetch rows from cursor
! * 5) SQL statement used to reset cursor
! * 6) SQL statement used to close cursor
*
* These items are indexed with the enum FdwPrivateIndex, so an item
* can be accessed directly via list_nth(). For example of FETCH
* statement:
* list_nth(fdw_private, FdwPrivateFetchSql)
*/
+ fdw_exprs = list_nth(fdw_private, FdwPrivateFdwExprs);
sql = strVal(list_nth(fdw_private, FdwPrivateSelectSql));
/* Construct cursor name from sequential value */
*************** pgsqlGetForeignPlan(PlannerInfo *root,
*** 420,426 ****
return make_foreignscan(tlist,
scan_clauses,
scan_relid,
! NIL,
fdw_private);
}
--- 498,504 ----
return make_foreignscan(tlist,
scan_clauses,
scan_relid,
! fdw_exprs,
fdw_private);
}
diff --git a/contrib/pgsql_fdw/pgsql_fdw.h b/contrib/pgsql_fdw/pgsql_fdw.h
index 6eb99d5..b7d1310 100644
*** a/contrib/pgsql_fdw/pgsql_fdw.h
--- b/contrib/pgsql_fdw/pgsql_fdw.h
*************** char *deparseSimpleSql(Oid relid,
*** 28,32 ****
--- 28,39 ----
PlannerInfo *root,
RelOptInfo *baserel,
ForeignTable *table);
+ List *extractRemoteExprs(Oid relid,
+ PlannerInfo *root,
+ RelOptInfo *baserel);
+ void deparseExpr(StringInfo buf,
+ Expr *expr,
+ PlannerInfo *root,
+ RelOptInfo *baserel);
#endif /* PGSQL_FDW_H */