Re: Unnecessary lateral dependencies implied by PHVs

On Tue, Oct 18, 2022 at 9:15 AM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

> On Mon, Oct 10, 2022 at 10:35 AM Richard Guo <guofenglinux(at)gmail(dot)com>
> wrote:
>
>> ... I'm asking because
>> PHVs may imply lateral dependencies which may make us have to use
>> nestloop join.
>>
>
> I thought lateral join imply nestloop join, am I missing something? Here
> is my simple
> testing.
>

I think it's true most of the time. And that's why we should try to
avoid unnecessary lateral dependencies, as discussed in this thread.

ISTM in your example nestloop is chosen because there is no available
mergejoinable/hashjoinable clause. In your query the lateral subquery
would be pulled up into the parent query and there would be no lateral
dependencies afterwards.

Thanks
Richard