Unnecessary lateral dependencies implied by PHVs

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Unnecessary lateral dependencies implied by PHVs
Date: 2022-10-10 02:35:04
Message-ID: CAMbWs4--Qy-nLq_Eq61_rsQ3JUYteNh2G8-GnDkt+Fsp_t_Wjw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers,

As we know when we pull up a simple subquery, if the subquery is within
the nullable side of an outer join, lateral references to non-nullable
items may have to be turned into PlaceHolderVars. I happened to wonder
what should we do about the PHVs if the outer join is reduced to inner
join afterwards. Should we unwrap the related PHVs? I'm asking because
PHVs may imply lateral dependencies which may make us have to use
nestloop join. As an example, consider

explain (costs off)
select * from a left join lateral (select a.i as ai, b.i as bi from b) ss
on true where ss.bi = ss.ai;
QUERY PLAN
---------------------------
Nested Loop
-> Seq Scan on a
-> Seq Scan on b
Filter: (i = a.i)
(4 rows)

Although the JOIN_LEFT has been reduced to JOIN_INNER, the lateral
reference implied by the PHV makes us have no choice but the nestloop
with parameterized inner path. Considering there is no index on b, this
plan is very inefficient.

Is there anything we can do to improve this situation?

Thanks
Richard

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Smith 2022-10-10 02:38:26 Re: Support logical replication of DDLs
Previous Message bt22nakamorit 2022-10-10 02:30:03 Re: ps command does not show walsender's connected db