Aggregate Push Down - Performing aggregation on foreign server

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).

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.