1

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

1
  • 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.
    – dcbeckman
    Jan 15, 2022 at 2:06

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.