postgres_fdw prefers fast plans
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t52864psql -h localhost -U postgresBuilt from patchset v3 (message #3), September 20, 2026 at 02:13 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t52864_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t52864_3 && git checkout t52864_3Patchset v3 (message #3) is on t52864_3
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)
/messages/by-id/CA+FpmFcmO5ctjYgQxSomJC=mCugqPo+51Le2wdxX0kWxjvBBig@mail.gmail.com
--
Best regards,
Alexander Pyhalov,
Postgres Professional
Attachments:
0001-Add-DECLARE-CURSOR-.-FETCH-ALL-and-use-it-in-postgre.patchtext/x-diff; name=0001-Add-DECLARE-CURSOR-.-FETCH-ALL-and-use-it-in-postgre.patchDownload+281-153
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