On 21st of October, Robert Haas committed patch:

postgres_fdw: Push down aggregates to remote servers. Now that the upper planner uses paths, and now that we have proper hooks to inject paths into the upper planning process, it's possible for foreign data wrappers to arrange to push aggregates to the remote side instead of fetching all of the rows and aggregating them locally. This figures to be a massive win for performance, so teach postgres_fdw to do it. Jeevan Chalke and Ashutosh Bapat. Reviewed by Ashutosh Bapat with additional testing by Prabhat Sahu. Various mostly cosmetic changes by me.

Description seems to be simple, but let's see how it works in Pg9.6 and Pg10.

First the old version (9.6):

$ CREATE TABLE sample_data AS SELECT i AS id , CAST ( random ( ) * 10 AS int4 ) AS group_id FROM generate_series ( 1 , 100000 ) i; SELECT 100000 $ CREATE extension postgres_fdw; CREATE EXTENSION $ CREATE server origin FOREIGN DATA wrapper postgres_fdw options ( dbname 'depesz' ) ; CREATE SERVER $ CREATE USER mapping FOR depesz server origin options ( USER 'depesz' ) ; CREATE USER MAPPING $ CREATE FOREIGN TABLE data_from_origin ( id int4 , group_id int4 ) server origin options ( TABLE_NAME 'sample_data' ) ; CREATE FOREIGN TABLE

Sanity check:

$ SELECT group_id , COUNT ( * ) FROM sample_data GROUP BY group_id ORDER BY group_id; group_id | COUNT ----------+------- 0 | 4935 1 | 10131 2 | 10083 3 | 10036 4 | 9828 5 | 10073 6 | 9912 7 | 10027 8 | 10130 9 | 9816 10 | 5029 ( 11 ROWS ) $ SELECT group_id , COUNT ( * ) FROM data_from_origin GROUP BY group_id ORDER BY group_id; group_id | COUNT ----------+------- 0 | 4935 1 | 10131 2 | 10083 3 | 10036 4 | 9828 5 | 10073 6 | 9912 7 | 10027 8 | 10130 9 | 9816 10 | 5029 ( 11 ROWS )

OK. Data sets are the same. Let's see explain analyze for the second query:

$ EXPLAIN ( analyze ON , verbose ON ) SELECT group_id , COUNT ( * ) FROM data_from_origin GROUP BY group_id ORDER BY group_id; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ GroupAggregate ( cost = 100.00 .. 222 . 22 ROWS = 200 width = 12 ) ( actual TIME = 55.823 .. 119 . 840 ROWS = 11 loops = 1 ) Output: group_id , COUNT ( * ) GROUP KEY : data_from_origin . group_id -> FOREIGN Scan ON public . data_from_origin ( cost = 100.00 .. 205 . 60 ROWS = 2925 width = 4 ) ( actual TIME = 51.703 .. 110 . 939 ROWS = 100000 loops = 1 ) Output: id , group_id Remote SQL : SELECT group_id FROM public . sample_data ORDER BY group_id ASC NULLS LAST Planning TIME : 0.056 ms Execution TIME : 120.534 ms ( 8 ROWS )

Now, I did all of this also on pg 10. But the final explain analyze looks different:

$ EXPLAIN ( analyze ON , verbose ON ) SELECT group_id , COUNT ( * ) FROM data_from_origin GROUP BY group_id ORDER BY group_id; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Sort ( cost = 167.52 .. 168 . 02 ROWS = 200 width = 12 ) ( actual TIME = 21.606 .. 21 . 607 ROWS = 11 loops = 1 ) Output: group_id , ( COUNT ( * ) ) Sort KEY : data_from_origin . group_id Sort Method: quicksort Memory: 25kB -> FOREIGN Scan ( cost = 114.62 .. 159 . 88 ROWS = 200 width = 12 ) ( actual TIME = 21.596 .. 21 . 597 ROWS = 11 loops = 1 ) Output: group_id , ( COUNT ( * ) ) Relations: Aggregate ON ( public . data_from_origin ) Remote SQL : SELECT group_id , COUNT ( * ) FROM public . sample_data GROUP BY group_id Planning TIME : 0.073 ms Execution TIME : 21.900 ms ( 10 ROWS )

Please note that “Foreign Scan" in the first explain – it shows actual…rows=100000, but in second explain, it shows actual…rows=11. Given that data has to be transferred over the network (in my case over unix socket on the same machine) it's clear win, which can be also seen in the execution time for both queries – 120ms vs. 21ms. Pretty cool.

I would assume that drivers for foreign servers will have to be modified to support handling aggregates, but it is definitely cool anyway.