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

Commitf4c00d1

Browse files
committed
When removing a left join, clean out references in EquivalenceClasses.
Since commitb448f1c, we've been able to remove left joins(that are otherwise removable) even when they are underneathother left joins, a case that was previously prevented by adelay_upper_joins check. This is a clear improvement, butit has a surprising side-effect: it's now possible that thereare EquivalenceClasses whose relid sets mention the removedbaserel and/or outer join. If we fail to clean those up,we may drop essential join quals due to not having any joinlevel that appears to satisfy their relid sets.(It's not quite 100% clear that this was impossible before.But the lack of complaints since we added join removal a dozenyears ago strongly suggests that it was impossible.)Richard Guo and Tom Lane, per bug #17976 from Zuming JiangDiscussion:https://postgr.es/m/17976-4b638b525e9a983b@postgresql.org
1 parent5c8c807 commitf4c00d1

File tree

3 files changed

+122
-0
lines changed

3 files changed

+122
-0
lines changed

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

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,8 @@ static void remove_rel_from_query(PlannerInfo *root, int relid,
3939
SpecialJoinInfo*sjinfo);
4040
staticvoidremove_rel_from_restrictinfo(RestrictInfo*rinfo,
4141
intrelid,intojrelid);
42+
staticvoidremove_rel_from_eclass(EquivalenceClass*ec,
43+
intrelid,intojrelid);
4244
staticList*remove_rel_from_joinlist(List*joinlist,intrelid,int*nremoved);
4345
staticboolrel_supports_distinctness(PlannerInfo*root,RelOptInfo*rel);
4446
staticboolrel_is_distinct_for(PlannerInfo*root,RelOptInfo*rel,
@@ -511,6 +513,18 @@ remove_rel_from_query(PlannerInfo *root, int relid, SpecialJoinInfo *sjinfo)
511513
}
512514
}
513515

516+
/*
517+
* Likewise remove references from EquivalenceClasses.
518+
*/
519+
foreach(l,root->eq_classes)
520+
{
521+
EquivalenceClass*ec= (EquivalenceClass*)lfirst(l);
522+
523+
if (bms_is_member(relid,ec->ec_relids)||
524+
bms_is_member(ojrelid,ec->ec_relids))
525+
remove_rel_from_eclass(ec,relid,ojrelid);
526+
}
527+
514528
/*
515529
* There may be references to the rel in root->fkey_list, but if so,
516530
* match_foreign_keys_to_quals() will get rid of them.
@@ -583,6 +597,60 @@ remove_rel_from_restrictinfo(RestrictInfo *rinfo, int relid, int ojrelid)
583597
}
584598
}
585599

600+
/*
601+
* Remove any references to relid or ojrelid from the EquivalenceClass.
602+
*
603+
* Like remove_rel_from_restrictinfo, we don't worry about cleaning out
604+
* any nullingrel bits in contained Vars and PHVs. (This might have to be
605+
* improved sometime.) We do need to fix the EC and EM relid sets to ensure
606+
* that implied join equalities will be generated at the appropriate join
607+
* level(s).
608+
*/
609+
staticvoid
610+
remove_rel_from_eclass(EquivalenceClass*ec,intrelid,intojrelid)
611+
{
612+
ListCell*lc;
613+
614+
/* Fix up the EC's overall relids */
615+
ec->ec_relids=bms_del_member(ec->ec_relids,relid);
616+
ec->ec_relids=bms_del_member(ec->ec_relids,ojrelid);
617+
618+
/*
619+
* Fix up the member expressions. Any non-const member that ends with
620+
* empty em_relids must be a Var or PHV of the removed relation. We don't
621+
* need it anymore, so we can drop it.
622+
*/
623+
foreach(lc,ec->ec_members)
624+
{
625+
EquivalenceMember*cur_em= (EquivalenceMember*)lfirst(lc);
626+
627+
if (bms_is_member(relid,cur_em->em_relids)||
628+
bms_is_member(ojrelid,cur_em->em_relids))
629+
{
630+
Assert(!cur_em->em_is_const);
631+
cur_em->em_relids=bms_del_member(cur_em->em_relids,relid);
632+
cur_em->em_relids=bms_del_member(cur_em->em_relids,ojrelid);
633+
if (bms_is_empty(cur_em->em_relids))
634+
ec->ec_members=foreach_delete_current(ec->ec_members,lc);
635+
}
636+
}
637+
638+
/* Fix up the source clauses, in case we can re-use them later */
639+
foreach(lc,ec->ec_sources)
640+
{
641+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lc);
642+
643+
remove_rel_from_restrictinfo(rinfo,relid,ojrelid);
644+
}
645+
646+
/*
647+
* Rather than expend code on fixing up any already-derived clauses, just
648+
* drop them. (At this point, any such clauses would be base restriction
649+
* clauses, which we'd not need anymore anyway.)
650+
*/
651+
ec->ec_derives=NIL;
652+
}
653+
586654
/*
587655
* Remove any occurrences of the target relid from a joinlist structure.
588656
*

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

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5881,6 +5881,37 @@ where ss.stringu2 !~* ss.case1;
58815881
doh!
58825882
(1 row)
58835883

5884+
rollback;
5885+
-- another join removal bug: we must clean up EquivalenceClasses too
5886+
begin;
5887+
create temp table t (a int unique);
5888+
insert into t values (1);
5889+
explain (costs off)
5890+
select 1
5891+
from t t1
5892+
left join (select 2 as c
5893+
from t t2 left join t t3 on t2.a = t3.a) s
5894+
on true
5895+
where t1.a = s.c;
5896+
QUERY PLAN
5897+
------------------------------
5898+
Nested Loop Left Join
5899+
Filter: (t1.a = (2))
5900+
-> Seq Scan on t t1
5901+
-> Materialize
5902+
-> Seq Scan on t t2
5903+
(5 rows)
5904+
5905+
select 1
5906+
from t t1
5907+
left join (select 2 as c
5908+
from t t2 left join t t3 on t2.a = t3.a) s
5909+
on true
5910+
where t1.a = s.c;
5911+
?column?
5912+
----------
5913+
(0 rows)
5914+
58845915
rollback;
58855916
-- test cases where we can remove a join, but not a PHV computed at it
58865917
begin;

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2167,6 +2167,29 @@ where ss.stringu2 !~* ss.case1;
21672167

21682168
rollback;
21692169

2170+
-- another join removal bug: we must clean up EquivalenceClasses too
2171+
begin;
2172+
2173+
create temp table t (aint unique);
2174+
insert into tvalues (1);
2175+
2176+
explain (costs off)
2177+
select1
2178+
from t t1
2179+
left join (select2as c
2180+
from t t2left join t t3ont2.a=t3.a) s
2181+
on true
2182+
wheret1.a=s.c;
2183+
2184+
select1
2185+
from t t1
2186+
left join (select2as c
2187+
from t t2left join t t3ont2.a=t3.a) s
2188+
on true
2189+
wheret1.a=s.c;
2190+
2191+
rollback;
2192+
21702193
-- test cases where we can remove a join, but not a PHV computed at it
21712194
begin;
21722195

0 commit comments

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