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

Commitb7f315c

Browse files
committed
Fix how SJE checks against PHVs
It seems that a PHV evaluated/needed at or below the self join should not havea problem if we remove the self join. But this requires further investigation.For now, we just do not remove self joins if the rel to be removed is laterallyreferenced by PHVs.Discussion:https://postgr.es/m/CAMbWs4-ns73VF9gi37q61G3dS6Xuos+HtryMaBh37WQn=BsaJw@mail.gmail.comAuthor: Richard Guo
1 parenteb81e8e commitb7f315c

File tree

3 files changed

+23
-2
lines changed

3 files changed

+23
-2
lines changed

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -476,7 +476,6 @@ remove_rel_from_query(PlannerInfo *root, RelOptInfo *rel,
476476
phv->phrels=replace_relid(phv->phrels,ojrelid,subst);
477477
Assert(!bms_is_empty(phv->phrels));
478478
replace_varno((Node*)phv->phexpr,relid,subst);
479-
phinfo->ph_lateral=replace_relid(phinfo->ph_lateral,relid,subst);
480479
Assert(phv->phnullingrels==NULL);/* no need to adjust */
481480
}
482481
}
@@ -2134,7 +2133,8 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids)
21342133

21352134
/* there isn't any other place to eval PHV */
21362135
if (bms_is_subset(phinfo->ph_eval_at,joinrelids)||
2137-
bms_is_subset(phinfo->ph_needed,joinrelids))
2136+
bms_is_subset(phinfo->ph_needed,joinrelids)||
2137+
bms_is_member(r,phinfo->ph_lateral))
21382138
break;
21392139
}
21402140
if (lc)

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6821,6 +6821,21 @@ on true;
68216821
Filter: (id IS NOT NULL)
68226822
(8 rows)
68236823

6824+
-- Check that SJE does not remove self joins if a PHV references the removed
6825+
-- rel laterally.
6826+
explain (costs off)
6827+
select * from emp1 t1 join emp1 t2 on t1.id = t2.id left join
6828+
lateral (select t1.id as t1id, * from generate_series(1,1) t3) s on true;
6829+
QUERY PLAN
6830+
---------------------------------------------------
6831+
Nested Loop Left Join
6832+
-> Nested Loop
6833+
-> Seq Scan on emp1 t1
6834+
-> Index Scan using emp1_pkey on emp1 t2
6835+
Index Cond: (id = t1.id)
6836+
-> Function Scan on generate_series t3
6837+
(6 rows)
6838+
68246839
-- We can remove the join even if we find the join can't duplicate rows and
68256840
-- the base quals of each side are different. In the following case we end up
68266841
-- moving quals over to s1 to make it so it can't match any rows.

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

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2600,6 +2600,12 @@ select * from emp1 t1 left join
26002600
on true)
26012601
on true;
26022602

2603+
-- Check that SJE does not remove self joins if a PHV references the removed
2604+
-- rel laterally.
2605+
explain (costs off)
2606+
select*from emp1 t1join emp1 t2ont1.id=t2.idleft join
2607+
lateral (selectt1.idas t1id,*from generate_series(1,1) t3) son true;
2608+
26032609
-- We can remove the join even if we find the join can't duplicate rows and
26042610
-- the base quals of each side are different. In the following case we end up
26052611
-- moving quals over to s1 to make it so it can't match any rows.

0 commit comments

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