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?

klin klin · Accepted Answer · 2017-05-10T14:54:27

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;

Postgres Foreign Data Wrapper Aggregate function pushdown

2 Answers