Subject: Re: Asynchronous Append on postgres_fdw nodes.
To: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 Justin Pryzby <pryzby@telsasoft.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: 
 <CAPmGK14igABfEruPPh1X=9KnjY=LquRHQij48oATSo1vWr7yOg@mail.gmail.com>
 <CAPmGK15xtFk2gb9-2b24mFtYBVWoGkmCPFPkM9RHpKVXGLk=sw@mail.gmail.com>
 <CAPmGK178d9-+aFgYorOY4n0pZmZ55WpQ7i1JcCagovYzAh+ueg@mail.gmail.com>
 <20210331.101137.148208859959175708.horikyota.ntt@gmail.com>
 <CAPmGK17T9Swy2FHZk4AVVKNzRxVnd96PX-18DSC2jeDXWNiBoQ@mail.gmail.com>
 <CAPmGK1637W30Wx3MnrReewhafn6F_0J76mrJGoFXFnpPq4QfvA@mail.gmail.com>
 <CAPmGK17O6fMfX84wWfypLWAO4NvD4fgDhm--oyzZ97YP+TxckA@mail.gmail.com>
 <2eb662bb-105d-fc20-7412-2f027cc3ca72@postgrespro.ru>
 <CAPmGK146cxSJyfM4Xv2jGTsF0ghmPEtwA7dS5_4cKo1oF6+ipw@mail.gmail.com>
 <CAPmGK17yjZg2ajhs3erpfOKj+sg6yZU_gwvaeOV+j8g2E2bdcA@mail.gmail.com>
 <a38bb206-8340-9528-5ef6-37de2d5cb1a3@postgrespro.ru>
 <CAPmGK16R=U2ok=H_QnibLS297_vmt68b6bJ8OYJzQ0DxMo+gyw@mail.gmail.com>
 <2080193d-da8d-4132-f48c-8e29be8fcffc@postgrespro.ru>
 <CAPmGK15CyEmstJyzkGsT_1OmArJwD6vQguBEwiz8oYx4zz6ssA@mail.gmail.com>
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
Message-ID: <58fa2aa5-07f5-80b5-59a1-fec8a349fee7@postgrespro.ru>
Date: Tue, 11 May 2021 14:27:10 +0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:78.0)
 Gecko/20100101 Thunderbird/78.10.1
MIME-Version: 1.0
In-Reply-To: 
 <CAPmGK15CyEmstJyzkGsT_1OmArJwD6vQguBEwiz8oYx4zz6ssA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/58fa2aa5-07f5-80b5-59a1-fec8a349fee7%40postgrespro.ru>
Precedence: bulk

On 11/5/21 12:24, Etsuro Fujita wrote:
> On Tue, May 11, 2021 at 11:58 AM Andrey Lepikhov
> The extra tuple, which is from f1 or f2, would have been kept in the
> Append node's as_asyncresults, not returned from the Append node to
> the Limit node.  The async Foreign Scan nodes would fetch tuples
> before the Append node ask the tuples, so the fetched tuples may or
> may not be used.
Ok.>>      ->  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. ..."

-- 
regards,
Andrey Lepikhov
Postgres Professional