Hi,Attached is a WIP patch extending the postgres_fdw DML pushdown in 9.6 so that it can perform an update/delete on a join remotely. An example is shown below:
* without the patch: postgres=# explain verbose delete from ft1 using ft2 where ft1.a = ft2.a;QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------- Delete on public.ft1 (cost=100.00..102.04 rows=1 width=38) Remote SQL: DELETE FROM public.t1 WHERE ctid = $1 -> Foreign Scan (cost=100.00..102.04 rows=1 width=38) Output: ft1.ctid, ft2.* Relations: (public.ft1) INNER JOIN (public.ft2)Remote SQL: SELECT r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.a, r2.b) END FROM (public.t1 r1 INNER JOIN public.t2 r2 ON (((r1.a =
r2.a)))) FOR UPDATE OF r1 -> Nested Loop (cost=200.00..202.07 rows=1 width=38) Output: ft1.ctid, ft2.* Join Filter: (ft1.a = ft2.a)-> Foreign Scan on public.ft1 (cost=100.00..101.03 rows=1 width=10)
Output: ft1.ctid, ft1.a Remote SQL: SELECT a, ctid FROM public.t1 FOR UPDATE-> Foreign Scan on public.ft2 (cost=100.00..101.03 rows=1 width=36)
Output: ft2.*, ft2.a Remote SQL: SELECT a, b FROM public.t2 (15 rows) * with the patch: postgres=# explain verbose delete from ft1 using ft2 where ft1.a = ft2.a; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Delete on public.ft1 (cost=100.00..102.04 rows=1 width=38) -> Foreign Delete (cost=100.00..102.04 rows=1 width=38)Remote SQL: DELETE FROM public.t1 r1 USING (SELECT ROW(a, b), a FROM public.t2) ss1(c1, c2) WHERE ((r1.a = ss1.c2))
(3 rows)The WIP patch has been created on top of the join pushdown patch [1]. So, for testing, please apply the patch in [1] first.
I'll add this to the the November commitfest. Best regards, Etsuro Fujita[1] https://www.postgresql.org/message-id/1688885b-5fb1-8bfa-b1b8-c2758dbe0...@lab.ntt.co.jp
postgres-fdw-more-update-pushdown-WIP.patch
Description: binary/octet-stream
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers