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

Commitd0f9526

Browse files
committed
Fix thinko in join removal.
In commit9df8f90 I (tgl) switched join_is_removable() fromusing the min relid sets of the join under consideration tousing its full syntactic relid sets. This was a mistake,as it allowed join removal in cases where a reference to thejoin output would survive in some syntactically-lower joincondition. Revert to the former coding.Richard GuoDiscussion:https://postgr.es/m/CAMbWs4-EU9uBGSP7G-iTwLBhRQ=rnZKvFDhD+n+xhajokyPCKg@mail.gmail.com
1 parent70b42f2 commitd0f9526

File tree

3 files changed

+25
-7
lines changed

3 files changed

+25
-7
lines changed

‎src/backend/optimizer/plan/analyzejoins.c

Lines changed: 4 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -88,11 +88,8 @@ remove_useless_joins(PlannerInfo *root, List *joinlist)
8888
*/
8989
innerrelid=bms_singleton_member(sjinfo->min_righthand);
9090

91-
/*
92-
* Compute the relid set for the join we are considering. We can
93-
* assume things are done in syntactic order.
94-
*/
95-
joinrelids=bms_union(sjinfo->syn_lefthand,sjinfo->syn_righthand);
91+
/* Compute the relid set for the join we are considering */
92+
joinrelids=bms_union(sjinfo->min_lefthand,sjinfo->min_righthand);
9693
if (sjinfo->ojrelid!=0)
9794
joinrelids=bms_add_member(joinrelids,sjinfo->ojrelid);
9895

@@ -204,8 +201,8 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo)
204201
if (!rel_supports_distinctness(root,innerrel))
205202
return false;
206203

207-
/* Compute thesyntacticrelid set for the join we are considering */
208-
inputrelids=bms_union(sjinfo->syn_lefthand,sjinfo->syn_righthand);
204+
/* Compute the relid set for the join we are considering */
205+
inputrelids=bms_union(sjinfo->min_lefthand,sjinfo->min_righthand);
209206
Assert(sjinfo->ojrelid!=0);
210207
joinrelids=bms_copy(inputrelids);
211208
joinrelids=bms_add_member(joinrelids,sjinfo->ojrelid);

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5432,6 +5432,22 @@ select d.* from d left join (select distinct * from b) s
54325432
-> Seq Scan on d
54335433
(9 rows)
54345434

5435+
-- join removal is not possible here
5436+
explain (costs off)
5437+
select 1 from a t1
5438+
left join (a t2 left join a t3 on t2.id = 1) on t2.id = 1;
5439+
QUERY PLAN
5440+
--------------------------------------------------------
5441+
Nested Loop Left Join
5442+
-> Seq Scan on a t1
5443+
-> Materialize
5444+
-> Nested Loop Left Join
5445+
Join Filter: (t2.id = 1)
5446+
-> Index Only Scan using a_pkey on a t2
5447+
Index Cond: (id = 1)
5448+
-> Seq Scan on a t3
5449+
(8 rows)
5450+
54355451
-- check join removal works when uniqueness of the join condition is enforced
54365452
-- by a UNION
54375453
explain (costs off)

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

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1958,6 +1958,11 @@ explain (costs off)
19581958
select d.*from dleft join (select distinct*from b) s
19591959
ond.a=s.id;
19601960

1961+
-- join removal is not possible here
1962+
explain (costs off)
1963+
select1from a t1
1964+
left join (a t2left join a t3ont2.id=1)ont2.id=1;
1965+
19611966
-- check join removal works when uniqueness of the join condition is enforced
19621967
-- by a UNION
19631968
explain (costs off)

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp