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

Commitff8fa0b

Browse files
committed
Handle SubPlan cases in find_nonnullable_rels/vars.
We can use some variants of SubPlan to deduce that Vars appearingin the testexpr must be non-null.Richard GuoDiscussion:https://postgr.es/m/CAMbWs4-jV=199A2Y_6==99dYnpnmaO_Wz_RGkRTTaCB=Pihw2w@mail.gmail.com
1 parentc3652cd commitff8fa0b

File tree

3 files changed

+75
-0
lines changed

3 files changed

+75
-0
lines changed

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

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1511,6 +1511,31 @@ find_nonnullable_rels_walker(Node *node, bool top_level)
15111511
expr->booltesttype==IS_NOT_UNKNOWN))
15121512
result=find_nonnullable_rels_walker((Node*)expr->arg, false);
15131513
}
1514+
elseif (IsA(node,SubPlan))
1515+
{
1516+
SubPlan*splan= (SubPlan*)node;
1517+
1518+
/*
1519+
* For some types of SubPlan, we can infer strictness from Vars in the
1520+
* testexpr (the LHS of the original SubLink).
1521+
*
1522+
* For ANY_SUBLINK, if the subquery produces zero rows, the result is
1523+
* always FALSE. If the subquery produces more than one row, the
1524+
* per-row results of the testexpr are combined using OR semantics.
1525+
* Hence ANY_SUBLINK can be strict only at top level, but there it's
1526+
* as strict as the testexpr is.
1527+
*
1528+
* For ROWCOMPARE_SUBLINK, if the subquery produces zero rows, the
1529+
* result is always NULL. Otherwise, the result is as strict as the
1530+
* testexpr is. So we can check regardless of top_level.
1531+
*
1532+
* We can't prove anything for other sublink types (in particular,
1533+
* note that ALL_SUBLINK will return TRUE if the subquery is empty).
1534+
*/
1535+
if ((top_level&&splan->subLinkType==ANY_SUBLINK)||
1536+
splan->subLinkType==ROWCOMPARE_SUBLINK)
1537+
result=find_nonnullable_rels_walker(splan->testexpr,top_level);
1538+
}
15141539
elseif (IsA(node,PlaceHolderVar))
15151540
{
15161541
PlaceHolderVar*phv= (PlaceHolderVar*)node;
@@ -1736,6 +1761,15 @@ find_nonnullable_vars_walker(Node *node, bool top_level)
17361761
expr->booltesttype==IS_NOT_UNKNOWN))
17371762
result=find_nonnullable_vars_walker((Node*)expr->arg, false);
17381763
}
1764+
elseif (IsA(node,SubPlan))
1765+
{
1766+
SubPlan*splan= (SubPlan*)node;
1767+
1768+
/* See analysis in find_nonnullable_rels_walker */
1769+
if ((top_level&&splan->subLinkType==ANY_SUBLINK)||
1770+
splan->subLinkType==ROWCOMPARE_SUBLINK)
1771+
result=find_nonnullable_vars_walker(splan->testexpr,top_level);
1772+
}
17391773
elseif (IsA(node,PlaceHolderVar))
17401774
{
17411775
PlaceHolderVar*phv= (PlaceHolderVar*)node;

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

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4650,6 +4650,34 @@ select a.q2, b.q1
46504650

46514651
reset enable_hashjoin;
46524652
reset enable_nestloop;
4653+
--
4654+
-- test join strength reduction with a SubPlan providing the proof
4655+
--
4656+
explain (costs off)
4657+
select a.unique1, b.unique2
4658+
from onek a left join onek b on a.unique1 = b.unique2
4659+
where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1);
4660+
QUERY PLAN
4661+
----------------------------------------------------------
4662+
Hash Join
4663+
Hash Cond: (b.unique2 = a.unique1)
4664+
-> Seq Scan on onek b
4665+
Filter: (SubPlan 1)
4666+
SubPlan 1
4667+
-> Seq Scan on int8_tbl c
4668+
Filter: (q1 < b.unique1)
4669+
-> Hash
4670+
-> Index Only Scan using onek_unique1 on onek a
4671+
(9 rows)
4672+
4673+
select a.unique1, b.unique2
4674+
from onek a left join onek b on a.unique1 = b.unique2
4675+
where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1);
4676+
unique1 | unique2
4677+
---------+---------
4678+
123 | 123
4679+
(1 row)
4680+
46534681
--
46544682
-- test join removal
46554683
--

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1603,6 +1603,19 @@ select a.q2, b.q1
16031603
reset enable_hashjoin;
16041604
reset enable_nestloop;
16051605

1606+
--
1607+
-- test join strength reduction with a SubPlan providing the proof
1608+
--
1609+
1610+
explain (costs off)
1611+
selecta.unique1,b.unique2
1612+
from onek aleft join onek bona.unique1=b.unique2
1613+
whereb.unique2= any (select q1from int8_tbl cwherec.q1<b.unique1);
1614+
1615+
selecta.unique1,b.unique2
1616+
from onek aleft join onek bona.unique1=b.unique2
1617+
whereb.unique2= any (select q1from int8_tbl cwherec.q1<b.unique1);
1618+
16061619
--
16071620
-- test join removal
16081621
--

0 commit comments

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