Re: Join push down on FDW partitions

Hi.

I have not found any in-depth examples regarding join push down and the support for FDWs but from what I have read my
test of it should work but is does not. I have tested on both PG 11 and PG 13

I have two remote shards one on server cloud1 and the other cloud2

I do the following
 

CREATE SCHEMA cloud1;

CREATE SERVER cloud1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'x.y.z.w', port '5436', dbname 'mydb');

CREATE USER MAPPING FOR user1 SERVER cloud1 OPTIONS (user 'user1', password 'igP3tasdsad');

IMPORT FOREIGN SCHEMA public LIMIT TO (psg_240 ,pm) FROM SERVER cloud1 INTO cloud1;

 

CREATE SCHEMA cloud2;

CREATE SERVER cloud2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'x.y.z.w', port '5436', dbname 'mydb');

CREATE USER MAPPING FOR agama SERVER cloud2 OPTIONS (user 'agama', password 'igP3tasdsad');

IMPORT FOREIGN SCHEMA public LIMIT TO (psg_240, pm) FROM SERVER cloud2 INTO cloud2;

 

DROP TABLE IF EXISTS pm;

CREATE TABLE pm (LIKE cloud1.pm INCLUDING ALL) PARTITION BY HASH(pid);

ALTER TABLE pm ATTACH PARTITION cloud1.pm FOR VALUES WITH (modulus 2, remainder 0);

ALTER TABLE pm ATTACH PARTITION cloud2.pm FOR VALUES WITH (modulus 2, remainder 1);

 

DROP TABLE IF EXISTS psg_240;

CREATE TABLE psg_240 (LIKE cloud1.psg_240 INCLUDING ALL) PARTITION BY HASH(pid);

ALTER TABLE psg_240 ATTACH PARTITION cloud1.psg_240 FOR VALUES WITH (modulus 2, remainder 0);

ALTER TABLE psg_240 ATTACH PARTITION cloud2.psg_240 FOR VALUES WITH (modulus 2, remainder 1);

Then the query 

explain verbose select pm.pid from pm left join psg_240 on (psg_240.pid = pm.pid) where param1='fooBar';                                                                          

Gives

 

                                                  QUERY PLAN                                                  

----------------------------------------------------------------------------------------------------------------

 Hash Right Join  (cost=384.97..710.03 rows=666 width=8)

   Output: pm.pid

   Hash Cond: (psg.pid = pm.pid)

   ->  Append  (cost=100.00..399.20 rows=5120 width=8)

         ->  Foreign Scan on cloud1.psg_240 psg_1  (cost=100.00..186.80 rows=2560 width=8)

               Output: psg_1.pid

               Remote SQL: SELECT pid FROM public.psg_240

         ->  Foreign Scan on cloud2.psg_240 psg_2  (cost=100.00..186.80 rows=2560 width=8)

               Output: psg_2.probe_id

               Remote SQL: SELECT pid FROM public.psg_240

   ->  Hash  (cost=284.65..284.65 rows=26 width=8)

         Output: pm.pid

         ->  Append  (cost=100.00..284.65 rows=26 width=8)

               ->  Foreign Scan on cloud1.pm pm_1  (cost=100.00..142.26 rows=13 width=8)

                     Output: pm_1.pid

                     Remote SQL: SELECT pid FROM public.probe_metadata WHERE ((param1 = 'fooBar'::text))

               ->  Foreign Scan on cloud2.pm pm_2  (cost=100.00..142.26 rows=13 width=8)

                     Output: pm_2.pid

                     Remote SQL: SELECT pid FROM public.pm WHERE ((param1 = 'fooBar'::text))

(19 rows)

I would expect that the join where push:ed down to the shards.

Does Postgres support this kind of usecase? If so what do I do wrong here?

best regards,
  Anders