Re: An inefficient query caused by unnecessary PlaceHolderVar

On 12/2/24 10:46, Richard Guo wrote:
> On Wed, Nov 27, 2024 at 5:45 PM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
>> I ended up using 'under the same lowest nulling outer join' to
>> keep consistent with the wording used elsewhere. Please see the
>> updated patch attached.
>
> Commit e032e4c7d computes the nullingrel data for each leaf RTE, and
> we can leverage that to determine if the referenced rel is under the
> same lowest nulling outer join: we just need to check if the
> nullingrels of the subquery RTE are a subset of those of the lateral
> referenced rel. This eliminates the need to introduce
> lowest_nullable_side. Please see attached.
Thanks for drawing attention to e032e4c7d. It is a really helpful
structure. I remember last year, we discussed [1] one sophisticated
subquery pull-up technique, and we needed exactly the same data - it was
too invasive to commit, and we committed only a small part of it. The
nullingrel_info structure may give this feature one more chance.

A couple of words about your patch. These few lines of code caused a lot
of discoveries, but in my opinion, they look fine. But I didn't find
negative tests, where we need to wrap a Var with PHV like the following:

explain (verbose, costs off)
select t1.q1, x from
int8_tbl t1 left join
(int8_tbl t2 left join
lateral (select t2.q2 as x, * from int8_tbl t3) ss on t2.q2 = ss.q1)
on t1.q1 = t2.q1
order by 1, 2;

If regression tests doesn't contain such check it would be nice to add.

[1]
https://www.postgresql.org/message-id/35c8a3e8-d080-dfa8-2be3-cf5fe702010a%40postgrespro.ru

--
regards, Andrei Lepikhov