From ff8fa0bf7eb9001f321c2af4bbe9c0f2bd5c1bf8 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 5 Nov 2022 15:24:36 -0400 Subject: [PATCH] Handle SubPlan cases in find_nonnullable_rels/vars. We can use some variants of SubPlan to deduce that Vars appearing in the testexpr must be non-null. Richard Guo Discussion: https://postgr.es/m/CAMbWs4-jV=199A2Y_6==99dYnpnmaO_Wz_RGkRTTaCB=Pihw2w@mail.gmail.com --- src/backend/optimizer/util/clauses.c | 34 ++++++++++++++++++++++++++++ src/test/regress/expected/join.out | 28 +++++++++++++++++++++++ src/test/regress/sql/join.sql | 13 +++++++++++ 3 files changed, 75 insertions(+) diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 7fb32a07103..5e791333cbd 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -1511,6 +1511,31 @@ find_nonnullable_rels_walker(Node *node, bool top_level) expr->booltesttype == IS_NOT_UNKNOWN)) result = find_nonnullable_rels_walker((Node *) expr->arg, false); } + else if (IsA(node, SubPlan)) + { + SubPlan *splan = (SubPlan *) node; + + /* + * For some types of SubPlan, we can infer strictness from Vars in the + * testexpr (the LHS of the original SubLink). + * + * For ANY_SUBLINK, if the subquery produces zero rows, the result is + * always FALSE. If the subquery produces more than one row, the + * per-row results of the testexpr are combined using OR semantics. + * Hence ANY_SUBLINK can be strict only at top level, but there it's + * as strict as the testexpr is. + * + * For ROWCOMPARE_SUBLINK, if the subquery produces zero rows, the + * result is always NULL. Otherwise, the result is as strict as the + * testexpr is. So we can check regardless of top_level. + * + * We can't prove anything for other sublink types (in particular, + * note that ALL_SUBLINK will return TRUE if the subquery is empty). + */ + if ((top_level && splan->subLinkType == ANY_SUBLINK) || + splan->subLinkType == ROWCOMPARE_SUBLINK) + result = find_nonnullable_rels_walker(splan->testexpr, top_level); + } else if (IsA(node, PlaceHolderVar)) { PlaceHolderVar *phv = (PlaceHolderVar *) node; @@ -1736,6 +1761,15 @@ find_nonnullable_vars_walker(Node *node, bool top_level) expr->booltesttype == IS_NOT_UNKNOWN)) result = find_nonnullable_vars_walker((Node *) expr->arg, false); } + else if (IsA(node, SubPlan)) + { + SubPlan *splan = (SubPlan *) node; + + /* See analysis in find_nonnullable_rels_walker */ + if ((top_level && splan->subLinkType == ANY_SUBLINK) || + splan->subLinkType == ROWCOMPARE_SUBLINK) + result = find_nonnullable_vars_walker(splan->testexpr, top_level); + } else if (IsA(node, PlaceHolderVar)) { PlaceHolderVar *phv = (PlaceHolderVar *) node; diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index b901d7299fa..93583710725 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4650,6 +4650,34 @@ select a.q2, b.q1 reset enable_hashjoin; reset enable_nestloop; +-- +-- test join strength reduction with a SubPlan providing the proof +-- +explain (costs off) +select a.unique1, b.unique2 + from onek a left join onek b on a.unique1 = b.unique2 + where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1); + QUERY PLAN +---------------------------------------------------------- + Hash Join + Hash Cond: (b.unique2 = a.unique1) + -> Seq Scan on onek b + Filter: (SubPlan 1) + SubPlan 1 + -> Seq Scan on int8_tbl c + Filter: (q1 < b.unique1) + -> Hash + -> Index Only Scan using onek_unique1 on onek a +(9 rows) + +select a.unique1, b.unique2 + from onek a left join onek b on a.unique1 = b.unique2 + where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1); + unique1 | unique2 +---------+--------- + 123 | 123 +(1 row) + -- -- test join removal -- diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index ccbbe5454c5..a81c7dce7d5 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1603,6 +1603,19 @@ select a.q2, b.q1 reset enable_hashjoin; reset enable_nestloop; +-- +-- test join strength reduction with a SubPlan providing the proof +-- + +explain (costs off) +select a.unique1, b.unique2 + from onek a left join onek b on a.unique1 = b.unique2 + where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1); + +select a.unique1, b.unique2 + from onek a left join onek b on a.unique1 = b.unique2 + where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1); + -- -- test join removal -- -- 2.30.2