<div><div>Hi Anders,</div><div> </div><div>I haven't tested with hash partitioning. But, I think enabling <em>enable_partitionwise_join</em> can help you. Could you please try to enable and check its query plan?</div></div><div> </div><div>set enable_partitionwise_join to on;</div><div><div>explain verbose select pm.pid from pm left join psg_240 on (psg_240.pid = pm.pid) where param1='fooBar'; </div><div> </div><div>Note: It will enable this option only for your session. You need to update your postgresql.conf and reload it to enable for everybody.</div><div><div>* https://www.postgresql.org/docs/13/runtime-config-query.html</div></div></div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div>02.12.2020, 17:35, "Anders Svensson" <anders(dot)svensson(at)agama(dot)tv>:</div><blockquote><div><div style="color:rgb( 0 , 0 , 0 );font-family:'calibri' , 'arial' , 'helvetica' , sans-serif;font-size:12pt">Hi.<br /><br />I have not found any in-depth examples regarding join push down and the support for FDWs but from what I have read my<br />test of it should work but is does not. I have tested on both PG 11 and PG 13<br /><br />I have two remote shards one on server cloud1 and the other cloud2<br /><br />I do the following<br /> <div>CREATE SCHEMA cloud1;</div><div>CREATE SERVER cloud1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'x.y.z.w', port '5436', dbname 'mydb');</div><div>CREATE USER MAPPING FOR user1 SERVER cloud1 OPTIONS (user 'user1', password 'igP3tasdsad');</div><div>IMPORT FOREIGN SCHEMA public LIMIT TO (psg_240 ,pm) FROM SERVER cloud1 INTO cloud1;</div><div> </div><div>CREATE SCHEMA cloud2;</div><div>CREATE SERVER cloud2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'x.y.z.w', port '5436', dbname 'mydb');</div><div>CREATE USER MAPPING FOR agama SERVER cloud2 OPTIONS (user 'agama', password 'igP3t<span style="background-color:#ffffff">asdsad</span>');</div><div>IMPORT FOREIGN SCHEMA public LIMIT TO (psg_240, pm) FROM SERVER cloud2 INTO cloud2;</div><div> </div><div>DROP TABLE IF EXISTS pm;</div><div>CREATE TABLE pm (LIKE cloud1.pm INCLUDING ALL) PARTITION BY HASH(pid);</div><div>ALTER TABLE pm ATTACH PARTITION cloud1.pm FOR VALUES WITH (modulus 2, remainder 0);</div><div>ALTER TABLE pm ATTACH PARTITION cloud2.pm FOR VALUES WITH (modulus 2, remainder 1);</div><div> </div><div>DROP TABLE IF EXISTS psg_240;</div><div>CREATE TABLE psg_240 (LIKE cloud1.psg_240 INCLUDING ALL) PARTITION BY HASH(pid);</div><div>ALTER TABLE psg_240 ATTACH PARTITION cloud1.psg_240 FOR VALUES WITH (modulus 2, remainder 0);</div><div>ALTER TABLE psg_240 ATTACH PARTITION cloud2.psg_240 FOR VALUES WITH (modulus 2, remainder 1);</div><br />Then the query <br /><br />explain verbose select pm.pid from pm left join psg_240 on (psg_240.pid = pm.pid) where param1='fooBar'; <br /><br />Gives</div><div style="color:rgb( 0 , 0 , 0 );font-family:'calibri' , 'arial' , 'helvetica' , sans-serif;font-size:12pt"> </div><div style="color:rgb( 0 , 0 , 0 );font-family:'calibri' , 'arial' , 'helvetica' , sans-serif;font-size:12pt"> QUERY PLAN <div>----------------------------------------------------------------------------------------------------------------</div><div> Hash Right Join (cost=384.97..710.03 rows=666 width=8)</div><div> Output: pm.pid</div><div> Hash Cond: (psg.pid = pm.pid)</div><div> -> Append (cost=100.00..399.20 rows=5120 width=8)</div><div> -> Foreign Scan on cloud1.psg_240 psg_1 (cost=100.00..186.80 rows=2560 width=8)</div><div> Output: psg_1.pid</div><div> Remote SQL: SELECT pid FROM public.psg_240</div><div> -> Foreign Scan on cloud2.psg_240 psg_2 (cost=100.00..186.80 rows=2560 width=8)</div><div> Output: psg_2.probe_id</div><div> Remote SQL: SELECT pid FROM public.psg_240</div><div> -> Hash (cost=284.65..284.65 rows=26 width=8)</div><div> Output: pm.pid</div><div> -> Append (cost=100.00..284.65 rows=26 width=8)</div><div> -> Foreign Scan on cloud1.pm pm_1 (cost=100.00..142.26 rows=13 width=8)</div><div> Output: pm_1.pid</div><div> Remote SQL: SELECT pid FROM public.probe_metadata WHERE ((param1 = 'fooBar'::text))</div><div> -> Foreign Scan on cloud2.pm pm_2 (cost=100.00..142.26 rows=13 width=8)</div><div> Output: pm_2.pid</div><div> Remote SQL: SELECT pid FROM public.pm WHERE ((param1 = 'fooBar'::text))</div><div>(19 rows)</div><br /><br />I would expect that the join where push:ed down to the shards.<br /><br />Does Postgres support this kind of usecase? If so what do I do wrong here?<br /><br />best regards,<br /> Anders</div></div></blockquote>