Horton

Re: Asynchronous Append on postgres_fdw nodes.

Andrei Lepikhov <a.lepikhov@postgrespro.ru>

View thread View on postgr.es
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>, pryzby@telsasoft.com
Cc: etsuro.fujita@gmail.com, movead.li@highgo.ca, pgsql-hackers@lists.postgresql.org
Date: 2020-09-25T18:34:04Z
Lists: pgsql-hackers 
Your AsyncAppend doesn't switch to another source if the data in current 
leader is available:

/*
  * The request for the next node cannot be sent before the leader
  * responds. Finish the current leader if possible.
  */
if (PQisBusy(leader_state->s.conn))
{
   int rc = WaitLatchOrSocket(NULL, WL_SOCKET_READABLE | WL_TIMEOUT | 
WL_EXIT_ON_PM_DEATH, PQsocket(leader_state->s.conn), 0, 
WAIT_EVENT_ASYNC_WAIT);
   if (!(rc & WL_SOCKET_READABLE))
     available = false;
}

/* fetch the leader's data and enqueue it for the next request */
if (available)
{
   fetch_received_data(leader);
   add_async_waiter(leader);
}

I don't understand, why it is needed. If we have fdw connections with 
different latency, then we will read data from the fast connection 
first. I think this may be a source of skew and decrease efficiency of 
asynchronous append.

For example, see below synthetic query:
CREATE TABLE l (a integer) PARTITION BY LIST (a);
CREATE FOREIGN TABLE f1 PARTITION OF l FOR VALUES IN (1) SERVER lb 
OPTIONS (table_name 'l1');
CREATE FOREIGN TABLE f2 PARTITION OF l FOR VALUES IN (2) SERVER lb 
OPTIONS (table_name 'l2');

INSERT INTO l (a) SELECT 2 FROM generate_series(1,200) as gs;
INSERT INTO l (a) SELECT 1 FROM generate_series(1,1000) as gs;

EXPLAIN ANALYZE (SELECT * FROM f1) UNION ALL (SELECT * FROM f2) LIMIT 400;

Result:
Limit  (cost=100.00..122.21 rows=400 width=4) (actual time=0.483..1.183 
rows=400 loops=1)
    ->  Append  (cost=100.00..424.75 rows=5850 width=4) (actual 
time=0.482..1.149 rows=400 loops=1)
          ->  Foreign Scan on f1  (cost=100.00..197.75 rows=2925 
width=4) (actual time=0.481..1.115 rows=400 loops=1)
          ->  Foreign Scan on f2  (cost=100.00..197.75 rows=2925 
width=4) (never executed)

As you can see, executor scans one input and doesn't tried to scan another.

-- 
regards,
Andrey Lepikhov
Postgres Professional

Commits

  1. Improve comments for trivial_subqueryscan().

  2. Disable asynchronous execution if using gating Result nodes.

  3. Allow asynchronous execution in more cases.

  4. Doc: Further update documentation for asynchronous execution.

  5. Fix rescanning of async-aware Append nodes.

  6. Doc: Update documentation for asynchronous execution.

  7. Fix EXPLAIN ANALYZE for async-capable nodes.

  8. Minor code cleanup in asynchronous execution support.

  9. Adjust input value to WaitEventSetWait() in ExecAppendAsyncEventWait().

  10. Add support for asynchronous execution.