Re: Draft LIMIT pushdown to Append and MergeAppend patch

> On 9 Oct 2023, at 15:04, Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>
> On Mon, Oct 9, 2023 at 4:33 PM David Rowley <dgrowleyml(at)gmail(dot)com <mailto:dgrowleyml(at)gmail(dot)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