Re: Asynchronous Append on postgres_fdw nodes.

On Mon, Mar 1, 2021 at 5:56 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> Here is an updated version of the patch.

Another thing I'm concerned about in the postgres_fdw part is the case
where all/many postgres_fdw ForeignScans of an Append use the same
connection, because in that case those ForeignScans are executed one
by one, not in parallel, and hence the overhead of async execution
(i.e., doing ExecAppendAsyncEventWait()) would merely cause a
performance degradation. Here is such an example:

postgres=# create server loopback foreign data wrapper postgres_fdw
options (dbname 'postgres');
postgres=# create user mapping for current_user server loopback;
postgres=# create table pt (a int, b int, c text) partition by range (a);
postgres=# create table loct1 (a int, b int, c text);
postgres=# create table loct2 (a int, b int, c text);
postgres=# create table loct3 (a int, b int, c text);
postgres=# create foreign table p1 partition of pt for values from
(10) to (20) server loopback options (table_name 'loct1');
postgres=# create foreign table p2 partition of pt for values from
(20) to (30) server loopback options (table_name 'loct2');
postgres=# create foreign table p3 partition of pt for values from
(30) to (40) server loopback options (table_name 'loct3');
postgres=# insert into p1 select 10 + i % 10, i, to_char(i, 'FM00000')
from generate_series(0, 99999) i;
postgres=# insert into p2 select 20 + i % 10, i, to_char(i, 'FM00000')
from generate_series(0, 99999) i;
postgres=# insert into p3 select 30 + i % 10, i, to_char(i, 'FM00000')
from generate_series(0, 99999) i;
postgres=# analyze pt;

postgres=# set enable_async_append to off;
postgres=# select count(*) from pt;
count
--------
300000
(1 row)

Time: 366.905 ms

postgres=# set enable_async_append to on;
postgres=# select count(*) from pt;
count
--------
300000
(1 row)

Time: 385.431 ms

People would use postgres_fdw to access old partitions archived in a
single remote server. So the same degradation would be likely to
happen in such a use case. To avoid that, how about 1) adding the
table/server options to postgres_fdw that allow/disallow async
execution, and 2) setting them to false by default?

Best regards,
Etsuro Fujita