> On 9 Oct 2023, at 15:04, Ashutosh Bapat <ashutosh.bapat....@gmail.com> wrote: > > On Mon, Oct 9, 2023 at 4:33 PM David Rowley <dgrowle...@gmail.com > <mailto:dgrowle...@gmail.com>> wrote: >> >> What are there benefits if the paths are already ordered? e.g if it's >> an index scan then we'll only pull the tuples we need from it. >> > > postgres_fdw creates a path with pathkeys based on the clauses on that > relation. There is no index involved there. Pushing down LIMIT will > limit the number of rows fetched from the foreign server and the > foreign server may have opportunity to optimize query based on the > LIMIT.
I would add another benefit: opportunity to fetch everything early, buffer it and release the session. Without limit information fdw has to keep cursors open. — Michal
