Re: Asynchronous Append on postgres_fdw nodes.

On Thu, Mar 4, 2021 at 1:00 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> 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

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?

Best regards,
Etsuro Fujita