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

Commit207d5a6

Browse files
committed
Fix mishandling of equivalence-class tests in parameterized plans.
Given a three-or-more-way equivalence class, such as X.Y = Y.Y = Z.Z,it was possible for the planner to omit one of the quals needed toenforce that all members of the equivalence class are actually equal.This only happened in the case of a parameterized join node for twoof the relations, that is a plan tree likeNested Loop -> Scan X -> Nested Loop -> Scan Y -> Scan Z Filter: Z.Z = X.XThe eclass machinery normally expects to apply X.X = Y.Y when thosetwo relations are joined, but in this shape of plan tree they aren'tjoined until the top node --- and, if the lower nested loop is markedas parameterized by X, the top node will assume that the relevant eclasscondition(s) got pushed down into the lower node. On the other hand,the scan of Z assumes that it's only responsible for constraining Z.Zto match any one of the other eclass members. So one or another ofthe required quals sometimes fell between the cracks, depending onwhether consideration of the eclass in get_joinrel_parampathinfo()for the lower nested loop chanced to generate X.X = Y.Y or X.X = Z.Zas the appropriate constraint there. If it generated the latter,it'd erroneously suppose that the Z scan would take care of matters.To fix, force X.X = Y.Y to be generated and applied at that join nodewhen this case occurs.This is *extremely* hard to hit in practice, because various plannerbehaviors conspire to mask the problem; starting with the fact that theplanner doesn't really like to generate a parameterized plan of theabove shape. (It might have been impossible to hit it before wetweaked things to allow this plan shape for star-schema cases.) Manythanks to Alexander Kirkouski for submitting a reproducible test case.The bug can be demonstrated in all branches back to 9.2 where parameterizedpaths were introduced, so back-patch that far.
1 parent7c3e803 commit207d5a6

File tree

5 files changed

+149
-9
lines changed

5 files changed

+149
-9
lines changed

