aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/optimizer/prep/prepjointree.c67
-rw-r--r--src/test/regress/expected/subselect.out103
-rw-r--r--src/test/regress/sql/subselect.sql33
3 files changed, 186 insertions, 17 deletions
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 3fa4d78c3e0..adad7ea9a9e 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -2649,11 +2649,12 @@ pullup_replace_vars_callback(Var *var,
{
/*
* If the node contains Var(s) or PlaceHolderVar(s) of the
- * subquery being pulled up, and does not contain any
- * non-strict constructs, then instead of adding a PHV on top
- * we can add the required nullingrels to those Vars/PHVs.
- * (This is fundamentally a generalization of the above cases
- * for bare Vars and PHVs.)
+ * subquery being pulled up, or of rels that are under the
+ * same lowest nulling outer join as the subquery, and does
+ * not contain any non-strict constructs, then instead of
+ * adding a PHV on top we can add the required nullingrels to
+ * those Vars/PHVs. (This is fundamentally a generalization
+ * of the above cases for bare Vars and PHVs.)
*
* This test is somewhat expensive, but it avoids pessimizing
* the plan in cases where the nullingrels get removed again
@@ -2661,14 +2662,16 @@ pullup_replace_vars_callback(Var *var,
*
* Note that we don't force wrapping of expressions containing
* lateral references, so long as they also contain Vars/PHVs
- * of the subquery. This is okay because of the restriction
- * to strict constructs: if the subquery's Vars/PHVs have been
- * forced to NULL by an outer join then the end result of the
- * expression will be NULL too, regardless of the lateral
- * references. So it's not necessary to force the expression
- * to be evaluated below the outer join. This can be a very
- * valuable optimization, because it may allow us to avoid
- * using a nested loop to pass the lateral reference down.
+ * of the subquery, or of rels that are under the same lowest
+ * nulling outer join as the subquery. This is okay because
+ * of the restriction to strict constructs: if those Vars/PHVs
+ * have been forced to NULL by an outer join then the end
+ * result of the expression will be NULL too, regardless of
+ * the lateral references. So it's not necessary to force the
+ * expression to be evaluated below the outer join. This can
+ * be a very valuable optimization, because it may allow us to
+ * avoid using a nested loop to pass the lateral reference
+ * down.
*
* This analysis could be tighter: in particular, a non-strict
* construct hidden within a lower-level PlaceHolderVar is not
@@ -2679,10 +2682,40 @@ pullup_replace_vars_callback(Var *var,
* membership of the node, but if it's non-lateral then any
* level-zero var must belong to the subquery.
*/
- if ((rcon->target_rte->lateral ?
- bms_overlap(pull_varnos(rcon->root, newnode),
- rcon->relids) :
- contain_vars_of_level(newnode, 0)) &&
+ bool contain_nullable_vars = false;
+
+ if (!rcon->target_rte->lateral)
+ {
+ if (contain_vars_of_level(newnode, 0))
+ contain_nullable_vars = true;
+ }
+ else
+ {
+ Relids all_varnos;
+
+ all_varnos = pull_varnos(rcon->root, newnode);
+ if (bms_overlap(all_varnos, rcon->relids))
+ contain_nullable_vars = true;
+ else
+ {
+ nullingrel_info *nullinfo = rcon->nullinfo;
+ int varno;
+
+ varno = -1;
+ while ((varno = bms_next_member(all_varnos, varno)) >= 0)
+ {
+ Assert(varno > 0 && varno <= nullinfo->rtlength);
+ if (bms_is_subset(nullinfo->nullingrels[rcon->varno],
+ nullinfo->nullingrels[varno]))
+ {
+ contain_nullable_vars = true;
+ break;
+ }
+ }
+ }
+ }
+
+ if (contain_nullable_vars &&
!contain_nonstrict_functions(newnode))
{
/* No wrap needed */
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index b997eb76909..857ba1c40c0 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1848,6 +1848,109 @@ order by 1, 2;
4567890123456789 | 9135780246913578
(11 rows)
+-- strict expressions containing variables of rels under the same lowest
+-- nulling outer join can escape being wrapped
+explain (verbose, costs off)
+select t1.q1, x from
+ int8_tbl t1 left join
+ (int8_tbl t2 inner join
+ lateral (select t2.q1+1 as x, * from int8_tbl t3) t3 on t2.q2 = t3.q2)
+ on t1.q2 = t2.q2
+order by 1, 2;
+ QUERY PLAN
+--------------------------------------------------------
+ Sort
+ Output: t1.q1, ((t2.q1 + 1))
+ Sort Key: t1.q1, ((t2.q1 + 1))
+ -> Hash Right Join
+ Output: t1.q1, (t2.q1 + 1)
+ Hash Cond: (t2.q2 = t1.q2)
+ -> Hash Join
+ Output: t2.q1, t2.q2
+ Hash Cond: (t2.q2 = t3.q2)
+ -> Seq Scan on public.int8_tbl t2
+ Output: t2.q1, t2.q2
+ -> Hash
+ Output: t3.q2
+ -> Seq Scan on public.int8_tbl t3
+ Output: t3.q2
+ -> Hash
+ Output: t1.q1, t1.q2
+ -> Seq Scan on public.int8_tbl t1
+ Output: t1.q1, t1.q2
+(19 rows)
+
+select t1.q1, x from
+ int8_tbl t1 left join
+ (int8_tbl t2 inner join
+ lateral (select t2.q1+1 as x, * from int8_tbl t3) t3 on t2.q2 = t3.q2)
+ on t1.q2 = t2.q2
+order by 1, 2;
+ q1 | x
+------------------+------------------
+ 123 | 124
+ 123 | 124
+ 123 | 124
+ 123 | 4567890123456790
+ 123 | 4567890123456790
+ 4567890123456789 | 124
+ 4567890123456789 | 124
+ 4567890123456789 | 4567890123456790
+ 4567890123456789 | 4567890123456790
+ 4567890123456789 | 4567890123456790
+ 4567890123456789 | 4567890123456790
+(11 rows)
+
+-- otherwise we need to wrap the strict expressions
+explain (verbose, costs off)
+select t1.q1, x from
+ int8_tbl t1 left join
+ (int8_tbl t2 left join
+ lateral (select t2.q1+1 as x, * from int8_tbl t3) t3 on t2.q2 = t3.q2)
+ on t1.q2 = t2.q2
+order by 1, 2;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Output: t1.q1, ((t2.q1 + 1))
+ Sort Key: t1.q1, ((t2.q1 + 1))
+ -> Hash Right Join
+ Output: t1.q1, ((t2.q1 + 1))
+ Hash Cond: (t2.q2 = t1.q2)
+ -> Nested Loop Left Join
+ Output: t2.q2, ((t2.q1 + 1))
+ -> Seq Scan on public.int8_tbl t2
+ Output: t2.q1, t2.q2
+ -> Seq Scan on public.int8_tbl t3
+ Output: t3.q2, (t2.q1 + 1)
+ Filter: (t2.q2 = t3.q2)
+ -> Hash
+ Output: t1.q1, t1.q2
+ -> Seq Scan on public.int8_tbl t1
+ Output: t1.q1, t1.q2
+(17 rows)
+
+select t1.q1, x from
+ int8_tbl t1 left join
+ (int8_tbl t2 left join
+ lateral (select t2.q1+1 as x, * from int8_tbl t3) t3 on t2.q2 = t3.q2)
+ on t1.q2 = t2.q2
+order by 1, 2;
+ q1 | x
+------------------+------------------
+ 123 | 124
+ 123 | 124
+ 123 | 124
+ 123 | 4567890123456790
+ 123 | 4567890123456790
+ 4567890123456789 | 124
+ 4567890123456789 | 124
+ 4567890123456789 | 4567890123456790
+ 4567890123456789 | 4567890123456790
+ 4567890123456789 | 4567890123456790
+ 4567890123456789 | 4567890123456790
+(11 rows)
+
-- lateral references for simple Vars can escape being wrapped if the
-- referenced rel is under the same lowest nulling outer join
explain (verbose, costs off)
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index af5a1d8b55a..a6ab42454f0 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -939,6 +939,39 @@ select t1.q1, x from
on t1.q2 = t2.q2
order by 1, 2;
+-- strict expressions containing variables of rels under the same lowest
+-- nulling outer join can escape being wrapped
+explain (verbose, costs off)
+select t1.q1, x from
+ int8_tbl t1 left join
+ (int8_tbl t2 inner join
+ lateral (select t2.q1+1 as x, * from int8_tbl t3) t3 on t2.q2 = t3.q2)
+ on t1.q2 = t2.q2
+order by 1, 2;
+
+select t1.q1, x from
+ int8_tbl t1 left join
+ (int8_tbl t2 inner join
+ lateral (select t2.q1+1 as x, * from int8_tbl t3) t3 on t2.q2 = t3.q2)
+ on t1.q2 = t2.q2
+order by 1, 2;
+
+-- otherwise we need to wrap the strict expressions
+explain (verbose, costs off)
+select t1.q1, x from
+ int8_tbl t1 left join
+ (int8_tbl t2 left join
+ lateral (select t2.q1+1 as x, * from int8_tbl t3) t3 on t2.q2 = t3.q2)
+ on t1.q2 = t2.q2
+order by 1, 2;
+
+select t1.q1, x from
+ int8_tbl t1 left join
+ (int8_tbl t2 left join
+ lateral (select t2.q1+1 as x, * from int8_tbl t3) t3 on t2.q2 = t3.q2)
+ on t1.q2 = t2.q2
+order by 1, 2;
+
-- lateral references for simple Vars can escape being wrapped if the
-- referenced rel is under the same lowest nulling outer join
explain (verbose, costs off)
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-20 17:34:18 +0000