Horton

Re: Asynchronous Append on postgres_fdw nodes.

Etsuro Fujita <etsuro.fujita@gmail.com>

View thread View on postgr.es
From: Etsuro Fujita <etsuro.fujita@gmail.com>
To: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Andrey Lepikhov <a.lepikhov@postgrespro.ru>, "movead.li" <movead.li@highgo.ca>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2020-09-30T07:30:41Z
Lists: pgsql-hackers 
On Mon, Sep 28, 2020 at 10:35 AM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
> At Sat, 26 Sep 2020 19:45:39 +0900, Etsuro Fujita <etsuro.fujita@gmail.com> wrote in
> > Your patch (and the original patch by Robert [1]) modified
> > ExecAppend() so that it can process local plan nodes while waiting for
> > the results from remote queries, which would be also a feature that’s
> > not supported by Thomas’ patch, but I’d like to know performance
> > results.

> At least, even though theoretically, I think it's obvious that it's
> performant to do something than just sitting waitng for the next tuple
> to come from abroad.

I did a simple test on my laptop:

create table t1 (a int, b int, c text);
create foreign table p1 (a int, b int, c text) server server1 options
(table_name 't1');
create table p2 (a int, b int, c text);

insert into p1 select 10 + i % 10, i, to_char(i, 'FM00000') from
generate_series(0, 99999) i;
insert into p2 select 20 + i % 10, i, to_char(i, 'FM00000') from
generate_series(0, 99999) i;

analyze p1;
vacuum analyze p2;

create table pt (a int, b int, c text) partition by range (a);
alter table pt attach partition p1 for values from (10) to (20);
alter table pt attach partition p2 for values from (20) to (30);

set enable_partitionwise_aggregate to on;

select a, count(*) from pt group by a;

HEAD: 47.734 ms
With your patch: 32.400 ms

This test is pretty simple, but I think this shows that the mentioned
feature would be useful for cases where it takes time to get the
results from remote queries.

Cool!

Best regards,
Etsuro Fujita

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.