Re: Add semi-join pushdown to postgres_fdw

Hi, Alexander!

On Tue, Mar 18, 2025 at 6:04 PM Alexander Pyhalov
<a(dot)pyhalov(at)postgrespro(dot)ru> wrote:
> This shouldn't. When semi-join is found below left/right join, it's
> deparsed as subquery.
> Interesting enough, this mechanics (deparsing as subquery) is used
> 1) for semi-joins under left/right join,
> 2) for full outer joins when inner or outer part has some remote_conds.
>
> The issue here is that after subquery is deparsed, we don't consider if
> its target attributes are available to the upper level
> join . As for semi-join itself, all conditions are still deparsed on
> left/right join boundary, they are just not propagated further.
> This shouldn't be a problem, as they are evaluated in subquery. As for
> left/right join without semi-join beneath it - its behavior is not
> affected
> (as hidden_subquery_rels is empty).

Thank you for the explanation. But I have another question. Aren't
the checks you've proposed too strict? hidden_subquery_rels are
propagated all the way to the join tree. So, pulling conditions would
be disables all the way to the join tree too. Is it enough to just
disable pulling conditions directly from semi-joins, then their
further pulls will be disabled automatically? See the attached patch.
It also contains other (mostly cosmetic improvements).

------
Regards,
Alexander Korotkov
Supabase