Re: Add semi-join pushdown to postgres_fdw

On Mon, Mar 24, 2025 at 9:07 AM Alexander Pyhalov
<a(dot)pyhalov(at)postgrespro(dot)ru> wrote:
> Alexander Korotkov писал(а) 2025-03-24 04:21:
> > 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
>
> Hi. No, they are not too strict. Look at the following example
>
> EXPLAIN (verbose, costs off)
> SELECT x1.c1 FROM
> (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE
> ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
> RIGHT JOIN
> (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE
> ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
> ON (x1.c1 = x2.c1)
> LEFT JOIN
> (SELECT * FROM ft2 WHERE c2 < 11) x3
> ON (x1.c1 = x3.c1)
> ORDER BY x1.c1 LIMIT 10;
>
> With patch which you suggest, we'll deparse left part of left join as
> subquery, but will try to pop c2 < 10 condition from
> (8) LEFT JOIN ((6) SEMI JOIN (7)) subquery. When we look at left join of
> this subquery and ft2, we still deparse left part as
> subquery, so can't pop up conditions from it.

I've checked, this query seems to result in the exactly same remote
SQLs with your and mine patches. Could you elaborate more on the
difference? Do you think foreign_join_ok() can give different results
on this query?

------
Regards,
Alexander Korotkov
Supabase