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

Commitb690e5f

Browse files
committed
Stabilize postgres_fdw tests on 32-bit machines
cac169d adjusted DEFAULT_FDW_TUPLE_COST and that seems to have causeda test to become unstable on 32-bit machines.4b14e18 tried to fix this as originally the plan was flipping betweena Nested Loop and Hash Join. That commit forced the Nested Loop, butthere's still flexibility to push or not push the sort to the remoteserver and 32-bit seems to prefer to push and on 64-bit, the costsprefer not to.Here let's just turn off enable_sort to significantly encourage the sortto take place on the remote server.Reported-by: Michael Paquier, Richard GuoDiscussion:https://postgr.es/m/ZUM2IhA8X2lrG50K@paquier.xyz
1 parent0bc726d commitb690e5f

File tree

2 files changed

+16
-15
lines changed

2 files changed

+16
-15
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 14 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -4019,27 +4019,25 @@ EXECUTE st1(101, 101);
40194019
(1 row)
40204020

40214021
SET enable_hashjoin TO off;
4022+
SET enable_sort TO off;
40224023
-- subquery using stable function (can't be sent to remote)
40234024
PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c4) = '1970-01-17'::date) ORDER BY c1;
40244025
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st2(10, 20);
4025-
QUERY PLAN
4026-
----------------------------------------------------------------------------------------------------------
4027-
Sort
4026+
QUERY PLAN
4027+
----------------------------------------------------------------------------------------------------------------------------------
4028+
Nested Loop Semi Join
40284029
Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
4029-
Sort Key:t1.c1
4030-
->Nested Loop Semi Join
4030+
Join Filter: (t2.c3 =t1.c3)
4031+
->Foreign Scan on public.ft1 t1
40314032
Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
4032-
Join Filter: (t2.c3 = t1.c3)
4033-
-> Foreign Scan on public.ft1 t1
4034-
Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
4035-
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 20))
4036-
-> Materialize
4033+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 20)) ORDER BY "C 1" ASC NULLS LAST
4034+
-> Materialize
4035+
Output: t2.c3
4036+
-> Foreign Scan on public.ft2 t2
40374037
Output: t2.c3
4038-
-> Foreign Scan on public.ft2 t2
4039-
Output: t2.c3
4040-
Filter: (date(t2.c4) = '01-17-1970'::date)
4041-
Remote SQL: SELECT c3, c4 FROM "S 1"."T 1" WHERE (("C 1" > 10))
4042-
(15 rows)
4038+
Filter: (date(t2.c4) = '01-17-1970'::date)
4039+
Remote SQL: SELECT c3, c4 FROM "S 1"."T 1" WHERE (("C 1" > 10))
4040+
(12 rows)
40434041

40444042
EXECUTE st2(10, 20);
40454043
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
@@ -4054,6 +4052,7 @@ EXECUTE st2(101, 121);
40544052
(1 row)
40554053

40564054
RESET enable_hashjoin;
4055+
RESET enable_sort;
40574056
-- subquery using immutable function (can be sent to remote)
40584057
PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c5) = '1970-01-17'::date) ORDER BY c1;
40594058
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20);

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1123,12 +1123,14 @@ EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1, 2);
11231123
EXECUTE st1(1,1);
11241124
EXECUTE st1(101,101);
11251125
SET enable_hashjoin TO off;
1126+
SET enable_sort TO off;
11261127
-- subquery using stable function (can't be sent to remote)
11271128
PREPARE st2(int)ASSELECT*FROM ft1 t1WHEREt1.c1< $2ANDt1.c3IN (SELECT c3FROM ft2 t2WHERE c1> $1ANDdate(c4)='1970-01-17'::date)ORDER BY c1;
11281129
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st2(10,20);
11291130
EXECUTE st2(10,20);
11301131
EXECUTE st2(101,121);
11311132
RESET enable_hashjoin;
1133+
RESET enable_sort;
11321134
-- subquery using immutable function (can be sent to remote)
11331135
PREPARE st3(int)ASSELECT*FROM ft1 t1WHEREt1.c1< $2ANDt1.c3IN (SELECT c3FROM ft2 t2WHERE c1> $1ANDdate(c5)='1970-01-17'::date)ORDER BY c1;
11341136
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10,20);

0 commit comments

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