postgres_fdw and postgres version upgrade

Question

Case explained

I'm facing difficulties concerning performance of queries using a Postgres 9.5.4 database set up on a different server connecting via Foreign Data Wrapper to another instance of Postgres 9.5.4.

I've already tried enabling use_remote_estimate setting via ALTER SERVER but this seems not to fix it as much as I thought it would.

Regular queries with joins tend to take>3X longer when issued via FDW database.

I've investigated data transfers, gave more RAM and CPU to utilize on FDW but with no success. Anyway it seems like it's not even using that much resources.

Possible improvement

I've read about join and aggregate pushdowns for FDW introduced in Postgres 9.6 which seems great and should improve response time, but I'm unsure whether or not it's enough to just upgrade the FDW Postgres server. Do I need to upgrade the main server I'm connecting to as well?

Example

Simple example of a query that I think could benefit a lot from upgrading Postgres to 9.6 would be:

db=# EXPLAIN VERBOSE select max(add_date) from mytable;
 QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate (cost=2084783.99..2084784.00 rows=1 width=8)
 Output: max(add_date)
 -> Foreign Scan on public.mytable (cost=100.00..1963010.07 rows=48709569 width=8)
 Output: id, id_a, id_b, id_c, allowed, mod_date, add_date, idv
 Remote SQL: SELECT add_date FROM public.mytable

There is no index, and won't be, on production, but it takes around 10s to complete on master server (50M rows), while on FDW server it goes as high as 250s, which I believe is due to the fact that remote SQL executed is sending all the data to the server in order to allow it to aggregate.

Back to question

Again, to benefit from what 9.6 has to offer do I only need to upgrade Postgres instance on server that has the FDW Server and foreign tables, or also the master server?

• Kamil Gosciminski: I've tested it and seems that upgrading server that uses FDW mechanism to Postgres 9.6 does not apply aggregation pushdown.

Answer 1

Join and aggregate pushdowns are supposed to go live on Postgres 10, not Postgres 9.6 which is why remote queries are not being sent the way I expected it to.