‎src/backend/optimizer/path/equivclass.c

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -988,13 +988,31 @@ generate_base_implied_equalities_broken(PlannerInfo *root,
988988
*
989989
* join_relids should always equal bms_union(outer_relids, inner_rel->relids).
990990
* We could simplify this function's API by computing it internally, but in
991-
*all current uses, the caller has the value at hand anyway.
991+
*most current uses, the caller has the value at hand anyway.
992992
*/
993993
List*
994994
generate_join_implied_equalities(PlannerInfo*root,
995995
Relidsjoin_relids,
996996
Relidsouter_relids,
997997
RelOptInfo*inner_rel)
998+
{
999+
returngenerate_join_implied_equalities_for_ecs(root,
1000+
root->eq_classes,
1001+
join_relids,
1002+
outer_relids,
1003+
inner_rel);
1004+
}
1005+
1006+
/*
1007+
* generate_join_implied_equalities_for_ecs
1008+
* As above, but consider only the listed ECs.
1009+
*/
1010+
List*
1011+
generate_join_implied_equalities_for_ecs(PlannerInfo*root,
1012+
List*eclasses,
1013+
Relidsjoin_relids,
1014+
Relidsouter_relids,
1015+
RelOptInfo*inner_rel)
9981016
{
9991017
List*result=NIL;
10001018
Relidsinner_relids=inner_rel->relids;
@@ -1016,7 +1034,7 @@ generate_join_implied_equalities(PlannerInfo *root,
10161034
nominal_join_relids=join_relids;
10171035
}
10181036

1019-
foreach(lc,root->eq_classes)
1037+
foreach(lc,eclasses)
10201038
{
10211039
EquivalenceClass*ec= (EquivalenceClass*)lfirst(lc);
10221040
List*sublist=NIL;

‎src/backend/optimizer/util/relnode.c

Lines changed: 75 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1100,6 +1100,7 @@ get_joinrel_parampathinfo(PlannerInfo *root, RelOptInfo *joinrel,
11001100
Relidsinner_and_req;
11011101
List*pclauses;
11021102
List*eclauses;
1103+
List*dropped_ecs;
11031104
doublerows;
11041105
ListCell*lc;
11051106

@@ -1153,6 +1154,7 @@ get_joinrel_parampathinfo(PlannerInfo *root, RelOptInfo *joinrel,
11531154
required_outer,
11541155
joinrel);
11551156
/* We only want ones that aren't movable to lower levels */
1157+
dropped_ecs=NIL;
11561158
foreach(lc,eclauses)
11571159
{
11581160
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lc);
@@ -1169,13 +1171,79 @@ get_joinrel_parampathinfo(PlannerInfo *root, RelOptInfo *joinrel,
11691171
joinrel->relids,
11701172
join_and_req));
11711173
#endif
1172-
if (!join_clause_is_movable_into(rinfo,
1173-
outer_path->parent->relids,
1174-
outer_and_req)&&
1175-
!join_clause_is_movable_into(rinfo,
1176-
inner_path->parent->relids,
1177-
inner_and_req))
1178-
pclauses=lappend(pclauses,rinfo);
1174+
if (join_clause_is_movable_into(rinfo,
1175+
outer_path->parent->relids,
1176+
outer_and_req))
1177+
continue;/* drop if movable into LHS */
1178+
if (join_clause_is_movable_into(rinfo,
1179+
inner_path->parent->relids,
1180+
inner_and_req))
1181+
{
1182+
/* drop if movable into RHS, but remember EC for use below */
1183+
Assert(rinfo->left_ec==rinfo->right_ec);
1184+
dropped_ecs=lappend(dropped_ecs,rinfo->left_ec);
1185+
continue;
1186+
}
1187+
pclauses=lappend(pclauses,rinfo);
1188+
}
1189+
1190+
/*
1191+
* EquivalenceClasses are harder to deal with than we could wish, because
1192+
* of the fact that a given EC can generate different clauses depending on
1193+
* context. Suppose we have an EC {X.X, Y.Y, Z.Z} where X and Y are the
1194+
* LHS and RHS of the current join and Z is in required_outer, and further
1195+
* suppose that the inner_path is parameterized by both X and Z. The code
1196+
* above will have produced either Z.Z = X.X or Z.Z = Y.Y from that EC,
1197+
* and in the latter case will have discarded it as being movable into the
1198+
* RHS. However, the EC machinery might have produced either Y.Y = X.X or
1199+
* Y.Y = Z.Z as the EC enforcement clause within the inner_path; it will
1200+
* not have produced both, and we can't readily tell from here which one
1201+
* it did pick. If we add no clause to this join, we'll end up with
1202+
* insufficient enforcement of the EC; either Z.Z or X.X will fail to be
1203+
* constrained to be equal to the other members of the EC. (When we come
1204+
* to join Z to this X/Y path, we will certainly drop whichever EC clause
1205+
* is generated at that join, so this omission won't get fixed later.)
1206+
*
1207+
* To handle this, for each EC we discarded such a clause from, try to
1208+
* generate a clause connecting the required_outer rels to the join's LHS
1209+
* ("Z.Z = X.X" in the terms of the above example). If successful, and if
1210+
* the clause can't be moved to the LHS, add it to the current join's
1211+
* restriction clauses. (If an EC cannot generate such a clause then it
1212+
* has nothing that needs to be enforced here, while if the clause can be
1213+
* moved into the LHS then it should have been enforced within that path.)
1214+
*
1215+
* Note that we don't need similar processing for ECs whose clause was
1216+
* considered to be movable into the LHS, because the LHS can't refer to
1217+
* the RHS so there is no comparable ambiguity about what it might
1218+
* actually be enforcing internally.
1219+
*/
1220+
if (dropped_ecs)
1221+
{
1222+
Relidsreal_outer_and_req;
1223+
1224+
real_outer_and_req=bms_union(outer_path->parent->relids,
1225+
required_outer);
1226+
eclauses=
1227+
generate_join_implied_equalities_for_ecs(root,
1228+
dropped_ecs,
1229+
real_outer_and_req,
1230+
required_outer,
1231+
outer_path->parent);
1232+
foreach(lc,eclauses)
1233+
{
1234+
RestrictInfo*rinfo= (RestrictInfo*)lfirst(lc);
1235+
1236+
/* As above, can't quite assert this here */
1237+
#ifdefNOT_USED
1238+
Assert(join_clause_is_movable_into(rinfo,
1239+
outer_path->parent->relids,
1240+
real_outer_and_req));
1241+
#endif
1242+
if (!join_clause_is_movable_into(rinfo,
1243+
outer_path->parent->relids,
1244+
outer_and_req))
1245+
pclauses=lappend(pclauses,rinfo);
1246+
}
11791247
}
11801248

11811249
/*

‎src/include/optimizer/paths.h

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -135,6 +135,11 @@ extern List *generate_join_implied_equalities(PlannerInfo *root,
135135
Relidsjoin_relids,
136136
Relidsouter_relids,
137137
RelOptInfo*inner_rel);
138+
externList*generate_join_implied_equalities_for_ecs(PlannerInfo*root,
139+
List*eclasses,
140+
Relidsjoin_relids,
141+
Relidsouter_relids,
142+
RelOptInfo*inner_rel);
138143
externboolexprs_known_equal(PlannerInfo*root,Node*item1,Node*item2);
139144
externvoidadd_child_rel_equivalences(PlannerInfo*root,
140145
AppendRelInfo*appinfo,

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

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2236,6 +2236,38 @@ where t4.thousand = t5.unique1 and ss.x1 = t4.tenthous and ss.x2 = t5.stringu1;
22362236
1000
22372237
(1 row)
22382238

2239+
--
2240+
-- regression test: check a case where we formerly missed including an EC
2241+
-- enforcement clause because it was expected to be handled at scan level
2242+
--
2243+
explain (costs off)
2244+
select a.f1, b.f1, t.thousand, t.tenthous from
2245+
tenk1 t,
2246+
(select sum(f1)+1 as f1 from int4_tbl i4a) a,
2247+
(select sum(f1) as f1 from int4_tbl i4b) b
2248+
where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous;
2249+
QUERY PLAN
2250+
-----------------------------------------------------------------------------------------------------------------------
2251+
Nested Loop
2252+
-> Aggregate
2253+
-> Seq Scan on int4_tbl i4b
2254+
-> Nested Loop
2255+
Join Filter: ((sum(i4b.f1)) = ((sum(i4a.f1) + 1)))
2256+
-> Aggregate
2257+
-> Seq Scan on int4_tbl i4a
2258+
-> Index Only Scan using tenk1_thous_tenthous on tenk1 t
2259+
Index Cond: ((thousand = (sum(i4b.f1))) AND (tenthous = ((((sum(i4a.f1) + 1)) + (sum(i4b.f1))) + 999)))
2260+
(9 rows)
2261+
2262+
select a.f1, b.f1, t.thousand, t.tenthous from
2263+
tenk1 t,
2264+
(select sum(f1)+1 as f1 from int4_tbl i4a) a,
2265+
(select sum(f1) as f1 from int4_tbl i4b) b
2266+
where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous;
2267+
f1 | f1 | thousand | tenthous
2268+
----+----+----------+----------
2269+
(0 rows)
2270+
22392271
--
22402272
-- Clean up
22412273
--

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -391,6 +391,23 @@ from
391391
tenk1 t5
392392
wheret4.thousand=t5.unique1andss.x1=t4.tenthousandss.x2=t5.stringu1;
393393

394+
--
395+
-- regression test: check a case where we formerly missed including an EC
396+
-- enforcement clause because it was expected to be handled at scan level
397+
--
398+
explain (costs off)
399+
selecta.f1,b.f1,t.thousand,t.tenthousfrom
400+
tenk1 t,
401+
(selectsum(f1)+1as f1from int4_tbl i4a) a,
402+
(selectsum(f1)as f1from int4_tbl i4b) b
403+
whereb.f1=t.thousandanda.f1=b.f1and (a.f1+b.f1+999)=t.tenthous;
404+
405+
selecta.f1,b.f1,t.thousand,t.tenthousfrom
406+
tenk1 t,
407+
(selectsum(f1)+1as f1from int4_tbl i4a) a,
408+
(selectsum(f1)as f1from int4_tbl i4b) b
409+
whereb.f1=t.thousandanda.f1=b.f1and (a.f1+b.f1+999)=t.tenthous;
410+
394411

395412
--
396413
-- Clean up

0 commit comments

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