Postgres Foreign Data Wrapper Aggregate function pushdown

Question

Situation:

• I table a table (foreign_table) in a postgres database (db1)
• I create a foreign date wrapper to foreign_table in a different postgres database (db2) using postgres_fdw
• I then perform a "select count(*) from foreign_table" from db2
• This query returns the entire contents of foreign_table to db1 in batches of 100 rows (set by fetch_size).

Issue:

• This causes the query to be incredibly slow as the foreign_table has ~100 million rows.

My question:

Is it possible to "push down" this aggregate function so that the count(*) is performed on the remote postgres database?

Answer 1

If you do not want to wait for Postgres 10, use this workaround:

Create a view in the foreign database:

-- in db1:
create view count_my_table as (
    select count(*) 
    from foreign_table);

Create a foreign table for the view in the local database:

-- in db2:
create foreign table count_my_table (
    count bigint
)
server foreign_server
options (table_name 'count_my_table');

select count 
from count_my_table;

Answer 2

I'm going to go ahead and answer my own question.

Aggregate pushdown for foreign tables will be coming in postgres 10.

See https://www.enterprisedb.com/blog/postgresql-aggregate-push-down-postgresfdw for details.