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

Commit2057a58

Browse files
committed
Fix mis-optimization of semijoins with more than one LHS relation.
The inner-unique patch (commit9c7f522) supposed that if we'reconsidering a JOIN_UNIQUE_INNER join path, we can always set inner_uniquefor the join, because the inner path produced by create_unique_path shouldbe unique relative to the outer relation. However, that's true only ifwe're considering joining to the whole outer relation --- otherwise we maybe applying only some of the join quals, and so the inner path might benon-unique from the perspective of this join. Adjust the test to onlybelieve that we can set inner_unique if we have the whole semijoin LHS onthe outer side.There is more that can be done in this area, but this commit is onlyintended to provide the minimal fix needed to get correct plans.Per report from Teodor Sigaev. Thanks to David Rowley for preliminaryinvestigation.Discussion:https://postgr.es/m/f994fc98-389f-4a46-d1bc-c42e05cb43ed@sigaev.ru
1 parent74a20d0 commit2057a58

File tree

3 files changed

+44
-3
lines changed

3 files changed

+44
-3
lines changed

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

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -126,8 +126,11 @@ add_paths_to_joinrel(PlannerInfo *root,
126126
*
127127
* We have some special cases: for JOIN_SEMI and JOIN_ANTI, it doesn't
128128
* matter since the executor can make the equivalent optimization anyway;
129-
* we need not expend planner cycles on proofs. For JOIN_UNIQUE_INNER, we
130-
* know we're going to force uniqueness of the innerrel below. For
129+
* we need not expend planner cycles on proofs. For JOIN_UNIQUE_INNER, if
130+
* the LHS covers all of the associated semijoin's min_lefthand, then it's
131+
* appropriate to set inner_unique because the path produced by
132+
* create_unique_path will be unique relative to the LHS. (If we have an
133+
* LHS that's only part of the min_lefthand, that is *not* true.) For
131134
* JOIN_UNIQUE_OUTER, pass JOIN_INNER to avoid letting that value escape
132135
* this module.
133136
*/
@@ -138,7 +141,8 @@ add_paths_to_joinrel(PlannerInfo *root,
138141
extra.inner_unique= false;/* well, unproven */
139142
break;
140143
caseJOIN_UNIQUE_INNER:
141-
extra.inner_unique= true;
144+
extra.inner_unique=bms_is_subset(sjinfo->min_lefthand,
145+
outerrel->relids);
142146
break;
143147
caseJOIN_UNIQUE_OUTER:
144148
extra.inner_unique=innerrel_is_unique(root,outerrel,innerrel,

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

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5634,3 +5634,32 @@ reset enable_sort;
56345634
drop table j1;
56355635
drop table j2;
56365636
drop table j3;
5637+
-- check that semijoin inner is not seen as unique for a portion of the outerrel
5638+
explain (verbose, costs off)
5639+
select t1.unique1, t2.hundred
5640+
from onek t1, tenk1 t2
5641+
where exists (select 1 from tenk1 t3
5642+
where t3.thousand = t1.unique1 and t3.tenthous = t2.hundred)
5643+
and t1.unique1 < 1;
5644+
QUERY PLAN
5645+
---------------------------------------------------------------------------------
5646+
Nested Loop
5647+
Output: t1.unique1, t2.hundred
5648+
-> Hash Join
5649+
Output: t1.unique1, t3.tenthous
5650+
Hash Cond: (t3.thousand = t1.unique1)
5651+
-> HashAggregate
5652+
Output: t3.thousand, t3.tenthous
5653+
Group Key: t3.thousand, t3.tenthous
5654+
-> Index Only Scan using tenk1_thous_tenthous on public.tenk1 t3
5655+
Output: t3.thousand, t3.tenthous
5656+
-> Hash
5657+
Output: t1.unique1
5658+
-> Index Only Scan using onek_unique1 on public.onek t1
5659+
Output: t1.unique1
5660+
Index Cond: (t1.unique1 < 1)
5661+
-> Index Only Scan using tenk1_hundred on public.tenk1 t2
5662+
Output: t2.hundred
5663+
Index Cond: (t2.hundred = t3.tenthous)
5664+
(18 rows)
5665+

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1856,3 +1856,11 @@ reset enable_sort;
18561856
droptable j1;
18571857
droptable j2;
18581858
droptable j3;
1859+
1860+
-- check that semijoin inner is not seen as unique for a portion of the outerrel
1861+
explain (verbose, costs off)
1862+
selectt1.unique1,t2.hundred
1863+
from onek t1, tenk1 t2
1864+
where exists (select1from tenk1 t3
1865+
wheret3.thousand=t1.unique1andt3.tenthous=t2.hundred)
1866+
andt1.unique1<1;

0 commit comments

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