Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5843659

Browse files
committed
Stabilize test case added by commitf61db90.
Buildfarm members ayu and tern have sometimes shown a differentplan than expected for this query. I'd been unable to reproducethat before today, but I finally realized what is happening.If there is a concurrent open transaction (probably an autovacuumrun in the buildfarm, but this can also be arranged manually),then the index entries for the rows removed by the DELETE a fewlines up are not killed promptly, causing a change in the planner'sestimate of the extremal value of ft2.c1, which moves the rowcountestimate for "c1 > 1100" by enough to change the join plan fromnestloop to hash.To fix, change the query condition to "c1 > 1000", causing thehash plan to be preferred whether or not a concurrent opentransaction exists. Since this UPDATE is tailored to be a no-op,nothing else changes.Report:https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=ayu&dt=2021-06-09%2022%3A45%3A48Report:https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=ayu&dt=2021-06-13%2022%3A38%3A18Report:https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=tern&dt=2021-06-20%2004%3A55%3A36
1 parent6991e77 commit5843659

File tree

2 files changed

+13
-10
lines changed

2 files changed

+13
-10
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 11 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -5535,27 +5535,30 @@ UPDATE ft2 AS target SET (c2) = (
55355535
-- but a SET clause that can't be
55365536
EXPLAIN (VERBOSE, COSTS OFF)
55375537
UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END
5538-
FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 >1100;
5538+
FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 >1000;
55395539
QUERY PLAN
55405540
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
55415541
Update on public.ft2 d
55425542
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2 WHERE ctid = $1
55435543
-> Foreign Scan
55445544
Output: CASE WHEN (random() >= '0'::double precision) THEN d.c2 ELSE 0 END, d.ctid, d.*, t.*
55455545
Relations: (public.ft2 d) INNER JOIN (public.ft2 t)
5546-
Remote SQL: SELECT r1.c2, r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")) AND ((r1."C 1" >1100)))) FOR UPDATE OF r1
5547-
->Nested Loop
5546+
Remote SQL: SELECT r1.c2, r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")) AND ((r1."C 1" >1000)))) FOR UPDATE OF r1
5547+
->Hash Join
55485548
Output: d.c2, d.ctid, d.*, t.*
5549+
Hash Cond: (d.c1 = t.c1)
55495550
-> Foreign Scan on public.ft2 d
55505551
Output: d.c2, d.ctid, d.*, d.c1
5551-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" >1100)) ORDER BY "C 1" ASC NULLS LAST FOR UPDATE
5552-
->Foreign Scan on public.ft2 t
5552+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" >1000)) ORDER BY "C 1" ASC NULLS LAST FOR UPDATE
5553+
->Hash
55535554
Output: t.*, t.c1
5554-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (($1::integer = "C 1"))
5555-
(14 rows)
5555+
-> Foreign Scan on public.ft2 t
5556+
Output: t.*, t.c1
5557+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
5558+
(17 rows)
55565559

55575560
UPDATE ft2 d SET c2 = CASE WHEN random() >= 0 THEN d.c2 ELSE 0 END
5558-
FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 >1100;
5561+
FROM ft2 AS t WHERE d.c1 = t.c1 AND d.c1 >1000;
55595562
-- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing
55605563
-- user-defined operators/functions
55615564
ALTER SERVER loopback OPTIONS (DROP extensions);

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1259,9 +1259,9 @@ UPDATE ft2 AS target SET (c2) = (
12591259
-- but a SET clause that can't be
12601260
EXPLAIN (VERBOSE, COSTS OFF)
12611261
UPDATE ft2 dSET c2= CASE WHEN random()>=0 THENd.c2 ELSE0 END
1262-
FROM ft2AS tWHEREd.c1=t.c1ANDd.c1>1100;
1262+
FROM ft2AS tWHEREd.c1=t.c1ANDd.c1>1000;
12631263
UPDATE ft2 dSET c2= CASE WHEN random()>=0 THENd.c2 ELSE0 END
1264-
FROM ft2AS tWHEREd.c1=t.c1ANDd.c1>1100;
1264+
FROM ft2AS tWHEREd.c1=t.c1ANDd.c1>1000;
12651265

12661266
-- Test UPDATE/DELETE with WHERE or JOIN/ON conditions containing
12671267
-- user-defined operators/functions

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp