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

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.