Re: Asynchronous Append on postgres_fdw nodes.

On Thu, Oct 8, 2020 at 6:39 PM Andrey V. Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> I found a small problem. If we have a mix of async and sync subplans
> when we catch an assertion on a busy connection. Just for example:
>
> PLAN
> ====
> Nested Loop  (cost=100.00..174316.95 rows=975 width=8) (actual
> time=5.191..9.262 rows=9 loops=1)
>     Join Filter: (frgn.a = l.a)
>     Rows Removed by Join Filter: 8991
>     ->  Append  (cost=0.00..257.20 rows=11890 width=4) (actual
> time=0.419..2.773 rows=1000 loops=1)
>           Async subplans: 4
>           ->  Async Foreign Scan on f_1 l_2  (cost=100.00..197.75
> rows=2925 width=4) (actual time=0.381..0.585 rows=211 loops=1)
>           ->  Async Foreign Scan on f_2 l_3  (cost=100.00..197.75
> rows=2925 width=4) (actual time=0.005..0.206 rows=195 loops=1)
>           ->  Async Foreign Scan on f_3 l_4  (cost=100.00..197.75
> rows=2925 width=4) (actual time=0.003..0.282 rows=187 loops=1)
>           ->  Async Foreign Scan on f_4 l_5  (cost=100.00..197.75
> rows=2925 width=4) (actual time=0.003..0.316 rows=217 loops=1)
>           ->  Seq Scan on l_0 l_1  (cost=0.00..2.90 rows=190 width=4)
> (actual time=0.017..0.057 rows=190 loops=1)
>     ->  Materialize  (cost=100.00..170.94 rows=975 width=4) (actual
> time=0.001..0.002 rows=9 loops=1000)
>           ->  Foreign Scan on frgn  (cost=100.00..166.06 rows=975
> width=4) (actual time=0.766..0.768 rows=9 loops=1)

Actually I also found a similar issue before [1].  But in the first
place I'm not sure the way of handling concurrent data fetches by
multiple ForeignScan nodes using the same connection in postgres_fdw
implemented in Horiguchi-san's patch would be really acceptable,
because that would impact performance *negatively* in some cases as
mentioned in [1].  So I feel inclined to just disable this feature in
problematic cases including the above one in the first cut.  Even with
such a limitation, I think it would be useful, because it would cover
typical use cases such as partitionwise joins and partitionwise
aggregates.

Thanks for the report!

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/CAPmGK16E1erFV9STg8yokoewY6E-zEJtLzHUJcQx%2B3dyivCT%3DA%40mail.gmail.com