From: | Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Push down more UPDATEs/DELETEs in postgres_fdw |
Date: | 2016-09-06 12:45:34 |
Message-ID: | b9cee735-62f8-6c07-7528-6364ce9347d0@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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-c2758dbe0b38@lab.ntt.co.jp
Attachment | Content-Type | Size |
---|---|---|
postgres-fdw-more-update-pushdown-WIP.patch | binary/octet-stream | 42.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Kyotaro HORIGUCHI | 2016-09-06 13:00:26 | Re: IF (NOT) EXISTS in psql-completion |
Previous Message | Michael Paquier | 2016-09-06 12:36:49 | Re: pgsql: Add putenv support for msvcrt from Visual Studio 2013 |