Re: Asynchronous Append on postgres_fdw nodes.

Andrei Lepikhov <a.lepikhov@postgrespro.ru>

From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>

To: Etsuro Fujita <etsuro.fujita@gmail.com>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>

Cc: Justin Pryzby <pryzby@telsasoft.com>, "movead.li" <movead.li@highgo.ca>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

Date: 2020-10-08T09:39:47Z

Lists: pgsql-hackers

Attachments

test1.sql (application/sql)
asyncmix.patch (text/x-patch) patch

On 10/5/20 11:35 AM, Etsuro Fujita wrote:
Hi,
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)

Reproduction script 'test1.sql' see in attachment. Here I force the 
problem reproduction with setting enable_hashjoin and enable_mergejoin 
to off.

'asyncmix.patch' contains my solution to this problem.

-- 
regards,
Andrey Lepikhov
Postgres Professional

Commits

Improve comments for trivial_subqueryscan().
- 4a8a5dd7f59c 15.0 landed
Disable asynchronous execution if using gating Result nodes.
- ebb790241523 14.3 landed
- 5c854e7a2c8a 15.0 landed
Allow asynchronous execution in more cases.
- c2bb02bc2e85 15.0 landed
Doc: Further update documentation for asynchronous execution.
- eab81953682d 14.0 landed
Fix rescanning of async-aware Append nodes.
- f3baaf28a6da 14.0 landed
Doc: Update documentation for asynchronous execution.
- 15fcd33e0694 14.0 landed
Fix EXPLAIN ANALYZE for async-capable nodes.
- a363bc6da96b 14.0 landed
Minor code cleanup in asynchronous execution support.
- bb684c82f733 14.0 landed
Adjust input value to WaitEventSetWait() in ExecAppendAsyncEventWait().
- a8af856d3257 14.0 landed
Add support for asynchronous execution.
- 27e1f14563cf 14.0 landed