From 24d08f3c0a1f04ea8bac68eb3aa3b069680e43f5 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 21 Feb 2019 18:55:29 -0500 Subject: [PATCH] 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 rather than a join between separate tables, except that it saved a couple of SQL commands for setup. A proposed patch to optimize away self-joins breaks the test, so adjust it to avoid that happening. Discussion: https://postgr.es/m/64486b0b-0404-e39e-322d-0801154901f3@postgrespro.ru --- src/test/regress/expected/join.out | 20 ++++++++++++-------- src/test/regress/sql/join.sql | 15 ++++++++++----- 2 files changed, 22 insertions(+), 13 deletions(-) diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index fcc82a1926..593aec2f7d 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -5932,22 +5932,26 @@ left join j2 on j1.id1 = j2.id1 where j1.id2 = 1; set enable_nestloop to 0; set enable_hashjoin to 0; set enable_sort to 0; --- create an index that will be preferred over the PK to perform the join +-- create indexes that will be preferred over the PKs to perform the join create index j1_id1_idx on j1 (id1) where id1 % 1000 = 1; -explain (costs off) select * from j1 j1 -inner join j1 j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 +create index j2_id1_idx on j2 (id1) where id1 % 1000 = 1; +-- need an additional row in j2, if we want j2_id1_idx to be preferred +insert into j2 values(1,2); +analyze j2; +explain (costs off) select * from j1 +inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1; - QUERY PLAN --------------------------------------------- + QUERY PLAN +----------------------------------------- Merge Join Merge Cond: (j1.id1 = j2.id1) Join Filter: (j1.id2 = j2.id2) -> Index Scan using j1_id1_idx on j1 - -> Index Scan using j1_id1_idx on j1 j2 + -> Index Scan using j2_id1_idx on j2 (5 rows) -select * from j1 j1 -inner join j1 j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 +select * from j1 +inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1; id1 | id2 | id1 | id2 -----+-----+-----+----- diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 11dc4c7a54..34d21d029f 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2014,15 +2014,20 @@ set enable_nestloop to 0; set enable_hashjoin to 0; set enable_sort to 0; --- create an index that will be preferred over the PK to perform the join +-- create indexes that will be preferred over the PKs to perform the join create index j1_id1_idx on j1 (id1) where id1 % 1000 = 1; +create index j2_id1_idx on j2 (id1) where id1 % 1000 = 1; -explain (costs off) select * from j1 j1 -inner join j1 j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 +-- need an additional row in j2, if we want j2_id1_idx to be preferred +insert into j2 values(1,2); +analyze j2; + +explain (costs off) select * from j1 +inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1; -select * from j1 j1 -inner join j1 j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 +select * from j1 +inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1; reset enable_nestloop; -- 2.40.0