diff options
| -rw-r--r-- | src/backend/optimizer/plan/createplan.c | 393 | ||||
| -rw-r--r-- | src/backend/pgxc/plan/planner.c | 2 | ||||
| -rw-r--r-- | src/backend/pgxc/pool/postgresql_fdw.c | 83 | ||||
| -rw-r--r-- | src/include/pgxc/postgresql_fdw.h | 10 | ||||
| -rw-r--r-- | src/test/regress/expected/xc_groupby.out | 140 | ||||
| -rw-r--r-- | src/test/regress/expected/xc_having.out | 514 |
6 files changed, 657 insertions, 485 deletions
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 7b28b892f6..699a476f99 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -99,6 +99,9 @@ static void pgxc_locate_grouping_columns(PlannerInfo *root, List *tlist, AttrNumber *grpColIdx); static List *pgxc_process_grouping_targetlist(PlannerInfo *root, List **local_tlist); +static List *pgxc_process_having_clause(PlannerInfo *root, List *remote_tlist, + Node *havingQual, List **local_qual, + List **remote_qual, bool *reduce_plan); #endif static ForeignScan *create_foreignscan_plan(PlannerInfo *root, ForeignPath *best_path, List *tlist, List *scan_clauses); @@ -820,7 +823,7 @@ create_remotejoin_plan(PlannerInfo *root, JoinPath *best_path, Plan *parent, Pla * If the JOIN ON clause has a local dependency then we cannot ship * the join to the remote side at all, bail out immediately. */ - if (!is_foreign_qual((Node *)nest_parent->join.joinqual)) + if (!is_foreign_expr((Node *)nest_parent->join.joinqual, NULL)) { elog(DEBUG1, "cannot reduce: local dependencies in the joinqual"); return parent; @@ -832,7 +835,7 @@ create_remotejoin_plan(PlannerInfo *root, JoinPath *best_path, Plan *parent, Pla * entire list. These local quals will become part of the quals * list of the reduced remote scan node down later. */ - if (!is_foreign_qual((Node *)nest_parent->join.plan.qual)) + if (!is_foreign_expr((Node *)nest_parent->join.plan.qual, NULL)) { elog(DEBUG1, "local dependencies in the join plan qual"); @@ -850,7 +853,7 @@ create_remotejoin_plan(PlannerInfo *root, JoinPath *best_path, Plan *parent, Pla * is currentof clause, so keep that information intact and * pass a dummy argument here. */ - if (!is_foreign_qual((Node *)clause)) + if (!is_foreign_expr((Node *)clause, NULL)) local_scan_clauses = lappend(local_scan_clauses, clause); else remote_scan_clauses = lappend(remote_scan_clauses, clause); @@ -2507,7 +2510,7 @@ create_remotequery_plan(PlannerInfo *root, Path *best_path, { Node *clause = lfirst(l); - if (is_foreign_qual(clause)) + if (is_foreign_expr(clause, NULL)) remote_scan_clauses = lappend(remote_scan_clauses, clause); else local_scan_clauses = lappend(local_scan_clauses, clause); @@ -5633,18 +5636,22 @@ create_remotegrouping_plan(PlannerInfo *root, Plan *local_plan) List *temp_vars; /* temporarily hold the VARs */ List *temp_vartlist; /* temporarity hold tlist of VARs */ ListCell *temp; - StringInfo remote_targetlist = makeStringInfo();/* SELECT clause of remote query */ - StringInfo remote_sql_stmt = makeStringInfo(); - StringInfo groupby_clause = makeStringInfo(); /* remote query GROUP BY */ - StringInfo orderby_clause = makeStringInfo(); /* remote query ORDER BY */ - StringInfo remote_fromlist = makeStringInfo(); /* remote query FROM */ - StringInfo in_alias = makeStringInfo(); + StringInfo remote_targetlist;/* SELECT clause of remote query */ + StringInfo remote_sql_stmt; + StringInfo groupby_clause; /* remote query GROUP BY */ + StringInfo orderby_clause; /* remote query ORDER BY */ + StringInfo remote_fromlist; /* remote query FROM */ + StringInfo in_alias; + StringInfo having_clause; /* remote query HAVING clause */ Relids in_relids; /* the list of Relids referenced by lefttree */ Index dummy_rtindex; List *base_tlist; RangeTblEntry *dummy_rte; int numGroupCols; AttrNumber *grpColIdx; + bool reduce_plan; + List *remote_qual; + List *local_qual; /* * We don't push aggregation and grouping to datanodes, in case there are @@ -5652,8 +5659,7 @@ create_remotegrouping_plan(PlannerInfo *root, Plan *local_plan) */ if (query->hasWindowFuncs || query->distinctClause || - query->sortClause || - query->havingQual) + query->sortClause) return local_plan; /* @@ -5750,14 +5756,22 @@ create_remotegrouping_plan(PlannerInfo *root, Plan *local_plan) * targetlist of the query to be shipped to the remote side */ base_tlist = pgxc_process_grouping_targetlist(root, &(local_plan->targetlist)); + /* + * If can not construct a targetlist shippable to the datanode. Resort to + * the plan created by grouping_planner() + */ if (!base_tlist) - { - /* - * for some reason we can not construct a targetlist shippable to the - * datanode. Resort to the plan created by grouping_planner() - */ return local_plan; - } + + base_tlist = pgxc_process_having_clause(root, base_tlist, query->havingQual, + &local_qual, &remote_qual, &reduce_plan); + /* + * Because of HAVING clause, we can not push the aggregates and GROUP BY + * clause to the data node. Resort to the plan created by grouping planner. + */ + if (!reduce_plan) + return local_plan; + Assert(base_tlist); /* * We are now ready to create the RemoteQuery node to push the query to @@ -5767,6 +5781,14 @@ create_remotegrouping_plan(PlannerInfo *root, Plan *local_plan) * 2. Modify the Grouping node passed in, to accept the results sent by the * Datanodes, then group and aggregate them, if needed. */ + remote_targetlist = makeStringInfo(); + remote_sql_stmt = makeStringInfo(); + groupby_clause = makeStringInfo(); + orderby_clause = makeStringInfo(); + remote_fromlist = makeStringInfo(); + in_alias = makeStringInfo(); + having_clause = makeStringInfo(); + appendStringInfo(in_alias, "%s_%d", "group", root->rs_alias_index); /* @@ -5798,13 +5820,13 @@ create_remotegrouping_plan(PlannerInfo *root, Plan *local_plan) create_remote_expr(root, local_plan, remote_targetlist, expr, remote_group); - /* If this is not last target entry, add a comma with space */ + /* If this is not last target entry, add a comma */ if (lnext(temp)) appendStringInfoString(remote_targetlist, ","); } /* Generate the from clause of the remote query */ - appendStringInfo(remote_fromlist, "FROM (%s) %s", + appendStringInfo(remote_fromlist, " FROM (%s) %s", remote_group->inner_statement, remote_group->inner_alias); /* @@ -5860,11 +5882,17 @@ create_remotegrouping_plan(PlannerInfo *root, Plan *local_plan) } } + if (remote_qual) + { + appendStringInfoString(having_clause, "HAVING "); + create_remote_clause_expr(root, local_plan, having_clause, remote_qual, + remote_group); + } + /* Generate the remote sql statement from the pieces */ - appendStringInfo(remote_sql_stmt, "%s %s %s %s", remote_targetlist->data, + appendStringInfo(remote_sql_stmt, "%s %s %s %s %s", remote_targetlist->data, remote_fromlist->data, groupby_clause->data, - orderby_clause->data); - + orderby_clause->data, having_clause->data); /* * Create a dummy RTE for the remote query being created. Append the dummy * range table entry to the range table. Note that this modifies the master @@ -5901,17 +5929,7 @@ create_remotegrouping_plan(PlannerInfo *root, Plan *local_plan) remote_group->partitioned_replicated = remote_scan->partitioned_replicated; remote_group->read_only = query->commandType == CMD_SELECT; - /* - * Only quals that can be pushed to the remote side are the ones in the having - * clause. Till we work out how to handle having quals in XC, we don't have - * any quals here. - * - * PGXCTODO: the RemoteQuery node that was earlier the lefttree of Agg - * node, may have local quals. In such case, we have to aggregate and group - * at coordinator and can not push the grouping clause to the datanodes. Is - * there a case in XC, where we can have local quals? - * we actually need not worry about costs since this is the final plan - */ + /* we actually need not worry about costs since this is the final plan */ remote_group_plan->startup_cost = remote_scan->scan.plan.startup_cost; remote_group_plan->total_cost = remote_scan->scan.plan.total_cost; remote_group_plan->plan_rows = remote_scan->scan.plan.plan_rows; @@ -5923,6 +5941,7 @@ create_remotegrouping_plan(PlannerInfo *root, Plan *local_plan) * the scan. */ local_plan->lefttree = (Plan *) make_material(remote_group_plan); + local_plan->qual = local_qual; /* indicate that we should apply collection function directly */ if (IsA(local_plan, Agg)) ((Agg *)local_plan)->skip_trans = true; @@ -5966,6 +5985,85 @@ pgxc_locate_grouping_columns(PlannerInfo *root, List *tlist, } /* + * pgxc_add_node_to_grouping_tlist + * Add the given node to the target list to be sent to the datanode. If it's + * Aggref node, also change the passed in node to point to the Aggref node in + * the data node's target list + */ +static List * +pgxc_add_node_to_grouping_tlist(List *remote_tlist, Node *expr, Index ressortgroupref) +{ + TargetEntry *remote_tle; + Oid saved_aggtype; + + /* + * When we add an aggregate to the remote targetlist the aggtype of such + * Aggref node is changed to aggtrantype. Hence while searching a given + * Aggref in remote targetlist, we need to change the aggtype accordingly + * and then switch it back. + */ + if (IsA(expr, Aggref)) + { + Aggref *aggref = (Aggref *)expr; + saved_aggtype = aggref->aggtype; + aggref->aggtype = aggref->aggtrantype; + } + remote_tle = tlist_member(expr, remote_tlist); + if (IsA(expr, Aggref)) + ((Aggref *)expr)->aggtype = saved_aggtype; + + if (!remote_tle) + { + remote_tle = makeTargetEntry(copyObject(expr), + list_length(remote_tlist) + 1, + NULL, + false); + /* Copy GROUP BY/SORT BY reference for the locating group by columns */ + remote_tle->ressortgroupref = ressortgroupref; + remote_tlist = lappend(remote_tlist, remote_tle); + } + else + { + + if (remote_tle->ressortgroupref == 0) + remote_tle->ressortgroupref = ressortgroupref; + else if (ressortgroupref == 0) + { + /* do nothing remote_tle->ressortgroupref has the right value */ + } + else + { + /* + * if the expression's TLE already has a Sorting/Grouping reference, + * and caller has passed a non-zero one as well, better both of them + * be same + */ + Assert(remote_tle->ressortgroupref == ressortgroupref); + } + } + + /* + * Replace the args of the local Aggref with Aggref node to be + * included in RemoteQuery node, so that set_plan_refs can convert + * the args into VAR pointing to the appropriate result in the tuple + * coming from RemoteQuery node + * PGXCTODO: should we push this change in targetlists of plans + * above? + */ + if (IsA(expr, Aggref)) + { + Aggref *local_aggref = (Aggref *)expr; + Aggref *remote_aggref = (Aggref *)remote_tle->expr; + Assert(IsA(remote_tle->expr, Aggref)); + remote_aggref->aggtype = remote_aggref->aggtrantype; + /* Is copyObject() needed here? probably yes */ + local_aggref->args = list_make1(makeTargetEntry(copyObject(remote_tle->expr), + 1, NULL, + false)); + } + return remote_tlist; +} +/* * pgxc_process_grouping_targetlist * The function scans the targetlist to check if the we can push anything * from the targetlist to the datanode. Following rules govern the choice @@ -5988,10 +6086,8 @@ pgxc_process_grouping_targetlist(PlannerInfo *root, List **local_tlist) { bool shippable_remote_tlist = true; List *remote_tlist = NIL; - int next_resno = 1; /* resno start from 1 */ List *orig_local_tlist = NIL;/* Copy original local_tlist, in case it changes */ ListCell *temp; - Query *query = root->parse; /* * Walk through the target list and find out whether we can push the @@ -6001,83 +6097,90 @@ pgxc_process_grouping_targetlist(PlannerInfo *root, List **local_tlist) */ foreach(temp, *local_tlist) { - TargetEntry *local_tle = lfirst(temp); - TargetEntry *remote_tle; - Node *expr = (Node *)local_tle->expr; + TargetEntry *local_tle = lfirst(temp); + Node *expr = (Node *)local_tle->expr; + foreign_qual_context context; - if (IsA(expr, Aggref)) + pgxc_foreign_qual_context_init(&context); + /* + * If the expression is not Aggref but involves aggregates (has Aggref + * nodes in the expression tree, we can not push the entire expression + * to the datanode, but push those aggregates to the data node, if those + * aggregates can be evaluated at the data nodes (if is_foreign_expr + * returns true for entire expression). To evaluate the rest of the + * expression, we need to fetch the values of VARs participating in the + * expression. But, if we include the VARs under the aggregate nodes, + * they may not be part of GROUP BY clause, thus generating an invalid + * query. Hence, is_foreign_expr() wouldn't collect VARs under the + * expression tree rooted under Aggref node. + * For example, the original query is + * SELECT sum(val) * val2 FROM tab1 GROUP BY val2; + * the query pushed to the data node is + * SELECT sum(val), val2 FROM tab1 GROUP BY val2; + * Notice that, if we include val in the query, it will become invalid. + */ + context.collect_vars = true; + + if (!is_foreign_expr(expr, &context)) { - Aggref *aggref = (Aggref *)expr; - /* - * If the aggregation needs tuples ordered specifically, or only - * accepts distinct values, we can not aggregate unless we have all - * the qualifying rows. Hence partial aggregation at data nodes can - * give wrong results. Hence we can not such aggregates to the - * datanodes. - * If there is no collection function, we can not combine the - * partial aggregation results from the data nodes, hence can not - * push such aggregate to the data nodes. - * PGXCTODO: If the transition type of the collection is polymorphic we - * need to resolve it first. That tells us the partial aggregation type - * expected from data node. - */ - if (aggref->aggorder || - aggref->aggdistinct || - aggref->agglevelsup || - !aggref->agghas_collectfn || - IsPolymorphicType(aggref->aggtrantype)) - { shippable_remote_tlist = false; break; - } - } - else if (query->hasAggs && checkExprHasAggs(expr)) - { - /* - * Targetlist expressions which have aggregates embedded inside - * are not handled right now. - * PGXCTODO: We should be able to extract those aggregates out. - * Add those to remote targetlist and modify the local - * targetlist accordingly. Thus we get those aggregates grouped - * and "transitioned" at the datanode. - */ - shippable_remote_tlist = false; - break; } - remote_tle = makeTargetEntry(copyObject(expr), - next_resno++, - NULL, - false); - /* Copy GROUP BY/SORT BY reference for the locating group by columns */ - remote_tle->ressortgroupref = local_tle->ressortgroupref; - remote_tlist = lappend(remote_tlist, remote_tle); + /* + * We are about to change the local_tlist, check if we have already + * copied original local_tlist, if not take a copy + */ + if (!orig_local_tlist && (IsA(expr, Aggref) || context.aggs)) + orig_local_tlist = copyObject(*local_tlist); /* - * Replace the args of the local Aggref with Aggref node to be - * included in RemoteQuery node, so that set_plan_refs can convert - * the args into VAR pointing to the appropriate result in the tuple - * coming from RemoteQuery node - * PGXCTODO: should we push this change in targetlists of plans - * above? + * if there are aggregates involved in the expression, whole expression + * can not be pushed to the data node. Pick up the aggregates and the + * VAR nodes not covered by aggregates. */ - if (IsA(expr, Aggref)) + if (context.aggs) { - Aggref *local_aggref = (Aggref *)expr; - Aggref *remote_aggref = (Aggref *)remote_tle->expr; - Assert(IsA(remote_tle->expr, Aggref)); - remote_aggref->aggtype = remote_aggref->aggtrantype; + ListCell *lcell; /* - * We are about to change the local_tlist, check if we have already - * copied original local_tlist, if not take a copy + * if the target list expression is an Aggref, then the context should + * have only one Aggref in the list and no VARs. */ - if (!orig_local_tlist) - orig_local_tlist = copyObject(*local_tlist); - /* Is copyObject() needed here? probably yes */ - local_aggref->args = list_make1(makeTargetEntry(copyObject(remote_tle->expr), - 1, NULL, - false)); + Assert(!IsA(expr, Aggref) || + (list_length(context.aggs) == 1 && + linitial(context.aggs) == expr && + !context.vars)); + /* + * this expression is not going to be pushed as whole, thus other + * clauses won't be able to find out this TLE in the results + * obtained from data node. Hence can't optimize this query. + */ + if (local_tle->ressortgroupref > 0) + { + shippable_remote_tlist = false; + break; + } + /* copy the aggregates into the remote target list */ + foreach (lcell, context.aggs) + { + Assert(IsA(lfirst(lcell), Aggref)); + remote_tlist = pgxc_add_node_to_grouping_tlist(remote_tlist, lfirst(lcell), + 0); + } + /* copy the vars into the remote target list */ + foreach (lcell, context.vars) + { + Assert(IsA(lfirst(lcell), Var)); + remote_tlist = pgxc_add_node_to_grouping_tlist(remote_tlist, lfirst(lcell), + 0); + } } + /* Expression doesn't contain any aggregate */ + else + remote_tlist = pgxc_add_node_to_grouping_tlist(remote_tlist, expr, + local_tle->ressortgroupref); + + pgxc_foreign_qual_context_free(&context); } if (!shippable_remote_tlist) @@ -6104,4 +6207,94 @@ pgxc_process_grouping_targetlist(PlannerInfo *root, List **local_tlist) return remote_tlist; } +/* + * pgxc_process_having_clause + * For every expression in the havingQual take following action + * 1. If it has aggregates, which can be evaluated at the data nodes, add those + * aggregates to the targetlist and modify the local aggregate expressions to + * point to the aggregate expressions being pushed to the data node. Add this + * expression to the local qual to be evaluated locally. + * 2. If the expression does not have aggregates and the whole expression can be + * evaluated at the data node, add the expression to the remote qual to be + * evaluated at the data node. + * 3. If qual contains an expression which can not be evaluated at the data + * node, the parent group plan can not be reduced to a remote_query. + */ +static List * +pgxc_process_having_clause(PlannerInfo *root, List *remote_tlist, Node *havingQual, + List **local_qual, List **remote_qual, + bool *reduce_plan) +{ + foreign_qual_context context; + List *qual; + ListCell *temp; + + *reduce_plan = true; + *remote_qual = NIL; + *local_qual = NIL; + + if (!havingQual) + return remote_tlist; + /* + * PGXCTODO: we expect the quals in the form of List only. Is there a + * possibility that the quals will be another form? + */ + if (!IsA(havingQual, List)) + { + *reduce_plan = false; + return remote_tlist; + } + /* + * Copy the havingQual so that the copy can be modified later. In case we + * back out in between, the original expression remains intact. + */ + qual = copyObject(havingQual); + foreach(temp, qual) + { + Node *expr = lfirst(temp); + pgxc_foreign_qual_context_init(&context); + if (!is_foreign_expr(expr, &context)) + { + *reduce_plan = false; + break; + } + + if (context.aggs) + { + ListCell *lcell; + /* + * if the target list havingQual is an Aggref, then the context should + * have only one Aggref in the list and no VARs. + */ + Assert(!IsA(expr, Aggref) || + (list_length(context.aggs) == 1 && + linitial(context.aggs) == expr && + !context.vars)); + /* copy the aggregates into the remote target list */ + foreach (lcell, context.aggs) + { + Assert(IsA(lfirst(lcell), Aggref)); + remote_tlist = pgxc_add_node_to_grouping_tlist(remote_tlist, lfirst(lcell), + 0); + } + /* copy the vars into the remote target list */ + foreach (lcell, context.vars) + { + Assert(IsA(lfirst(lcell), Var)); + remote_tlist = pgxc_add_node_to_grouping_tlist(remote_tlist, lfirst(lcell), + 0); + } + *local_qual = lappend(*local_qual, expr); + } + else + *remote_qual = lappend(*remote_qual, expr); + + pgxc_foreign_qual_context_free(&context); + } + + if (!(*reduce_plan)) + list_free_deep(qual); + + return remote_tlist; +} #endif diff --git a/src/backend/pgxc/plan/planner.c b/src/backend/pgxc/plan/planner.c index f2ee26f222..4753088a90 100644 --- a/src/backend/pgxc/plan/planner.c +++ b/src/backend/pgxc/plan/planner.c @@ -1642,7 +1642,7 @@ get_plan_nodes_walker(Node *query_node, XCWalkerContext *context) /* Examine the WHERE clause, too */ if (examine_conditions_walker(query->jointree->quals, context) || - !is_foreign_qual(query->jointree->quals)) + !is_foreign_expr(query->jointree->quals, NULL)) return true; if (context->query_step->exec_nodes) diff --git a/src/backend/pgxc/pool/postgresql_fdw.c b/src/backend/pgxc/pool/postgresql_fdw.c index 1a158a697f..b63690e4e4 100644 --- a/src/backend/pgxc/pool/postgresql_fdw.c +++ b/src/backend/pgxc/pool/postgresql_fdw.c @@ -13,6 +13,7 @@ #include "pgxc/postgresql_fdw.h" #include "catalog/pg_operator.h" #include "catalog/pg_proc.h" +#include "catalog/pg_type.h" #include "funcapi.h" #include "mb/pg_wchar.h" #include "miscadmin.h" @@ -37,7 +38,7 @@ #define OPTIMIZE_WHERE_CLAUSE EVAL_QUAL_FOREIGN /* deparse SQL from the request */ -static bool foreign_qual_walker(Node *node, void *context); +static bool foreign_qual_walker(Node *node, foreign_qual_context *context); /* * Check whether the function is IMMUTABLE. @@ -93,17 +94,35 @@ is_immutable_func(Oid funcid) * - scalar array operator (ANY/ALL) */ bool -is_foreign_qual(Node *node) +is_foreign_expr(Node *node, foreign_qual_context *context) { - return !foreign_qual_walker(node, NULL); + return !foreign_qual_walker(node, context); } +void +pgxc_foreign_qual_context_init(foreign_qual_context *context) +{ + context->collect_vars = false; + context->vars = NIL; + context->aggs = NIL; +} + +void +pgxc_foreign_qual_context_free(foreign_qual_context *context) +{ + list_free(context->vars); + context->vars = NIL; + list_free(context->aggs); + context->aggs = NIL; +} /* * return true if node cannot be evaluatated in foreign server. */ static bool -foreign_qual_walker(Node *node, void *context) +foreign_qual_walker(Node *node, foreign_qual_context *context) { + bool ret_val; + bool saved_collect_vars; if (node == NULL) return false; @@ -137,7 +156,44 @@ foreign_qual_walker(Node *node, void *context) if (!is_immutable_func(((FuncExpr*) node)->funcid)) return true; break; - case T_TargetEntry: + case T_Aggref: + { + Aggref *aggref = (Aggref *)node; + /* + * An aggregate with ORDER BY, DISTINCT directives need to be + * computed at coordinator using all the rows. An aggregate + * without collection function needs to be computed at + * coordinator. + * PGXCTODO: polymorphic transition types need to be resolved to + * correctly interpret the transition results from data nodes. + * For now compute such aggregates at coordinator. + */ + if (aggref->aggorder || + aggref->aggdistinct || + aggref->agglevelsup || + !aggref->agghas_collectfn || + IsPolymorphicType(aggref->aggtrantype)) + return true; + /* + * data node can compute transition results, so, add the + * aggregate to the context if context is present + */ + if (context) + { + /* + * Don't collect VARs under the Aggref node. See + * pgxc_process_grouping_targetlist() for details. + */ + saved_collect_vars = context->collect_vars; + context->collect_vars = false; + context->aggs = lappend(context->aggs, aggref); + } + } + break; + case T_Var: + if (context && context->collect_vars) + context->vars = lappend(context->vars, node); + break; case T_PlaceHolderVar: case T_AppendRelInfo: case T_PlaceHolderInfo: @@ -147,13 +203,22 @@ foreign_qual_walker(Node *node, void *context) break; } - return expression_tree_walker(node, foreign_qual_walker, context); + ret_val = expression_tree_walker(node, foreign_qual_walker, context); + + /* + * restore value of collect_vars in the context, since we have finished + * traversing tree rooted under and Aggref node + */ + if (context && IsA(node, Aggref)) + context->collect_vars = saved_collect_vars; + + return ret_val; } /* * Deparse SQL string from query request. * - * The expressions in Plan.qual are deparsed when it satisfies is_foreign_qual() + * The expressions in Plan.qual are deparsed when it satisfies is_foreign_expr() * and removed. */ char * @@ -255,7 +320,7 @@ elog(DEBUG2, "%s(%u) called", __FUNCTION__, __LINE__); /* * deparse WHERE cluase * - * The expressions which satisfy is_foreign_qual() are deparsed into WHERE + * The expressions which satisfy is_foreign_expr() are deparsed into WHERE * clause of result SQL string, and they could be removed from qual of * PlanState to avoid duplicate evaluation at ExecScan(). * @@ -278,7 +343,7 @@ elog(DEBUG2, "%s(%u) called", __FUNCTION__, __LINE__); { ExprState *state = lfirst(lc); - if (is_foreign_qual((Node *) state)) + if (is_foreign_expr((Node *) state, NULL)) { elog(DEBUG1, "foreign qual: %s", nodeToString(state->expr)); foreign_qual = lappend(foreign_qual, state); diff --git a/src/include/pgxc/postgresql_fdw.h b/src/include/pgxc/postgresql_fdw.h index 563236c39d..e092951226 100644 --- a/src/include/pgxc/postgresql_fdw.h +++ b/src/include/pgxc/postgresql_fdw.h @@ -17,8 +17,16 @@ #include "postgres.h" #include "pgxc/execRemote.h" +typedef struct +{ + bool collect_vars; + List *aggs; + List *vars; +} foreign_qual_context; +void pgxc_foreign_qual_context_init(foreign_qual_context *context); +void pgxc_foreign_qual_context_free(foreign_qual_context *context); bool is_immutable_func(Oid funcid); -bool is_foreign_qual(Node *node); char *deparseSql(RemoteQueryState *scanstate); +bool is_foreign_expr(Node *node, foreign_qual_context *context); #endif diff --git a/src/test/regress/expected/xc_groupby.out b/src/test/regress/expected/xc_groupby.out index 08f8da53c4..36a2697cd1 100644 --- a/src/test/regress/expected/xc_groupby.out +++ b/src/test/regress/expected/xc_groupby.out @@ -19,14 +19,14 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_gro (3 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_groupby_tab1 group by val2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.05 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_groupby_tab1.val))), pg_catalog.avg((avg(xc_groupby_tab1.val))), ((pg_catalog.sum((sum(xc_groupby_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_groupby_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2 -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2 (6 rows) -- joins and group by @@ -127,9 +127,9 @@ select val + val2, val, val2 from xc_groupby_tab1 group by val, val2; ----------+-----+------ 7 | 4 | 3 4 | 3 | 1 - 4 | 1 | 3 4 | 2 | 2 3 | 2 | 1 + 4 | 1 | 3 2 | 1 | 1 8 | 6 | 2 9 | 6 | 3 @@ -198,14 +198,14 @@ select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2; (3 rows) explain verbose select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2; - QUERY PLAN ----------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.02..1.04 rows=1 width=8) - Output: (((count(*) + sum(val)))::numeric + avg(val)), val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_groupby_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_groupby_tab1.val)))), xc_groupby_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2 -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2 (6 rows) -- group by with expressions in group by clause @@ -284,12 +284,12 @@ select avg(a) from xc_groupby_def group by a; 6.0000000000000000 8.0000000000000000 5.0000000000000000 - 2.0000000000000000 1.00000000000000000000 9.0000000000000000 - 3.0000000000000000 + 2.0000000000000000 10.0000000000000000 7.0000000000000000 + 3.0000000000000000 4.0000000000000000 (11 rows) @@ -387,9 +387,9 @@ select count(*) from xc_groupby_def where a is not null group by a; 1 1 1 + 2 1 1 - 2 1 1 1 @@ -593,14 +593,14 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_gro (3 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_groupby_tab1 group by val2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.05 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_groupby_tab1.val))), pg_catalog.avg((avg(xc_groupby_tab1.val))), ((pg_catalog.sum((sum(xc_groupby_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_groupby_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2 -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2 (6 rows) -- joins and group by @@ -772,14 +772,14 @@ select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2; (3 rows) explain verbose select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2; - QUERY PLAN ----------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.02..1.04 rows=1 width=8) - Output: (((count(*) + sum(val)))::numeric + avg(val)), val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_groupby_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_groupby_tab1.val)))), xc_groupby_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2 -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2 (6 rows) -- group by with expressions in group by clause @@ -1179,20 +1179,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_gro (3 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_groupby_tab1 group by val2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.06 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_groupby_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(11 rows) + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_groupby_tab1.val))), pg_catalog.avg((avg(xc_groupby_tab1.val))), ((pg_catalog.sum((sum(xc_groupby_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_groupby_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2 + -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2 +(6 rows) -- joins and group by select count(*), sum(xc_groupby_tab1.val * xc_groupby_tab2.val), avg(xc_groupby_tab1.val*xc_groupby_tab2.val), sum(xc_groupby_tab1.val*xc_groupby_tab2.val)::float8/count(*), xc_groupby_tab1.val2, xc_groupby_tab2.val2 from xc_groupby_tab1 full outer join xc_groupby_tab2 on xc_groupby_tab1.val2 = xc_groupby_tab2.val2 group by xc_groupby_tab1.val2, xc_groupby_tab2.val2; @@ -1371,20 +1366,15 @@ select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2; (3 rows) explain verbose select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2; - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.06 rows=1 width=8) - Output: (((count(*) + sum(val)))::numeric + avg(val)), val2 - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_groupby_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(11 rows) + Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_groupby_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_groupby_tab1.val)))), xc_groupby_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2 + -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2 +(6 rows) -- group by with expressions in group by clause select sum(val), avg(val), 2 * val2 from xc_groupby_tab1 group by 2 * val2; @@ -1771,20 +1761,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_gro (3 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_groupby_tab1 group by val2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.06 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_groupby_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(11 rows) + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_groupby_tab1.val))), pg_catalog.avg((avg(xc_groupby_tab1.val))), ((pg_catalog.sum((sum(xc_groupby_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_groupby_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2 + -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2 +(6 rows) -- joins and group by select count(*), sum(xc_groupby_tab1.val * xc_groupby_tab2.val), avg(xc_groupby_tab1.val*xc_groupby_tab2.val), sum(xc_groupby_tab1.val*xc_groupby_tab2.val)::float8/count(*), xc_groupby_tab1.val2, xc_groupby_tab2.val2 from xc_groupby_tab1 full outer join xc_groupby_tab2 on xc_groupby_tab1.val2 = xc_groupby_tab2.val2 group by xc_groupby_tab1.val2, xc_groupby_tab2.val2; @@ -1963,20 +1948,15 @@ select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2; (3 rows) explain verbose select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2; - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.06 rows=1 width=8) - Output: (((count(*) + sum(val)))::numeric + avg(val)), val2 - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_groupby_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(11 rows) + Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_groupby_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_groupby_tab1.val)))), xc_groupby_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2 + -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2 +(6 rows) -- group by with expressions in group by clause select sum(val), avg(val), 2 * val2 from xc_groupby_tab1 group by 2 * val2; diff --git a/src/test/regress/expected/xc_having.out b/src/test/regress/expected/xc_having.out index 6dc95d43e0..b10d4f81b7 100644 --- a/src/test/regress/expected/xc_having.out +++ b/src/test/regress/expected/xc_having.out @@ -18,14 +18,14 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (1 row) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having val2 + 1 > 3; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.05 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - -> Materialize (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 (6 rows) -- having clause containing aggregate @@ -36,15 +36,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (1 row) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.06 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: (avg(xc_having_tab1.val) > 3.75) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 (7 rows) select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2; @@ -55,15 +55,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (2 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.06 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: ((avg(xc_having_tab1.val) > 3.75) OR (xc_having_tab1.val2 > 2)) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: ((pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) OR (xc_having_tab1.val2 > 2)) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 (7 rows) select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2; @@ -72,15 +72,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (0 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.06 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: (avg(xc_having_tab1.val) > 3.75) - -> Materialize (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 (7 rows) -- joins and group by and having @@ -119,12 +119,12 @@ explain verbose select val2 from xc_having_tab1 group by val2 having sum(val) > QUERY PLAN ---------------------------------------------------------------------------------- HashAggregate (cost=1.02..1.03 rows=1 width=8) - Output: val2 - Filter: (sum(xc_having_tab1.val) > 8) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: xc_having_tab1.val2 + Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 8) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: xc_having_tab1.val2, (sum(xc_having_tab1.val)) -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: xc_having_tab1.val2, sum(xc_having_tab1.val) (7 rows) select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; @@ -136,18 +136,16 @@ select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; (3 rows) explain verbose select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; - QUERY PLAN ----------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- HashAggregate (cost=1.02..1.04 rows=1 width=8) - Output: ((val + val2)) - Filter: (sum(xc_having_tab1.val) > 5) - -> Result (cost=0.00..1.02 rows=1 width=8) - Output: val, val2, (val + val2) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(9 rows) + Output: ((xc_having_tab1.val + xc_having_tab1.val2)) + Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 5) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: ((xc_having_tab1.val + xc_having_tab1.val2)), (sum(xc_having_tab1.val)) + -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) + Output: (xc_having_tab1.val + xc_having_tab1.val2), sum(xc_having_tab1.val) +(7 rows) -- group by with aggregates in expression select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2; @@ -157,15 +155,15 @@ select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 ha (1 row) explain verbose select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2; - QUERY PLAN ----------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.05 rows=1 width=8) - Output: (((count(*) + sum(val)))::numeric + avg(val)), val2 - Filter: (min(xc_having_tab1.val) < xc_having_tab1.val2) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_having_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_having_tab1.val)))), xc_having_tab1.val2 + Filter: (min((min(xc_having_tab1.val))) < xc_having_tab1.val2) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2, (min(xc_having_tab1.val)) -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2, min(xc_having_tab1.val) (7 rows) drop table xc_having_tab1; @@ -185,14 +183,14 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (1 row) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having val2 + 1 > 3; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.05 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - -> Materialize (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 (6 rows) -- having clause containing aggregate @@ -203,15 +201,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (1 row) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.06 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: (avg(xc_having_tab1.val) > 3.75) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 (7 rows) select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2; @@ -222,15 +220,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (2 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.06 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: ((avg(xc_having_tab1.val) > 3.75) OR (xc_having_tab1.val2 > 2)) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: ((pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) OR (xc_having_tab1.val2 > 2)) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 (7 rows) select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2; @@ -239,15 +237,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (0 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.06 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: (avg(xc_having_tab1.val) > 3.75) - -> Materialize (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 (7 rows) -- joins and group by and having @@ -286,12 +284,12 @@ explain verbose select val2 from xc_having_tab1 group by val2 having sum(val) > QUERY PLAN ---------------------------------------------------------------------------------- HashAggregate (cost=1.02..1.03 rows=1 width=8) - Output: val2 - Filter: (sum(xc_having_tab1.val) > 8) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: xc_having_tab1.val2 + Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 8) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: xc_having_tab1.val2, (sum(xc_having_tab1.val)) -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: xc_having_tab1.val2, sum(xc_having_tab1.val) (7 rows) select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; @@ -303,18 +301,16 @@ select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; (3 rows) explain verbose select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; - QUERY PLAN ----------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- HashAggregate (cost=1.02..1.04 rows=1 width=8) - Output: ((val + val2)) - Filter: (sum(xc_having_tab1.val) > 5) - -> Result (cost=0.00..1.02 rows=1 width=8) - Output: val, val2, (val + val2) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(9 rows) + Output: ((xc_having_tab1.val + xc_having_tab1.val2)) + Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 5) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: ((xc_having_tab1.val + xc_having_tab1.val2)), (sum(xc_having_tab1.val)) + -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) + Output: (xc_having_tab1.val + xc_having_tab1.val2), sum(xc_having_tab1.val) +(7 rows) -- group by with aggregates in expression select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2; @@ -324,15 +320,15 @@ select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 ha (1 row) explain verbose select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2; - QUERY PLAN ----------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1.03..1.05 rows=1 width=8) - Output: (((count(*) + sum(val)))::numeric + avg(val)), val2 - Filter: (min(xc_having_tab1.val) < xc_having_tab1.val2) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 + Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_having_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_having_tab1.val)))), xc_having_tab1.val2 + Filter: (min((min(xc_having_tab1.val))) < xc_having_tab1.val2) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2, (min(xc_having_tab1.val)) -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2, min(xc_having_tab1.val) (7 rows) drop table xc_having_tab1; @@ -352,20 +348,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (1 row) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having val2 + 1 > 3; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.03..1.07 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - -> Sort (cost=1.03..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(11 rows) + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 + -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 +(6 rows) -- having clause containing aggregate select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75; @@ -375,21 +366,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (1 row) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.07 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: (avg(xc_having_tab1.val) > 3.75) - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 + -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 +(7 rows) select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2; count | sum | avg | ?column? | val2 @@ -399,21 +385,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (2 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.07 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: ((avg(xc_having_tab1.val) > 3.75) OR (xc_having_tab1.val2 > 2)) - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: ((pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) OR (xc_having_tab1.val2 > 2)) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 + -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 +(7 rows) select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2; count | sum | avg | ?column? | val2 @@ -421,21 +402,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (0 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.03..1.07 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: (avg(xc_having_tab1.val) > 3.75) - -> Sort (cost=1.03..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 + -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 +(7 rows) -- joins and group by and having select count(*), sum(xc_having_tab1.val * xc_having_tab2.val), avg(xc_having_tab1.val*xc_having_tab2.val), sum(xc_having_tab1.val*xc_having_tab2.val)::float8/count(*), xc_having_tab1.val2, xc_having_tab2.val2 from xc_having_tab1 full outer join xc_having_tab2 on xc_having_tab1.val2 = xc_having_tab2.val2 group by xc_having_tab1.val2, xc_having_tab2.val2 having xc_having_tab1.val2 + xc_having_tab2.val2 > 2; @@ -473,21 +449,16 @@ select val2 from xc_having_tab1 group by val2 having sum(val) > 8; (1 row) explain verbose select val2 from xc_having_tab1 group by val2 having sum(val) > 8; - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.05 rows=1 width=8) - Output: val2 - Filter: (sum(xc_having_tab1.val) > 8) - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val2, val - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val2, val - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: xc_having_tab1.val2 + Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 8) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: xc_having_tab1.val2, (sum(xc_having_tab1.val)) + -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) + Output: xc_having_tab1.val2, sum(xc_having_tab1.val) +(7 rows) select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; ?column? @@ -498,21 +469,16 @@ select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; (3 rows) explain verbose select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- GroupAggregate (cost=1.03..1.05 rows=1 width=8) - Output: ((val + val2)) - Filter: (sum(xc_having_tab1.val) > 5) - -> Sort (cost=1.03..1.03 rows=1 width=8) - Output: val, val2, ((val + val2)) - Sort Key: ((xc_having_tab1.val + xc_having_tab1.val2)) - -> Result (cost=0.00..1.02 rows=1 width=8) - Output: val, val2, (val + val2) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: ((xc_having_tab1.val + xc_having_tab1.val2)) + Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 5) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: ((xc_having_tab1.val + xc_having_tab1.val2)), (sum(xc_having_tab1.val)) + -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) + Output: (xc_having_tab1.val + xc_having_tab1.val2), sum(xc_having_tab1.val) +(7 rows) -- group by with aggregates in expression select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2; @@ -522,21 +488,16 @@ select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 ha (1 row) explain verbose select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2; - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.06 rows=1 width=8) - Output: (((count(*) + sum(val)))::numeric + avg(val)), val2 - Filter: (min(xc_having_tab1.val) < xc_having_tab1.val2) - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_having_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_having_tab1.val)))), xc_having_tab1.val2 + Filter: (min((min(xc_having_tab1.val))) < xc_having_tab1.val2) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2, (min(xc_having_tab1.val)) + -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2, min(xc_having_tab1.val) +(7 rows) drop table xc_having_tab1; drop table xc_having_tab2; @@ -555,20 +516,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (1 row) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having val2 + 1 > 3; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.03..1.07 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - -> Sort (cost=1.03..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(11 rows) + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 + -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 +(6 rows) -- having clause containing aggregate select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75; @@ -578,21 +534,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (1 row) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.07 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: (avg(xc_having_tab1.val) > 3.75) - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 + -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 +(7 rows) select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2; count | sum | avg | ?column? | val2 @@ -602,21 +553,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (2 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.07 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: ((avg(xc_having_tab1.val) > 3.75) OR (xc_having_tab1.val2 > 2)) - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: ((pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) OR (xc_having_tab1.val2 > 2)) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 + -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 +(7 rows) select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2; count | sum | avg | ?column? | val2 @@ -624,21 +570,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav (0 rows) explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2; - QUERY PLAN -------------------------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.03..1.07 rows=1 width=8) - Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2 - Filter: (avg(xc_having_tab1.val) > 3.75) - -> Sort (cost=1.03..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.02 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2 + Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2 + -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2 +(7 rows) -- joins and group by and having select count(*), sum(xc_having_tab1.val * xc_having_tab2.val), avg(xc_having_tab1.val*xc_having_tab2.val), sum(xc_having_tab1.val*xc_having_tab2.val)::float8/count(*), xc_having_tab1.val2, xc_having_tab2.val2 from xc_having_tab1 full outer join xc_having_tab2 on xc_having_tab1.val2 = xc_having_tab2.val2 group by xc_having_tab1.val2, xc_having_tab2.val2 having xc_having_tab1.val2 + xc_having_tab2.val2 > 2; @@ -676,21 +617,16 @@ select val2 from xc_having_tab1 group by val2 having sum(val) > 8; (1 row) explain verbose select val2 from xc_having_tab1 group by val2 having sum(val) > 8; - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.05 rows=1 width=8) - Output: val2 - Filter: (sum(xc_having_tab1.val) > 8) - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val2, val - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val2, val - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: xc_having_tab1.val2 + Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 8) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: xc_having_tab1.val2, (sum(xc_having_tab1.val)) + -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) + Output: xc_having_tab1.val2, sum(xc_having_tab1.val) +(7 rows) select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; ?column? @@ -701,21 +637,16 @@ select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; (3 rows) explain verbose select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5; - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------- GroupAggregate (cost=1.03..1.05 rows=1 width=8) - Output: ((val + val2)) - Filter: (sum(xc_having_tab1.val) > 5) - -> Sort (cost=1.03..1.03 rows=1 width=8) - Output: val, val2, ((val + val2)) - Sort Key: ((xc_having_tab1.val + xc_having_tab1.val2)) - -> Result (cost=0.00..1.02 rows=1 width=8) - Output: val, val2, (val + val2) - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: ((xc_having_tab1.val + xc_having_tab1.val2)) + Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 5) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: ((xc_having_tab1.val + xc_having_tab1.val2)), (sum(xc_having_tab1.val)) + -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) + Output: (xc_having_tab1.val + xc_having_tab1.val2), sum(xc_having_tab1.val) +(7 rows) -- group by with aggregates in expression select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2; @@ -725,21 +656,16 @@ select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 ha (1 row) explain verbose select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2; - QUERY PLAN ----------------------------------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=1.02..1.06 rows=1 width=8) - Output: (((count(*) + sum(val)))::numeric + avg(val)), val2 - Filter: (min(xc_having_tab1.val) < xc_having_tab1.val2) - -> Sort (cost=1.02..1.03 rows=1 width=8) - Output: val, val2 - Sort Key: xc_having_tab1.val2 - -> Result (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Materialize (cost=0.00..1.01 rows=1 width=8) - Output: val, val2 - -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) - Output: val, val2 -(12 rows) + Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_having_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_having_tab1.val)))), xc_having_tab1.val2 + Filter: (min((min(xc_having_tab1.val))) < xc_having_tab1.val2) + -> Materialize (cost=0.00..0.00 rows=0 width=0) + Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2, (min(xc_having_tab1.val)) + -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8) + Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2, min(xc_having_tab1.val) +(7 rows) drop table xc_having_tab1; drop table xc_having_tab2; |