Avoid unnecessary wrapping for more complex expressions

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Avoid unnecessary wrapping for more complex expressions
Date: 2024-12-11 07:31:45
Message-ID: CAMbWs4_ENtfRdLaM_bXAxiKRYO7DmwDBDG4_2=VTDi0mJP-jAw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Avoid-unnecessary-wrapping-for-more-complex-expressions.patch application/octet-stream 11.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bertrand Drouvot 2024-12-11 07:32:38 Re: Fix comments related to pending statistics
Previous Message John Naylor 2024-12-11 07:08:58 Re: CRC32C Parallel Computation Optimization on ARM