Re: Add semi-join pushdown to postgres_fdw

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.
--
Best regards,
Alexander Pyhalov,
Postgres Professional