Join push down on FDW partitions

From: Anders Svensson <anders(dot)svensson(at)agama(dot)tv>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Join push down on FDW partitions
Date: 2020-12-02 13:30:31
Message-ID: HE1P193MB0170A10AB662EF6E2E49A32493F30@HE1P193MB0170.EURP193.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Samed YILDIRIM 2020-12-02 14:44:43 Re: Join push down on FDW partitions
Previous Message Alvaro Herrera 2020-11-27 17:03:03 Re: CTE materialized/not materialized