In commit f64ec81a8 we introduced an optimization that avoids wrapping for Vars and PHVs if they are lateral references to something outside the subquery, and the referenced rel is under the same lowest nulling outer join. It could be beneficial to get rid of such PHVs because they imply lateral dependencies, which force us to resort to nestloop joins.
As mentioned in that thread, I feel that we can apply a similar
optimization to more complex non-var expressions: if a strict
expression contains any variables of rels that are under the same
lowest nulling outer join as the subquery, we can also avoid wrapping
it.
The rationale behind is that if the subquery variable is forced to
NULL by the outer join, the variables of rels that are under the same
lowest nulling outer join will also be forced to NULL, resulting in
the expression evaluating to NULL as well. So it's not necessary to
force the expression to be evaluated below the outer join. As an
example, consider
explain (costs off)
select * from t t1 left join
(t t2 inner join
lateral (select t2.a+1 as x, * from t t3) s on t2.a = s.a)
on t1.b = t2.b;
QUERY PLAN
------------------------------------
Hash Right Join
Hash Cond: (t2.b = t1.b)
-> Hash Join
Hash Cond: (t2.a = t3.a)
-> Seq Scan on t t2
-> Hash
-> Seq Scan on t t3
-> Hash
-> Seq Scan on t t1
(9 rows)
If s.x is forced to NULL by the left join, t2.a will also be forced to
NULL, and 't2.a+1' will come out as NULL as well because of the
restriction to strict constructs, so we do not need to wrap it in a
PHV.
Any thoughts?
Thanks
Richard
v1-0001-Avoid-unnecessary-wrapping-for-more-complex-expressions.patch
Description: Binary data
