Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ek5s1-0008Qn-CD for pgsql-hackers@arkaria.postgresql.org; Fri, 09 Feb 2018 10:25:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ek5s0-0003xb-C8 for pgsql-hackers@arkaria.postgresql.org; Fri, 09 Feb 2018 10:25:28 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ek5s0-0003xJ-2I for pgsql-hackers@lists.postgresql.org; Fri, 09 Feb 2018 10:25:28 +0000 Received: from tama500.ecl.ntt.co.jp ([129.60.39.148]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ek5ru-0001QY-JK for pgsql-hackers@postgresql.org; Fri, 09 Feb 2018 10:25:25 +0000 Received: from vc2.ecl.ntt.co.jp (vc2.ecl.ntt.co.jp [129.60.86.154]) by tama500.ecl.ntt.co.jp (8.13.8/8.13.8) with ESMTP id w19APHiZ005462; Fri, 9 Feb 2018 19:25:17 +0900 Received: from vc2.ecl.ntt.co.jp (localhost [127.0.0.1]) by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id 0F0E96392F5; Fri, 9 Feb 2018 19:25:17 +0900 (JST) Received: from jcms-pop21.ecl.ntt.co.jp (jcms-pop21.ecl.ntt.co.jp [129.60.87.134]) by vc2.ecl.ntt.co.jp (Postfix) with ESMTP id 0376863929D; Fri, 9 Feb 2018 19:25:17 +0900 (JST) Received: from [IPv6:::1] (unknown [129.60.241.75]) by jcms-pop21.ecl.ntt.co.jp (Postfix) with ESMTPSA id F22F94007DC; Fri, 9 Feb 2018 19:25:16 +0900 (JST) Message-ID: <5A7D76CF.8080601@lab.ntt.co.jp> Date: Fri, 09 Feb 2018 19:24:15 +0900 From: Etsuro Fujita User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20120327 Thunderbird/11.0.1 MIME-Version: 1.0 Subject: Re: postgres_fdw: perform UPDATE/DELETE .. RETURNING on a join directly References: <5A438A45.2000909@lab.ntt.co.jp> <5A57193A.2080003@lab.ntt.co.jp> <6166.1518044513@sss.pgh.pa.us> <5A7BE8E6.200@lab.ntt.co.jp> <28606.1518105934@sss.pgh.pa.us> <5A7CFDDA.8050906@lab.ntt.co.jp> In-Reply-To: <5A7CFDDA.8050906@lab.ntt.co.jp> Content-Type: multipart/mixed; boundary="------------070504060907050102070007" X-CC-Mail-RelayStamp: 1 To: Robert Haas Cc: Tom Lane , Pg Hackers , Rushabh Lathia , Michael Paquier , Ashutosh Bapat X-TM-AS-MML: disable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------070504060907050102070007 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit (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 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 --------------070504060907050102070007 Content-Type: text/x-diff; name="postgres-fdw-regress.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="postgres-fdw-regress.patch" *** 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 --------------070504060907050102070007--