Subject: Re: Asynchronous Append on postgres_fdw nodes.
To: Stephen Frost <sfrost@snowman.net>,
 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: 
 <CAPmGK15Hvh5x_cOYNjdu3FjY-owxXfv3bdBiL20UPmJs6PAS3g@mail.gmail.com>
 <CAPmGK15Td6P41zq1Uy9_DOuwF_UCZsTAL6k+RAyy3g0ROe-otw@mail.gmail.com>
 <CAPmGK1425Phh0VDFNuU1sANF=OAtrzk4noDPtNXJdip1Wnh_pA@mail.gmail.com>
 <20210218.151557.1106337659785292399.horikyota.ntt@gmail.com>
 <CAPmGK14dGe3i7kF_2tz59Gb11P+_HQOeeQLP_ER4BMN5nEJzYg@mail.gmail.com>
 <CAPmGK17kzTxbS=Av_HN3kKgeWTrt9Kwz5eO_UtKN1nDYLru=6Q@mail.gmail.com>
 <CAPmGK17Ap6AGTFrtn3==PsVfHUkuiRPFXZqXSQ=XWQDtDbNNBQ@mail.gmail.com>
 <CAPmGK14FqDS-u_Vdfwrha5tZPh3w=PXj7pqmFF53Sn2W8YtSkg@mail.gmail.com>
 <20210506171224.GV20766@tamriel.snowman.net>
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
Message-ID: <c4ec1aa8-282a-f497-d544-e0668486cd53@postgrespro.ru>
Date: Fri, 7 May 2021 14:59:52 +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: <20210506171224.GV20766@tamriel.snowman.net>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/c4ec1aa8-282a-f497-d544-e0668486cd53%40postgrespro.ru>
Precedence: bulk

On 6/5/21 22:12, Stephen Frost wrote:
> * Etsuro Fujita (etsuro.fujita@gmail.com) wrote:
>> I think the user should be careful about this.  How about adding a
>> note about it to the “Asynchronous Execution Options” section in
>> postgres-fdw.sgml, like the attached?
+1
> ... then again, it'd really be better if we could figure out a way to
> just do the right thing here.  I haven't looked at this in depth but I
> would think that the overhead of async would be well worth it just about
> any time there's more than one foreign server involved.  Is it not
> reasonable to have a heuristic where we disable async in the cases where
> there's only one foreign server, but have it enabled all the other time?
> While continuing to allow users to manage it explicitly if they want.
Bechmarking of SELECT from foreign partitions hosted on the same server, 
i see results:

With async append:
1 partition - 178 ms; 4 - 263; 8 - 450; 16 - 860; 32 - 1740.

Without:
1 - 178 ms; 4 - 583; 8 - 1140; 16 - 2302; 32 - 4620.

So, these results show that we have a reason to use async append in the 
case where there's only one foreign server.

-- 
regards,
Andrey Lepikhov
Postgres Professional