Re: Asynchronous Append on postgres_fdw nodes.

On Tue, May 11, 2021 at 6:55 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Tue, May 11, 2021 at 6:27 PM Andrey Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
> > On 11/5/21 12:24, Etsuro Fujita wrote:
>
> > >>      ->  Append (actual rows=3000 loops=1)
> > >>            ->  Async Foreign Scan on f1 (actual rows=0 loops=1)
> > >>            ->  Async Foreign Scan on f2 (actual rows=0 loops=1)
> > >>            ->  Foreign Scan on f3 (actual rows=3000 loops=1)
> > >>
> > >> Here we give preference to the synchronous scan. Why?
> > >
> > > This would be expected behavior, and the reason is avoid performance
> > > degradation; you might think it would be better to execute the async
> > > Foreign Scan nodes more aggressively, but it would require
> > > waiting/polling for file descriptor events many times, which is
> > > expensive and might cause performance degradation.  I think there is
> > > room for improvement, though.
> > Yes, I agree with you. Maybe you can add note in documentation on
> > async_capable, for example:
> > "... Synchronous and asynchronous scanning strategies can be mixed by
> > optimizer in one scan plan of a partitioned table or an 'UNION ALL'
> > command. For performance reasons, synchronous scans executes before the
> > first of async scan. ..."
>
> +1  But I think this is an independent issue, so I think it would be
> better to address the issue separately.

I think that since postgres-fdw.sgml would be for users rather than
developers, unlike fdwhandler.sgml, it would be better to explain this
more in a not-too-technical way.  So how about something like this?

Asynchronous execution is applied even when an Append node contains
subplan(s) executed synchronously as well as subplan(s) executed
asynchronously.  In that case, if the asynchronous subplans are ones
executed using postgres_fdw, tuples from the asynchronous subplans are
not returned until after at least one synchronous subplan returns all
tuples, as that subplan is executed while the asynchronous subplans
are waiting for the results of queries sent to foreign servers.  This
behavior might change in a future release.

Best regards,
Etsuro Fujita