Thread

Re: Add semi-join pushdown to postgres_fdw

Alexander Pyhalov <a.pyhalov@postgrespro.ru> — 2025-03-18T16:04:49Z

Alexander Korotkov писал(а) 2025-03-18 14:19:
> Hi, Alexander!
> 
> On Tue, Mar 18, 2025 at 1:13 PM Alexander Pyhalov
> <a.pyhalov@postgrespro.ru> wrote:
>> Alexander Korotkov писал(а) 2025-03-18 03:27:
>> > Hi, Robins!
>> >
>> > On Tue, Mar 18, 2025 at 2:20 AM Robins Tharakan <tharakan@gmail.com>
>> > wrote:
>> >> On Mon, 4 Dec 2023 at 07:22, Alexander Korotkov <aekorotkov@gmail.com>
>> >> wrote:
>> >> >
>> >> >
>> >> > Now, I think this looks good.  I'm going to push this if no objections.
>> >>
>> >> After this commit, I began seeing an unexpected ERROR - see this
>> >> bug-report.
>> >> https://www.postgresql.org/message-id/18852-fb75b88160678f78%40postgresql.org
>> >
>> > Thank you for pointing.
>> > I'll check this in the next couple of days.
>> >
>> 
>> It seems conditions, coming from semi-joins, are handled incorrectly
>> under left and right join. When deparsing left/right joins and there 
>> are
>> semi-joins in inner or outer part of the query, the corresponding part
>> is deparsed as subquery. And we can't refer subquery vars from above, 
>> so
>> just should not pull up the restrictinfos. The attached patch does
>> exactly this.
> 
> Thank you for the fix.  It looks like this fix generally affects
> left/right joins, not just semi-joins.  Can you confirm this doesn't
> cause regression for other cases?
> 

Hi.
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).

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional