pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Rushabh Lathia <rushabh.lathia@gmail.com>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Subject: Re: postgres_fdw: perform UPDATE/DELETE .. RETURNING on a join directly
Date: Fri, 09 Feb 2018 19:24:15 +0900
Message-ID: <5A7D76CF.8080601@lab.ntt.co.jp> (raw )
In-Reply-To: <5A7CFDDA.8050906@lab.ntt.co.jp >
References: <5A438A45.2000909@lab.ntt.co.jp >
<5A57193A.2080003@lab.ntt.co.jp >
<CA+TgmoYef9czAA1gJeqh4v47Tv=dqOp4EfdU6bV8EYkOdCwvUw@mail.gmail.com >
<6166.1518044513@sss.pgh.pa.us >
<CA+TgmoaxGGZyUAWQMz7c-3V85TV5-w2wmvpOYrLnALi2d9Z-dQ@mail.gmail.com >
<5A7BE8E6.200@lab.ntt.co.jp >
<28606.1518105934@sss.pgh.pa.us >
<CA+Tgmobf+AXEwO4r0oCkvNhkaVnxPgoJaApoX+rAi0G7-N-Dyg@mail.gmail.com >
<5A7CFDDA.8050906@lab.ntt.co.jp >
(2018/02/09 10:48), Etsuro Fujita wrote:
> (2018/02/09 4:32), Robert Haas wrote:
>> On Thu, Feb 8, 2018 at 11:05 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
>>> there's still an intermittent issue. I ran "make installcheck" in
>>> contrib/postgres_fdw in a loop, and got a similar failure on the
>>> 47th try --- my result duplicates the second plan change shown by
>>> rhinoceros , but not the first one. I speculate that the plan change
>>> is a result of autovacuum kicking in partway through the run.
>
> Will look into this.
I tried to reproduce that in my environment, but I couldn't. On
reflection I think an easy and reliable way to address that concern is
to use local stats on foreign tables. Attached is a patch for that.
Best regards,
Etsuro Fujita
Attachments:
postgres-fdw-regress.patch (text/x-diff, 8.0K) download
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 4244,4249 **** explain (verbose, costs off) select * from ft3 f, loct3 l
--- 4244,4253 ----
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
+ -- Autovacuum on the remote side might affect remote estimates,
+ -- so use local stats on ft2 as well
+ ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate 'false');
+ ANALYZE ft2;
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
QUERY PLAN
***************
*** 5520,5551 **** UPDATE ft2 SET c3 = 'baz'
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1
RETURNING ft2.*, ft4.*, ft5.*; -- can't be pushed down
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
! -> Nested Loop
Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
! Join Filter: (ft2.c2 === ft4.c1)
! -> Foreign Scan on public.ft2
! Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid
! Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan
! Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3
! Relations: (public.ft4) INNER JOIN (public.ft5)
! Remote SQL: SELECT CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r2.c1, r2.c2, r2.c3, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r3.c1, r3.c2, r3.c3 FROM ("S 1"."T 3" r2 INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1))))
! -> Hash Join
! Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3
! Hash Cond: (ft4.c1 = ft5.c1)
-> Foreign Scan on public.ft4
Output: ft4.*, ft4.c1, ft4.c2, ft4.c3
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
! -> Hash
! Output: ft5.*, ft5.c1, ft5.c2, ft5.c3
! -> Foreign Scan on public.ft5
! Output: ft5.*, ft5.c1, ft5.c2, ft5.c3
! Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
(24 rows)
UPDATE ft2 SET c3 = 'baz'
--- 5524,5555 ----
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1
RETURNING ft2.*, ft4.*, ft5.*; -- can't be pushed down
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
! -> Hash Join
Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
! Hash Cond: (ft4.c1 = ft5.c1)
-> Foreign Scan
! Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft4.c1, ft4.c2, ft4.c3
! Filter: (ft2.c2 === ft4.c1)
! Relations: (public.ft2) INNER JOIN (public.ft4)
! Remote SQL: SELECT r1."C 1", r1.c2, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r2.c1, r2.c2, r2.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1."C 1" > 2000)))) FOR UPDATE OF r1
! -> Nested Loop
! Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft4.c1, ft4.c2, ft4.c3
! -> Foreign Scan on public.ft2
! Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid
! Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan on public.ft4
Output: ft4.*, ft4.c1, ft4.c2, ft4.c3
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
! -> Hash
! Output: ft5.*, ft5.c1, ft5.c2, ft5.c3
! -> Foreign Scan on public.ft5
! Output: ft5.*, ft5.c1, ft5.c2, ft5.c3
! Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
(24 rows)
UPDATE ft2 SET c3 = 'baz'
***************
*** 5999,6004 **** select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
--- 6003,6011 ----
407 | 100
(13 rows)
+ -- Go back to use remote-estimate mode on ft2
+ VACUUM ANALYZE "S 1"."T 1";
+ ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate 'true');
-- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs
-- FIRST behavior here.
-- ORDER BY DESC NULLS LAST options
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 1068,1073 **** explain (verbose, costs off) select * from ft3 f, loct3 l
--- 1068,1078 ----
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
+ -- Autovacuum on the remote side might affect remote estimates,
+ -- so use local stats on ft2 as well
+ ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate 'false');
+ ANALYZE ft2;
+
EXPLAIN (verbose, costs off)
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
***************
*** 1208,1213 **** commit;
--- 1213,1222 ----
select c2, count(*) from ft2 where c2 < 500 group by 1 order by 1;
select c2, count(*) from "S 1"."T 1" where c2 < 500 group by 1 order by 1;
+ -- Go back to use remote-estimate mode on ft2
+ VACUUM ANALYZE "S 1"."T 1";
+ ALTER FOREIGN TABLE ft2 OPTIONS (SET use_remote_estimate 'true');
+
-- Above DMLs add data with c6 as NULL in ft1, so test ORDER BY NULLS LAST and NULLs
-- FIRST behavior here.
-- ORDER BY DESC NULLS LAST options
view thread (33+ messages) latest in thread
Message-ID: <5A7D76CF.8080601@lab.ntt.co.jp>
Permalink: ../5A7D76CF.8080601@lab.ntt.co.jp/
Also on: postgresql.org/message-id/5A7D76CF.8080601@lab.ntt.co.jp
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: fujita.etsuro@lab.ntt.co.jp, robertmhaas@gmail.com, tgl@sss.pgh.pa.us, rushabh.lathia@gmail.com, michael.paquier@gmail.com, ashutosh.bapat@enterprisedb.com
Subject: Re: postgres_fdw: perform UPDATE/DELETE .. RETURNING on a join directly
In-Reply-To: <5A7D76CF.8080601@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox