Are subqueries pushed down to a remote data source by postgres_fdw?

Question

Given this query:

select *
from fdw_schema.customer c
where exists
( select *
 from fdw_schema.purchase p
 where c.id = p.customer_id
 and p.purchase_date > now() - interval '30 days');

Will the full query get pushed down by postgres_fdw to the remote server?

My alternative would be:

select distinct c.*
from fdw_schema.customer c
join fdw_schema.purchase p on c.id = p.customer_id
where p.purchase_date > now() - interval '30 days';

I'm looking at pg_stat_activity on the foreign server, but all I can see is

FETCH 100 FROM c2

• dcbeckman: It might depend on what version of Postgres you are using. I know that earlier versions did not push much of the query to the foreign server, which led to long query times and generally unusable performance. I would try both and see which has better performance.

Answer 1

explain (verbose) will explain this question. In verbose mode, explain will add a Remote SQL string with the exact query it plans to execute on the remote database.

Something like such plan:

EXPLAIN (VERBOSE, COSTS OFF)
 SELECT * FROM "S 1"."T 1" a, ft2 b WHERE a."C 1" = 47 AND b.c1 = a.c2;
 QUERY PLAN 
-------------------------------------------------------------------------------------------------------------
 Nested Loop
 Output: a."C 1", a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8, b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
 -> Index Scan using t1_pkey on "S 1"."T 1" a
 Output: a."C 1", a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, a.c8
 Index Cond: (a."C 1" = 47)
 -> Foreign Scan on public.ft2 b
 Output: b.c1, b.c2, b.c3, b.c4, b.c5, b.c6, b.c7, b.c8
 Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((1ドル::integer = "C 1"))

This is from postgresql test suite, which is why costs off was used here.