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
Appearance settings

Commit a75ff55

Browse filesBrowse files
tglsfdctglsfdc
committed
Fix some issues with wrong placement of pseudo-constant quals.
initsplan.c figured that it could push Var-free qual clauses to the top of the current JoinDomain, which is okay in the abstract. But if the current domain is inside some outer join, and we later commute 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 scope of the qual clause; with the exception that if we're in the top-level join domain we can still use the full query relid set, ensuring the resulting gating Result node goes to the top of the plan. (This is approximately as smart as the pre-v16 code was. Perhaps we can do better later, but it's not clear that such cases are worth a lot of sweat.) In process_implied_equality, we don't have a clear notion of syntactic scope, but we do have the results of SpecialJoinInfo construction. Thumb through those and remove any lower outer joins that might get commuted to above the join domain. Again, we can make an exception for the top-level join domain. It'd be possible to work harder here (for example, by keeping outer joins that aren't shown as potentially commutable), but I'm going to stop here for the moment. This issue has convinced me that the current representation of join domains probably needs further refinement, so I'm disinclined to write inessential dependent logic just yet. In passing, tighten the qualscope passed to process_implied_equality by generate_base_implied_equalities_no_const; there's no need for it 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 parent 7fe1aa9 commit a75ff55
Copy full SHA for a75ff55

File tree

4 files changed

+155
-9
lines changed
Filter options

4 files changed

+155
-9
lines changed

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

Copy file name to clipboardExpand all lines: src/backend/optimizer/path/equivclass.c
+2-2Lines 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

Copy file name to clipboardExpand all lines: src/backend/optimizer/plan/initsplan.c
+67-7Lines 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
bool is_clone,
126126
List **postponed_oj_qual_list);
127127
static bool check_redundant_nullability_qual(PlannerInfo *root, Node *clause);
128+
static Relids get_join_domain_min_rels(PlannerInfo *root, Relids domain_relids);
128129
static void check_mergejoinable(RestrictInfo *restrictinfo);
129130
static void check_hashjoinable(RestrictInfo *restrictinfo);
130131
static void check_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
return restrictinfo;
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+
static Relids
2895+
get_join_domain_min_rels(PlannerInfo *root, Relids domain_relids)
2896+
{
2897+
Relids result = bms_copy(domain_relids);
2898+
ListCell *lc;
2899+
2900+
/* Top-level join domain? */
2901+
if (bms_equal(result, root->all_query_rels))
2902+
return result;
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+
return result;
2917+
}
2918+
28592919

28602920
/*
28612921
* match_foreign_keys_to_quals

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/join.out
+61Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/sql/join.sql
+25Lines 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
on t2.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 t1 left join
1854+
(int8_tbl t2 inner join int8_tbl t3 on false
1855+
left join int8_tbl t4 on t2.q2 = t4.q2)
1856+
on t1.q1 = t2.q1;
1857+
1858+
-- deduce constant-false from an EquivalenceClass
1859+
explain (costs off)
1860+
select * from int8_tbl t1 left join
1861+
(int8_tbl t2 inner join int8_tbl t3 on (t2.q1-t3.q2) = 0 and (t2.q1-t3.q2) = 1
1862+
left join int8_tbl t4 on t2.q2 = t4.q2)
1863+
on t1.q1 = t2.q1;
1864+
1865+
-- pseudoconstant based on an outer-level Param
1866+
explain (costs off)
1867+
select exists(
1868+
select * from int8_tbl t1 left join
1869+
(int8_tbl t2 inner join int8_tbl t3 on x0.f1 = 1
1870+
left join int8_tbl t4 on t2.q2 = t4.q2)
1871+
on t1.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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.