Обсуждение: postgres_fdw prefers fast plans

Hi.

There's a long-standing issue with postgres_fdw - as it uses cursors, it 
prefers plans, optimized for fetching first rows. In bad scenarios this 
leads to suboptimal choice of join methods (e.g. choosing nest loop over 
hash join) on remote side. I've crafted WIP patch, which tries to fix 
this issue. It adds FETCH ALL cursors and uses them in postgres_fdw. 
What do you think? Should we go in this direction?

I've looked at attempt to avoid cursors with postgres_fdw [1], but it 
seems to avoid dealing with async foreign plans (and looks more 
intrusive).


1) 
https://www.postgresql.org/message-id/flat/CA%2BFpmFcmO5ctjYgQxSomJC%3DmCugqPo%2B51Le2wdxX0kWxjvBBig%40mail.gmail.com

-- 
Best regards,
Alexander Pyhalov,
Postgres Professional

On Wed, 2025-12-10 at 15:44 +0300, Alexander Pyhalov wrote:
> There's a long-standing issue with postgres_fdw - as it uses cursors, it
> prefers plans, optimized for fetching first rows. In bad scenarios this
> leads to suboptimal choice of join methods (e.g. choosing nest loop over
> hash join) on remote side. I've crafted WIP patch, which tries to fix
> this issue. It adds FETCH ALL cursors and uses them in postgres_fdw.
> What do you think? Should we go in this direction?

Why not simply set cursor_tuple_fraction to 1.0 in postgres_fdw sessions?

Yours,
Laurenz Albe

Laurenz Albe писал(а) 2025-12-10 16:12:
> On Wed, 2025-12-10 at 15:44 +0300, Alexander Pyhalov wrote:
>> There's a long-standing issue with postgres_fdw - as it uses cursors, 
>> it
>> prefers plans, optimized for fetching first rows. In bad scenarios 
>> this
>> leads to suboptimal choice of join methods (e.g. choosing nest loop 
>> over
>> hash join) on remote side. I've crafted WIP patch, which tries to fix
>> this issue. It adds FETCH ALL cursors and uses them in postgres_fdw.
>> What do you think? Should we go in this direction?
> 
> Why not simply set cursor_tuple_fraction to 1.0 in postgres_fdw 
> sessions?
> 
> Yours,
> Laurenz Albe

Hi.
Haven't thought about this. Thanks for the idea. The only drawback I see 
is that this will affect all cursors, which can be used, for example, by 
shippable functions. But looks much simpler.
-- 
Best regards,
Alexander Pyhalov,
Postgres Professional