Subject: Re: Asynchronous Append on postgres_fdw nodes.
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>
References: 
 <CAPmGK153oorYtTpW_-aZrjH-iecHbykX7qbxX_5630ZK8nqVHg@mail.gmail.com>
 <20201002.153925.2111528346612886434.horikyota.ntt@gmail.com>
 <CAPmGK16YXCADSwsFLSxqTBBLbt3E_=iigKTtjS=dqu+8K8DWCw@mail.gmail.com>
 <20201005.132959.1424481454550541470.horikyota.ntt@gmail.com>
 <CAPmGK17Ura3NUXYP98W4jz0VT9BAyVFhNRqZyFV-_mqFDB0j7Q@mail.gmail.com>
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
Message-ID: <7fe10f95-ac6c-c81d-a9d3-227493eb9055@postgrespro.ru>
Date: Thu, 8 Oct 2020 16:40:24 +0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: 
 <CAPmGK17Ura3NUXYP98W4jz0VT9BAyVFhNRqZyFV-_mqFDB0j7Q@mail.gmail.com>
Content-Type: multipart/mixed;
 boundary="------------B18826FEA5786A238C8598D4"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------B18826FEA5786A238C8598D4
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi,
I want to suggest one more improvement. Currently the
is_async_capable_path() routine allow only ForeignPath nodes as async 
capable path. But in some cases we can allow SubqueryScanPath as async 
capable too.

For example:
SELECT * FROM ((SELECT * FROM foreign_1)
UNION ALL
(SELECT a FROM foreign_2)) AS b;

is async capable, but:

SELECT * FROM ((SELECT * FROM foreign_1 LIMIT 10)
UNION ALL
(SELECT a FROM foreign_2 LIMIT 10)) AS b;

doesn't async capable.

The patch in attachment tries to improve this situation.

-- 
regards,
Andrey Lepikhov
Postgres Professional

--------------B18826FEA5786A238C8598D4
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-async_capable_subqueries.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="0001-async_capable_subqueries.patch"

From fa73b84e8c456c48ef4788304d2ed14f31365aac Mon Sep 17 00:00:00 2001
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
Date: Thu, 8 Oct 2020 15:46:41 +0500
Subject: [PATCH] 2

---
 contrib/postgres_fdw/expected/postgres_fdw.out | 3 ++-
 src/backend/optimizer/path/allpaths.c          | 4 ++++
 src/backend/optimizer/plan/createplan.c        | 2 +-
 3 files changed, 7 insertions(+), 2 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index eca44a4f40..e5972574b6 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2082,6 +2082,7 @@ SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2
                      Output: t1.c1, t2.c1
                      Group Key: t1.c1, t2.c1
                      ->  Append
+                           Async subplans: 2 
                            ->  Foreign Scan
                                  Output: t1.c1, t2.c1
                                  Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
@@ -2090,7 +2091,7 @@ SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2
                                  Output: t1_1.c1, t2_1.c1
                                  Relations: (public.ft1 t1_1) INNER JOIN (public.ft2 t2_1)
                                  Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1"))))
-(20 rows)
+(21 rows)
 
 SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
  t1c1 |         avg          
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 17e9a7a897..5822ba83e0 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -3991,6 +3991,10 @@ is_async_capable_path(Path *path)
 					fdwroutine->IsForeignPathAsyncCapable((ForeignPath *) path))
 					return true;
 			}
+			break;
+		case T_SubqueryScanPath:
+			if (is_async_capable_path(((SubqueryScanPath *) path)->subpath))
+				return true;
 		default:
 			break;
 	}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 3ae46ed6f1..efb1b0cb4e 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -1231,7 +1231,7 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path, int flags)
 		 * Classify as async-capable or not. If we have decided to run the
 		 * children in parallel, we cannot any one of them run asynchronously.
 		 * Planner thinks that all subnodes are executed in order if this
-		 * append is orderd. No subpaths cannot be run asynchronously in that
+		 * append is ordered. No subpaths cannot be run asynchronously in that
 		 * case.
 		 */
 		if (pathkeys == NIL &&
-- 
2.17.1


--------------B18826FEA5786A238C8598D4--