Hi 2016-08-30 15:02 GMT+02:00 Jeevan Chalke <jeevan.cha...@enterprisedb.com>:
> Hi all, > > Attached is the patch which adds support to push down aggregation and > grouping > to the foreign server for postgres_fdw. Performing aggregation on foreign > server results into fetching fewer rows from foreign side as compared to > fetching all the rows and aggregating/grouping locally. Performing > grouping on > foreign server may use indexes if available. So pushing down aggregates/ > grouping on foreign server performs better than doing that locally. > (Attached > EXPLAIN output for few simple grouping queries, with and without push > down). > is it work without FDW too?. It can be pretty interesting too. Regards Pavel > > Here are the few details of the implementation > > Creating Paths: > > Implements the FDW hook GetForeignUpperPaths, which adds foreign scan path > to > the output relation when upper relation kind is UPPERREL_GROUP_AGG. This > path > represents the aggregation/grouping operations to be performed on the > foreign > server. We are able to push down aggregation/grouping if (implemented in > foreign_grouping_ok()), > a. Underlying input relation is safe to push down and has no local > conditions, > as local conditions need to be applied before aggregation. > b. All the aggregates, GROUP BY expressions are safe to push down. > foreign_grouping_ok() functions assesses it. > > While checking for shippability, we build the target list which is passed > to > the foreign server as fdw_scan_tlist. The target list contains > a. All the GROUP BY expressions > b. Shippable entries from the target list of upper relation > c. Var and Aggref nodes from non-shippable entries from the target list of > upper relation > d. Var and Aggref nodes from non-shippable HAVING conditions. > > The shippable having conditions are sent to the foreign server as part of > the > HAVING clause of the remote SQL. > > is_foreign_expr() function, now handles T_Aggref node. Aggregate is safe to > push down if, > a. Aggregate is a built-in aggregate > b. All its arguments are safe to push-down > c. Other expressions involved like aggorder, aggdistinct, aggfilter etc. > are > safe to be pushed down. > > > Costing: > > If use_foreign_estimate is true for input relation, like JOIN case, we use > EXPLAIN output to get the cost of query with aggregation/grouping on the > foreign server. If not we calculate the costs locally. Similar to core, we > use > get_agg_clause_costs() to get costs for aggregation and then using logic > similar to cost_agg() we calculate startup and total cost. Since we have no > idea which aggregation strategy will be used at foreign side, we add all > startup cost (startup cost of input relation, aggregates etc.) into startup > cost for the grouping path and similarly for total cost. > > Deparsing the query: > > Target list created while checking for shippability is deparsed using > deparseExplicitTargetList(). sortgroupref are adjusted according to this > target list. Most of the logic to deparse an Aggref is inspired from > get_agg_expr(). For an upper relation, FROM and WHERE clauses come from the > underlying scan relation and thus for simplicity, FROM clause deparsing > logic > is moved from deparseSelectSql() to a new function deparseFromClause(). The > same function adds WHERE clause to the remote SQL. > > > Area of future work: > > 1. Adding path with path-keys to push ORDER BY clause along with > aggregation/ > grouping. Should be supported as a separate patch. > > 2. Grouping Sets/Rollup/Cube is not supported in current version. I have > left > this aside to keep patch smaller. If required I can add that support in the > next version of the patch. > > > Most of the code in this patch is inspired from the JOIN push down code. > Ashutosh Bapat provided a high-level design and a skeleton patch to > start-with > offlist. Thanks to Tom Lane for his upper-planner pathification work and > adding > GetForeignUpperPaths callback function. > > > Thanks > -- > Jeevan B Chalke > Principal Software Engineer, Product Development > EnterpriseDB Corporation > The Enterprise PostgreSQL Company > > Phone: +91 20 30589500 > > Website: www.enterprisedb.com > EnterpriseDB Blog: http://blogs.enterprisedb.com/ > Follow us on Twitter: http://www.twitter.com/enterprisedb > > This e-mail message (and any attachment) is intended for the use of the > individual or entity to whom it is addressed. This message contains > information from EnterpriseDB Corporation that may be privileged, > confidential, or exempt from disclosure under applicable law. If you are > not the intended recipient or authorized to receive this for the intended > recipient, any use, dissemination, distribution, retention, archiving, or > copying of this communication is strictly prohibited. If you have received > this e-mail in error, please notify the sender immediately by reply e-mail > and delete this message. > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > >
