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

Commit24d08f3

Browse files
committed
Fix mark-and-restore-skipping test case to not be a self-join.
There isn't any good reason for this test to be a self-join ratherthan a join between separate tables, except that it saved a coupleof SQL commands for setup. A proposed patch to optimize awayself-joins breaks the test, so adjust it to avoid that happening.Discussion:https://postgr.es/m/64486b0b-0404-e39e-322d-0801154901f3@postgrespro.ru
1 parent0c7d537 commit24d08f3

File tree

2 files changed

+22
-13
lines changed

2 files changed

+22
-13
lines changed

‎src/test/regress/expected/join.out

Lines changed: 12 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -5932,22 +5932,26 @@ left join j2 on j1.id1 = j2.id1 where j1.id2 = 1;
59325932
set enable_nestloop to 0;
59335933
set enable_hashjoin to 0;
59345934
set enable_sort to 0;
5935-
-- createan indexthat will be preferred over thePK to perform the join
5935+
-- createindexesthat will be preferred over thePKs to perform the join
59365936
create index j1_id1_idx on j1 (id1) where id1 % 1000 = 1;
5937-
explain (costs off) select * from j1 j1
5938-
inner join j1 j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
5937+
create index j2_id1_idx on j2 (id1) where id1 % 1000 = 1;
5938+
-- need an additional row in j2, if we want j2_id1_idx to be preferred
5939+
insert into j2 values(1,2);
5940+
analyze j2;
5941+
explain (costs off) select * from j1
5942+
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
59395943
where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1;
5940-
QUERY PLAN
5941-
--------------------------------------------
5944+
QUERY PLAN
5945+
-----------------------------------------
59425946
Merge Join
59435947
Merge Cond: (j1.id1 = j2.id1)
59445948
Join Filter: (j1.id2 = j2.id2)
59455949
-> Index Scan using j1_id1_idx on j1
5946-
-> Index Scan usingj1_id1_idx on j1 j2
5950+
-> Index Scan usingj2_id1_idx on j2
59475951
(5 rows)
59485952

5949-
select * from j1 j1
5950-
inner joinj1j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
5953+
select * from j1
5954+
inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2
59515955
where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1;
59525956
id1 | id2 | id1 | id2
59535957
-----+-----+-----+-----

‎src/test/regress/sql/join.sql

Lines changed: 10 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2014,15 +2014,20 @@ set enable_nestloop to 0;
20142014
set enable_hashjoin to0;
20152015
set enable_sort to0;
20162016

2017-
-- createan indexthat will be preferred over thePK to perform the join
2017+
-- createindexesthat will be preferred over thePKs to perform the join
20182018
createindexj1_id1_idxon j1 (id1)where id1 %1000=1;
2019+
createindexj2_id1_idxon j2 (id1)where id1 %1000=1;
20192020

2020-
explain (costs off)select*from j1 j1
2021-
inner join j1 j2onj1.id1=j2.id1andj1.id2=j2.id2
2021+
-- need an additional row in j2, if we want j2_id1_idx to be preferred
2022+
insert into j2values(1,2);
2023+
analyze j2;
2024+
2025+
explain (costs off)select*from j1
2026+
inner join j2onj1.id1=j2.id1andj1.id2=j2.id2
20222027
wherej1.id1 %1000=1andj2.id1 %1000=1;
20232028

2024-
select*from j1 j1
2025-
inner joinj1j2onj1.id1=j2.id1andj1.id2=j2.id2
2029+
select*from j1
2030+
inner join j2onj1.id1=j2.id1andj1.id2=j2.id2
20262031
wherej1.id1 %1000=1andj2.id1 %1000=1;
20272032

20282033
reset enable_nestloop;

0 commit comments

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