Re: Add semi-join pushdown to postgres_fdw

Alexander Korotkov писал(а) 2025-03-24 11:49:
> 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?

Hi.
With your patch this example gives the same
ERROR: unexpected expression in subquery output

This happens, because we don't keep knowledge that we have deparsed all
semi-joins below this left join. As long as left/right join has
semi-join in its left or right part, this part will be deparsed as
subquery (look at the following lines in foreign_join_ok()):

else if (jointype == JOIN_LEFT || jointype == JOIN_RIGHT ||
jointype == JOIN_FULL)
{
/*
* Conditions, generated from semi-joins, should be
evaluated before
* LEFT/RIGHT/FULL join.
*/
if (!bms_is_empty(fpinfo_o->hidden_subquery_rels))
{
fpinfo->make_outerrel_subquery = true;
fpinfo->lower_subquery_rels =
bms_add_members(fpinfo->lower_subquery_rels, outerrel->relids);
}

if (!bms_is_empty(fpinfo_i->hidden_subquery_rels))
{
fpinfo->make_innerrel_subquery = true;
fpinfo->lower_subquery_rels =
bms_add_members(fpinfo->lower_subquery_rels, innerrel->relids);
}
}

So, we still can't refer to its remote_conds from upper level queries
(as not all Vars are available from subquery after subquery is created
in one part of left or right join). It's not necessary to have semi-join
for this as immediate left/right join inner or outer for inner/outer to
be deparsed as subquery. But it shouldn't be an issue - we've already
used remote_conds when created this subquery.
What I'm trying to say - logic of 'making subquery' and extracting
conditions should match (or we need more sophisticated way of forming
subquery targetlist, so that extracted conditions could be used above
subqueries).

--
Best regards,
Alexander Pyhalov,
Postgres Professional