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.



> 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

Reply via email to