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

Commit6742718

Browse files
tglsfdcpull[bot]
authored andcommitted
Fix some issues with wrong placement of pseudo-constant quals.
initsplan.c figured that it could push Var-free qual clauses tothe top of the current JoinDomain, which is okay in the abstract.But if the current domain is inside some outer join, and we latercommute an inside-the-domain outer join with one outside it,we end up placing the pushed-up qual clause incorrectly.In distribute_qual_to_rels, avoid this by using the syntactic scopeof the qual clause; with the exception that if we're in the top-leveljoin domain we can still use the full query relid set, ensuring theresulting gating Result node goes to the top of the plan. (This isapproximately as smart as the pre-v16 code was. Perhaps we can dobetter later, but it's not clear that such cases are worth a lot ofsweat.)In process_implied_equality, we don't have a clear notion of syntacticscope, but we do have the results of SpecialJoinInfo construction.Thumb through those and remove any lower outer joins that might getcommuted to above the join domain. Again, we can make an exceptionfor the top-level join domain. It'd be possible to work harder here(for example, by keeping outer joins that aren't shown as potentiallycommutable), but I'm going to stop here for the moment. This issuehas convinced me that the current representation of join domainsprobably needs further refinement, so I'm disinclined to writeinessential dependent logic just yet.In passing, tighten the qualscope passed to process_implied_equalityby generate_base_implied_equalities_no_const; there's no need forit to be larger than the rel we are currently considering.Tom Lane and Richard Guo, per report from Tender Wang.Discussion:https://postgr.es/m/CAHewXNk9eJ35ru5xATWioTV4+xZPHptjy9etdcNPjUfY9RQ+uQ@mail.gmail.com
1 parent31c35cd commit6742718

File tree

4 files changed

+155
-9
lines changed

4 files changed

+155
-9
lines changed

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1248,11 +1248,11 @@ generate_base_implied_equalities_no_const(PlannerInfo *root,
12481248
/*
12491249
* The expressions aren't constants, so the passed qualscope will
12501250
* never be used to place the generated clause. We just need to
1251-
* be sure it covers both expressions,so ec_relids will serve.
1251+
* be sure it covers both expressions,which em_relids should do.
12521252
*/
12531253
rinfo=process_implied_equality(root,eq_op,ec->ec_collation,
12541254
prev_em->em_expr,cur_em->em_expr,
1255-
ec->ec_relids,
1255+
cur_em->em_relids,
12561256
ec->ec_min_security,
12571257
false);
12581258

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

Lines changed: 67 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -125,6 +125,7 @@ static void distribute_qual_to_rels(PlannerInfo *root, Node *clause,
125125
boolis_clone,
126126
List**postponed_oj_qual_list);
127127
staticboolcheck_redundant_nullability_qual(PlannerInfo*root,Node*clause);
128+
staticRelidsget_join_domain_min_rels(PlannerInfo*root,Relidsdomain_relids);
128129
staticvoidcheck_mergejoinable(RestrictInfo*restrictinfo);
129130
staticvoidcheck_hashjoinable(RestrictInfo*restrictinfo);
130131
staticvoidcheck_memoizable(RestrictInfo*restrictinfo);
@@ -2250,8 +2251,7 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
22502251
* RestrictInfo lists for the moment, but eventually createplan.c will
22512252
* pull it out and make a gating Result node immediately above whatever
22522253
* plan node the pseudoconstant clause is assigned to. It's usually best
2253-
* to put a gating node as high in the plan tree as possible, which we can
2254-
* do by assigning it the full relid set of the current JoinDomain.
2254+
* to put a gating node as high in the plan tree as possible.
22552255
*/
22562256
if (bms_is_empty(relids))
22572257
{
@@ -2269,8 +2269,19 @@ distribute_qual_to_rels(PlannerInfo *root, Node *clause,
22692269
}
22702270
else
22712271
{
2272-
/* eval at join domain level */
2273-
relids=bms_copy(jtitem->jdomain->jd_relids);
2272+
/*
2273+
* If we are in the top-level join domain, we can push the qual to
2274+
* the top of the plan tree. Otherwise, be conservative and eval
2275+
* it at original syntactic level. (Ideally we'd push it to the
2276+
* top of the current join domain in all cases, but that causes
2277+
* problems if we later rearrange outer-join evaluation order.
2278+
* Pseudoconstant quals below the top level are a pretty odd case,
2279+
* so it's not clear that it's worth working hard on.)
2280+
*/
2281+
if (jtitem->jdomain== (JoinDomain*)linitial(root->join_domains))
2282+
relids=bms_copy(jtitem->jdomain->jd_relids);
2283+
else
2284+
relids=bms_copy(qualscope);
22742285
/* mark as gating qual */
22752286
pseudoconstant= true;
22762287
/* tell createplan.c to check for gating quals */
@@ -2734,12 +2745,13 @@ process_implied_equality(PlannerInfo *root,
27342745
/*
27352746
* If the clause is variable-free, our normal heuristic for pushing it
27362747
* down to just the mentioned rels doesn't work, because there are none.
2737-
* Apply it as a gating qual at the given qualscope.
2748+
* Apply it as a gating qual at the appropriate level (see comments for
2749+
* get_join_domain_min_rels).
27382750
*/
27392751
if (bms_is_empty(relids))
27402752
{
2741-
/* eval at join domain level */
2742-
relids=bms_copy(qualscope);
2753+
/* eval at join domain's safe level */
2754+
relids=get_join_domain_min_rels(root,qualscope);
27432755
/* mark as gating qual */
27442756
pseudoconstant= true;
27452757
/* tell createplan.c to check for gating quals */
@@ -2856,6 +2868,54 @@ build_implied_join_equality(PlannerInfo *root,
28562868
returnrestrictinfo;
28572869
}
28582870

2871+
/*
2872+
* get_join_domain_min_rels
2873+
* Identify the appropriate join level for derived quals belonging
2874+
* to the join domain with the given relids.
2875+
*
2876+
* When we derive a pseudoconstant (Var-free) clause from an EquivalenceClass,
2877+
* we'd ideally apply the clause at the top level of the EC's join domain.
2878+
* However, if there are any outer joins inside that domain that get commuted
2879+
* with joins outside it, that leads to not finding a correct place to apply
2880+
* the clause. Instead, remove any lower outer joins from the relid set,
2881+
* and apply the clause to just the remaining rels. This still results in a
2882+
* correct answer, since if the clause produces FALSE then the LHS of these
2883+
* joins will be empty leading to an empty join result.
2884+
*
2885+
* However, there's no need to remove outer joins if this is the top-level
2886+
* join domain of the query, since then there's nothing else to commute with.
2887+
*
2888+
* Note: it's tempting to use this in distribute_qual_to_rels where it's
2889+
* dealing with pseudoconstant quals; but we can't because the necessary
2890+
* SpecialJoinInfos aren't all formed at that point.
2891+
*
2892+
* The result is always freshly palloc'd; we do not modify domain_relids.
2893+
*/
2894+
staticRelids
2895+
get_join_domain_min_rels(PlannerInfo*root,Relidsdomain_relids)
2896+
{
2897+
Relidsresult=bms_copy(domain_relids);
2898+
ListCell*lc;
2899+
2900+
/* Top-level join domain? */
2901+
if (bms_equal(result,root->all_query_rels))
2902+
returnresult;
2903+
2904+
/* Nope, look for lower outer joins that could potentially commute out */
2905+
foreach(lc,root->join_info_list)
2906+
{
2907+
SpecialJoinInfo*sjinfo= (SpecialJoinInfo*)lfirst(lc);
2908+
2909+
if (sjinfo->jointype==JOIN_LEFT&&
2910+
bms_is_member(sjinfo->ojrelid,result))
2911+
{
2912+
result=bms_del_member(result,sjinfo->ojrelid);
2913+
result=bms_del_members(result,sjinfo->syn_righthand);
2914+
}
2915+
}
2916+
returnresult;
2917+
}
2918+
28592919

28602920
/*
28612921
* match_foreign_keys_to_quals

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

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5119,6 +5119,67 @@ from int8_tbl t1
51195119
-> Seq Scan on onek t4
51205120
(13 rows)
51215121

5122+
-- More tests of correct placement of pseudoconstant quals
5123+
-- simple constant-false condition
5124+
explain (costs off)
5125+
select * from int8_tbl t1 left join
5126+
(int8_tbl t2 inner join int8_tbl t3 on false
5127+
left join int8_tbl t4 on t2.q2 = t4.q2)
5128+
on t1.q1 = t2.q1;
5129+
QUERY PLAN
5130+
--------------------------------------
5131+
Hash Left Join
5132+
Hash Cond: (t1.q1 = q1)
5133+
-> Seq Scan on int8_tbl t1
5134+
-> Hash
5135+
-> Result
5136+
One-Time Filter: false
5137+
(6 rows)
5138+
5139+
-- deduce constant-false from an EquivalenceClass
5140+
explain (costs off)
5141+
select * from int8_tbl t1 left join
5142+
(int8_tbl t2 inner join int8_tbl t3 on (t2.q1-t3.q2) = 0 and (t2.q1-t3.q2) = 1
5143+
left join int8_tbl t4 on t2.q2 = t4.q2)
5144+
on t1.q1 = t2.q1;
5145+
QUERY PLAN
5146+
--------------------------------------
5147+
Hash Left Join
5148+
Hash Cond: (t1.q1 = q1)
5149+
-> Seq Scan on int8_tbl t1
5150+
-> Hash
5151+
-> Result
5152+
One-Time Filter: false
5153+
(6 rows)
5154+
5155+
-- pseudoconstant based on an outer-level Param
5156+
explain (costs off)
5157+
select exists(
5158+
select * from int8_tbl t1 left join
5159+
(int8_tbl t2 inner join int8_tbl t3 on x0.f1 = 1
5160+
left join int8_tbl t4 on t2.q2 = t4.q2)
5161+
on t1.q1 = t2.q1
5162+
) from int4_tbl x0;
5163+
QUERY PLAN
5164+
---------------------------------------------------------------------
5165+
Seq Scan on int4_tbl x0
5166+
SubPlan 1
5167+
-> Nested Loop Left Join
5168+
Join Filter: (t2.q2 = t4.q2)
5169+
-> Nested Loop Left Join
5170+
Join Filter: (t1.q1 = t2.q1)
5171+
-> Seq Scan on int8_tbl t1
5172+
-> Materialize
5173+
-> Result
5174+
One-Time Filter: (x0.f1 = 1)
5175+
-> Nested Loop
5176+
-> Seq Scan on int8_tbl t2
5177+
-> Materialize
5178+
-> Seq Scan on int8_tbl t3
5179+
-> Materialize
5180+
-> Seq Scan on int8_tbl t4
5181+
(16 rows)
5182+
51225183
-- check that join removal works for a left join when joining a subquery
51235184
-- that is guaranteed to be unique by its GROUP BY clause
51245185
explain (costs off)

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

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1846,6 +1846,31 @@ from int8_tbl t1
18461846
left join onek t4
18471847
ont2.q2<t3.unique2;
18481848

1849+
-- More tests of correct placement of pseudoconstant quals
1850+
1851+
-- simple constant-false condition
1852+
explain (costs off)
1853+
select*from int8_tbl t1left join
1854+
(int8_tbl t2inner join int8_tbl t3on false
1855+
left join int8_tbl t4ont2.q2=t4.q2)
1856+
ont1.q1=t2.q1;
1857+
1858+
-- deduce constant-false from an EquivalenceClass
1859+
explain (costs off)
1860+
select*from int8_tbl t1left join
1861+
(int8_tbl t2inner join int8_tbl t3on (t2.q1-t3.q2)=0and (t2.q1-t3.q2)=1
1862+
left join int8_tbl t4ont2.q2=t4.q2)
1863+
ont1.q1=t2.q1;
1864+
1865+
-- pseudoconstant based on an outer-level Param
1866+
explain (costs off)
1867+
select exists(
1868+
select*from int8_tbl t1left join
1869+
(int8_tbl t2inner join int8_tbl t3onx0.f1=1
1870+
left join int8_tbl t4ont2.q2=t4.q2)
1871+
ont1.q1=t2.q1
1872+
)from int4_tbl x0;
1873+
18491874
-- check that join removal works for a left join when joining a subquery
18501875
-- that is guaranteed to be unique by its GROUP BY clause
18511876
explain (costs off)

0 commit comments

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