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

Commitb5fb673

Browse files
committed
Don't constrain self-join removal due to PHVs
Self-join removal appears to be safe to apply with placeholder variablesas long as we handle PlaceHolderVar in replace_varno_walker() and replacerelid in phinfo->ph_lateral.Discussion:https://postgr.es/m/18187-831da249cbd2ff8e%40postgresql.orgAuthor: Richard GuoReviewed-by: Andrei Lepikhov
1 parent8a8ed91 commitb5fb673

File tree

3 files changed

+39
-28
lines changed

3 files changed

+39
-28
lines changed

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

Lines changed: 4 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -453,7 +453,7 @@ remove_rel_from_query(PlannerInfo *root, RelOptInfo *rel,
453453
{
454454
PlaceHolderInfo*phinfo= (PlaceHolderInfo*)lfirst(l);
455455

456-
Assert(!bms_is_member(relid,phinfo->ph_lateral));
456+
Assert(sjinfo==NULL||!bms_is_member(relid,phinfo->ph_lateral));
457457
if (bms_is_subset(phinfo->ph_needed,joinrelids)&&
458458
bms_is_member(relid,phinfo->ph_eval_at)&&
459459
!bms_is_member(ojrelid,phinfo->ph_eval_at))
@@ -472,6 +472,8 @@ remove_rel_from_query(PlannerInfo *root, RelOptInfo *rel,
472472
phinfo->ph_needed=replace_relid(phinfo->ph_needed,relid,subst);
473473
phinfo->ph_needed=replace_relid(phinfo->ph_needed,ojrelid,subst);
474474
/* ph_needed might or might not become empty */
475+
phinfo->ph_lateral=replace_relid(phinfo->ph_lateral,relid,subst);
476+
/* ph_lateral might or might not be empty */
475477
phv->phrels=replace_relid(phv->phrels,relid,subst);
476478
phv->phrels=replace_relid(phv->phrels,ojrelid,subst);
477479
Assert(!bms_is_empty(phv->phrels));
@@ -2115,20 +2117,8 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids)
21152117
joinrelids=bms_add_member(joinrelids,k);
21162118

21172119
/*
2118-
*Be safe to donotremove tables participated in complicated PH
2120+
*PHVs shouldnotimpose any constraints on removing self joins.
21192121
*/
2120-
foreach(lc,root->placeholder_list)
2121-
{
2122-
PlaceHolderInfo*phinfo= (PlaceHolderInfo*)lfirst(lc);
2123-
2124-
/* there isn't any other place to eval PHV */
2125-
if (bms_is_subset(phinfo->ph_eval_at,joinrelids)||
2126-
bms_is_subset(phinfo->ph_needed,joinrelids)||
2127-
bms_is_member(r,phinfo->ph_lateral))
2128-
break;
2129-
}
2130-
if (lc)
2131-
continue;
21322122

21332123
/*
21342124
* At this stage, joininfo lists of inner and outer can contain

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

Lines changed: 28 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -6821,20 +6821,37 @@ 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)
6824+
-- Check that PHVs do not impose any constraints on removing self joins
6825+
explain (verbose, costs off)
68276826
select * from emp1 t1 join emp1 t2 on t1.id = t2.id left join
68286827
lateral (select t1.id as t1id, * from generate_series(1,1) t3) s on true;
6829-
QUERY PLAN
6830-
---------------------------------------------------
6828+
QUERY PLAN
6829+
----------------------------------------------------------
68316830
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)
6831+
Output: t2.id, t2.code, t2.id, t2.code, (t2.id), t3.t3
6832+
-> Seq Scan on public.emp1 t2
6833+
Output: t2.id, t2.code
6834+
Filter: (t2.id IS NOT NULL)
6835+
-> Function Scan on pg_catalog.generate_series t3
6836+
Output: t3.t3, t2.id
6837+
Function Call: generate_series(1, 1)
6838+
(8 rows)
6839+
6840+
explain (verbose, costs off)
6841+
select * from generate_series(1,10) t1(id) left join
6842+
lateral (select t1.id as t1id, t2.id from emp1 t2 join emp1 t3 on t2.id = t3.id)
6843+
on true;
6844+
QUERY PLAN
6845+
------------------------------------------------------
6846+
Nested Loop Left Join
6847+
Output: t1.id, (t1.id), t3.id
6848+
-> Function Scan on pg_catalog.generate_series t1
6849+
Output: t1.id
6850+
Function Call: generate_series(1, 10)
6851+
-> Seq Scan on public.emp1 t3
6852+
Output: t3.id, t1.id
6853+
Filter: (t3.id IS NOT NULL)
6854+
(8 rows)
68386855

68396856
-- We can remove the join even if we find the join can't duplicate rows and
68406857
-- the base quals of each side are different. In the following case we end up

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

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2600,12 +2600,16 @@ 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)
2603+
-- Check that PHVs do not impose any constraints on removing self joins
2604+
explain (verbose, costs off)
26062605
select*from emp1 t1join emp1 t2ont1.id=t2.idleft join
26072606
lateral (selectt1.idas t1id,*from generate_series(1,1) t3) son true;
26082607

2608+
explain (verbose, costs off)
2609+
select*from generate_series(1,10) t1(id)left join
2610+
lateral (selectt1.idas t1id,t2.idfrom emp1 t2join emp1 t3ont2.id=t3.id)
2611+
on true;
2612+
26092613
-- We can remove the join even if we find the join can't duplicate rows and
26102614
-- the base quals of each side are different. In the following case we end up
26112615
-- 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