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@gmail.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