> While checking for shippability, we build the target list which is passed
> to
> the foreign server as fdw_scan_tlist. The target list contains
> a. All the GROUP BY expressions
> b. Shippable entries from the target list of upper relation
> c. Var and Aggref nodes from non-shippable entries from the target list of
> upper relation
>
The code in the patch doesn't seem to add Var nodes explicitly. It assumes
that
the Var nodes will be part of GROUP BY clause. The code is correct, I think.
> d. Var and Aggref nodes from non-shippable HAVING conditions.
>
This needs to be changed as per the comments below.
>
> Costing:
>
> If use_foreign_estimate is true for input relation, like JOIN case, we use
> EXPLAIN output to get the cost of query with aggregation/grouping on the
> foreign server. If not we calculate the costs locally. Similar to core, we
> use
> get_agg_clause_costs() to get costs for aggregation and then using logic
> similar to cost_agg() we calculate startup and total cost. Since we have no
> idea which aggregation strategy will be used at foreign side, we add all
> startup cost (startup cost of input relation, aggregates etc.) into startup
> cost for the grouping path and similarly for total cost.
>
This looks OK to me.
>
> Deparsing the query:
>
> Target list created while checking for shippability is deparsed using
> deparseExplicitTargetList(). sortgroupref are adjusted according to this
> target list. Most of the logic to deparse an Aggref is inspired from
> get_agg_expr(). For an upper relation, FROM and WHERE clauses come from the
> underlying scan relation and thus for simplicity, FROM clause deparsing
> logic
> is moved from deparseSelectSql() to a new function deparseFromClause(). The
> same function adds WHERE clause to the remote SQL.
>
Ok.
>
>
> Area of future work:
>
> 1. Adding path with path-keys to push ORDER BY clause along with
> aggregation/
> grouping. Should be supported as a separate patch.
>
> 2. Grouping Sets/Rollup/Cube is not supported in current version. I have
> left
> this aside to keep patch smaller. If required I can add that support in the
> next version of the patch.
>
I am fine with these limitations for first cut of this feature.
I think we should try to measure performance gain because of aggregate
pushdown. The EXPLAIN
doesn't show actual improvement in the execution times.
Here are the comments on the patch.
Patch compiles without errors/warnings - Yes
make check passes - Yes.
make check in contrib/postgres_fdw passes - Yes
Attached patch (based on your patch) has some typos corrected, some comments
rephrased. It also has some code changes, as explained in various comments
below. Please see if those look good.
1. Usually for any deparseSomething function, Something is the type of node
produced by the parser when the string output by that function is parsed.
deparseColumnRef, for example, produces a string, which when parsed
produces a
columnRef node. There is are nodes of type FromClause and AggOrderBy. I
guess,
you meant FromExpr instead of FromClause. deparseAggOrderBy() may be
renamed as
appendOrderByFromList() or something similar. It may be utilized for window
functions if required.
2. Can we infer the value of foragg flag from the RelOptInfo passed to
is_foreign_expr()? For any upper relation, it is ok to have aggregate in
there. For any other relation aggregate is not expected.
3. In function foreign_grouping_ok(), should we use classifyConditions()?
The
function is written and used for base relations. There's nothing in that
function, which prohibits it being used for other relations. I feel that
foreign_join_ok() should have used the same function to classify the other
clauses.
4. May be the individual criteria in the comment block
+ /*
+ * Aggregate is safe to pushdown if
+ * 1. It is a built-in aggregate
+ * 2. All its arguments are safe to push-down
+ * 3. The functions involved are immutable.
+ * 4. Other expressions involved like aggorder,
aggdistinct are
+ * safe to be pushed down.
+ */
should be associated with the code blocks which implement those. As the
criteria change it's difficult to maintain the numbered list in sync with
the
code.
5. The comment
+ /* Aggregates other than simple one are non-pushable. */
should read /* Only non-split aggregates are pushable. */ as AGGSPLIT_SIMPLE
means a complete, non-split aggregation step.
6. An aggregate function has transition, combination and finalization
function
associated with it. Should we check whether all of the functions are
shippable?
But probably it suffices to check whether aggregate function as whole is
shippable or not using is_shippable() since it's the whole aggregate we are
interested in and not the intermediate results. Probably, we should add a
comment explaining why it's sufficient to check the aggregate function as a
whole. I wondered whether we should check shippability of aggregate return
type, but we don't do that for functions as well. So it's fine.
7. It looks like aggdirectargs, aggdistinct, aggorder expressions are all
present in args list. If all the expressions in args are shippable, it means
those lists are also shippable and hence corresponding aggregate subclauses
are
shippable. Are we unnecessarily checking shippability of those other lists?
8. The prologue of build_tlist_to_deparse() mentions that the output
targetlist
contains columns, which is not true with your patch. The targetlist
returned by
this function can have expressions for upper relations. Please update the
prologue to reflect this change.
9. In build_tlist_to_deparse(), we are including aggregates from unshippable
conditions without checking whether they are shippable or not. This can
cause
an unshippable expression to be pushed to the foreign server as seen below
explain verbose select avg(c1) from ft1 having avg(c1 * random()) > 100;
QUERY
PLAN
--------------------------------------------------------------------------------------
Foreign Scan (cost=112.50..133.53 rows=1 width=32)
Output: (avg(c1))
Filter: ((avg(((ft1.c1)::double precision * random()))) > '100'::double
precision)
Relations: Aggregate on (public.ft1)
Remote SQL: SELECT avg(r1."C 1"), avg((r1."C 1" * random())) FROM "S
1"."T 1" r1
(5 rows)
We should check shippability of aggregates in unshippable conditions in
foreign_grouping_ok(). If any of those are not shippable, aggregation can
not
be pushed down. Otherwise, include those in the grouped_tlist.
10. Comment /* Construct FROM clause */ should read "Construct FROM and
WHERE
clauses." to be in sync with the next function call. deparseFromClause()
should
be renamed as deparseFromExpr() inline with the naming convention of
deparse<something> functions. From the function signature of
deparseFromClause(), it doesn't become clear as to what contributes to the
FROM
clause. Should we add a comment in the prologue of that function explaining
the
same. Also it strikes odd that for an upper relation, we pass remote_conds
to
this function, but it doesn't deparse those but deparses the remote
conditions
from the scan relation and later deparses the same remote_conds as HAVING
clause. Although this is correct, the code looks odd. May be the codeblock
in
deparseFuncClause()
1008 /*
1009 * For aggregates the FROM clause will be build from underneath
scan rel.
1010 * WHERE clause conditions too taken from there.
1011 */
1012 if (foreignrel->reloptkind == RELOPT_UPPER_REL)
1013 {
1014 PgFdwRelationInfo *ofpinfo;
1015
1016 ofpinfo = (PgFdwRelationInfo *) fpinfo->outerrel->fdw_private;
1017 scan_rel = fpinfo->outerrel;
1018 context->foreignrel = scan_rel;
1019 remote_conds = ofpinfo->remote_conds;
1020 }
should be moved into deparseSelectStmtForRel() to make the things clear.
11. Whether to qualify a column name by an alias should be based on whether
the
underlying scan relation is a join or base relation scan. That can be
achieved
by setting scanrel in the deparse_context. Attached patch has this
implemented.
Instead of scanrel, we may also have use_alias value as part of the context,
which is used everywhere.
12. The code to print the function name in deparseAggref() seems similar to
that in deparseFuncExpr(). Should we extract it out into a separate function
and call that function in deparseAggref() and deparseFuncExpr() both?
13. While deparsing the direct aggregates, the code is not adding ","
between
two arguments, resulting in error like below.
select rank(c1, c2) within group (order by c1, c2) from ft1 group by c1, c2;
ERROR: syntax error at or near "c2"
CONTEXT: Remote SQL command: SELECT rank("C 1"c2) WITHIN GROUP (ORDER BY "C
1", c2), "C 1", c2 FROM "S 1"."T 1" GROUP BY "C 1", c2
May be we should add support to deparse List expressions by separating list
items by "," similar to get_rule_expr() and use that here.
14. In deparseAggOrderBy() we do not output anything for default cases like
ASC
or NULLS FIRST (for DESC). But like appendOrderByClause() it's better to be
explicit and output even the default specifications. That way, we do not
leave
anything to the interpretation of the foreign server.
15. deparseAggOrderBy supports deparsing USING subclause for ORDER BY for an
aggregate, but there is no corresponding shippability check for ordering
operator. Also, we should try to produce a testcase for the same.
16. The code to deparse a sort/group reference in deparseAggOrderBy() and
appendGroupByClause() looks similar. Should we extract it out into a
function
by itself and call that function in those two places similar to
get_rule_sortgroupclause()?
17. In postgresGetForeignPlan() the code to extract conditions and
targetlist
is duplicated for join and upper relation. Attached patch removes this
duplication. Since DML, FOR SHARE/UPDATE is not allowed with aggregation and
grouping, we should get an outer plan in that code.
18. estimate_path_cost_size() has grown quite long (~400 lines). Half of
that
function deals with estimating costs locally. Should we split this function
into smaller functions, one for estimating size and costs of each kind of
relations locally?
19. Condition below
+ if (sgref && query->groupClause && query->groupingSets == NIL &&
+ get_sortgroupref_clause_noerr(sgref, query->groupClause) !=
NULL)
can be rewritten as
if (sgref && get_sortgroupref_clause_noerr(sgref, query->groupClause))
since we won't come here with non-NULL query->groupingSets and
get_sortgroupref_clause_noerr() will return NULL, if there aren't any
groupClause.
20. Please use word "foreign" instead of "remote" in comments.
21. This code looks dubious
+ /* If not GROUP BY ref, reset it as we are not pushing those */
+ if (sgref)
+ grouping_target->sortgrouprefs[i] = 0;
grouping_target comes from RelOptInfo, which is being modified here. We
shouldn't be modifying anything in the RelOptInfo while checking whether the
aggregate/grouping is shippable. You may want to copy the pathtaget and
modify
the copy.
22. Following comment needs more elaboration.
+ /*
+ * Add aggregates, if any, into tlist. Plain Var nodes
pulled
+ * are already part of GROUP BY and thus no need to add
them
+ * explicitly.
+ */
Plain Var nodes will either be same as some GROUP BY expression or should be
part of some GROUP BY expression. In the later case, the query can not refer
those without the surrounding expression. which will be part of the
targetlist
list. Hence we don't need to add plain Var nodes in the targetlist. In fact
adding those in SELECT clause will cause error on the foreign server if they
are not part of GROUP BY clause.
23. Probably you don't need to save this in fpinfo. You may want to
calculate
it whenever it's needed.
+ fpinfo->grouped_exprs = get_sortgrouplist_exprs(query->groupClause,
tlist);
24. Can this ever happen for postgres_fdw? The core sets fdwroutine and
calls
this function when the underlying scan relation has fdwroutine set, which
implies that it called corresponding GetForeignPath hooks, which should have
set fdw_private. Nonetheless, I think, still the condition is useful to
avoid
crashing the server in case the fdw_private is not set. But we need better
comments.
+ /* If input rel is not aware of fdw, simply return */
+ if (!input_rel->fdw_private)
+ return;
25. Although it's desirable to get a switch case in
postgresGetForeignUpperPaths() eventually, for this patch I guess we should
have an if condition there.
26. Attached patch has restructured code in appendGroupByClause() to reduce
indentation and make the code readable. Let me know if this looks good to
you.
27. Prologue of create_foreign_grouping_paths() does not have formatting
similar to the surrounding functions. Please fix it. Since the function
"create"s and "add"s paths, it might be better to rename it as
add_foreign_grouping_paths().
28. Isn't the following true for any upper relation and shouldn't it be
part of
postgresGetForeignUpperPaths(), rather than create_foreign_grouping_paths()?
+ /*
+ * If input rel is not safe to pushdown, we cannot do grouping and/or
+ * aggregation on it.
+ */
+ if (!ifpinfo || !ifpinfo->pushdown_safe)
+ return;
29. We require RelOptInfo::relids since create_foreignscan_plan() copies
them
into ForeignPlan::fs_relids and executor uses them. So, I guess, we have to
set
those in the core somewhere. May be while calling fetch_upper_rel() in
grouping_planner(), we should call it with relids of the underlying scan
relation. I don't see any function calling fetch_upper_rel() with non-NULL
relids. In fact, if we are to create an upper relation with relids in
future,
this code is going to wipe that out, which will be undesirable. Also,
generally, when copying relids, it's better to use bms_copy() to make a copy
of Bitmapset, instead of just assigning the pointer.
30. By the time postgresGetForeignUpperPaths() gets called, the core has
already added its own paths, so it doesn't make much sense to set rows and
width grouped_rel in create_foreign_grouping_paths().
31. fpinfo->server and user fields are being set twice, once in
create_foreign_grouping_paths() then in foreign_grouping_ok(). Do we need
that?
32.
+ /*
+ * Do we want to create paths with pathkeys corresponding for
+ * root->query_pathkeys.
+ */
Yes, it would be desirable to do that. If we are not going to do that in
this
patch, may be remove that line or add a TODO kind of comment.
33. The patch marks build_path_tlist() as non-static but does not use it
anywhere outside creatplan.c. Is this change intentional?
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 5c35414..92a82a0 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -98,7 +98,9 @@ typedef struct foreign_loc_cxt
typedef struct deparse_expr_cxt
{
PlannerInfo *root; /* global planner state */
+
RelOptInfo *foreignrel; /* the foreign relation we are planning for */
+ RelOptInfo *scanrel; /* the underlying scan relation. */
StringInfo buf; /* output buffer to append to */
List **params_list; /* exprs that will become remote Params */
} deparse_expr_cxt;
@@ -903,6 +905,7 @@ deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
List **retrieved_attrs, List **params_list)
{
deparse_expr_cxt context;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *)rel->fdw_private;
/* We handle all relations other than dead one. */
Assert(rel->reloptkind != RELOPT_DEADREL);
@@ -911,6 +914,7 @@ deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
context.buf = buf;
context.root = root;
context.foreignrel = rel;
+ context.scanrel = (rel->reloptkind == RELOPT_UPPER_REL) ? fpinfo->outerrel : rel;
context.params_list = params_list;
/* Construct SELECT clause */
@@ -991,10 +995,11 @@ deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context)
}
/*
- * Construct a FROM clause and WHERE clause, if any. And append it to "buf".
- * The final output contains "SELECT ... FROM ... [WHERE ... ]".
+ * Construct a FROM clause and a WHERE clause, if any, and append those to
+ * "buf".
*
- * remote_conds is the list of WHERE clauses, NIL if none.
+ * remote_conds is the list of clauses to be included in the WHERE clause,
+ * NIL if none.
*/
static void
deparseFromClause(List *remote_conds, deparse_expr_cxt *context)
@@ -1003,26 +1008,23 @@ deparseFromClause(List *remote_conds, deparse_expr_cxt *context)
RelOptInfo *foreignrel = context->foreignrel;
PlannerInfo *root = context->root;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
- RelOptInfo *scan_rel = foreignrel;
+ RelOptInfo *scan_rel = context->scanrel;
/*
- * For aggregates the FROM clause will be build from underneath scan rel.
- * WHERE clause conditions too taken from there.
+ * For aggregates the FROM clause will be built from the underneath scan
+ * relation. Conditions in the WHERE clause are also taken from there.
*/
if (foreignrel->reloptkind == RELOPT_UPPER_REL)
{
PgFdwRelationInfo *ofpinfo;
ofpinfo = (PgFdwRelationInfo *) fpinfo->outerrel->fdw_private;
- scan_rel = fpinfo->outerrel;
- context->foreignrel = scan_rel;
remote_conds = ofpinfo->remote_conds;
}
/* Construct FROM clause */
appendStringInfoString(buf, " FROM ");
deparseFromExprForRel(buf, root, scan_rel,
- foreignrel->reloptkind == RELOPT_UPPER_REL ? true :
(scan_rel->reloptkind == RELOPT_JOINREL),
context->params_list);
@@ -1032,10 +1034,6 @@ deparseFromClause(List *remote_conds, deparse_expr_cxt *context)
appendStringInfo(buf, " WHERE ");
appendConditions(remote_conds, context);
}
-
- /* Restore context's foreignrel */
- if (foreignrel->reloptkind == RELOPT_UPPER_REL)
- context->foreignrel = foreignrel;
}
/*
@@ -1138,14 +1136,14 @@ deparseTargetList(StringInfo buf,
/*
* Deparse the appropriate locking clause (FOR UPDATE or FOR SHARE) for a
- * given relation (context->foreignrel).
+ * given relation (context->scanrel).
*/
static void
deparseLockingClause(deparse_expr_cxt *context)
{
StringInfo buf = context->buf;
PlannerInfo *root = context->root;
- RelOptInfo *rel = context->foreignrel;
+ RelOptInfo *rel = context->scanrel;
int relid = -1;
while ((relid = bms_next_member(rel->relids, relid)) >= 0)
@@ -1366,6 +1364,7 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
context.buf = buf;
context.foreignrel = foreignrel;
+ context.scanrel = foreignrel;
context.root = root;
context.params_list = params_list;
@@ -1534,6 +1533,7 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
/* Set up context struct for recursion */
context.root = root;
context.foreignrel = baserel;
+ context.scanrel = baserel;
context.buf = buf;
context.params_list = params_list;
@@ -1618,6 +1618,7 @@ deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
/* Set up context struct for recursion */
context.root = root;
context.foreignrel = baserel;
+ context.scanrel = baserel;
context.buf = buf;
context.params_list = params_list;
@@ -2044,10 +2045,10 @@ deparseExpr(Expr *node, deparse_expr_cxt *context)
static void
deparseVar(Var *node, deparse_expr_cxt *context)
{
- bool qualify_col = (context->foreignrel->reloptkind == RELOPT_JOINREL ||
- context->foreignrel->reloptkind == RELOPT_UPPER_REL);
+ bool qualify_col = (context->scanrel->reloptkind == RELOPT_JOINREL ||
+ context->scanrel->reloptkind == RELOPT_UPPER_REL);
- if (bms_is_member(node->varno, context->foreignrel->relids) &&
+ if (bms_is_member(node->varno, context->scanrel->relids) &&
node->varlevelsup == 0)
deparseColumnRef(context->buf, node->varno, node->varattno,
context->root, qualify_col);
@@ -2824,42 +2825,45 @@ appendGroupByClause(List *tlist, deparse_expr_cxt *context)
{
StringInfo buf = context->buf;
Query *query = context->root->parse;
+ ListCell *lc;
+ bool first = true;
- if (query->groupClause != NULL)
- {
- appendStringInfo(buf, " GROUP BY ");
+ /* Nothing to be done, if there's no GROUP BY clause in the query. */
+ if (!query->groupClause)
+ return;
- if (query->groupingSets == NIL)
- {
- ListCell *lc;
- bool first = true;
+ appendStringInfo(buf, " GROUP BY ");
- foreach(lc, query->groupClause)
- {
- SortGroupClause *grp = (SortGroupClause *) lfirst(lc);
- Index ref = grp->tleSortGroupRef;
- TargetEntry *tle;
- Expr *expr;
+ /*
+ * Queries with grouping sets are not pushed down, so we don't grouping
+ * sets here.
+ */
+ Assert(!query->groupingSets);
- if (!first)
- appendStringInfoString(buf, ", ");
- first = false;
+ foreach(lc, query->groupClause)
+ {
+ SortGroupClause *grp = (SortGroupClause *) lfirst(lc);
+ Index ref = grp->tleSortGroupRef;
+ TargetEntry *tle;
+ Expr *expr;
+
+ if (!first)
+ appendStringInfoString(buf, ", ");
+ first = false;
- tle = get_sortgroupref_tle(ref, tlist);
- expr = tle->expr;
+ tle = get_sortgroupref_tle(ref, tlist);
+ expr = tle->expr;
- if (expr && IsA(expr, Const))
- deparseConst((Const *) expr, context);
- else if (!expr || IsA(expr, Var))
- deparseExpr(expr, context);
- else
- {
- /* Must force parens for other expressions */
- appendStringInfoString(buf, "(");
- deparseExpr(expr, context);
- appendStringInfoString(buf, ")");
- }
- }
+ if (expr && IsA(expr, Const))
+ deparseConst((Const *) expr, context);
+ else if (!expr || IsA(expr, Var))
+ deparseExpr(expr, context);
+ else
+ {
+ /* Must force parens for other expressions */
+ appendStringInfoString(buf, "(");
+ deparseExpr(expr, context);
+ appendStringInfoString(buf, ")");
}
}
}
@@ -2875,7 +2879,7 @@ appendOrderByClause(List *pathkeys, deparse_expr_cxt *context)
ListCell *lcell;
int nestlevel;
char *delim = " ";
- RelOptInfo *baserel = context->foreignrel;
+ RelOptInfo *baserel = context->scanrel;
StringInfo buf = context->buf;
/* Make sure any constants in the exprs are printed portably */
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 6ba1895..8079071 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -861,12 +861,12 @@ CREATE OPERATOR === (
-- built-in operators and functions can be shipped for remote execution
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
- QUERY PLAN
----------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Foreign Scan
Output: (count(c3))
Relations: Aggregate on (public.ft1 t1)
- Remote SQL: SELECT count(r1.c3) FROM "S 1"."T 1" r1 WHERE (("C 1" = abs(c2)))
+ Remote SQL: SELECT count(c3) FROM "S 1"."T 1" WHERE (("C 1" = abs(c2)))
(4 rows)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
@@ -877,12 +877,12 @@ SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2;
- QUERY PLAN
-----------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------
Foreign Scan
Output: (count(c3))
Relations: Aggregate on (public.ft1 t1)
- Remote SQL: SELECT count(r1.c3) FROM "S 1"."T 1" r1 WHERE (("C 1" = c2))
+ Remote SQL: SELECT count(c3) FROM "S 1"."T 1" WHERE (("C 1" = c2))
(4 rows)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2;
@@ -935,12 +935,12 @@ ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
-- ... now they can be shipped
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
- QUERY PLAN
------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
Foreign Scan
Output: (count(c3))
Relations: Aggregate on (public.ft1 t1)
- Remote SQL: SELECT count(r1.c3) FROM "S 1"."T 1" r1 WHERE (("C 1" = public.postgres_fdw_abs(c2)))
+ Remote SQL: SELECT count(c3) FROM "S 1"."T 1" WHERE (("C 1" = public.postgres_fdw_abs(c2)))
(4 rows)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
@@ -951,12 +951,12 @@ SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
- QUERY PLAN
------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
Foreign Scan
Output: (count(c3))
Relations: Aggregate on (public.ft1 t1)
- Remote SQL: SELECT count(r1.c3) FROM "S 1"."T 1" r1 WHERE (("C 1" OPERATOR(public.===) c2))
+ Remote SQL: SELECT count(c3) FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(public.===) c2))
(4 rows)
SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
@@ -4437,12 +4437,12 @@ SELECT * FROM ft1 ORDER BY c6 ASC NULLS FIRST, c1 OFFSET 15 LIMIT 10;
-- Consistent check constraints provide consistent results
ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2positive CHECK (c2 >= 0);
EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 < 0;
- QUERY PLAN
---------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------
Foreign Scan
Output: (count(*))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT count(*) FROM "S 1"."T 1" r1 WHERE ((c2 < 0))
+ Remote SQL: SELECT count(*) FROM "S 1"."T 1" WHERE ((c2 < 0))
(4 rows)
SELECT count(*) FROM ft1 WHERE c2 < 0;
@@ -4481,12 +4481,12 @@ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c2positive;
-- But inconsistent check constraints provide inconsistent results
ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c2negative CHECK (c2 < 0);
EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ft1 WHERE c2 >= 0;
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Foreign Scan
Output: (count(*))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT count(*) FROM "S 1"."T 1" r1 WHERE ((c2 >= 0))
+ Remote SQL: SELECT count(*) FROM "S 1"."T 1" WHERE ((c2 >= 0))
(4 rows)
SELECT count(*) FROM ft1 WHERE c2 >= 0;
@@ -5883,12 +5883,12 @@ ROLLBACK;
-- A. Simple aggregates
explain (verbose, costs off)
select sum(c1), avg(c1), min(c2), max(c1), stddev(c2) from ft1;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
Foreign Scan
Output: (sum(c1)), (avg(c1)), (min(c2)), (max(c1)), (stddev(c2))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT sum(r1."C 1"), avg(r1."C 1"), min(r1.c2), max(r1."C 1"), stddev(r1.c2) FROM "S 1"."T 1" r1
+ Remote SQL: SELECT sum("C 1"), avg("C 1"), min(c2), max("C 1"), stddev(c2) FROM "S 1"."T 1"
(4 rows)
select sum(c1), avg(c1), min(c2), max(c1), stddev(c2) from ft1;
@@ -5905,22 +5905,22 @@ select sum("C 1"), avg("C 1"), min(c2), max("C 1"), stddev(c2) from "S 1"."T 1";
explain (verbose, costs off)
select sum(c1) * random() from ft1;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Foreign Scan
Output: (((sum(c1)))::double precision * random())
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT sum(r1."C 1") FROM "S 1"."T 1" r1
+ Remote SQL: SELECT sum("C 1") FROM "S 1"."T 1"
(4 rows)
explain (verbose, costs off)
select count(c6) from ft1;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Foreign Scan
Output: (count(c6))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT count(r1.c6) FROM "S 1"."T 1" r1
+ Remote SQL: SELECT count(c6) FROM "S 1"."T 1"
(4 rows)
select count(c6) from ft1;
@@ -5949,12 +5949,12 @@ select sum(c1 * random()), avg(c1) from ft1;
explain (verbose, costs off)
select sum(c1) from ft2 where c2 < 5;
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Foreign Scan
Output: (sum(c1))
Relations: Aggregate on (public.ft2)
- Remote SQL: SELECT sum(r1."C 1") FROM "S 1"."T 1" r1 WHERE ((c2 < 5))
+ Remote SQL: SELECT sum("C 1") FROM "S 1"."T 1" WHERE ((c2 < 5))
(4 rows)
select sum(c1) from ft2 where c2 < 5;
@@ -6035,15 +6035,15 @@ select count(*) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2 * random());
-- B. Aggregates with GROUP BY
explain (verbose, costs off)
select c2, count(*) from ft1 group by c2 order by 1;
- QUERY PLAN
--------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------
Sort
Output: c2, (count(*))
Sort Key: ft1.c2
-> Foreign Scan
Output: c2, (count(*))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT r1.c2, count(*) FROM "S 1"."T 1" r1 GROUP BY r1.c2
+ Remote SQL: SELECT c2, count(*) FROM "S 1"."T 1" GROUP BY c2
(7 rows)
select c2, count(*) from ft1 group by c2 order by 1;
@@ -6100,15 +6100,15 @@ select c2, count(*) from "S 1"."T 1" group by c2 order by 1;
explain (verbose, costs off)
select count(c1), length(c6) from ft2 group by c6 order by 1, 2;
- QUERY PLAN
------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------
Sort
Output: (count(c1)), (length((c6)::text)), c6
Sort Key: (count(ft2.c1)), (length((ft2.c6)::text))
-> Foreign Scan
Output: (count(c1)), (length((c6)::text)), c6
Relations: Aggregate on (public.ft2)
- Remote SQL: SELECT count(r1."C 1"), length(r1.c6), r1.c6 FROM "S 1"."T 1" r1 GROUP BY r1.c6
+ Remote SQL: SELECT count("C 1"), length(c6), c6 FROM "S 1"."T 1" GROUP BY c6
(7 rows)
select count(c1), length(c6) from ft2 group by c6 order by 1, 2;
@@ -6143,15 +6143,15 @@ select count("C 1"), length(c6) from "S 1"."T 1" group by c6 order by 1, 2;
explain (verbose, costs off)
select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Sort
Output: ((c2 / 2)), ((sum(c2) * (c2 / 2)))
Sort Key: ((ft1.c2 / 2))
-> Foreign Scan
Output: ((c2 / 2)), ((sum(c2) * (c2 / 2)))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT (r1.c2 / 2), (sum(r1.c2) * (r1.c2 / 2)) FROM "S 1"."T 1" r1 GROUP BY ((r1.c2 / 2))
+ Remote SQL: SELECT (c2 / 2), (sum(c2) * (c2 / 2)) FROM "S 1"."T 1" GROUP BY ((c2 / 2))
(7 rows)
select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2;
@@ -6204,8 +6204,8 @@ select c2/2, sum(c2) * (c2/2) from "S 1"."T 1" group by c2/2 order by c2/2;
explain (verbose, costs off)
select count(a), sum(a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(c1) order by 1, 2) x;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
Aggregate
Output: count(ft1.c2), sum(ft1.c2)
-> Sort
@@ -6214,7 +6214,7 @@ select count(a), sum(a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(
-> Foreign Scan
Output: ft1.c2, (sum(ft1.c1)), (sqrt((ft1.c1)::double precision))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT r1.c2, sum(r1."C 1"), sqrt(r1."C 1") FROM "S 1"."T 1" r1 GROUP BY r1.c2, (sqrt(r1."C 1"))
+ Remote SQL: SELECT c2, sum("C 1"), sqrt("C 1") FROM "S 1"."T 1" GROUP BY c2, (sqrt("C 1"))
(9 rows)
select count(a), sum(a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(c1) order by 1, 2) x;
@@ -6232,12 +6232,12 @@ select count(a), sum(a) from (select c2 a, sum("C 1") b from "S 1"."T 1" group b
-- Aggregate is still pushed down by taking random() out
explain (verbose, costs off)
select c1 * random(), sum(c1) * c1 from ft1 group by c1;
- QUERY PLAN
--------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------
Foreign Scan
Output: ((c1)::double precision * random()), ((sum(c1) * c1)), c1
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT (sum(r1."C 1") * r1."C 1"), r1."C 1" FROM "S 1"."T 1" r1 GROUP BY r1."C 1"
+ Remote SQL: SELECT (sum("C 1") * "C 1"), "C 1" FROM "S 1"."T 1" GROUP BY "C 1"
(4 rows)
--select c1 * random(), sum(c1) * c1 from ft1 group by c1;
@@ -6258,15 +6258,15 @@ select c1 * random() from ft2 group by c1 * random(), c2;
-- C. Aggregates with HAVING clause
explain (verbose, costs off)
select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 order by 1;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
Sort
Output: c2, (sum(c1))
Sort Key: ft2.c2
-> Foreign Scan
Output: c2, (sum(c1))
Relations: Aggregate on (public.ft2)
- Remote SQL: SELECT r1.c2, sum(r1."C 1") FROM "S 1"."T 1" r1 GROUP BY r1.c2 HAVING ((avg(r1."C 1") < 500::numeric))
+ Remote SQL: SELECT c2, sum("C 1") FROM "S 1"."T 1" GROUP BY c2 HAVING ((avg("C 1") < 500::numeric))
(7 rows)
select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 order by 1;
@@ -6285,15 +6285,15 @@ select c2, sum("C 1") from "S 1"."T 1" group by c2 having avg("C 1") < 500 order
explain (verbose, costs off)
select c2, sum(c1) from ft1 group by c2 having avg(c1) < 500 and sum(c1) < 200000 order by c2;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------
Sort
Output: c2, (sum(c1))
Sort Key: ft1.c2
-> Foreign Scan
Output: c2, (sum(c1))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT r1.c2, sum(r1."C 1") FROM "S 1"."T 1" r1 GROUP BY r1.c2 HAVING ((avg(r1."C 1") < 500::numeric)) AND ((sum(r1."C 1") < 200000))
+ Remote SQL: SELECT c2, sum("C 1") FROM "S 1"."T 1" GROUP BY c2 HAVING ((avg("C 1") < 500::numeric)) AND ((sum("C 1") < 200000))
(7 rows)
select c2, sum(c1) from ft1 group by c2 having avg(c1) < 500 and sum(c1) < 200000 order by c2;
@@ -6312,15 +6312,15 @@ select c2, sum("C 1") from "S 1"."T 1" group by c2 having avg("C 1") < 500 and s
explain (verbose, costs off)
select c5, count(c2) from ft1 group by c5, sqrt(c2) having sqrt(max(c2)) < 2 order by 1, 2;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------
Sort
Output: c5, (count(c2)), (sqrt((c2)::double precision))
Sort Key: ft1.c5, (count(ft1.c2))
-> Foreign Scan
Output: c5, (count(c2)), (sqrt((c2)::double precision))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT r1.c5, count(r1.c2), sqrt(r1.c2) FROM "S 1"."T 1" r1 GROUP BY r1.c5, (sqrt(r1.c2)) HAVING ((sqrt(max(r1.c2)) < 2::double precision))
+ Remote SQL: SELECT c5, count(c2), sqrt(c2) FROM "S 1"."T 1" GROUP BY c5, (sqrt(c2)) HAVING ((sqrt(max(c2)) < 2::double precision))
(7 rows)
select c5, count(c2) from ft1 group by c5, sqrt(c2) having sqrt(max(c2)) < 2 order by 1, 2;
@@ -6369,7 +6369,7 @@ select c2, sum(c1) from ft2 group by c2 having sum(c1) * random() < 500000 order
Output: c2, (sum(c1))
Filter: ((((sum(ft2.c1)))::double precision * random()) < '500000'::double precision)
Relations: Aggregate on (public.ft2)
- Remote SQL: SELECT r1.c2, sum(r1."C 1") FROM "S 1"."T 1" r1 GROUP BY r1.c2
+ Remote SQL: SELECT c2, sum("C 1") FROM "S 1"."T 1" GROUP BY c2
(8 rows)
select c2, sum(c1) from ft2 group by c2 having sum(c1) * random() < 500000 order by c2;
@@ -6427,15 +6427,15 @@ select c2, sum("C 1") from "S 1"."T 1" group by c2 having sum("C 1") * random()
-- Having clause with random() will be evaluated locally, and other having qual is pushed
explain (verbose, costs off)
select count(*) from (select c5, count(c1) from ft1 group by c5, sqrt(c2) having avg(c1) * random() < 500 and avg(c1) < 500) x;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate
Output: count(*)
-> Foreign Scan
Output: ft1.c5, (NULL::bigint), (sqrt((ft1.c2)::double precision))
Filter: ((((avg(ft1.c1)))::double precision * random()) < '500'::double precision)
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT r1.c5, NULL::bigint, sqrt(r1.c2), avg(r1."C 1") FROM "S 1"."T 1" r1 GROUP BY r1.c5, (sqrt(r1.c2)) HAVING ((avg(r1."C 1") < 500::numeric))
+ Remote SQL: SELECT c5, NULL::bigint, sqrt(c2), avg("C 1") FROM "S 1"."T 1" GROUP BY c5, (sqrt(c2)) HAVING ((avg("C 1") < 500::numeric))
(7 rows)
select count(*) from (select c5, count(c1) from ft1 group by c5, sqrt(c2) having avg(c1) * random() < 500 and avg(c1) < 500) x;
@@ -6453,12 +6453,12 @@ select count(*) from (select c5, count("C 1") from "S 1"."T 1" group by c5, sqrt
-- D. ORDER BY, DISTINCT, FILTER, Ordered-sets and VARIADIC within aggregates
explain (verbose, costs off)
select array_agg(c1 order by c3) from ft1 where c2 = 6 and c1 < 100;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
Foreign Scan
Output: (array_agg(c1 ORDER BY c3))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT array_agg(r1."C 1" ORDER BY r1.c3) FROM "S 1"."T 1" r1 WHERE (("C 1" < 100)) AND ((c2 = 6))
+ Remote SQL: SELECT array_agg("C 1" ORDER BY c3) FROM "S 1"."T 1" WHERE (("C 1" < 100)) AND ((c2 = 6))
(4 rows)
select array_agg(c1 order by c3) from ft1 where c2 = 6 and c1 < 100;
@@ -6475,15 +6475,15 @@ select array_agg("C 1" order by c3) from "S 1"."T 1" where c2 = 6 and "C 1" < 10
explain (verbose, costs off)
select array_agg(c1 order by c1) from ft2 where c1 < 100 group by c2 order by 1;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------
Sort
Output: (array_agg(c1 ORDER BY c1)), c2
Sort Key: (array_agg(ft2.c1 ORDER BY ft2.c1))
-> Foreign Scan
Output: (array_agg(c1 ORDER BY c1)), c2
Relations: Aggregate on (public.ft2)
- Remote SQL: SELECT array_agg(r1."C 1" ORDER BY r1."C 1"), r1.c2 FROM "S 1"."T 1" r1 WHERE (("C 1" < 100)) GROUP BY r1.c2
+ Remote SQL: SELECT array_agg("C 1" ORDER BY "C 1"), c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) GROUP BY c2
(7 rows)
select array_agg(c1 order by c1) from ft2 where c1 < 100 group by c2 order by 1;
@@ -6516,12 +6516,12 @@ select array_agg("C 1" order by "C 1") from "S 1"."T 1" where "C 1" < 100 group
explain (verbose, costs off)
select array_agg(c1 order by c1 desc) from ft1 where c2 = 6 and c1 < 100 group by c2;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: (array_agg(c1 ORDER BY c1 DESC)), c2
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT array_agg(r1."C 1" ORDER BY r1."C 1" DESC), r1.c2 FROM "S 1"."T 1" r1 WHERE (("C 1" < 100)) AND ((c2 = 6)) GROUP BY r1.c2
+ Remote SQL: SELECT array_agg("C 1" ORDER BY "C 1" DESC), c2 FROM "S 1"."T 1" WHERE (("C 1" < 100)) AND ((c2 = 6)) GROUP BY c2
(4 rows)
select array_agg(c1 order by c1 desc) from ft1 where c2 = 6 and c1 < 100 group by c2;
@@ -6538,12 +6538,12 @@ select array_agg("C 1" order by "C 1" desc) from "S 1"."T 1" where c2 = 6 and "C
explain (verbose, costs off)
select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: (array_agg(c5 ORDER BY c1 DESC))
Relations: Aggregate on (public.ft2)
- Remote SQL: SELECT array_agg(r1.c5 ORDER BY r1."C 1" DESC) FROM "S 1"."T 1" r1 WHERE (("C 1" < 50)) AND ((c2 = 6))
+ Remote SQL: SELECT array_agg(c5 ORDER BY "C 1" DESC) FROM "S 1"."T 1" WHERE (("C 1" < 50)) AND ((c2 = 6))
(4 rows)
select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50;
@@ -6668,12 +6668,12 @@ select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from "S
explain (verbose, costs off)
select sum(c1%3), sum(distinct c1%3) filter (where c1%3 < 2), c2 from ft1 where c2 = 6 group by c2;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: (sum((c1 % 3))), (sum(DISTINCT (c1 % 3)) FILTER (WHERE ((c1 % 3) < 2))), c2
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT sum((r1."C 1" % 3)), sum(DISTINCT (r1."C 1" % 3)) FILTER (WHERE ((r1."C 1" % 3) < 2)), r1.c2 FROM "S 1"."T 1" r1 WHERE ((c2 = 6)) GROUP BY r1.c2
+ Remote SQL: SELECT sum(("C 1" % 3)), sum(DISTINCT ("C 1" % 3)) FILTER (WHERE (("C 1" % 3) < 2)), c2 FROM "S 1"."T 1" WHERE ((c2 = 6)) GROUP BY c2
(4 rows)
select sum(c1%3), sum(distinct c1%3) filter (where c1%3 < 2), c2 from ft1 where c2 = 6 group by c2;
@@ -6691,8 +6691,8 @@ select sum("C 1"%3), sum(distinct "C 1"%3) filter (where "C 1"%3 < 2), c2 from "
-- DISTINCT itself is not pushed down, whereas underneath aggregate is pushed
explain (verbose, costs off)
select distinct sum(c1), c2 from ft2 where c2 < 6 group by c2 order by c2;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
Unique
Output: (sum(c1)), c2
-> Sort
@@ -6701,7 +6701,7 @@ select distinct sum(c1), c2 from ft2 where c2 < 6 group by c2 order by c2;
-> Foreign Scan
Output: (sum(c1)), c2
Relations: Aggregate on (public.ft2)
- Remote SQL: SELECT sum(r1."C 1"), r1.c2 FROM "S 1"."T 1" r1 WHERE ((c2 < 6)) GROUP BY r1.c2
+ Remote SQL: SELECT sum("C 1"), c2 FROM "S 1"."T 1" WHERE ((c2 < 6)) GROUP BY c2
(9 rows)
select distinct sum(c1), c2 from ft2 where c2 < 6 group by c2 order by c2;
@@ -6720,15 +6720,15 @@ select distinct sum("C 1"), c2 from "S 1"."T 1" where c2 < 6 group by c2 order b
explain (verbose, costs off)
select sum(c1) filter (where c1 < 100) from ft1 group by c2 order by 1 nulls last;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Sort
Output: (sum(c1) FILTER (WHERE (c1 < 100))), c2
Sort Key: (sum(ft1.c1) FILTER (WHERE (ft1.c1 < 100)))
-> Foreign Scan
Output: (sum(c1) FILTER (WHERE (c1 < 100))), c2
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT sum(r1."C 1") FILTER (WHERE (r1."C 1" < 100)), r1.c2 FROM "S 1"."T 1" r1 GROUP BY r1.c2
+ Remote SQL: SELECT sum("C 1") FILTER (WHERE ("C 1" < 100)), c2 FROM "S 1"."T 1" GROUP BY c2
(7 rows)
select sum(c1) filter (where c1 < 100) from ft1 group by c2 order by 1 nulls last;
@@ -6785,15 +6785,15 @@ select sum("C 1") filter (where "C 1" < 100) from "S 1"."T 1" group by c2 order
explain (verbose, costs off)
select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------
Sort
Output: (sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5)))), c2
Sort Key: (sum(ft1.c1) FILTER (WHERE ((ft1.c1 < 100) AND (ft1.c2 > 5))))
-> Foreign Scan
Output: (sum(c1) FILTER (WHERE ((c1 < 100) AND (c2 > 5)))), c2
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT sum(r1."C 1") FILTER (WHERE ((r1."C 1" < 100) AND (r1.c2 > 5))), r1.c2 FROM "S 1"."T 1" r1 GROUP BY r1.c2
+ Remote SQL: SELECT sum("C 1") FILTER (WHERE (("C 1" < 100) AND (c2 > 5))), c2 FROM "S 1"."T 1" GROUP BY c2
(7 rows)
select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last;
@@ -6851,8 +6851,8 @@ select sum("C 1") filter (where "C 1" < 100 and c2 > 5) from "S 1"."T 1" group b
-- Outer query is aggregation query
explain (verbose, costs off)
select distinct (select count(*) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft1 t2 order by 1;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------
Unique
Output: ((SubPlan 1))
-> Sort
@@ -6861,7 +6861,7 @@ select distinct (select count(*) filter (where t2.c2 = 6 and t2.c1 < 10) from ft
-> Foreign Scan
Output: (SubPlan 1)
Relations: Aggregate on (public.ft1 t2)
- Remote SQL: SELECT count(*) FILTER (WHERE ((r1.c2 = 6) AND (r1."C 1" < 10))) FROM "S 1"."T 1" r1
+ Remote SQL: SELECT count(*) FILTER (WHERE ((c2 = 6) AND ("C 1" < 10))) FROM "S 1"."T 1"
SubPlan 1
-> Foreign Scan on public.ft1 t1
Output: (count(*) FILTER (WHERE ((t2.c2 = 6) AND (t2.c1 < 10))))
@@ -6883,8 +6883,8 @@ select distinct (select count(*) filter (where t2.c2 = 6 and t2."C 1" < 10) from
-- Inner query is aggregation query
explain (verbose, costs off)
select distinct (select count(t1.c1) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft1 t2 order by 1;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------
Unique
Output: ((SubPlan 1))
-> Sort
@@ -6897,7 +6897,7 @@ select distinct (select count(t1.c1) filter (where t2.c2 = 6 and t2.c1 < 10) fro
-> Foreign Scan
Output: (count(t1.c1) FILTER (WHERE ((t2.c2 = 6) AND (t2.c1 < 10))))
Relations: Aggregate on (public.ft1 t1)
- Remote SQL: SELECT count(r1."C 1") FILTER (WHERE (($1::integer = 6) AND ($2::integer < 10))) FROM "S 1"."T 1" r1 WHERE (("C 1" = 6))
+ Remote SQL: SELECT count("C 1") FILTER (WHERE (($1::integer = 6) AND ($2::integer < 10))) FROM "S 1"."T 1" WHERE (("C 1" = 6))
(13 rows)
select distinct (select count(t1.c1) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft1 t2 order by 1;
@@ -6944,12 +6944,12 @@ select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1;
explain (verbose, costs off)
select percentile_disc(0.25) within group (order by c2) from ft1;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------
Foreign Scan
Output: (percentile_disc('0.25'::double precision) WITHIN GROUP (ORDER BY c2))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT percentile_disc(0.25::double precision) WITHIN GROUP (ORDER BY r1.c2) FROM "S 1"."T 1" r1
+ Remote SQL: SELECT percentile_disc(0.25::double precision) WITHIN GROUP (ORDER BY c2) FROM "S 1"."T 1"
(4 rows)
select percentile_disc(0.25) within group (order by c2) from ft1;
@@ -6966,15 +6966,15 @@ select percentile_disc(0.25) within group (order by c2) from "S 1"."T 1";
explain (verbose, costs off)
select c2, percentile_cont(0.5) within group (order by c1) from ft1 where c2 < 10 group by c2 order by 1;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------
Sort
Output: c2, (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((c1)::double precision)))
Sort Key: ft1.c2
-> Foreign Scan
Output: c2, (percentile_cont('0.5'::double precision) WITHIN GROUP (ORDER BY ((c1)::double precision)))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT r1.c2, percentile_cont(0.5::double precision) WITHIN GROUP (ORDER BY (r1."C 1")) FROM "S 1"."T 1" r1 WHERE ((c2 < 10)) GROUP BY r1.c2
+ Remote SQL: SELECT c2, percentile_cont(0.5::double precision) WITHIN GROUP (ORDER BY ("C 1")) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY c2
(7 rows)
select c2, percentile_cont(0.5) within group (order by c1) from ft1 where c2 < 10 group by c2 order by 1;
@@ -6996,33 +6996,33 @@ select c2, percentile_cont(0.5) within group (order by "C 1") from "S 1"."T 1" w
-- Error from remote server
explain (verbose, costs off)
select c2, percentile_cont(c2) within group (order by c1) from ft1 group by c2 order by c2;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------
Sort
Output: c2, (percentile_cont((c2)::double precision) WITHIN GROUP (ORDER BY ((c1)::double precision)))
Sort Key: ft1.c2
-> Foreign Scan
Output: c2, (percentile_cont((c2)::double precision) WITHIN GROUP (ORDER BY ((c1)::double precision)))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT r1.c2, percentile_cont(r1.c2) WITHIN GROUP (ORDER BY (r1."C 1")) FROM "S 1"."T 1" r1 GROUP BY r1.c2
+ Remote SQL: SELECT c2, percentile_cont(c2) WITHIN GROUP (ORDER BY ("C 1")) FROM "S 1"."T 1" GROUP BY c2
(7 rows)
select c2, percentile_cont(c2) within group (order by c1) from ft1 group by c2 order by c2;
ERROR: percentile value 2 is not between 0 and 1
-CONTEXT: Remote SQL command: SELECT r1.c2, percentile_cont(r1.c2) WITHIN GROUP (ORDER BY (r1."C 1")) FROM "S 1"."T 1" r1 GROUP BY r1.c2
+CONTEXT: Remote SQL command: SELECT c2, percentile_cont(c2) WITHIN GROUP (ORDER BY ("C 1")) FROM "S 1"."T 1" GROUP BY c2
select c2, percentile_cont(c2) within group (order by "C 1") from "S 1"."T 1" group by c2 order by c2;
ERROR: percentile value 2 is not between 0 and 1
explain (verbose, costs off)
select c2, percentile_cont(c2/10::numeric) within group (order by c1) from ft1 where c2 < 10 group by c2 having percentile_cont(c2/10::numeric) within group (order by c1) < 500 order by c2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort
Output: c2, (percentile_cont((((c2)::numeric / '10'::numeric))::double precision) WITHIN GROUP (ORDER BY ((c1)::double precision)))
Sort Key: ft1.c2
-> Foreign Scan
Output: c2, (percentile_cont((((c2)::numeric / '10'::numeric))::double precision) WITHIN GROUP (ORDER BY ((c1)::double precision)))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT r1.c2, percentile_cont((r1.c2 / 10::numeric)) WITHIN GROUP (ORDER BY (r1."C 1")) FROM "S 1"."T 1" r1 WHERE ((c2 < 10)) GROUP BY r1.c2 HAVING ((percentile_cont((r1.c2 / 10::numeric)) WITHIN GROUP (ORDER BY (r1."C 1")) < 500::double precision))
+ Remote SQL: SELECT c2, percentile_cont((c2 / 10::numeric)) WITHIN GROUP (ORDER BY ("C 1")) FROM "S 1"."T 1" WHERE ((c2 < 10)) GROUP BY c2 HAVING ((percentile_cont((c2 / 10::numeric)) WITHIN GROUP (ORDER BY ("C 1")) < 500::double precision))
(7 rows)
select c2, percentile_cont(c2/10::numeric) within group (order by c1) from ft1 where c2 < 10 group by c2 having percentile_cont(c2/10::numeric) within group (order by c1) < 500 order by c2;
@@ -7041,12 +7041,12 @@ select c2, percentile_cont(c2/10::numeric) within group (order by "C 1") from "S
explain (verbose, costs off)
select rank('10'::varchar) within group (order by c6) from ft1;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Foreign Scan
Output: (rank('10'::character varying) WITHIN GROUP (ORDER BY c6))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT rank('10'::character varying) WITHIN GROUP (ORDER BY r1.c6) FROM "S 1"."T 1" r1
+ Remote SQL: SELECT rank('10'::character varying) WITHIN GROUP (ORDER BY c6) FROM "S 1"."T 1"
(4 rows)
select rank('10'::varchar) within group (order by c6) from ft1;
@@ -7091,15 +7091,15 @@ ALTER SERVER loopback OPTIONS (SET extensions 'postgres_fdw');
-- Now aggregate with VARIADIC will be pushed
explain (verbose, costs off)
select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
Sort
Output: c2, (least_agg(VARIADIC ARRAY[c1]))
Sort Key: ft1.c2
-> Foreign Scan
Output: c2, (least_agg(VARIADIC ARRAY[c1]))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT r1.c2, public.least_agg(VARIADIC ARRAY[r1."C 1"]) FROM "S 1"."T 1" r1 WHERE ((c2 < 100)) GROUP BY r1.c2
+ Remote SQL: SELECT c2, public.least_agg(VARIADIC ARRAY["C 1"]) FROM "S 1"."T 1" WHERE ((c2 < 100)) GROUP BY c2
(7 rows)
select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2;
@@ -7126,15 +7126,15 @@ select c2, least_agg("C 1") from "S 1"."T 1" where c2 < 100 group by c2 order by
-- Clauses with random() will be evaluated locally, and other clauses are pushed
explain (verbose, costs off)
select count(*) from (select c2 * random(), count(c1) from ft2 group by c2 having sum(c1) * random() < 100000 and avg(c1) < 50000) x;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
Aggregate
Output: count(*)
-> Foreign Scan
Output: ((ft2.c2)::double precision * random()), (NULL::bigint), ft2.c2
Filter: ((((sum(ft2.c1)))::double precision * random()) < '100000'::double precision)
Relations: Aggregate on (public.ft2)
- Remote SQL: SELECT NULL::bigint, r1.c2, sum(r1."C 1") FROM "S 1"."T 1" r1 GROUP BY r1.c2 HAVING ((avg(r1."C 1") < 50000::numeric))
+ Remote SQL: SELECT NULL::bigint, c2, sum("C 1") FROM "S 1"."T 1" GROUP BY c2 HAVING ((avg("C 1") < 50000::numeric))
(7 rows)
select count(*) from (select c2 * random(), count(c1) from ft2 group by c2 having sum(c1) * random() < 100000 and avg(c1) < 50000) x;
@@ -7151,15 +7151,15 @@ select count(*) from (select c2 * random(), count("C 1") from "S 1"."T 1" group
explain (verbose, costs off)
select c2, sum(c2) from ft1 group by c2 having avg(c2) + c2 < 10 order by c2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
Sort
Output: c2, (sum(c2))
Sort Key: ft1.c2
-> Foreign Scan
Output: c2, (sum(c2))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT r1.c2, sum(r1.c2) FROM "S 1"."T 1" r1 GROUP BY r1.c2 HAVING (((avg(r1.c2) + r1.c2) < 10::numeric))
+ Remote SQL: SELECT c2, sum(c2) FROM "S 1"."T 1" GROUP BY c2 HAVING (((avg(c2) + c2) < 10::numeric))
(7 rows)
select c2, sum(c2) from ft1 group by c2 having avg(c2) + c2 < 10 order by c2;
@@ -7197,8 +7197,8 @@ select count(t1.c3) from ft1 t1, ft1 t2 where t1.c1 = postgres_fdw_abs(t1.c2);
-- Subquery in FROM clause having aggregate
explain (verbose, costs off)
select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x where ft1.c2 = x.a group by x.b order by 1, 2;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
Sort
Output: (count(*)), x.b
Sort Key: (count(*)), x.b
@@ -7218,7 +7218,7 @@ select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x w
-> Foreign Scan
Output: ft1_1.c2, (sum(ft1_1.c1))
Relations: Aggregate on (public.ft1)
- Remote SQL: SELECT r1.c2, sum(r1."C 1") FROM "S 1"."T 1" r1 GROUP BY r1.c2
+ Remote SQL: SELECT c2, sum("C 1") FROM "S 1"."T 1" GROUP BY c2
(20 rows)
select count(*), x.b from ft1, (select c2 a, sum(c1) b from ft1 group by c2) x where ft1.c2 = x.a group by x.b order by 1, 2;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index c33916f..4dda273 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -1183,7 +1183,8 @@ postgresGetForeignPlan(PlannerInfo *root,
local_exprs = lappend(local_exprs, rinfo->clause);
}
- if (foreignrel->reloptkind == RELOPT_JOINREL)
+ if (foreignrel->reloptkind == RELOPT_JOINREL ||
+ foreignrel->reloptkind == RELOPT_UPPER_REL)
{
/* For a join relation, get the conditions from fdw_private structure */
remote_conds = fpinfo->remote_conds;
@@ -1204,6 +1205,13 @@ postgresGetForeignPlan(PlannerInfo *root,
{
ListCell *lc;
+ /*
+ * Right now, we only consider grouping and aggregation beyond
+ * joins. Queries involving aggregates or grouping do not require
+ * EPQ mechanism, hence should not have an outer plan here.
+ */
+ Assert(foreignrel->reloptkind != RELOPT_UPPER_REL);
+
outer_plan->targetlist = fdw_scan_tlist;
foreach(lc, local_exprs)
@@ -1223,17 +1231,6 @@ postgresGetForeignPlan(PlannerInfo *root,
}
}
}
- else if (foreignrel->reloptkind == RELOPT_UPPER_REL)
- {
- /*
- * For a grouping relation, get the conditions from fdw_private
- * structure.
- */
- remote_conds = fpinfo->remote_conds;
- local_exprs = fpinfo->local_conds;
-
- fdw_scan_tlist = build_tlist_to_deparse(root, foreignrel);
- }
/*
* Build the query string to be sent for execution, and identify
@@ -2478,7 +2475,7 @@ postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
* estimate_path_cost_size
* Get cost and size estimates for a foreign scan on given foreign relation
* either a base relation or a join between foreign relations or an upper
- * relation.
+ * relation containing foreign relations.
*
* param_join_conds are the parameterization clauses with outer relations.
* pathkeys specify the expected sort order if any for given path being costed.
@@ -4443,7 +4440,7 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
/*
* 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
- * this function to PgFdwRelationInfo passed in.
+ * this function to PgFdwRelationInfo of the input relation.
*/
static bool
foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel)
@@ -4461,7 +4458,7 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel)
if (query->groupingSets)
return false;
- /* Get the fpinfo of the outerrel */
+ /* Get the fpinfo of the underlying scan relation. */
ofpinfo = (PgFdwRelationInfo *) fpinfo->outerrel->fdw_private;
/*
@@ -4474,10 +4471,10 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel)
/*
* Evaluate grouping targets and check whether they are safe to push down
- * to the remote side. All GROUP BY expressions will be part of the
- * grouping target and thus no need to evaluate it separately. While doing
- * so, add required expressions into target list which can then be used to
- * pass to remote server.
+ * to the foreign side. All GROUP BY expressions will be part of the
+ * grouping target and thus there is no need to evaluate it separately.
+ * While doing so, add required expressions into target list which
+ * can then be used to pass to foreign server.
*/
i = 0;
foreach(lc, grouping_target->exprs)
@@ -4490,7 +4487,10 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel)
if (sgref && query->groupClause && query->groupingSets == NIL &&
get_sortgroupref_clause_noerr(sgref, query->groupClause) != NULL)
{
- /* Expression matched with GROUP BY, check as is */
+ /*
+ * If any of the GROUP BY expression is not shippable we can not
+ * push down aggregation to the foreign server.
+ */
if (!is_foreign_expr(root, grouped_rel, expr, true))
return false;
@@ -4521,7 +4521,7 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel)
/*
* Add aggregates, if any, into tlist. Plain Var nodes pulled
- * are already part of GROUP BY and thus no need to add them
+ * are already part of some and thus no need to add them
* explicitly.
*/
foreach(l, aggvars)
@@ -4654,8 +4654,8 @@ postgresGetForeignUpperPaths(PlannerInfo *root, UpperRelationKind stage,
*
* Add foreign path for grouping and/or aggregation.
*
- * input_rel contains the underneath scan rel
- * grouped_rel is the rel for which paths need to be added
+ * input_rel represents the underlying scan. The paths are added to the
+ * grouped_rel.
*/
static void
create_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
