Horton

Thread

Commits

  1. Avoid unnecessary wrapping for more complex expressions

  2. Avoid unnecessary wrapping for Vars and PHVs

  1. Avoid unnecessary wrapping for more complex expressions

    Richard Guo <guofenglinux@gmail.com> — 2024-12-11T07:31:45Z

    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

  2. Re: Avoid unnecessary wrapping for more complex expressions

    Richard Guo <guofenglinux@gmail.com> — 2024-12-17T11:09:22Z

    On Wed, Dec 11, 2024 at 4:31 PM Richard Guo <guofenglinux@gmail.com> wrote:
> 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.

> Any thoughts?

I’ve gone ahead and pushed this patch.

Thanks
Richard