diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c index 691658f..88b76ff 100644 --- a/contrib/postgres_fdw/deparse.c +++ b/contrib/postgres_fdw/deparse.c @@ -38,6 +38,7 @@ #include "access/heapam.h" #include "access/htup_details.h" #include "access/sysattr.h" +#include "catalog/pg_aggregate.h" #include "catalog/pg_collation.h" #include "catalog/pg_namespace.h" #include "catalog/pg_operator.h" @@ -47,6 +48,7 @@ #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "nodes/plannodes.h" +#include "nodes/print.h" #include "optimizer/clauses.h" #include "optimizer/prep.h" #include "optimizer/tlist.h" @@ -55,7 +57,9 @@ #include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/rel.h" +#include "utils/ruleutils.h" #include "utils/syscache.h" +#include "utils/typcache.h" /* @@ -65,6 +69,7 @@ typedef struct foreign_glob_cxt { PlannerInfo *root; /* global planner state */ RelOptInfo *foreignrel; /* the foreign relation we are planning for */ + bool foragg; /* is in context of aggregate push down? */ } foreign_glob_cxt; /* @@ -159,6 +164,11 @@ static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context); static void appendConditions(List *exprs, deparse_expr_cxt *context); static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *joinrel, bool use_alias, List **params_list); +static void deparseFromClause(List *remote_conds, deparse_expr_cxt *context); +static void deparseAggref(Aggref *node, deparse_expr_cxt *context); +static void appendGroupByClause(List *tlist, deparse_expr_cxt *context); +static void deparseAggOrderBy(List *orderList, List *targetList, + deparse_expr_cxt *context); /* @@ -183,7 +193,7 @@ classifyConditions(PlannerInfo *root, { RestrictInfo *ri = (RestrictInfo *) lfirst(lc); - if (is_foreign_expr(root, baserel, ri->clause)) + if (is_foreign_expr(root, baserel, ri->clause, false)) *remote_conds = lappend(*remote_conds, ri); else *local_conds = lappend(*local_conds, ri); @@ -196,7 +206,8 @@ classifyConditions(PlannerInfo *root, bool is_foreign_expr(PlannerInfo *root, RelOptInfo *baserel, - Expr *expr) + Expr *expr, + bool foragg) { foreign_glob_cxt glob_cxt; foreign_loc_cxt loc_cxt; @@ -207,6 +218,7 @@ is_foreign_expr(PlannerInfo *root, */ glob_cxt.root = root; glob_cxt.foreignrel = baserel; + glob_cxt.foragg = foragg; loc_cxt.collation = InvalidOid; loc_cxt.state = FDW_COLLATE_NONE; if (!foreign_expr_walker((Node *) expr, &glob_cxt, &loc_cxt)) @@ -631,6 +643,113 @@ foreign_expr_walker(Node *node, check_type = false; } break; + case T_Aggref: + { + Aggref *agg = (Aggref *) node; + ListCell *lc; + + /* + * Aggregate is safe to pushdown if + * 1. It is a built-in aggregate + * 2. All its arguments are safe to push-down + * 3. Other expressions involved like aggorder, aggdistinct are + * safe to be pushed down. + */ + + /* Not safe to pushdown when not in grouping context */ + if (!glob_cxt->foragg) + return false; + + /* Aggregates other than simple one are non-pushable. */ + if (agg->aggsplit != AGGSPLIT_SIMPLE) + return false; + + /* + * If aggregate function used by the expression is not + * shippable, it can't be sent to remote because it might + * have incompatible semantics on remote side. + */ + if (!is_shippable(agg->aggfnoid, ProcedureRelationId, fpinfo)) + return false; + + /* Recurse to input subexpressions. */ + if (!foreign_expr_walker((Node *) agg->aggdirectargs, + glob_cxt, &inner_cxt)) + return false; + + foreach(lc, agg->args) + { + Node *n = (Node *) lfirst(lc); + + /* If TargetEntry, extract the expression from it */ + if (IsA(n, TargetEntry)) + { + TargetEntry *tle = (TargetEntry *) n; + n = (Node *) tle->expr; + } + + if (!foreign_expr_walker(n, glob_cxt, &inner_cxt)) + return false; + } + + if (agg->aggorder) + { + /* Get a list of order by expressions from arg list. */ + List *olist = get_sortgrouplist_exprs(agg->aggorder, + agg->args); + foreach (lc, olist) + { + Node *n = (Node *) lfirst(lc); + if (!foreign_expr_walker(n, glob_cxt, &inner_cxt)) + return false; + } + } + + if (agg->aggdistinct) + { + /* Get a list of distinct expressions from arg list. */ + List *dlist = get_sortgrouplist_exprs(agg->aggdistinct, + agg->args); + foreach (lc, dlist) + { + Node *n = (Node *) lfirst(lc); + if (!foreign_expr_walker(n, glob_cxt, &inner_cxt)) + return false; + } + } + + if (!foreign_expr_walker((Node *) agg->aggfilter, + glob_cxt, &inner_cxt)) + return false; + + /* + * If aggregate's input collation is not derived from a foreign + * Var, it can't be sent to remote. + */ + if (agg->inputcollid == InvalidOid) + /* OK, inputs are all noncollatable */ ; + else if (inner_cxt.state != FDW_COLLATE_SAFE || + agg->inputcollid != inner_cxt.collation) + return false; + + /* + * Detect whether node is introducing a collation not derived + * from a foreign Var. (If so, we just mark it unsafe for now + * rather than immediately returning false, since the parent + * node might not care.) + */ + collation = agg->aggcollid; + if (collation == InvalidOid) + state = FDW_COLLATE_NONE; + else if (inner_cxt.state == FDW_COLLATE_SAFE && + collation == inner_cxt.collation) + state = FDW_COLLATE_SAFE; + else if (collation == DEFAULT_COLLATION_OID) + state = FDW_COLLATE_NONE; + else + state = FDW_COLLATE_UNSAFE; + } + break; default: /* @@ -723,11 +842,26 @@ deparse_type_name(Oid type_oid, int32 typemod) * foreign server for the given relation. */ List * -build_tlist_to_deparse(RelOptInfo *foreignrel) +build_tlist_to_deparse(PlannerInfo *root, RelOptInfo *foreignrel) { List *tlist = NIL; PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private; + if (foreignrel->reloptkind == RELOPT_UPPER_REL) + { + PathTarget *ptarget = root->upper_targets[UPPERREL_GROUP_AGG]; + + /* Add Vars and aggregates from local conditions */ + tlist = add_to_flat_tlist(fpinfo->grouped_tlist, + pull_var_clause((Node *) fpinfo->local_conds, + PVC_INCLUDE_AGGREGATES)); + + /* Transfer any sortgroupref data to the replacement tlist */ + apply_pathtarget_labeling_to_tlist(tlist, ptarget); + + return tlist; + } + /* * We require columns specified in foreignrel->reltarget->exprs and those * required for evaluating the local conditions. @@ -769,27 +903,32 @@ deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel, { deparse_expr_cxt context; - /* We handle relations for foreign tables and joins between those */ - Assert(rel->reloptkind == RELOPT_JOINREL || - rel->reloptkind == RELOPT_BASEREL || - rel->reloptkind == RELOPT_OTHER_MEMBER_REL); + /* We handle all relations other than dead one. */ + Assert(rel->reloptkind != RELOPT_DEADREL); - /* Fill portions of context common to join and base relation */ + /* Fill portions of context common to upper, join and base relation */ context.buf = buf; context.root = root; context.foreignrel = rel; context.params_list = params_list; - /* Construct SELECT clause and FROM clause */ + /* Construct SELECT clause */ deparseSelectSql(tlist, retrieved_attrs, &context); - /* - * Construct WHERE clause - */ - if (remote_conds) + /* Construct FROM clause */ + deparseFromClause(remote_conds, &context); + + if (rel->reloptkind == RELOPT_UPPER_REL) { - appendStringInfo(buf, " WHERE "); - appendConditions(remote_conds, &context); + /* Append GROUP BY clause */ + appendGroupByClause(tlist, &context); + + /* Append HAVING clause */ + if (remote_conds) + { + appendStringInfo(buf, " HAVING "); + appendConditions(remote_conds, &context); + } } /* Add ORDER BY clause if we found any useful pathkeys */ @@ -803,7 +942,7 @@ deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel, /* * Construct a simple SELECT statement that retrieves desired columns * of the specified foreign table, and append it to "buf". The output - * contains just "SELECT ... FROM ....". + * contains just "SELECT ... ". * * We also create an integer List of the columns being retrieved, which is * returned to *retrieved_attrs. @@ -824,7 +963,8 @@ deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context) */ appendStringInfoString(buf, "SELECT "); - if (foreignrel->reloptkind == RELOPT_JOINREL) + if (foreignrel->reloptkind == RELOPT_JOINREL || + foreignrel->reloptkind == RELOPT_UPPER_REL) { /* For a join relation use the input tlist */ deparseExplicitTargetList(tlist, retrieved_attrs, context); @@ -847,14 +987,54 @@ deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context) fpinfo->attrs_used, false, retrieved_attrs); heap_close(rel, NoLock); } +} + +/* + * Construct a FROM clause and WHERE clause, if any. And append it to "buf". + * The final output contains "SELECT ... FROM ... [WHERE ... ]". + * + * remote_conds is the list of WHERE clauses, NIL if none. + */ +static void +deparseFromClause(List *remote_conds, deparse_expr_cxt *context) +{ + StringInfo buf = context->buf; + RelOptInfo *foreignrel = context->foreignrel; + PlannerInfo *root = context->root; + PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private; + RelOptInfo *scan_rel = foreignrel; /* - * Construct FROM clause + * For aggregates the FROM clause will be build from underneath scan rel. + * WHERE clause conditions too 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, foreignrel, - (foreignrel->reloptkind == RELOPT_JOINREL), + deparseFromExprForRel(buf, root, scan_rel, + foreignrel->reloptkind == RELOPT_UPPER_REL ? true : + (scan_rel->reloptkind == RELOPT_JOINREL), context->params_list); + + /* Construct WHERE clause */ + if (remote_conds) + { + appendStringInfo(buf, " WHERE "); + appendConditions(remote_conds, context); + } + + /* Restore context's foreignrel */ + if (foreignrel->reloptkind == RELOPT_UPPER_REL) + context->foreignrel = foreignrel; } /* @@ -1036,7 +1216,7 @@ deparseLockingClause(deparse_expr_cxt *context) * Deparse conditions from the provided list and append them to buf. * * The conditions in the list are assumed to be ANDed. This function is used to - * deparse both WHERE clauses and JOIN .. ON clauses. + * deparse WHERE clauses, JOIN .. ON clauses and HAVING clauses. */ static void appendConditions(List *exprs, deparse_expr_cxt *context) @@ -1126,22 +1306,15 @@ deparseExplicitTargetList(List *tlist, List **retrieved_attrs, foreach(lc, tlist) { TargetEntry *tle = (TargetEntry *) lfirst(lc); - Var *var; /* Extract expression if TargetEntry node */ Assert(IsA(tle, TargetEntry)); - var = (Var *) tle->expr; - - /* We expect only Var nodes here */ - if (!IsA(var, Var)) - elog(ERROR, "non-Var not expected in target list"); if (i > 0) appendStringInfoString(buf, ", "); - deparseVar(var, context); + deparseExpr((Expr *) tle->expr, context); *retrieved_attrs = lappend_int(*retrieved_attrs, i + 1); - i++; } @@ -1849,6 +2022,9 @@ deparseExpr(Expr *node, deparse_expr_cxt *context) case T_ArrayExpr: deparseArrayExpr((ArrayExpr *) node, context); break; + case T_Aggref: + deparseAggref((Aggref *)node, context); + break; default: elog(ERROR, "unsupported expression type for deparse: %d", (int) nodeTag(node)); @@ -1867,7 +2043,8 @@ deparseExpr(Expr *node, deparse_expr_cxt *context) static void deparseVar(Var *node, deparse_expr_cxt *context) { - bool qualify_col = (context->foreignrel->reloptkind == RELOPT_JOINREL); + bool qualify_col = (context->foreignrel->reloptkind == RELOPT_JOINREL || + context->foreignrel->reloptkind == RELOPT_UPPER_REL); if (bms_is_member(node->varno, context->foreignrel->relids) && node->varlevelsup == 0) @@ -2420,6 +2597,181 @@ deparseArrayExpr(ArrayExpr *node, deparse_expr_cxt *context) } /* + * Deparse an Aggref node. + */ +static void +deparseAggref(Aggref *node, deparse_expr_cxt *context) +{ + StringInfo buf = context->buf; + HeapTuple proctup; + Form_pg_proc procform; + const char *proname; + bool use_variadic; + + /* Only basic, non-split aggregation accepted. */ + Assert(node->aggsplit == AGGSPLIT_SIMPLE); + + /* Find aggregate name from aggfnoid which is a pg_proc entry */ + proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(node->aggfnoid)); + if (!HeapTupleIsValid(proctup)) + elog(ERROR, "cache lookup failed for aggregate function %u", + node->aggfnoid); + procform = (Form_pg_proc) GETSTRUCT(proctup); + + /* Check if need to print VARIADIC (cf. ruleutils.c) */ + use_variadic = node->aggvariadic; + + /* Print schema name only if it's not pg_catalog */ + if (procform->pronamespace != PG_CATALOG_NAMESPACE) + { + const char *schemaname; + + schemaname = get_namespace_name(procform->pronamespace); + appendStringInfo(buf, "%s.", quote_identifier(schemaname)); + } + + /* Deparse the aggregate function name ... */ + proname = NameStr(procform->proname); + appendStringInfo(buf, "%s(", quote_identifier(proname)); + + ReleaseSysCache(proctup); + + /* Add DISTINCT */ + appendStringInfo(buf, "%s", (node->aggdistinct != NIL) ? "DISTINCT " : ""); + + if (AGGKIND_IS_ORDERED_SET(node->aggkind)) + { + /* Add WITHIN GROUP (ORDER BY ..) */ + ListCell *arg; + + Assert(!node->aggvariadic); + Assert(node->aggorder != NIL); + + foreach(arg, node->aggdirectargs) + deparseExpr((Expr *) lfirst(arg), context); + + appendStringInfoString(buf, ") WITHIN GROUP (ORDER BY "); + deparseAggOrderBy(node->aggorder, node->args, context); + } + else + { + /* aggstar can be set only in zero-argument aggregates */ + if (node->aggstar) + appendStringInfoChar(buf, '*'); + else + { + ListCell *arg; + bool first = true; + + /* ... and all the arguments */ + foreach(arg, node->args) + { + TargetEntry *tle = (TargetEntry *) lfirst(arg); + Node *n = (Node *) tle->expr; + + if (tle->resjunk) + continue; + + if (!first) + appendStringInfoString(buf, ", "); + first = false; + + /* Add VARIADIC */ + if (use_variadic && lnext(arg) == NULL) + appendStringInfoString(buf, "VARIADIC "); + + deparseExpr((Expr *) n, context); + } + } + + /* Add ORDER BY */ + if (node->aggorder != NIL) + { + appendStringInfoString(buf, " ORDER BY "); + deparseAggOrderBy(node->aggorder, node->args, context); + } + } + + /* Add FILTER (WHERE ..) */ + if (node->aggfilter != NULL) + { + appendStringInfoString(buf, ") FILTER (WHERE "); + deparseExpr((Expr *) node->aggfilter, context); + } + + appendStringInfoChar(buf, ')'); +} + +/* + * Deparse ORDER BY within aggregate function. + */ +static void +deparseAggOrderBy(List *orderList, List *targetList, deparse_expr_cxt *context) +{ + StringInfo buf = context->buf; + ListCell *lc; + bool first = true; + + foreach (lc, orderList) + { + SortGroupClause *srt = (SortGroupClause *) lfirst(lc); + Expr *expr; + Oid sortcoltype; + TypeCacheEntry *typentry; + TargetEntry *tle; + + if (!first) + appendStringInfoString(buf, ", "); + first = false; + + tle = get_sortgroupref_tle(srt->tleSortGroupRef, targetList); + 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, ")"); + } + + sortcoltype = exprType((Node *) expr); + /* See whether operator is default < or > for datatype */ + typentry = lookup_type_cache(sortcoltype, + TYPECACHE_LT_OPR | TYPECACHE_GT_OPR); + if (srt->sortop == typentry->lt_opr) + { + /* ASC is default, so emit nothing for it */ + if (srt->nulls_first) + appendStringInfoString(buf, " NULLS FIRST"); + } + else if (srt->sortop == typentry->gt_opr) + { + appendStringInfoString(buf, " DESC"); + /* DESC defaults to NULLS FIRST */ + if (!srt->nulls_first) + appendStringInfoString(buf, " NULLS LAST"); + } + else + { + appendStringInfo(buf, " USING %s", + generate_operator_name(srt->sortop, + sortcoltype, + sortcoltype)); + /* be specific to eliminate ambiguity */ + if (srt->nulls_first) + appendStringInfoString(buf, " NULLS FIRST"); + else + appendStringInfoString(buf, " NULLS LAST"); + } + } +} + +/* * Print the representation of a parameter to be sent to the remote side. * * Note: we always label the Param's type explicitly rather than relying on @@ -2464,6 +2816,54 @@ printRemotePlaceholder(Oid paramtype, int32 paramtypmod, } /* + * Deparse GROUP BY clause. + */ +static void +appendGroupByClause(List *tlist, deparse_expr_cxt *context) +{ + StringInfo buf = context->buf; + Query *query = context->root->parse; + + if (query->groupClause != NULL) + { + appendStringInfo(buf, " GROUP BY "); + + if (query->groupingSets == NIL) + { + ListCell *lc; + bool first = true; + + 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; + + 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, ")"); + } + } + } + } +} + +/* * Deparse ORDER BY clause according to the given pathkeys for given base * relation. From given pathkeys expressions belonging entirely to the given * base relation are obtained and deparsed. diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index d97e694..6ba1895 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -861,14 +861,13 @@ 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 --------------------------------------------------------------------------- - Aggregate - Output: count(c3) - -> Foreign Scan on public.ft1 t1 - Output: c3 - Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = abs(c2))) -(5 rows) + 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))) +(4 rows) SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = abs(t1.c2); count @@ -878,14 +877,13 @@ 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 ---------------------------------------------------------------------- - Aggregate - Output: count(c3) - -> Foreign Scan on public.ft1 t1 - Output: c3 - Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = c2)) -(5 rows) + 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)) +(4 rows) SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = t1.c2; count @@ -937,14 +935,13 @@ 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 ----------------------------------------------------------------------------------------------- - Aggregate - Output: count(c3) - -> Foreign Scan on public.ft1 t1 - Output: c3 - Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = public.postgres_fdw_abs(c2))) -(5 rows) + 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))) +(4 rows) SELECT count(c3) FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2); count @@ -954,14 +951,13 @@ 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 ----------------------------------------------------------------------------------------- - Aggregate - Output: count(c3) - -> Foreign Scan on public.ft1 t1 - Output: c3 - Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" OPERATOR(public.===) c2)) -(5 rows) + 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)) +(4 rows) SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2; count @@ -4441,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 -------------------------------------------------------------------- - Aggregate - Output: count(*) - -> Foreign Scan on public.ft1 - Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE ((c2 < 0)) + QUERY PLAN +-------------------------------------------------------------------- + Foreign Scan + Output: (count(*)) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT count(*) FROM "S 1"."T 1" r1 WHERE ((c2 < 0)) (4 rows) SELECT count(*) FROM ft1 WHERE c2 < 0; @@ -4485,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 --------------------------------------------------------------------- - Aggregate - Output: count(*) - -> Foreign Scan on public.ft1 - Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE ((c2 >= 0)) + QUERY PLAN +--------------------------------------------------------------------- + Foreign Scan + Output: (count(*)) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT count(*) FROM "S 1"."T 1" r1 WHERE ((c2 >= 0)) (4 rows) SELECT count(*) FROM ft1 WHERE c2 >= 0; @@ -5881,3 +5877,1480 @@ AND ftoptions @> array['fetch_size=60000']; (1 row) ROLLBACK; +-- Test Aggregate Push Down +-- Both ft1 and ft2 are used to exercise cost estimates when +-- use_remote_estimate is false and true respectively. +-- A. Simple aggregates +explain (verbose, costs off) +select sum(c1), avg(c1), min(c2), max(c1), stddev(c2) from ft1; + 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 +(4 rows) + +select sum(c1), avg(c1), min(c2), max(c1), stddev(c2) from ft1; + sum | avg | min | max | stddev +--------+----------------------+-----+------+------------------ + 423821 | 515.5973236009732360 | 1 | 1218 | 233.490919491320 +(1 row) + +select sum("C 1"), avg("C 1"), min(c2), max("C 1"), stddev(c2) from "S 1"."T 1"; + sum | avg | min | max | stddev +--------+----------------------+-----+------+------------------ + 423821 | 515.5973236009732360 | 1 | 1218 | 233.490919491320 +(1 row) + +explain (verbose, costs off) +select sum(c1) * random() from ft1; + 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 +(4 rows) + +explain (verbose, costs off) +select count(c6) from ft1; + QUERY PLAN +------------------------------------------------------- + Foreign Scan + Output: (count(c6)) + Relations: Aggregate on (public.ft1) + Remote SQL: SELECT count(r1.c6) FROM "S 1"."T 1" r1 +(4 rows) + +select count(c6) from ft1; + count +------- + 801 +(1 row) + +select count(c6) from "S 1"."T 1"; + count +------- + 801 +(1 row) + +-- Aggregate is not pushed down as aggregation contains random() +explain (verbose, costs off) +select sum(c1 * random()), avg(c1) from ft1; + QUERY PLAN +------------------------------------------------------------- + Aggregate + Output: sum(((c1)::double precision * random())), avg(c1) + -> Foreign Scan on public.ft1 + Output: c1 + Remote SQL: SELECT "C 1" FROM "S 1"."T 1" +(5 rows) + +explain (verbose, costs off) +select sum(c1) from ft2 where c2 < 5; + 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)) +(4 rows) + +select sum(c1) from ft2 where c2 < 5; + sum +------- + 50711 +(1 row) + +select sum("C 1") from "S 1"."T 1" where c2 < 5; + sum +------- + 50711 +(1 row) + +explain (verbose, costs off) +select sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan + Output: (sum(t1.c1)), (avg(t2.c1)) + Relations: Aggregate on ((public.ft1 t1) INNER JOIN (public.ft1 t2)) + Remote SQL: SELECT sum(r1."C 1"), avg(r2."C 1") FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2.c2 = 6)) AND ((r1.c2 = 6)))) +(4 rows) + +select sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6; + sum | avg +---------+---------------------- + 5010000 | 501.0000000000000000 +(1 row) + +select sum(t1."C 1"), avg(t1."C 1") from "S 1"."T 1" t1 inner join "S 1"."T 1" t2 on (t1.c2 = t2.c2) where t1.c2 = 6; + sum | avg +---------+---------------------- + 5010000 | 501.0000000000000000 +(1 row) + +explain (verbose, costs off) +select count(*) from ft1 t1 inner join ft2 t2 on (t1.c2 = t2.c2) where t2.c2 = 6; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------- + Foreign Scan + Output: (count(*)) + Relations: Aggregate on ((public.ft1 t1) INNER JOIN (public.ft2 t2)) + Remote SQL: SELECT count(*) FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2.c2 = 6)) AND ((r1.c2 = 6)))) +(4 rows) + +select count(*) from ft1 t1 inner join ft2 t2 on (t1.c2 = t2.c2) where t2.c2 = 6; + count +------- + 10000 +(1 row) + +select count(*) from "S 1"."T 1" t1 inner join "S 1"."T 1" t2 on (t1.c2 = t2.c2) where t2.c2 = 6; + count +------- + 10000 +(1 row) + +-- Not pushed down due to local conditions present in underneath input rel +explain (verbose, costs off) +select count(*) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2 * random()); + QUERY PLAN +------------------------------------------------------------------------------------------- + Aggregate + Output: count(*) + -> Nested Loop + Join Filter: ((t1.c2)::double precision = ((t2.c2)::double precision * random())) + -> Foreign Scan on public.ft1 t1 + Output: t1.c2 + Remote SQL: SELECT c2 FROM "S 1"."T 1" + -> Materialize + Output: t2.c2 + -> Foreign Scan on public.ft1 t2 + Output: t2.c2 + Remote SQL: SELECT c2 FROM "S 1"."T 1" +(12 rows) + +-- B. Aggregates with GROUP BY +explain (verbose, costs off) +select c2, count(*) from ft1 group by c2 order by 1; + 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 +(7 rows) + +select c2, count(*) from ft1 group by c2 order by 1; + c2 | count +-----+------- + 1 | 99 + 2 | 2 + 6 | 100 + 42 | 100 + 44 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 + 503 | 1 + 507 | 2 + 509 | 100 + 608 | 100 + 609 | 2 + 708 | 2 + 818 | 2 +(21 rows) + +select c2, count(*) from "S 1"."T 1" group by c2 order by 1; + c2 | count +-----+------- + 1 | 99 + 2 | 2 + 6 | 100 + 42 | 100 + 44 | 100 + 100 | 2 + 101 | 2 + 104 | 2 + 106 | 2 + 201 | 1 + 204 | 1 + 303 | 100 + 403 | 2 + 407 | 100 + 503 | 1 + 507 | 2 + 509 | 100 + 608 | 100 + 609 | 2 + 708 | 2 + 818 | 2 +(21 rows) + +explain (verbose, costs off) +select count(c1), length(c6) from ft2 group by c6 order by 1, 2; + 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 +(7 rows) + +select count(c1), length(c6) from ft2 group by c6 order by 1, 2; + count | length +-------+-------- + 1 | 4 + 21 | + 100 | 1 + 100 | 1 + 100 | 1 + 100 | 1 + 100 | 1 + 100 | 1 + 100 | 1 + 100 | 1 +(10 rows) + +select count("C 1"), length(c6) from "S 1"."T 1" group by c6 order by 1, 2; + count | length +-------+-------- + 1 | 4 + 21 | + 100 | 1 + 100 | 1 + 100 | 1 + 100 | 1 + 100 | 1 + 100 | 1 + 100 | 1 + 100 | 1 +(10 rows) + +explain (verbose, costs off) +select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; + 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)) +(7 rows) + +select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; + ?column? | ?column? +----------+---------- + 0 | 0 + 1 | 4 + 3 | 1800 + 21 | 88200 + 22 | 96800 + 50 | 20100 + 52 | 10816 + 53 | 11236 + 100 | 20100 + 102 | 20808 + 151 | 4575300 + 201 | 162006 + 203 | 8262100 + 251 | 126253 + 253 | 256542 + 254 | 12928600 + 304 | 18853472 + 354 | 501264 + 409 | 669124 +(19 rows) + +select c2/2, sum(c2) * (c2/2) from "S 1"."T 1" group by c2/2 order by c2/2; + ?column? | ?column? +----------+---------- + 0 | 0 + 1 | 4 + 3 | 1800 + 21 | 88200 + 22 | 96800 + 50 | 20100 + 52 | 10816 + 53 | 11236 + 100 | 20100 + 102 | 20808 + 151 | 4575300 + 201 | 162006 + 203 | 8262100 + 251 | 126253 + 253 | 256542 + 254 | 12928600 + 304 | 18853472 + 354 | 501264 + 409 | 669124 +(19 rows) + +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 +---------------------------------------------------------------------------------------------------------------------------- + Aggregate + Output: count(ft1.c2), sum(ft1.c2) + -> Sort + Output: ft1.c2, (sum(ft1.c1)), (sqrt((ft1.c1)::double precision)) + Sort Key: ft1.c2, (sum(ft1.c1)) + -> 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")) +(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; + count | sum +-------+-------- + 822 | 199823 +(1 row) + +select count(a), sum(a) from (select c2 a, sum("C 1") b from "S 1"."T 1" group by c2, sqrt("C 1") order by 1, 2) x; + count | sum +-------+-------- + 822 | 199823 +(1 row) + +-- 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 +------------------------------------------------------------------------------------------------- + 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" +(4 rows) + +--select c1 * random(), sum(c1) * c1 from ft1 group by c1; +--select "C 1" * random(), sum("C 1") * "C 1" from "S 1"."T 1" group by "C 1"; +-- Aggregate is not pushed down as random() is part of group by expression +explain (verbose, costs off) +select c1 * random() from ft2 group by c1 * random(), c2; + QUERY PLAN +-------------------------------------------------------------- + HashAggregate + Output: (((c1)::double precision * random())), c2 + Group Key: ((ft2.c1)::double precision * random()), ft2.c2 + -> Foreign Scan on public.ft2 + Output: ((c1)::double precision * random()), c2 + Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" +(6 rows) + +-- 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 +---------------------------------------------------------------------------------------------------------------------------- + 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)) +(7 rows) + +select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 order by 1; + c2 | sum +-----+------- + 44 | 49900 + 303 | 49800 +(2 rows) + +select c2, sum("C 1") from "S 1"."T 1" group by c2 having avg("C 1") < 500 order by 1; + c2 | sum +-----+------- + 44 | 49900 + 303 | 49800 +(2 rows) + +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 +----------------------------------------------------------------------------------------------------------------------------------------------------------- + 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)) +(7 rows) + +select c2, sum(c1) from ft1 group by c2 having avg(c1) < 500 and sum(c1) < 200000 order by c2; + c2 | sum +-----+------- + 44 | 49900 + 303 | 49800 +(2 rows) + +select c2, sum("C 1") from "S 1"."T 1" group by c2 having avg("C 1") < 500 and sum("C 1") < 200000 order by c2; + c2 | sum +-----+------- + 44 | 49900 + 303 | 49800 +(2 rows) + +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 +----------------------------------------------------------------------------------------------------------------------------------------------------------------- + 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)) +(7 rows) + +select c5, count(c2) from ft1 group by c5, sqrt(c2) having sqrt(max(c2)) < 2 order by 1, 2; + c5 | count +--------------------------+------- + Fri Jan 02 00:00:00 1970 | 1 + Fri Jan 02 00:00:00 1970 | 9 + Mon Jan 12 00:00:00 1970 | 10 + Thu Jan 22 00:00:00 1970 | 10 + Sun Feb 01 00:00:00 1970 | 10 + Wed Feb 11 00:00:00 1970 | 10 + Sat Feb 21 00:00:00 1970 | 10 + Tue Mar 03 00:00:00 1970 | 10 + Fri Mar 13 00:00:00 1970 | 10 + Mon Mar 23 00:00:00 1970 | 10 + Thu Apr 02 00:00:00 1970 | 10 + | 1 +(12 rows) + +select c5, count(c2) from "S 1"."T 1" group by c5, sqrt(c2) having sqrt(max(c2)) < 2 order by 1, 2; + c5 | count +--------------------------+------- + Fri Jan 02 00:00:00 1970 | 1 + Fri Jan 02 00:00:00 1970 | 9 + Mon Jan 12 00:00:00 1970 | 10 + Thu Jan 22 00:00:00 1970 | 10 + Sun Feb 01 00:00:00 1970 | 10 + Wed Feb 11 00:00:00 1970 | 10 + Sat Feb 21 00:00:00 1970 | 10 + Tue Mar 03 00:00:00 1970 | 10 + Fri Mar 13 00:00:00 1970 | 10 + Mon Mar 23 00:00:00 1970 | 10 + Thu Apr 02 00:00:00 1970 | 10 + | 1 +(12 rows) + +-- Having clause with random() will be evaluated locally +explain (verbose, costs off) +select c2, sum(c1) from ft2 group by c2 having sum(c1) * random() < 500000 order by c2; + QUERY PLAN +----------------------------------------------------------------------------------------------- + Sort + Output: c2, (sum(c1)) + Sort Key: ft2.c2 + -> Foreign Scan + 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 +(8 rows) + +select c2, sum(c1) from ft2 group by c2 having sum(c1) * random() < 500000 order by c2; + c2 | sum +-----+------- + 1 | 49599 + 2 | 1112 + 6 | 50100 + 42 | 50500 + 44 | 49900 + 100 | 2030 + 101 | 2012 + 104 | 2018 + 106 | 2022 + 201 | 1101 + 204 | 1104 + 303 | 49800 + 403 | 2016 + 407 | 50200 + 503 | 1103 + 507 | 2024 + 509 | 50400 + 608 | 50300 + 609 | 2028 + 708 | 2026 + 818 | 2426 +(21 rows) + +select c2, sum("C 1") from "S 1"."T 1" group by c2 having sum("C 1") * random() < 500000 order by c2; + c2 | sum +-----+------- + 1 | 49599 + 2 | 1112 + 6 | 50100 + 42 | 50500 + 44 | 49900 + 100 | 2030 + 101 | 2012 + 104 | 2018 + 106 | 2022 + 201 | 1101 + 204 | 1104 + 303 | 49800 + 403 | 2016 + 407 | 50200 + 503 | 1103 + 507 | 2024 + 509 | 50400 + 608 | 50300 + 609 | 2028 + 708 | 2026 + 818 | 2426 +(21 rows) + +-- 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 +---------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 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)) +(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; + count +------- + 39 +(1 row) + +select count(*) from (select c5, count("C 1") from "S 1"."T 1" group by c5, sqrt(c2) having avg("C 1") * random() < 500 and avg("C 1") < 500) x; + count +------- + 39 +(1 row) + +-- 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 +------------------------------------------------------------------------------------------------------------------ + 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)) +(4 rows) + +select array_agg(c1 order by c3) from ft1 where c2 = 6 and c1 < 100; + array_agg +-------------------------------- + {6,16,26,36,46,56,66,76,86,96} +(1 row) + +select array_agg("C 1" order by c3) from "S 1"."T 1" where c2 = 6 and "C 1" < 100; + array_agg +-------------------------------- + {6,16,26,36,46,56,66,76,86,96} +(1 row) + +explain (verbose, costs off) +select array_agg(c1 order by c1) from ft2 where c1 < 100 group by c2 order by 1; + 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 +(7 rows) + +select array_agg(c1 order by c1) from ft2 where c1 < 100 group by c2 order by 1; + array_agg +-------------------------------- + {1} + {3,13,23,33,43,53,63,73,83,93} + {4,14,24,34,44,54,64,74,84,94} + {6,16,26,36,46,56,66,76,86,96} + {7,17,27,37,47,57,67,77,87,97} + {8,18,28,38,48,58,68,78,88,98} + {9,19,29,39,49,59,69,79,89,99} + {10,20,30,40,50,60,70,80,90} + {11,21,31,41,51,61,71,81,91} +(9 rows) + +select array_agg("C 1" order by "C 1") from "S 1"."T 1" where "C 1" < 100 group by c2 order by 1; + array_agg +-------------------------------- + {1} + {3,13,23,33,43,53,63,73,83,93} + {4,14,24,34,44,54,64,74,84,94} + {6,16,26,36,46,56,66,76,86,96} + {7,17,27,37,47,57,67,77,87,97} + {8,18,28,38,48,58,68,78,88,98} + {9,19,29,39,49,59,69,79,89,99} + {10,20,30,40,50,60,70,80,90} + {11,21,31,41,51,61,71,81,91} +(9 rows) + +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 +------------------------------------------------------------------------------------------------------------------------------------------------ + 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 +(4 rows) + +select array_agg(c1 order by c1 desc) from ft1 where c2 = 6 and c1 < 100 group by c2; + array_agg +-------------------------------- + {96,86,76,66,56,46,36,26,16,6} +(1 row) + +select array_agg("C 1" order by "C 1" desc) from "S 1"."T 1" where c2 = 6 and "C 1" < 100 group by c2; + array_agg +-------------------------------- + {96,86,76,66,56,46,36,26,16,6} +(1 row) + +explain (verbose, costs off) +select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; + 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)) +(4 rows) + +select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; + array_agg +------------------------------------------------------------------------------------------------------------------------------------------ + {"Mon Feb 16 00:00:00 1970","Fri Feb 06 00:00:00 1970","Tue Jan 27 00:00:00 1970","Sat Jan 17 00:00:00 1970","Wed Jan 07 00:00:00 1970"} +(1 row) + +select array_agg(c5 order by "C 1" desc) from "S 1"."T 1" where c2 = 6 and "C 1" < 50; + array_agg +------------------------------------------------------------------------------------------------------------------------------------------ + {"Mon Feb 16 00:00:00 1970","Fri Feb 06 00:00:00 1970","Tue Jan 27 00:00:00 1970","Sat Jan 17 00:00:00 1970","Wed Jan 07 00:00:00 1970"} +(1 row) + +explain (verbose, costs off) +select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Sort + Output: (array_agg(DISTINCT (t1.c1 % 5))), ((t2.c1 % 3)) + Sort Key: (array_agg(DISTINCT (t1.c1 % 5))) + -> Foreign Scan + Output: (array_agg(DISTINCT (t1.c1 % 5))), ((t2.c1 % 3)) + Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) + Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5)), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY ((r2.c1 % 3)) +(7 rows) + +select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + array_agg +-------------- + {0,1,2,3,4} + {1,2,3,NULL} +(2 rows) + +select array_agg(distinct (t1.c1)%5) from "S 1"."T 3" t1 full join "S 1"."T 4" t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + array_agg +-------------- + {0,1,2,3,4} + {1,2,3,NULL} +(2 rows) + +explain (verbose, costs off) +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Sort + Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5))), ((t2.c1 % 3)) + Sort Key: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5))) + -> Foreign Scan + Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5))), ((t2.c1 % 3)) + Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) + Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5))), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY ((r2.c1 % 3)) +(7 rows) + +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + array_agg +-------------- + {0,1,2,3,4} + {1,2,3,NULL} +(2 rows) + +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from "S 1"."T 3" t1 full join "S 1"."T 4" t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + array_agg +-------------- + {0,1,2,3,4} + {1,2,3,NULL} +(2 rows) + +explain (verbose, costs off) +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Sort + Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC)), ((t2.c1 % 3)) + Sort Key: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC)) + -> Foreign Scan + Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC)), ((t2.c1 % 3)) + Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) + Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) DESC), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY ((r2.c1 % 3)) +(7 rows) + +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + array_agg +-------------- + {4,3,2,1,0} + {NULL,3,2,1} +(2 rows) + +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc) from "S 1"."T 3" t1 full join "S 1"."T 4" t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + array_agg +-------------- + {4,3,2,1,0} + {NULL,3,2,1} +(2 rows) + +explain (verbose, costs off) +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Sort + Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC NULLS LAST)), ((t2.c1 % 3)) + Sort Key: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC NULLS LAST)) + -> Foreign Scan + Output: (array_agg(DISTINCT (t1.c1 % 5) ORDER BY (t1.c1 % 5) DESC NULLS LAST)), ((t2.c1 % 3)) + Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) + Remote SQL: SELECT array_agg(DISTINCT (r1.c1 % 5) ORDER BY ((r1.c1 % 5)) DESC NULLS LAST), (r2.c1 % 3) FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) WHERE (((r1.c1 < 20) OR ((r1.c1 IS NULL) AND (r2.c1 < 5)))) GROUP BY ((r2.c1 % 3)) +(7 rows) + +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + array_agg +-------------- + {3,2,1,NULL} + {4,3,2,1,0} +(2 rows) + +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from "S 1"."T 3" t1 full join "S 1"."T 4" t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + array_agg +-------------- + {3,2,1,NULL} + {4,3,2,1,0} +(2 rows) + +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 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 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 +(4 rows) + +select sum(c1%3), sum(distinct c1%3) filter (where c1%3 < 2), c2 from ft1 where c2 = 6 group by c2; + sum | sum | c2 +-----+-----+---- + 99 | 1 | 6 +(1 row) + +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; + sum | sum | c2 +-----+-----+---- + 99 | 1 | 6 +(1 row) + +-- 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 +----------------------------------------------------------------------------------------------------------- + Unique + Output: (sum(c1)), c2 + -> Sort + Output: (sum(c1)), c2 + Sort Key: ft2.c2, (sum(ft2.c1)) + -> 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 +(9 rows) + +select distinct sum(c1), c2 from ft2 where c2 < 6 group by c2 order by c2; + sum | c2 +-------+---- + 49599 | 1 + 1112 | 2 +(2 rows) + +select distinct sum("C 1"), c2 from "S 1"."T 1" where c2 < 6 group by c2 order by c2; + sum | c2 +-------+---- + 49599 | 1 + 1112 | 2 +(2 rows) + +explain (verbose, costs off) +select sum(c1) filter (where c1 < 100) from ft1 group by c2 order by 1 nulls last; + 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 +(7 rows) + +select sum(c1) filter (where c1 < 100) from ft1 group by c2 order by 1 nulls last; + sum +----- + 1 + 450 + 459 + 480 + 490 + 510 + 520 + 530 + 540 + + + + + + + + + + + + +(21 rows) + +select sum("C 1") filter (where "C 1" < 100) from "S 1"."T 1" group by c2 order by 1 nulls last; + sum +----- + 1 + 450 + 459 + 480 + 490 + 510 + 520 + 530 + 540 + + + + + + + + + + + + +(21 rows) + +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 +-------------------------------------------------------------------------------------------------------------------------------------- + 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 +(7 rows) + +select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; + sum +----- + 450 + 480 + 490 + 510 + 520 + 530 + 540 + + + + + + + + + + + + + + +(21 rows) + +select sum("C 1") filter (where "C 1" < 100 and c2 > 5) from "S 1"."T 1" group by c2 order by 1 nulls last; + sum +----- + 450 + 480 + 490 + 510 + 520 + 530 + 540 + + + + + + + + + + + + + + +(21 rows) + +-- 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 +---------------------------------------------------------------------------------------------------------------- + Unique + Output: ((SubPlan 1)) + -> Sort + Output: ((SubPlan 1)) + Sort Key: ((SubPlan 1)) + -> 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 + SubPlan 1 + -> Foreign Scan on public.ft1 t1 + Output: (count(*) FILTER (WHERE ((t2.c2 = 6) AND (t2.c1 < 10)))) + Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE (("C 1" = 6)) +(13 rows) + +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; + count +------- + 1 +(1 row) + +select distinct (select count(*) filter (where t2.c2 = 6 and t2."C 1" < 10) from "S 1"."T 1" t1 where t1."C 1" = 6) from "S 1"."T 1" t2 order by 1; + count +------- + 1 +(1 row) + +-- 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 +------------------------------------------------------------------------------------------------------------------------------------------------------------ + Unique + Output: ((SubPlan 1)) + -> Sort + Output: ((SubPlan 1)) + Sort Key: ((SubPlan 1)) + -> Foreign Scan on public.ft1 t2 + Output: (SubPlan 1) + Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" + SubPlan 1 + -> 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)) +(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; + count +------- + 0 + 1 +(2 rows) + +select distinct (select count(t1."C 1") filter (where t2.c2 = 6 and t2."C 1" < 10) from "S 1"."T 1" t1 where t1."C 1" = 6) from "S 1"."T 1" t2 order by 1; + count +------- + 0 + 1 +(2 rows) + +-- Aggregate not pushed down +explain (verbose, costs off) +select sum(c1) filter (where c1 * random() < 100) from ft1 group by c2; + QUERY PLAN +------------------------------------------------------------------------------------------------------ + HashAggregate + Output: sum(c1) FILTER (WHERE (((c1)::double precision * random()) < '100'::double precision)), c2 + Group Key: ft1.c2 + -> Foreign Scan on public.ft1 + Output: c2, c1 + Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" +(6 rows) + +explain (verbose, costs off) +select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1; + QUERY PLAN +------------------------------------------------------------------- + Aggregate + Output: sum(ft1.c2) FILTER (WHERE (hashed SubPlan 1)) + -> Foreign Scan on public.ft1 + Output: ft1.c2 + Remote SQL: SELECT c2 FROM "S 1"."T 1" + SubPlan 1 + -> Foreign Scan on public.ft1 ft1_1 + Output: ft1_1.c2 + Remote SQL: SELECT c2 FROM "S 1"."T 1" WHERE ((c2 < 5)) +(9 rows) + +explain (verbose, costs off) +select percentile_disc(0.25) within group (order by c2) from ft1; + 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 +(4 rows) + +select percentile_disc(0.25) within group (order by c2) from ft1; + percentile_disc +----------------- + 42 +(1 row) + +select percentile_disc(0.25) within group (order by c2) from "S 1"."T 1"; + percentile_disc +----------------- + 42 +(1 row) + +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 +------------------------------------------------------------------------------------------------------------------------------------------------------------------ + 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 +(7 rows) + +select c2, percentile_cont(0.5) within group (order by c1) from ft1 where c2 < 10 group by c2 order by 1; + c2 | percentile_cont +----+----------------- + 1 | 501 + 2 | 556 + 6 | 501 +(3 rows) + +select c2, percentile_cont(0.5) within group (order by "C 1") from "S 1"."T 1" where c2 < 10 group by c2 order by 1; + c2 | percentile_cont +----+----------------- + 1 | 501 + 2 | 556 + 6 | 501 +(3 rows) + +-- 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 +-------------------------------------------------------------------------------------------------------------------------------- + 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 +(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 +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 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 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)) +(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; + c2 | percentile_cont +----+----------------- + 1 | 109 + 2 | 223 +(2 rows) + +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 order by c2; + c2 | percentile_cont +----+----------------- + 1 | 109 + 2 | 223 +(2 rows) + +explain (verbose, costs off) +select rank('10'::varchar) within group (order by c6) from ft1; + 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 +(4 rows) + +select rank('10'::varchar) within group (order by c6) from ft1; + rank +------ + 202 +(1 row) + +select rank('10'::varchar) within group (order by c6) from "S 1"."T 1"; + rank +------ + 202 +(1 row) + +-- User defined function for user defined aggregate, VARIADIC +create function least_accum(anyelement, variadic anyarray) +returns anyelement language sql as + 'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)'; +create aggregate least_agg(variadic items anyarray) ( + stype = anyelement, sfunc = least_accum +); +-- Not pushed down due to user defined aggregate +explain (verbose, costs off) +select c2, least_agg(c1) from ft1 group by c2 order by c2; + QUERY PLAN +------------------------------------------------------------- + Sort + Output: c2, (least_agg(VARIADIC ARRAY[c1])) + Sort Key: ft1.c2 + -> HashAggregate + Output: c2, least_agg(VARIADIC ARRAY[c1]) + Group Key: ft1.c2 + -> Foreign Scan on public.ft1 + Output: c2, c1 + Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" +(9 rows) + +-- Add into extension +ALTER EXTENSION postgres_fdw ADD FUNCTION least_accum(anyelement, variadic anyarray); +ALTER EXTENSION postgres_fdw ADD AGGREGATE least_agg(variadic items anyarray); +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 +------------------------------------------------------------------------------------------------------------------------------------ + 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 +(7 rows) + +select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; + c2 | least_agg +----+----------- + 1 | 11 + 2 | 1 + 6 | 6 + 42 | 10 + 44 | 4 +(5 rows) + +select c2, least_agg("C 1") from "S 1"."T 1" where c2 < 100 group by c2 order by c2; + c2 | least_agg +----+----------- + 1 | 11 + 2 | 1 + 6 | 6 + 42 | 10 + 44 | 4 +(5 rows) + +-- E. Mix aggregate tests +-- 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 +-------------------------------------------------------------------------------------------------------------------------------------------- + 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)) +(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; + count +------- + 21 +(1 row) + +select count(*) from (select c2 * random(), count("C 1") from "S 1"."T 1" group by c2 having sum("C 1") * random() < 100000 and avg("C 1") < 50000) x; + count +------- + 21 +(1 row) + +explain (verbose, costs off) +select c2, sum(c2) from ft1 group by c2 having avg(c2) + c2 < 10 order by c2; + 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)) +(7 rows) + +select c2, sum(c2) from ft1 group by c2 having avg(c2) + c2 < 10 order by c2; + c2 | sum +----+----- + 1 | 99 + 2 | 4 +(2 rows) + +select c2, sum(c2) from "S 1"."T 1" group by c2 having avg(c2) + c2 < 10 order by c2; + c2 | sum +----+----- + 1 | 99 + 2 | 4 +(2 rows) + +-- Input rel to aggregate push down hook is not safe to pushdown and thus the aggregate cannot be pushed down to remote +explain (verbose, costs off) +select count(t1.c3) from ft1 t1, ft1 t2 where t1.c1 = postgres_fdw_abs(t1.c2); + QUERY PLAN +---------------------------------------------------------------------------------------------------------- + Aggregate + Output: count(t1.c3) + -> Nested Loop + Output: t1.c3 + -> Foreign Scan on public.ft1 t2 + Remote SQL: SELECT NULL FROM "S 1"."T 1" + -> Materialize + Output: t1.c3 + -> Foreign Scan on public.ft1 t1 + Output: t1.c3 + Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" = public.postgres_fdw_abs(c2))) +(11 rows) + +-- 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 +------------------------------------------------------------------------------------------------------------ + Sort + Output: (count(*)), x.b + Sort Key: (count(*)), x.b + -> HashAggregate + Output: count(*), x.b + Group Key: x.b + -> Hash Join + Output: x.b + Hash Cond: (ft1.c2 = x.a) + -> Foreign Scan on public.ft1 + Output: ft1.c2 + Remote SQL: SELECT c2 FROM "S 1"."T 1" + -> Hash + Output: x.b, x.a + -> Subquery Scan on x + Output: x.b, x.a + -> 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 +(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; + count | b +-------+------- + 1 | 1101 + 1 | 1103 + 1 | 1104 + 2 | 1112 + 2 | 2012 + 2 | 2016 + 2 | 2018 + 2 | 2022 + 2 | 2024 + 2 | 2026 + 2 | 2028 + 2 | 2030 + 2 | 2426 + 99 | 49599 + 100 | 49800 + 100 | 49900 + 100 | 50100 + 100 | 50200 + 100 | 50300 + 100 | 50400 + 100 | 50500 +(21 rows) + +select count(*), x.b from "S 1"."T 1", (select c2 a, sum("C 1") b from "S 1"."T 1" group by c2) x where "S 1"."T 1".c2 = x.a group by x.b order by 1, 2; + count | b +-------+------- + 1 | 1101 + 1 | 1103 + 1 | 1104 + 2 | 1112 + 2 | 2012 + 2 | 2016 + 2 | 2018 + 2 | 2022 + 2 | 2024 + 2 | 2026 + 2 | 2028 + 2 | 2030 + 2 | 2426 + 99 | 49599 + 100 | 49800 + 100 | 49900 + 100 | 50100 + 100 | 50200 + 100 | 50300 + 100 | 50400 + 100 | 50500 +(21 rows) + +-- FULL join with IS NULL check in HAVING +explain (verbose, costs off) +select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Foreign Scan + Output: (avg(t1.c1)), (sum(t2.c1)), t2.c1 + Relations: Aggregate on ((public.ft4 t1) FULL JOIN (public.ft5 t2)) + Remote SQL: SELECT avg(r1.c1), sum(r2.c1), r2.c1 FROM ("S 1"."T 3" r1 FULL JOIN "S 1"."T 4" r2 ON (((r1.c1 = r2.c1)))) GROUP BY r2.c1 HAVING ((((avg(r1.c1) IS NULL) AND (sum(r2.c1) < 10)) OR (sum(r2.c1) IS NULL))) +(4 rows) + +select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null; + avg | sum +---------------------+----- + 51.0000000000000000 | + | 3 + | 9 +(3 rows) + +select avg(t1.c1), sum(t2.c1) from "S 1"."T 3" t1 full join "S 1"."T 4" t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null; + avg | sum +---------------------+----- + 51.0000000000000000 | + | 3 + | 9 +(3 rows) + +-- F. Not supported cases +-- Grouping sets +explain (verbose, costs off) +select sum(c1) from ft1 group by rollup(c2); + QUERY PLAN +---------------------------------------------------------------------------------- + GroupAggregate + Output: sum(c1), c2 + Group Key: ft1.c2 + Group Key: () + -> Foreign Scan on public.ft1 + Output: c2, c1 + Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" ORDER BY c2 ASC NULLS LAST +(7 rows) + +explain (verbose, costs off) +select sum(c1) from ft1 group by cube(c2); + QUERY PLAN +---------------------------------------------------------------------------------- + GroupAggregate + Output: sum(c1), c2 + Group Key: ft1.c2 + Group Key: () + -> Foreign Scan on public.ft1 + Output: c2, c1 + Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" ORDER BY c2 ASC NULLS LAST +(7 rows) + +explain (verbose, costs off) +select sum(c1) from ft1 group by grouping sets(c2, c3); + QUERY PLAN +-------------------------------------------------------------------------------------- + GroupAggregate + Output: sum(c1), c2, c3 + Group Key: ft1.c2 + Sort Key: ft1.c3 + Group Key: ft1.c3 + -> Foreign Scan on public.ft1 + Output: c2, c3, c1 + Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1" ORDER BY c2 ASC NULLS LAST +(8 rows) + +explain (verbose, costs off) +select sum(c1), grouping(c2) from ft1 group by c2; + QUERY PLAN +------------------------------------------------------- + HashAggregate + Output: sum(c1), GROUPING(c2), c2 + Group Key: ft1.c2 + -> Foreign Scan on public.ft1 + Output: c2, c1 + Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" +(6 rows) + diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index daf0438..c33916f 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -24,7 +24,9 @@ #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" +#include "nodes/print.h" #include "optimizer/cost.h" +#include "optimizer/clauses.h" #include "optimizer/pathnode.h" #include "optimizer/paths.h" #include "optimizer/planmain.h" @@ -38,6 +40,7 @@ #include "utils/memutils.h" #include "utils/rel.h" #include "utils/sampling.h" +#include "utils/selfuncs.h" PG_MODULE_MAGIC; @@ -343,6 +346,10 @@ static void postgresGetForeignJoinPaths(PlannerInfo *root, JoinPathExtraData *extra); static bool postgresRecheckForeignScan(ForeignScanState *node, TupleTableSlot *slot); +static void postgresGetForeignUpperPaths(PlannerInfo *root, + UpperRelationKind stage, + RelOptInfo *input_rel, + RelOptInfo *output_rel); /* * Helper functions @@ -400,12 +407,15 @@ static void conversion_error_callback(void *arg); static bool foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype, RelOptInfo *outerrel, RelOptInfo *innerrel, JoinPathExtraData *extra); +static bool foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel); static List *get_useful_pathkeys_for_relation(PlannerInfo *root, RelOptInfo *rel); static List *get_useful_ecs_for_relation(PlannerInfo *root, RelOptInfo *rel); static void add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel, Path *epq_path); - +static void create_foreign_grouping_paths(PlannerInfo *root, + RelOptInfo *input_rel, + RelOptInfo *grouped_rel); /* * Foreign-data wrapper handler function: return a struct with pointers @@ -455,6 +465,9 @@ postgres_fdw_handler(PG_FUNCTION_ARGS) /* Support functions for join push-down */ routine->GetForeignJoinPaths = postgresGetForeignJoinPaths; + /* Support functions for upper relation push-down */ + routine->GetForeignUpperPaths = postgresGetForeignUpperPaths; + PG_RETURN_POINTER(routine); } @@ -805,7 +818,7 @@ get_useful_pathkeys_for_relation(PlannerInfo *root, RelOptInfo *rel) */ if (pathkey_ec->ec_has_volatile || !(em_expr = find_em_expr_for_rel(pathkey_ec, rel)) || - !is_foreign_expr(root, rel, em_expr)) + !is_foreign_expr(root, rel, em_expr, false)) { query_pathkeys_ok = false; break; @@ -858,7 +871,7 @@ get_useful_pathkeys_for_relation(PlannerInfo *root, RelOptInfo *rel) /* If no pushable expression for this rel, skip it. */ em_expr = find_em_expr_for_rel(cur_ec, rel); - if (em_expr == NULL || !is_foreign_expr(root, rel, em_expr)) + if (em_expr == NULL || !is_foreign_expr(root, rel, em_expr, false)) continue; /* Looks like we can generate a pathkey, so let's do it. */ @@ -942,7 +955,7 @@ postgresGetForeignPaths(PlannerInfo *root, continue; /* See if it is safe to send to remote */ - if (!is_foreign_expr(root, baserel, rinfo->clause)) + if (!is_foreign_expr(root, baserel, rinfo->clause, false)) continue; /* Calculate required outer rels for the resulting path */ @@ -1018,7 +1031,7 @@ postgresGetForeignPaths(PlannerInfo *root, continue; /* See if it is safe to send to remote */ - if (!is_foreign_expr(root, baserel, rinfo->clause)) + if (!is_foreign_expr(root, baserel, rinfo->clause, false)) continue; /* Calculate required outer rels for the resulting path */ @@ -1120,7 +1133,7 @@ postgresGetForeignPlan(PlannerInfo *root, * rel->baserestrictinfo + parameterization clauses through * scan_clauses. For a join rel->baserestrictinfo is NIL and we are * not considering parameterization right now, so there should be no - * scan_clauses for a joinrel. + * scan_clauses for a joinrel and upper rel either. */ Assert(!scan_clauses); } @@ -1161,7 +1174,7 @@ postgresGetForeignPlan(PlannerInfo *root, } else if (list_member_ptr(fpinfo->local_conds, rinfo)) local_exprs = lappend(local_exprs, rinfo->clause); - else if (is_foreign_expr(root, foreignrel, rinfo->clause)) + else if (is_foreign_expr(root, foreignrel, rinfo->clause, false)) { remote_conds = lappend(remote_conds, rinfo); remote_exprs = lappend(remote_exprs, rinfo->clause); @@ -1177,7 +1190,7 @@ postgresGetForeignPlan(PlannerInfo *root, local_exprs = fpinfo->local_conds; /* Build the list of columns to be fetched from the foreign server. */ - fdw_scan_tlist = build_tlist_to_deparse(foreignrel); + fdw_scan_tlist = build_tlist_to_deparse(root, foreignrel); /* * Ensure that the outer plan produces a tuple whose descriptor @@ -1210,6 +1223,17 @@ 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 @@ -1228,7 +1252,8 @@ postgresGetForeignPlan(PlannerInfo *root, remote_conds, retrieved_attrs, makeInteger(fpinfo->fetch_size)); - if (foreignrel->reloptkind == RELOPT_JOINREL) + if (foreignrel->reloptkind == RELOPT_JOINREL || + foreignrel->reloptkind == RELOPT_UPPER_REL) fdw_private = lappend(fdw_private, makeString(fpinfo->relation_name->data)); @@ -2151,7 +2176,7 @@ postgresPlanDirectModify(PlannerInfo *root, elog(ERROR, "attribute number %d not found in subplan targetlist", attno); - if (!is_foreign_expr(root, baserel, (Expr *) tle->expr)) + if (!is_foreign_expr(root, baserel, (Expr *) tle->expr, false)) return false; targetAttrs = lappend_int(targetAttrs, attno); @@ -2452,7 +2477,8 @@ 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. + * either a base relation or a join between foreign relations or an upper + * relation. * * param_join_conds are the parameterization clauses with outer relations. * pathkeys specify the expected sort order if any for given path being costed. @@ -2505,8 +2531,9 @@ estimate_path_cost_size(PlannerInfo *root, &remote_param_join_conds, &local_param_join_conds); /* Build the list of columns to be fetched from the foreign server. */ - if (foreignrel->reloptkind == RELOPT_JOINREL) - fdw_scan_tlist = build_tlist_to_deparse(foreignrel); + if (foreignrel->reloptkind == RELOPT_JOINREL || + foreignrel->reloptkind == RELOPT_UPPER_REL) + fdw_scan_tlist = build_tlist_to_deparse(root, foreignrel); else fdw_scan_tlist = NIL; @@ -2586,25 +2613,7 @@ estimate_path_cost_size(PlannerInfo *root, startup_cost = fpinfo->rel_startup_cost; run_cost = fpinfo->rel_total_cost - fpinfo->rel_startup_cost; } - else if (foreignrel->reloptkind != RELOPT_JOINREL) - { - /* Clamp retrieved rows estimates to at most foreignrel->tuples. */ - retrieved_rows = Min(retrieved_rows, foreignrel->tuples); - - /* - * Cost as though this were a seqscan, which is pessimistic. We - * effectively imagine the local_conds are being evaluated - * remotely, too. - */ - startup_cost = 0; - run_cost = 0; - run_cost += seq_page_cost * foreignrel->pages; - - startup_cost += foreignrel->baserestrictcost.startup; - cpu_per_tuple = cpu_tuple_cost + foreignrel->baserestrictcost.per_tuple; - run_cost += cpu_per_tuple * foreignrel->tuples; - } - else + else if (foreignrel->reloptkind == RELOPT_JOINREL) { PgFdwRelationInfo *fpinfo_i; PgFdwRelationInfo *fpinfo_o; @@ -2670,6 +2679,95 @@ estimate_path_cost_size(PlannerInfo *root, run_cost += nrows * remote_conds_cost.per_tuple; run_cost += fpinfo->local_conds_cost.per_tuple * retrieved_rows; } + else if (foreignrel->reloptkind == RELOPT_UPPER_REL) + { + PgFdwRelationInfo *ofpinfo; + PathTarget *ptarget = root->upper_targets[UPPERREL_GROUP_AGG]; + AggClauseCosts aggcosts; + double input_rows; + int numGroupCols; + double numGroups = 1; + + /* + * This cost model is mixture of costing done for sorted and hashed + * aggregates in cost_agg(). We are not sure which strategy will + * be considered at remote side, thus for simplicity, we put all + * startup related costs in startup_cost and all finalization and + * run cost are added in total_cost. + * + * Also, core does not care about costing HAVING expressions and + * adding that to the costs. So similarly, here too we are not + * considering remote and local conditions for costing. + */ + + ofpinfo = (PgFdwRelationInfo *) fpinfo->outerrel->fdw_private; + + /* Get rows and width from input rel */ + input_rows = ofpinfo->rows; + width = ofpinfo->width; + + /* Collect statistics about aggregates for estimating costs. */ + MemSet(&aggcosts, 0, sizeof(AggClauseCosts)); + if (root->parse->hasAggs) + { + get_agg_clause_costs(root, (Node *) fpinfo->grouped_tlist, + AGGSPLIT_SIMPLE, &aggcosts); + get_agg_clause_costs(root, (Node *) root->parse->havingQual, + AGGSPLIT_SIMPLE, &aggcosts); + } + + /* Get number of grouping columns and possible number of groups */ + numGroupCols = list_length(root->parse->groupClause); + numGroups = estimate_num_groups(root, fpinfo->grouped_exprs, + input_rows, NULL); + + /* + * Number of rows expected from remote server will be same as that + * of number of groups. + */ + rows = retrieved_rows = numGroups; + + /* + * Startup cost includes: + * 1. Startup cost for underneath input relation + * 2. Cost of performing aggregation, per cost_agg() + * 3. Startup cost for PathTarget eval + */ + startup_cost = ofpinfo->rel_startup_cost; + startup_cost += aggcosts.transCost.startup; + startup_cost += aggcosts.transCost.per_tuple * input_rows; + startup_cost += (cpu_operator_cost * numGroupCols) * input_rows; + startup_cost += ptarget->cost.startup; + + /* + * Run time cost includes: + * 1. Run time cost of underneath input relation + * 2. Run time cost of performing aggregation, per cost_agg() + * 4. PathTarget eval cost for each output row + */ + run_cost = ofpinfo->rel_total_cost - ofpinfo->rel_startup_cost; + run_cost += aggcosts.finalCost * numGroups; + run_cost += cpu_tuple_cost * numGroups; + run_cost += ptarget->cost.per_tuple * numGroups; + } + else + { + /* Clamp retrieved rows estimates to at most foreignrel->tuples. */ + retrieved_rows = Min(retrieved_rows, foreignrel->tuples); + + /* + * Cost as though this were a seqscan, which is pessimistic. We + * effectively imagine the local_conds are being evaluated + * remotely, too. + */ + startup_cost = 0; + run_cost = 0; + run_cost += seq_page_cost * foreignrel->pages; + + startup_cost += foreignrel->baserestrictcost.startup; + cpu_per_tuple = cpu_tuple_cost + foreignrel->baserestrictcost.per_tuple; + run_cost += cpu_per_tuple * foreignrel->tuples; + } /* * Without remote estimates, we have no real way to estimate the cost @@ -3997,7 +4095,7 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype, { Expr *expr = (Expr *) lfirst(lc); - if (!is_foreign_expr(root, joinrel, expr)) + if (!is_foreign_expr(root, joinrel, expr, false)) return false; } @@ -4036,7 +4134,7 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype, { Expr *expr = (Expr *) lfirst(lc); - if (!is_foreign_expr(root, joinrel, expr)) + if (!is_foreign_expr(root, joinrel, expr, false)) fpinfo->local_conds = lappend(fpinfo->local_conds, expr); else fpinfo->remote_conds = lappend(fpinfo->remote_conds, expr); @@ -4343,6 +4441,310 @@ 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. + */ +static bool +foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel) +{ + Query *query = root->parse; + PathTarget *grouping_target = root->upper_targets[UPPERREL_GROUP_AGG]; + PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) grouped_rel->fdw_private; + PgFdwRelationInfo *ofpinfo; + List *aggvars; + ListCell *lc; + int i; + List *tlist = NIL; + + /* Grouping Sets are not pushable */ + if (query->groupingSets) + return false; + + /* Get the fpinfo of the outerrel */ + ofpinfo = (PgFdwRelationInfo *) fpinfo->outerrel->fdw_private; + + /* + * If underneath input relation has any local conditions, those conditions + * are required to be applied before performing aggregation. Hence the + * aggregate cannot be pushed down. + */ + if (ofpinfo->local_conds) + return false; + + /* + * 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. + */ + i = 0; + foreach(lc, grouping_target->exprs) + { + Expr *expr = (Expr *) lfirst(lc); + Index sgref = get_pathtarget_sortgroupref(grouping_target, i); + ListCell *l; + + /* Check whether this expression is part of GROUP BY clause */ + if (sgref && query->groupClause && query->groupingSets == NIL && + get_sortgroupref_clause_noerr(sgref, query->groupClause) != NULL) + { + /* Expression matched with GROUP BY, check as is */ + if (!is_foreign_expr(root, grouped_rel, expr, true)) + return false; + + /* Pushable, add to tlist */ + tlist = add_to_flat_tlist(tlist, list_make1(expr)); + } + else + { + /* If not GROUP BY ref, reset it as we are not pushing those */ + if (sgref) + grouping_target->sortgrouprefs[i] = 0; + + /* Check entire expression whether it is pushable or not */ + if (is_foreign_expr(root, grouped_rel, expr, true)) + { + /* Pushable, add to tlist */ + tlist = add_to_flat_tlist(tlist, list_make1(expr)); + } + else + { + /* Not matched exactly, pull the var with aggregates then */ + aggvars = pull_var_clause((Node *) expr, + PVC_INCLUDE_AGGREGATES); + + if (!is_foreign_expr(root, grouped_rel, (Expr *) aggvars, + true)) + return false; + + /* + * Add aggregates, if any, into tlist. Plain Var nodes pulled + * are already part of GROUP BY and thus no need to add them + * explicitly. + */ + foreach(l, aggvars) + { + Expr *expr = (Expr *) lfirst(l); + + if (IsA(expr, Aggref)) + tlist = add_to_flat_tlist(tlist, list_make1(expr)); + } + } + } + + i++; + } + + /* Transfer any sortgroupref data to the replacement tlist */ + apply_pathtarget_labeling_to_tlist(tlist, grouping_target); + /* Store generated targetlist */ + fpinfo->grouped_tlist = tlist; + fpinfo->grouped_exprs = get_sortgrouplist_exprs(query->groupClause, tlist); + + /* + * Classify the pushable and non-pushable having clauses and save them in + * remote_conds and local_conds of grouped_rel::fpinfo. + */ + if (root->hasHavingQual && query->havingQual) + { + ListCell *lc; + + /* Having quals must be safe to push down. */ + foreach(lc, (List *) query->havingQual) + { + Expr *expr = (Expr *) lfirst(lc); + + if (!is_foreign_expr(root, grouped_rel, expr, true)) + fpinfo->local_conds = lappend(fpinfo->local_conds, expr); + else + fpinfo->remote_conds = lappend(fpinfo->remote_conds, expr); + } + } + + /* Safe to pushdown */ + fpinfo->pushdown_safe = true; + + /* + * If user is willing to estimate cost for a scan using EXPLAIN, he intends + * to estimate scans on that relation more accurately. Then, it makes sense + * to estimate the cost of the grouping on that relation more accurately + * using EXPLAIN. + */ + fpinfo->use_remote_estimate = ofpinfo->use_remote_estimate; + + /* Get user mapping */ + if (fpinfo->use_remote_estimate) + fpinfo->user = ofpinfo->user; + else + fpinfo->user = NULL; + + /* Get foreign server */ + fpinfo->server = ofpinfo->server; + + /* Copy startup and tuple cost as is from underneath input rel's fpinfo */ + fpinfo->fdw_startup_cost = ofpinfo->fdw_startup_cost; + fpinfo->fdw_tuple_cost = ofpinfo->fdw_tuple_cost; + + /* + * Set cached relation costs to some negative value, so that we can detect + * when they are set to some sensible costs, during one (usually the + * first) of the calls to estimate_path_cost_size(). + */ + fpinfo->rel_startup_cost = -1; + fpinfo->rel_total_cost = -1; + + /* Set fetch size same as that of underneath input rel's fpinfo */ + fpinfo->fetch_size = ofpinfo->fetch_size; + + /* + * Set the string describing this grouped relation to be used in EXPLAIN + * output of corresponding ForeignScan. + */ + fpinfo->relation_name = makeStringInfo(); + appendStringInfo(fpinfo->relation_name, "Aggregate on (%s)", + ofpinfo->relation_name->data); + + return true; +} + +/* + * postgresGetForeignUpperPaths + * Add paths for post-join operations like aggregation, grouping etc. if + * corresponding operations are safe to push down. + * + * Right now, we only support aggregate, grouping and having clause pushdown. + */ +static void +postgresGetForeignUpperPaths(PlannerInfo *root, UpperRelationKind stage, + RelOptInfo *input_rel, RelOptInfo *output_rel) +{ + PgFdwRelationInfo *fpinfo; + + /* If input rel is not aware of fdw, simply return */ + if (!input_rel->fdw_private) + return; + + /* + * This should not happen normally. In case we have already added paths for + * this output rel, nothing to be done again. + */ + if (output_rel->fdw_private) + return; + + fpinfo = (PgFdwRelationInfo *) palloc0(sizeof(PgFdwRelationInfo)); + fpinfo->pushdown_safe = false; + output_rel->fdw_private = fpinfo; + + switch (stage) + { + case UPPERREL_GROUP_AGG: + return create_foreign_grouping_paths(root, input_rel, output_rel); + break; + + default: + /* If it's anything else, we are not adding any foreign path. */ + return; + } +} + +/* + * create_foreign_grouping_paths + * + * 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 + */ +static void +create_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel, + RelOptInfo *grouped_rel) +{ + Query *parse = root->parse; + PgFdwRelationInfo *ifpinfo = input_rel->fdw_private; + PgFdwRelationInfo *fpinfo = grouped_rel->fdw_private; + ForeignPath *grouppath; + PathTarget *grouping_target; + double rows; + int width; + Cost startup_cost; + Cost total_cost; + + /* + * If input rel is not safe to pushdown, we cannot do grouping and/or + * aggregation on it. + */ + if (!ifpinfo || !ifpinfo->pushdown_safe) + return; + + /* Nothing to be done, if there is no grouping or aggregation required. */ + if (!parse->groupClause && !parse->groupingSets && !parse->hasAggs && + !root->hasHavingQual) + return; + + grouping_target = root->upper_targets[UPPERREL_GROUP_AGG]; + + /* + * We need relids to deparse column references from target list. + * FIXME: Is this a correct place to copy relids from underneath input_rel + * to the grouped_rel for which we are adding a path here? Or this should + * have been already set by core itself? + */ + grouped_rel->relids = input_rel->relids; + + /* save the input_rel as outerrel in fpinfo */ + fpinfo->outerrel = input_rel; + + /* + * Copy foreign table, foreign server, user mapping, shippable extensions + * etc. details from underneath input relation's fpinfo. + */ + fpinfo->table = ifpinfo->table; + fpinfo->server = ifpinfo->server; + fpinfo->user = ifpinfo->user; + fpinfo->shippable_extensions = ifpinfo->shippable_extensions; + + /* Assess if it is safe to push down aggregation and grouping. */ + if (!foreign_grouping_ok(root, grouped_rel)) + return; + + /* Estimate the cost of push down */ + estimate_path_cost_size(root, grouped_rel, NIL, NIL, &rows, + &width, &startup_cost, &total_cost); + + /* Now update this information in the joinrel */ + grouped_rel->rows = rows; + grouped_rel->reltarget->width = width; + fpinfo->rows = rows; + fpinfo->width = width; + fpinfo->startup_cost = startup_cost; + fpinfo->total_cost = total_cost; + + /* Create and add foreign path to the grouping relation. */ + grouppath = create_foreignscan_path(root, + grouped_rel, + grouping_target, + rows, + startup_cost, + total_cost, + NIL, /* no pathkeys */ + NULL, /* no required_outer */ + NULL, + NULL); /* no fdw_private */ + + /* Add generated path into grouped_rel by add_path(). */ + add_path(grouped_rel, (Path *) grouppath); + + /* + * Do we want to create paths with pathkeys corresponding for + * root->query_pathkeys. + */ + + return; +} + +/* * Create a tuple from the specified row of the PGresult. * * rel is the local representation of the foreign table, attinmeta is diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h index 67126bc..1dd4072 100644 --- a/contrib/postgres_fdw/postgres_fdw.h +++ b/contrib/postgres_fdw/postgres_fdw.h @@ -92,6 +92,10 @@ typedef struct PgFdwRelationInfo RelOptInfo *innerrel; JoinType jointype; List *joinclauses; + + /* Grouping information */ + List *grouped_tlist; + List *grouped_exprs; } PgFdwRelationInfo; /* in postgres_fdw.c */ @@ -123,7 +127,8 @@ extern void classifyConditions(PlannerInfo *root, List **local_conds); extern bool is_foreign_expr(PlannerInfo *root, RelOptInfo *baserel, - Expr *expr); + Expr *expr, + bool foragg); extern void deparseInsertSql(StringInfo buf, PlannerInfo *root, Index rtindex, Relation rel, List *targetAttrs, bool doNothing, List *returningList, @@ -155,7 +160,7 @@ extern void deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs); extern void deparseStringLiteral(StringInfo buf, const char *val); extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel); -extern List *build_tlist_to_deparse(RelOptInfo *foreign_rel); +extern List *build_tlist_to_deparse(PlannerInfo *root, RelOptInfo *foreignrel); extern void deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel, List *tlist, List *remote_conds, List *pathkeys, diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 4f68e89..48aca7c 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1356,3 +1356,276 @@ WHERE ftrelid = 'table30000'::regclass AND ftoptions @> array['fetch_size=60000']; ROLLBACK; + + +-- Test Aggregate Push Down +-- Both ft1 and ft2 are used to exercise cost estimates when +-- use_remote_estimate is false and true respectively. + +-- A. Simple aggregates +explain (verbose, costs off) +select sum(c1), avg(c1), min(c2), max(c1), stddev(c2) from ft1; +select sum(c1), avg(c1), min(c2), max(c1), stddev(c2) from ft1; +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; + +explain (verbose, costs off) +select count(c6) from ft1; +select count(c6) from ft1; +select count(c6) from "S 1"."T 1"; + +-- Aggregate is not pushed down as aggregation contains random() +explain (verbose, costs off) +select sum(c1 * random()), avg(c1) from ft1; + +explain (verbose, costs off) +select sum(c1) from ft2 where c2 < 5; +select sum(c1) from ft2 where c2 < 5; +select sum("C 1") from "S 1"."T 1" where c2 < 5; + +explain (verbose, costs off) +select sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6; +select sum(t1.c1), avg(t2.c1) from ft1 t1 inner join ft1 t2 on (t1.c2 = t2.c2) where t1.c2 = 6; +select sum(t1."C 1"), avg(t1."C 1") from "S 1"."T 1" t1 inner join "S 1"."T 1" t2 on (t1.c2 = t2.c2) where t1.c2 = 6; + +explain (verbose, costs off) +select count(*) from ft1 t1 inner join ft2 t2 on (t1.c2 = t2.c2) where t2.c2 = 6; +select count(*) from ft1 t1 inner join ft2 t2 on (t1.c2 = t2.c2) where t2.c2 = 6; +select count(*) from "S 1"."T 1" t1 inner join "S 1"."T 1" t2 on (t1.c2 = t2.c2) where t2.c2 = 6; + +-- Not pushed down due to local conditions present in underneath input rel +explain (verbose, costs off) +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; +select c2, count(*) from ft1 group by c2 order by 1; +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; +select count(c1), length(c6) from ft2 group by c6 order by 1, 2; +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; +select c2/2, sum(c2) * (c2/2) from ft1 group by c2/2 order by c2/2; +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; +select count(a), sum(a) from (select c2 a, sum(c1) b from ft1 group by c2, sqrt(c1) order by 1, 2) x; +select count(a), sum(a) from (select c2 a, sum("C 1") b from "S 1"."T 1" group by c2, sqrt("C 1") order by 1, 2) x; + +-- Aggregate is still pushed down by taking random() out +explain (verbose, costs off) +select c1 * random(), sum(c1) * c1 from ft1 group by c1; +--select c1 * random(), sum(c1) * c1 from ft1 group by c1; +--select "C 1" * random(), sum("C 1") * "C 1" from "S 1"."T 1" group by "C 1"; + +-- Aggregate is not pushed down as random() is part of group by expression +explain (verbose, costs off) +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; +select c2, sum(c1) from ft2 group by c2 having avg(c1) < 500 order by 1; +select c2, sum("C 1") from "S 1"."T 1" group by c2 having avg("C 1") < 500 order by 1; + +explain (verbose, costs off) +select c2, sum(c1) from ft1 group by c2 having avg(c1) < 500 and sum(c1) < 200000 order by c2; +select c2, sum(c1) from ft1 group by c2 having avg(c1) < 500 and sum(c1) < 200000 order by c2; +select c2, sum("C 1") from "S 1"."T 1" group by c2 having avg("C 1") < 500 and sum("C 1") < 200000 order by c2; + +explain (verbose, costs off) +select c5, count(c2) from ft1 group by c5, sqrt(c2) having sqrt(max(c2)) < 2 order by 1, 2; +select c5, count(c2) from ft1 group by c5, sqrt(c2) having sqrt(max(c2)) < 2 order by 1, 2; +select c5, count(c2) from "S 1"."T 1" group by c5, sqrt(c2) having sqrt(max(c2)) < 2 order by 1, 2; + +-- Having clause with random() will be evaluated locally +explain (verbose, costs off) +select c2, sum(c1) from ft2 group by c2 having sum(c1) * random() < 500000 order by c2; +select c2, sum(c1) from ft2 group by c2 having sum(c1) * random() < 500000 order by c2; +select c2, sum("C 1") from "S 1"."T 1" group by c2 having sum("C 1") * random() < 500000 order by c2; + +-- 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; +select count(*) from (select c5, count(c1) from ft1 group by c5, sqrt(c2) having avg(c1) * random() < 500 and avg(c1) < 500) x; +select count(*) from (select c5, count("C 1") from "S 1"."T 1" group by c5, sqrt(c2) having avg("C 1") * random() < 500 and avg("C 1") < 500) x; + + +-- 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; +select array_agg(c1 order by c3) from ft1 where c2 = 6 and c1 < 100; +select array_agg("C 1" order by c3) from "S 1"."T 1" where c2 = 6 and "C 1" < 100; + +explain (verbose, costs off) +select array_agg(c1 order by c1) from ft2 where c1 < 100 group by c2 order by 1; +select array_agg(c1 order by c1) from ft2 where c1 < 100 group by c2 order by 1; +select array_agg("C 1" order by "C 1") from "S 1"."T 1" where "C 1" < 100 group by c2 order by 1; + +explain (verbose, costs off) +select array_agg(c1 order by c1 desc) from ft1 where c2 = 6 and c1 < 100 group by c2; +select array_agg(c1 order by c1 desc) from ft1 where c2 = 6 and c1 < 100 group by c2; +select array_agg("C 1" order by "C 1" desc) from "S 1"."T 1" where c2 = 6 and "C 1" < 100 group by c2; + +explain (verbose, costs off) +select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; +select array_agg(c5 order by c1 desc) from ft2 where c2 = 6 and c1 < 50; +select array_agg(c5 order by "C 1" desc) from "S 1"."T 1" where c2 = 6 and "C 1" < 50; + +explain (verbose, costs off) +select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; +select array_agg(distinct (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; +select array_agg(distinct (t1.c1)%5) from "S 1"."T 3" t1 full join "S 1"."T 4" t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + +explain (verbose, costs off) +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5) from "S 1"."T 3" t1 full join "S 1"."T 4" t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + +explain (verbose, costs off) +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc) from "S 1"."T 3" t1 full join "S 1"."T 4" t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + +explain (verbose, costs off) +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; +select array_agg(distinct (t1.c1)%5 order by (t1.c1)%5 desc nulls last) from "S 1"."T 3" t1 full join "S 1"."T 4" t2 on (t1.c1 = t2.c1) where t1.c1 < 20 or (t1.c1 is null and t2.c1 < 5) group by (t2.c1)%3 order by 1; + +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; +select sum(c1%3), sum(distinct c1%3) filter (where c1%3 < 2), c2 from ft1 where c2 = 6 group by c2; +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; + +-- 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; +select distinct sum(c1), c2 from ft2 where c2 < 6 group by c2 order by c2; +select distinct sum("C 1"), c2 from "S 1"."T 1" where c2 < 6 group by c2 order by c2; + +explain (verbose, costs off) +select sum(c1) filter (where c1 < 100) from ft1 group by c2 order by 1 nulls last; +select sum(c1) filter (where c1 < 100) from ft1 group by c2 order by 1 nulls last; +select sum("C 1") filter (where "C 1" < 100) from "S 1"."T 1" group by c2 order by 1 nulls last; + +explain (verbose, costs off) +select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; +select sum(c1) filter (where c1 < 100 and c2 > 5) from ft1 group by c2 order by 1 nulls last; +select sum("C 1") filter (where "C 1" < 100 and c2 > 5) from "S 1"."T 1" group by c2 order by 1 nulls last; + +-- 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; +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; +select distinct (select count(*) filter (where t2.c2 = 6 and t2."C 1" < 10) from "S 1"."T 1" t1 where t1."C 1" = 6) from "S 1"."T 1" t2 order by 1; +-- 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; +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; +select distinct (select count(t1."C 1") filter (where t2.c2 = 6 and t2."C 1" < 10) from "S 1"."T 1" t1 where t1."C 1" = 6) from "S 1"."T 1" t2 order by 1; + +-- Aggregate not pushed down +explain (verbose, costs off) +select sum(c1) filter (where c1 * random() < 100) from ft1 group by c2; +explain (verbose, costs off) +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; +select percentile_disc(0.25) within group (order by c2) from ft1; +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; +select c2, percentile_cont(0.5) within group (order by c1) from ft1 where c2 < 10 group by c2 order by 1; +select c2, percentile_cont(0.5) within group (order by "C 1") from "S 1"."T 1" where c2 < 10 group by c2 order by 1; + +-- 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; +select c2, percentile_cont(c2) within group (order by c1) from ft1 group by c2 order by c2; +select c2, percentile_cont(c2) within group (order by "C 1") from "S 1"."T 1" group by c2 order by c2; + +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; +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; +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 order by c2; + +explain (verbose, costs off) +select rank('10'::varchar) within group (order by c6) from ft1; +select rank('10'::varchar) within group (order by c6) from ft1; +select rank('10'::varchar) within group (order by c6) from "S 1"."T 1"; + +-- User defined function for user defined aggregate, VARIADIC +create function least_accum(anyelement, variadic anyarray) +returns anyelement language sql as + 'select least($1, min($2[i])) from generate_subscripts($2,1) g(i)'; +create aggregate least_agg(variadic items anyarray) ( + stype = anyelement, sfunc = least_accum +); + +-- Not pushed down due to user defined aggregate +explain (verbose, costs off) +select c2, least_agg(c1) from ft1 group by c2 order by c2; + +-- Add into extension +ALTER EXTENSION postgres_fdw ADD FUNCTION least_accum(anyelement, variadic anyarray); +ALTER EXTENSION postgres_fdw ADD AGGREGATE least_agg(variadic items anyarray); +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; +select c2, least_agg(c1) from ft1 where c2 < 100 group by c2 order by c2; +select c2, least_agg("C 1") from "S 1"."T 1" where c2 < 100 group by c2 order by c2; + + +-- E. Mix aggregate tests +-- 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; +select count(*) from (select c2 * random(), count(c1) from ft2 group by c2 having sum(c1) * random() < 100000 and avg(c1) < 50000) x; +select count(*) from (select c2 * random(), count("C 1") from "S 1"."T 1" group by c2 having sum("C 1") * random() < 100000 and avg("C 1") < 50000) x; + +explain (verbose, costs off) +select c2, sum(c2) from ft1 group by c2 having avg(c2) + c2 < 10 order by c2; +select c2, sum(c2) from ft1 group by c2 having avg(c2) + c2 < 10 order by c2; +select c2, sum(c2) from "S 1"."T 1" group by c2 having avg(c2) + c2 < 10 order by c2; + +-- Input rel to aggregate push down hook is not safe to pushdown and thus the aggregate cannot be pushed down to remote +explain (verbose, costs off) +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; +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; +select count(*), x.b from "S 1"."T 1", (select c2 a, sum("C 1") b from "S 1"."T 1" group by c2) x where "S 1"."T 1".c2 = x.a group by x.b order by 1, 2; + +-- FULL join with IS NULL check in HAVING +explain (verbose, costs off) +select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null; +select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null; +select avg(t1.c1), sum(t2.c1) from "S 1"."T 3" t1 full join "S 1"."T 4" t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null; + + +-- F. Not supported cases +-- Grouping sets +explain (verbose, costs off) +select sum(c1) from ft1 group by rollup(c2); +explain (verbose, costs off) +select sum(c1) from ft1 group by cube(c2); +explain (verbose, costs off) +select sum(c1) from ft1 group by grouping sets(c2, c3); +explain (verbose, costs off) +select sum(c1), grouping(c2) from ft1 group by c2; diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 47158f6..268518e 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -72,7 +72,6 @@ static Plan *create_plan_recurse(PlannerInfo *root, Path *best_path, int flags); static Plan *create_scan_plan(PlannerInfo *root, Path *best_path, int flags); -static List *build_path_tlist(PlannerInfo *root, Path *path); static bool use_physical_tlist(PlannerInfo *root, Path *path, int flags); static List *get_gating_quals(PlannerInfo *root, List *quals); static Plan *create_gating_plan(PlannerInfo *root, Path *path, Plan *plan, @@ -689,7 +688,7 @@ create_scan_plan(PlannerInfo *root, Path *best_path, int flags) * This is almost just make_tlist_from_pathtarget(), but we also have to * deal with replacing nestloop params. */ -static List * +List * build_path_tlist(PlannerInfo *root, Path *path) { List *tlist = NIL; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 8a81d7a..e8a3956 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -447,7 +447,6 @@ static char *generate_function_name(Oid funcid, int nargs, List *argnames, Oid *argtypes, bool has_variadic, bool *use_variadic_p, ParseExprKind special_exprkind); -static char *generate_operator_name(Oid operid, Oid arg1, Oid arg2); static text *string_to_text(char *str); static char *flatten_reloptions(Oid relid); @@ -10041,7 +10040,7 @@ generate_function_name(Oid funcid, int nargs, List *argnames, Oid *argtypes, * plus the OPERATOR() decoration needed to use a qualified operator name * in an expression. */ -static char * +char * generate_operator_name(Oid operid, Oid arg1, Oid arg2) { StringInfoData buf; diff --git a/src/include/optimizer/tlist.h b/src/include/optimizer/tlist.h index 0d745a0..91e7d32 100644 --- a/src/include/optimizer/tlist.h +++ b/src/include/optimizer/tlist.h @@ -61,6 +61,7 @@ extern void add_column_to_pathtarget(PathTarget *target, extern void add_new_column_to_pathtarget(PathTarget *target, Expr *expr); extern void add_new_columns_to_pathtarget(PathTarget *target, List *exprs); extern void apply_pathtarget_labeling_to_tlist(List *tlist, PathTarget *target); +extern List *build_path_tlist(PlannerInfo *root, Path *path); /* Convenience macro to get a PathTarget with valid cost/width fields */ #define create_pathtarget(root, tlist) \ diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h index cec2418..d6c6b8e 100644 --- a/src/include/utils/ruleutils.h +++ b/src/include/utils/ruleutils.h @@ -31,5 +31,6 @@ extern List *set_deparse_context_planstate(List *dpcontext, extern List *select_rtable_names_for_explain(List *rtable, Bitmapset *rels_used); extern char *generate_collation_name(Oid collid); +extern char *generate_operator_name(Oid operid, Oid arg1, Oid arg2); #endif /* RULEUTILS_H */