pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedThe case when AsyncAppend exists also in the qual of Async ForeignScan
20+ messages / 3 participants
[nested] [flat]
* The case when AsyncAppend exists also in the qual of Async ForeignScan
@ 2021-07-02 13:24 Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-02 23:35 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 2 replies; 20+ messages in thread
From: Andrey Lepikhov @ 2021-07-02 13:24 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org, Etsuro Fujita <etsuro.fujita@gmail.com>
Hi,
I found one bug. Look at the case:
CREATE TABLE test (x int) PARTITION BY HASH (x);
CREATE TABLE test_1 (x int);
CREATE TABLE test_2 (x int);
CREATE FOREIGN TABLE ftest_1 PARTITION OF test
FOR VALUES WITH (modulus 2, remainder 0)
SERVER loopback OPTIONS (table_name 'test_1');
CREATE FOREIGN TABLE ftest_2 PARTITION OF test
FOR VALUES WITH (modulus 2, remainder 1)
SERVER loopback2 OPTIONS (table_name 'test_2');
INSERT INTO test (SELECT * FROM generate_series(1,10));
ANALYZE test,test_1,test_2,ftest_1,ftest_2;
EXPLAIN (ANALYZE)
SELECT * FROM test WHERE x NOT IN (
SELECT avg(x) FROM test WHERE x > 10
);
ERROR: InstrEndLoop called on running node
When I added ', COSTS OFF, SUMMARY OFF, TIMING OFF' this example fall
down into the SEGFAULT.
I prepared quick fix for this problem (see patch in attachment). Maybe
it is'nt a best solution but it can speedup search of it.
--
regards,
Andrey Lepikhov
Postgres Professional
From ce7a68c951344aa2163fb590ecdf3bb0649b83ae Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Date: Fri, 2 Jul 2021 16:20:19 +0300
Subject: [PATCH] Do not take a tuple immediately after finishing of async
request if this request was initialized by another append node.
---
contrib/postgres_fdw/connection.c | 4 +--
.../postgres_fdw/expected/postgres_fdw.out | 36 +++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 25 ++++++++++---
contrib/postgres_fdw/postgres_fdw.h | 2 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 22 ++++++++++++
5 files changed, 81 insertions(+), 8 deletions(-)
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 82aa14a65d..163825f4d7 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -203,7 +203,7 @@ GetConnection(UserMapping *user, bool will_prep_stmt, PgFdwConnState **state)
{
/* Process a pending asynchronous request if any. */
if (entry->state.pendingAreq)
- process_pending_request(entry->state.pendingAreq);
+ process_pending_request(entry->state.pendingAreq, true);
/* Start a new transaction or subtransaction if needed. */
begin_remote_xact(entry);
}
@@ -686,7 +686,7 @@ pgfdw_exec_query(PGconn *conn, const char *query, PgFdwConnState *state)
{
/* First, process a pending asynchronous request, if any. */
if (state && state->pendingAreq)
- process_pending_request(state->pendingAreq);
+ process_pending_request(state->pendingAreq, true);
/*
* Submit a query. Since we don't use non-blocking mode, this also can
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 31b5de91ad..9386453e60 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10470,7 +10470,43 @@ SELECT * FROM async_pt;
-> Seq Scan on async_p3 async_pt_3 (actual rows=0 loops=1)
(4 rows)
+--
+-- The case when we have the Async Append in the
+-- qual of the Async ForeignScan node.
+--
+CREATE TABLE test (x int) PARTITION BY HASH (x);
+CREATE TABLE test_1 (x int);
+CREATE TABLE test_2 (x int);
+CREATE FOREIGN TABLE ftest_1 PARTITION OF test
+ FOR VALUES WITH (modulus 2, remainder 0)
+ SERVER loopback OPTIONS (table_name 'test_1');
+CREATE FOREIGN TABLE ftest_2 PARTITION OF test
+ FOR VALUES WITH (modulus 2, remainder 1)
+ SERVER loopback2 OPTIONS (table_name 'test_2');
+INSERT INTO test (SELECT * FROM generate_series(1,10));
+ANALYZE test,test_1,test_2,ftest_1,ftest_2;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+SELECT * FROM test WHERE x NOT IN (
+ SELECT avg(x) FROM test WHERE x > 10
+);
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Append (actual rows=0 loops=1)
+ -> Async Foreign Scan on ftest_1 test_1 (actual rows=0 loops=1)
+ Filter: (NOT (hashed SubPlan 1))
+ Rows Removed by Filter: 2
+ SubPlan 1
+ -> Aggregate (actual rows=1 loops=2)
+ -> Append (actual rows=0 loops=2)
+ -> Async Foreign Scan on ftest_1 test_4 (actual rows=0 loops=2)
+ -> Async Foreign Scan on ftest_2 test_5 (actual rows=0 loops=2)
+ -> Async Foreign Scan on ftest_2 test_2 (actual rows=0 loops=1)
+ Filter: (NOT (hashed SubPlan 1))
+ Rows Removed by Filter: 8
+(12 rows)
+
-- Clean up
+DROP TABLE test CASCADE;
DROP TABLE async_pt;
DROP TABLE base_tbl1;
DROP TABLE base_tbl2;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index fafbab6b02..1c4ae328d0 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3696,7 +3696,7 @@ create_cursor(ForeignScanState *node)
/* First, process a pending asynchronous request, if any. */
if (fsstate->conn_state->pendingAreq)
- process_pending_request(fsstate->conn_state->pendingAreq);
+ process_pending_request(fsstate->conn_state->pendingAreq, true);
/*
* Construct array of query parameter values in text format. We do the
@@ -4066,7 +4066,7 @@ execute_foreign_modify(EState *estate,
/* First, process a pending asynchronous request, if any. */
if (fmstate->conn_state->pendingAreq)
- process_pending_request(fmstate->conn_state->pendingAreq);
+ process_pending_request(fmstate->conn_state->pendingAreq, true);
/*
* If the existing query was deparsed and prepared for a different number
@@ -4509,7 +4509,7 @@ execute_dml_stmt(ForeignScanState *node)
/* First, process a pending asynchronous request, if any. */
if (dmstate->conn_state->pendingAreq)
- process_pending_request(dmstate->conn_state->pendingAreq);
+ process_pending_request(dmstate->conn_state->pendingAreq, true);
/*
* Construct array of query parameter values in text format.
@@ -6850,7 +6850,7 @@ postgresForeignAsyncConfigureWait(AsyncRequest *areq)
*/
if (GetNumRegisteredWaitEvents(set) > 1)
return;
- process_pending_request(pendingAreq);
+ process_pending_request(pendingAreq, false);
fetch_more_data_begin(areq);
}
else if (pendingAreq->requestee != areq->requestee)
@@ -6995,7 +6995,7 @@ fetch_more_data_begin(AsyncRequest *areq)
* Process a pending asynchronous request.
*/
void
-process_pending_request(AsyncRequest *areq)
+process_pending_request(AsyncRequest *areq, bool fetch)
{
ForeignScanState *node = (ForeignScanState *) areq->requestee;
PgFdwScanState *fsstate PG_USED_FOR_ASSERTS_ONLY = (PgFdwScanState *) node->fdw_state;
@@ -7015,6 +7015,21 @@ process_pending_request(AsyncRequest *areq)
fetch_more_data(node);
+ /*
+ * If the request are made by another append we will only prepare connection
+ * for the next query and don't take a tuple immediately. It is needed to
+ * prevent possible recursion into a qual subplan.
+ */
+ if (!fetch)
+ {
+ AppendState *node = (AppendState *) areq->requestor;
+
+ ExecAsyncRequestDone(areq, NULL);
+ node->as_needrequest = bms_add_member(node->as_needrequest,
+ areq->request_index);
+ return;
+ }
+
/* We need to send a new query afterwards; don't fetch */
produce_tuple_asynchronously(areq, false);
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 9591c0f6c2..02c9c7bfa6 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -137,7 +137,7 @@ typedef struct PgFdwConnState
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
-extern void process_pending_request(AsyncRequest *areq);
+extern void process_pending_request(AsyncRequest *areq, bool fetch);
/* in connection.c */
extern PGconn *GetConnection(UserMapping *user, bool will_prep_stmt,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 286dd99573..38caf96cc8 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3328,7 +3328,29 @@ DELETE FROM async_p3;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
SELECT * FROM async_pt;
+--
+-- The case when we have the Async Append in the
+-- qual of the Async ForeignScan node.
+--
+CREATE TABLE test (x int) PARTITION BY HASH (x);
+CREATE TABLE test_1 (x int);
+CREATE TABLE test_2 (x int);
+CREATE FOREIGN TABLE ftest_1 PARTITION OF test
+ FOR VALUES WITH (modulus 2, remainder 0)
+ SERVER loopback OPTIONS (table_name 'test_1');
+CREATE FOREIGN TABLE ftest_2 PARTITION OF test
+ FOR VALUES WITH (modulus 2, remainder 1)
+ SERVER loopback2 OPTIONS (table_name 'test_2');
+INSERT INTO test (SELECT * FROM generate_series(1,10));
+ANALYZE test,test_1,test_2,ftest_1,ftest_2;
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+SELECT * FROM test WHERE x NOT IN (
+ SELECT avg(x) FROM test WHERE x > 10
+);
+
-- Clean up
+DROP TABLE test CASCADE;
DROP TABLE async_pt;
DROP TABLE base_tbl1;
DROP TABLE base_tbl2;
--
2.31.1
Attachments:
0001-Do-not-take-a-tuple-immediately-after-finishing-of-a.patch (text/plain, 8.0K) download
From ce7a68c951344aa2163fb590ecdf3bb0649b83ae Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Date: Fri, 2 Jul 2021 16:20:19 +0300
Subject: [PATCH] Do not take a tuple immediately after finishing of async
request if this request was initialized by another append node.
---
contrib/postgres_fdw/connection.c | 4 +--
.../postgres_fdw/expected/postgres_fdw.out | 36 +++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 25 ++++++++++---
contrib/postgres_fdw/postgres_fdw.h | 2 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 22 ++++++++++++
5 files changed, 81 insertions(+), 8 deletions(-)
diff --git a/contrib/postgres_fdw/connection.c b/contrib/postgres_fdw/connection.c
index 82aa14a65d..163825f4d7 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -203,7 +203,7 @@ GetConnection(UserMapping *user, bool will_prep_stmt, PgFdwConnState **state)
{
/* Process a pending asynchronous request if any. */
if (entry->state.pendingAreq)
- process_pending_request(entry->state.pendingAreq);
+ process_pending_request(entry->state.pendingAreq, true);
/* Start a new transaction or subtransaction if needed. */
begin_remote_xact(entry);
}
@@ -686,7 +686,7 @@ pgfdw_exec_query(PGconn *conn, const char *query, PgFdwConnState *state)
{
/* First, process a pending asynchronous request, if any. */
if (state && state->pendingAreq)
- process_pending_request(state->pendingAreq);
+ process_pending_request(state->pendingAreq, true);
/*
* Submit a query. Since we don't use non-blocking mode, this also can
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 31b5de91ad..9386453e60 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10470,7 +10470,43 @@ SELECT * FROM async_pt;
-> Seq Scan on async_p3 async_pt_3 (actual rows=0 loops=1)
(4 rows)
+--
+-- The case when we have the Async Append in the
+-- qual of the Async ForeignScan node.
+--
+CREATE TABLE test (x int) PARTITION BY HASH (x);
+CREATE TABLE test_1 (x int);
+CREATE TABLE test_2 (x int);
+CREATE FOREIGN TABLE ftest_1 PARTITION OF test
+ FOR VALUES WITH (modulus 2, remainder 0)
+ SERVER loopback OPTIONS (table_name 'test_1');
+CREATE FOREIGN TABLE ftest_2 PARTITION OF test
+ FOR VALUES WITH (modulus 2, remainder 1)
+ SERVER loopback2 OPTIONS (table_name 'test_2');
+INSERT INTO test (SELECT * FROM generate_series(1,10));
+ANALYZE test,test_1,test_2,ftest_1,ftest_2;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+SELECT * FROM test WHERE x NOT IN (
+ SELECT avg(x) FROM test WHERE x > 10
+);
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Append (actual rows=0 loops=1)
+ -> Async Foreign Scan on ftest_1 test_1 (actual rows=0 loops=1)
+ Filter: (NOT (hashed SubPlan 1))
+ Rows Removed by Filter: 2
+ SubPlan 1
+ -> Aggregate (actual rows=1 loops=2)
+ -> Append (actual rows=0 loops=2)
+ -> Async Foreign Scan on ftest_1 test_4 (actual rows=0 loops=2)
+ -> Async Foreign Scan on ftest_2 test_5 (actual rows=0 loops=2)
+ -> Async Foreign Scan on ftest_2 test_2 (actual rows=0 loops=1)
+ Filter: (NOT (hashed SubPlan 1))
+ Rows Removed by Filter: 8
+(12 rows)
+
-- Clean up
+DROP TABLE test CASCADE;
DROP TABLE async_pt;
DROP TABLE base_tbl1;
DROP TABLE base_tbl2;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index fafbab6b02..1c4ae328d0 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -3696,7 +3696,7 @@ create_cursor(ForeignScanState *node)
/* First, process a pending asynchronous request, if any. */
if (fsstate->conn_state->pendingAreq)
- process_pending_request(fsstate->conn_state->pendingAreq);
+ process_pending_request(fsstate->conn_state->pendingAreq, true);
/*
* Construct array of query parameter values in text format. We do the
@@ -4066,7 +4066,7 @@ execute_foreign_modify(EState *estate,
/* First, process a pending asynchronous request, if any. */
if (fmstate->conn_state->pendingAreq)
- process_pending_request(fmstate->conn_state->pendingAreq);
+ process_pending_request(fmstate->conn_state->pendingAreq, true);
/*
* If the existing query was deparsed and prepared for a different number
@@ -4509,7 +4509,7 @@ execute_dml_stmt(ForeignScanState *node)
/* First, process a pending asynchronous request, if any. */
if (dmstate->conn_state->pendingAreq)
- process_pending_request(dmstate->conn_state->pendingAreq);
+ process_pending_request(dmstate->conn_state->pendingAreq, true);
/*
* Construct array of query parameter values in text format.
@@ -6850,7 +6850,7 @@ postgresForeignAsyncConfigureWait(AsyncRequest *areq)
*/
if (GetNumRegisteredWaitEvents(set) > 1)
return;
- process_pending_request(pendingAreq);
+ process_pending_request(pendingAreq, false);
fetch_more_data_begin(areq);
}
else if (pendingAreq->requestee != areq->requestee)
@@ -6995,7 +6995,7 @@ fetch_more_data_begin(AsyncRequest *areq)
* Process a pending asynchronous request.
*/
void
-process_pending_request(AsyncRequest *areq)
+process_pending_request(AsyncRequest *areq, bool fetch)
{
ForeignScanState *node = (ForeignScanState *) areq->requestee;
PgFdwScanState *fsstate PG_USED_FOR_ASSERTS_ONLY = (PgFdwScanState *) node->fdw_state;
@@ -7015,6 +7015,21 @@ process_pending_request(AsyncRequest *areq)
fetch_more_data(node);
+ /*
+ * If the request are made by another append we will only prepare connection
+ * for the next query and don't take a tuple immediately. It is needed to
+ * prevent possible recursion into a qual subplan.
+ */
+ if (!fetch)
+ {
+ AppendState *node = (AppendState *) areq->requestor;
+
+ ExecAsyncRequestDone(areq, NULL);
+ node->as_needrequest = bms_add_member(node->as_needrequest,
+ areq->request_index);
+ return;
+ }
+
/* We need to send a new query afterwards; don't fetch */
produce_tuple_asynchronously(areq, false);
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 9591c0f6c2..02c9c7bfa6 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -137,7 +137,7 @@ typedef struct PgFdwConnState
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
-extern void process_pending_request(AsyncRequest *areq);
+extern void process_pending_request(AsyncRequest *areq, bool fetch);
/* in connection.c */
extern PGconn *GetConnection(UserMapping *user, bool will_prep_stmt,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 286dd99573..38caf96cc8 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3328,7 +3328,29 @@ DELETE FROM async_p3;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
SELECT * FROM async_pt;
+--
+-- The case when we have the Async Append in the
+-- qual of the Async ForeignScan node.
+--
+CREATE TABLE test (x int) PARTITION BY HASH (x);
+CREATE TABLE test_1 (x int);
+CREATE TABLE test_2 (x int);
+CREATE FOREIGN TABLE ftest_1 PARTITION OF test
+ FOR VALUES WITH (modulus 2, remainder 0)
+ SERVER loopback OPTIONS (table_name 'test_1');
+CREATE FOREIGN TABLE ftest_2 PARTITION OF test
+ FOR VALUES WITH (modulus 2, remainder 1)
+ SERVER loopback2 OPTIONS (table_name 'test_2');
+INSERT INTO test (SELECT * FROM generate_series(1,10));
+ANALYZE test,test_1,test_2,ftest_1,ftest_2;
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+SELECT * FROM test WHERE x NOT IN (
+ SELECT avg(x) FROM test WHERE x > 10
+);
+
-- Clean up
+DROP TABLE test CASCADE;
DROP TABLE async_pt;
DROP TABLE base_tbl1;
DROP TABLE base_tbl2;
--
2.31.1
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
@ 2021-07-02 23:35 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 07:49 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
1 sibling, 1 reply; 20+ messages in thread
From: Etsuro Fujita @ 2021-07-02 23:35 UTC (permalink / raw)
To: Andrey Lepikhov <a.lepikhov@postgrespro.ru>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Hi Andrey,
On Fri, Jul 2, 2021 at 10:24 PM Andrey Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> I found one bug. Look at the case:
>
> CREATE TABLE test (x int) PARTITION BY HASH (x);
> CREATE TABLE test_1 (x int);
> CREATE TABLE test_2 (x int);
> CREATE FOREIGN TABLE ftest_1 PARTITION OF test
> FOR VALUES WITH (modulus 2, remainder 0)
> SERVER loopback OPTIONS (table_name 'test_1');
> CREATE FOREIGN TABLE ftest_2 PARTITION OF test
> FOR VALUES WITH (modulus 2, remainder 1)
> SERVER loopback2 OPTIONS (table_name 'test_2');
> INSERT INTO test (SELECT * FROM generate_series(1,10));
> ANALYZE test,test_1,test_2,ftest_1,ftest_2;
>
> EXPLAIN (ANALYZE)
> SELECT * FROM test WHERE x NOT IN (
> SELECT avg(x) FROM test WHERE x > 10
> );
> ERROR: InstrEndLoop called on running node
>
> When I added ', COSTS OFF, SUMMARY OFF, TIMING OFF' this example fall
> down into the SEGFAULT.
>
> I prepared quick fix for this problem (see patch in attachment). Maybe
> it is'nt a best solution but it can speedup search of it.
I’ll looking into this. Thanks for the report and patch!
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-02 23:35 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2021-07-08 07:49 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 08:06 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-08 10:18 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-08 10:32 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
0 siblings, 3 replies; 20+ messages in thread
From: Etsuro Fujita @ 2021-07-08 07:49 UTC (permalink / raw)
To: Andrey Lepikhov <a.lepikhov@postgrespro.ru>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Sat, Jul 3, 2021 at 8:35 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Fri, Jul 2, 2021 at 10:24 PM Andrey Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
> > I found one bug. Look at the case:
> >
> > CREATE TABLE test (x int) PARTITION BY HASH (x);
> > CREATE TABLE test_1 (x int);
> > CREATE TABLE test_2 (x int);
> > CREATE FOREIGN TABLE ftest_1 PARTITION OF test
> > FOR VALUES WITH (modulus 2, remainder 0)
> > SERVER loopback OPTIONS (table_name 'test_1');
> > CREATE FOREIGN TABLE ftest_2 PARTITION OF test
> > FOR VALUES WITH (modulus 2, remainder 1)
> > SERVER loopback2 OPTIONS (table_name 'test_2');
> > INSERT INTO test (SELECT * FROM generate_series(1,10));
> > ANALYZE test,test_1,test_2,ftest_1,ftest_2;
> >
> > EXPLAIN (ANALYZE)
> > SELECT * FROM test WHERE x NOT IN (
> > SELECT avg(x) FROM test WHERE x > 10
> > );
> > ERROR: InstrEndLoop called on running node
> >
> > When I added ', COSTS OFF, SUMMARY OFF, TIMING OFF' this example fall
> > down into the SEGFAULT.
> >
> > I prepared quick fix for this problem (see patch in attachment). Maybe
> > it is'nt a best solution but it can speedup search of it.
>
> I’ll looking into this. Thanks for the report and patch!
I tried to reproduce this in my environment using HEAD and PG14, but I
couldn't. Could you elaborate a bit more on how to reproduce this?
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-02 23:35 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 07:49 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2021-07-08 08:06 ` Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-08 08:32 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2 siblings, 1 reply; 20+ messages in thread
From: Andrey Lepikhov @ 2021-07-08 08:06 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On 8/7/21 10:49, Etsuro Fujita wrote:
> On Sat, Jul 3, 2021 at 8:35 AM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>> On Fri, Jul 2, 2021 at 10:24 PM Andrey Lepikhov
>> <a.lepikhov@postgrespro.ru> wrote:
>>> I found one bug. Look at the case:
>>>
>>> CREATE TABLE test (x int) PARTITION BY HASH (x);
>>> CREATE TABLE test_1 (x int);
>>> CREATE TABLE test_2 (x int);
>>> CREATE FOREIGN TABLE ftest_1 PARTITION OF test
>>> FOR VALUES WITH (modulus 2, remainder 0)
>>> SERVER loopback OPTIONS (table_name 'test_1');
>>> CREATE FOREIGN TABLE ftest_2 PARTITION OF test
>>> FOR VALUES WITH (modulus 2, remainder 1)
>>> SERVER loopback2 OPTIONS (table_name 'test_2');
>>> INSERT INTO test (SELECT * FROM generate_series(1,10));
>>> ANALYZE test,test_1,test_2,ftest_1,ftest_2;
>>>
>>> EXPLAIN (ANALYZE)
>>> SELECT * FROM test WHERE x NOT IN (
>>> SELECT avg(x) FROM test WHERE x > 10
>>> );
>>> ERROR: InstrEndLoop called on running node
>>>
>>> When I added ', COSTS OFF, SUMMARY OFF, TIMING OFF' this example fall
>>> down into the SEGFAULT.
>>>
>>> I prepared quick fix for this problem (see patch in attachment). Maybe
>>> it is'nt a best solution but it can speedup search of it.
>>
>> I’ll looking into this. Thanks for the report and patch!
>
> I tried to reproduce this in my environment using HEAD and PG14, but I
> couldn't. Could you elaborate a bit more on how to reproduce this?
>
> Best regards,
> Etsuro Fujita
>
Initially I cought this bug during TPC-H benchmarking, query Q16, right
after start of execution.
Key thing here is using a hashed subplan.
--
regards,
Andrey Lepikhov
Postgres Professional
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-02 23:35 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 07:49 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 08:06 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
@ 2021-07-08 08:32 ` Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Etsuro Fujita @ 2021-07-08 08:32 UTC (permalink / raw)
To: Andrey Lepikhov <a.lepikhov@postgrespro.ru>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Thu, Jul 8, 2021 at 5:06 PM Andrey Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> Initially I cought this bug during TPC-H benchmarking, query Q16, right
> after start of execution.
> Key thing here is using a hashed subplan.
The plan I got also uses a hashed SubPlan; moreover, the plan is the
same as the one shown in your patch.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-02 23:35 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 07:49 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2021-07-08 10:18 ` Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-08 17:10 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2 siblings, 1 reply; 20+ messages in thread
From: Andrey Lepikhov @ 2021-07-08 10:18 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On 8/7/21 10:49, Etsuro Fujita wrote:
> I tried to reproduce this in my environment using HEAD and PG14, but I
> couldn't. Could you elaborate a bit more on how to reproduce this?
Interesting...
I use the only script (see t.sql) right after the instance has started.
Output see in t.out
All my specific preferences:
autovacuum = 'off'
max_worker_processes = 64
max_parallel_workers_per_gather = 16
max_parallel_workers = 32
parallel_setup_cost = 100
parallel_tuple_cost = 0.05
min_parallel_table_scan_size = 0
--
regards,
Andrey Lepikhov
Postgres Professional
psql -f t.sql
DROP TABLE
psql:t.sql:2: NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to server lb1
drop cascades to user mapping for public on server lb1
drop cascades to server lb2
drop cascades to user mapping for public on server lb2
DROP EXTENSION
CREATE EXTENSION
CREATE SERVER
CREATE SERVER
CREATE USER MAPPING
CREATE USER MAPPING
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE FOREIGN TABLE
CREATE FOREIGN TABLE
INSERT 0 10
ANALYZE
psql:t.sql:26: ERROR: InstrEndLoop called on running node
Attachments:
t.sql (application/sql, 954B) download
t.out (text/plain, 517B) download
psql -f t.sql
DROP TABLE
psql:t.sql:2: NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to server lb1
drop cascades to user mapping for public on server lb1
drop cascades to server lb2
drop cascades to user mapping for public on server lb2
DROP EXTENSION
CREATE EXTENSION
CREATE SERVER
CREATE SERVER
CREATE USER MAPPING
CREATE USER MAPPING
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE FOREIGN TABLE
CREATE FOREIGN TABLE
INSERT 0 10
ANALYZE
psql:t.sql:26: ERROR: InstrEndLoop called on running node
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-02 23:35 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 07:49 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 10:18 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
@ 2021-07-08 17:10 ` Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Etsuro Fujita @ 2021-07-08 17:10 UTC (permalink / raw)
To: Andrey Lepikhov <a.lepikhov@postgrespro.ru>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Thu, Jul 8, 2021 at 7:18 PM Andrey Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> On 8/7/21 10:49, Etsuro Fujita wrote:
> > I tried to reproduce this in my environment using HEAD and PG14, but I
> > couldn't. Could you elaborate a bit more on how to reproduce this?
> Interesting...
> I use the only script (see t.sql) right after the instance has started.
Reproduced here. I’ll continue to investigate this.
The reason I couldn’t reproduce this is my setting where partitions
were created on the same foreign server. :-(
Thanks!
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-02 23:35 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 07:49 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2021-07-08 10:32 ` Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2 siblings, 0 replies; 20+ messages in thread
From: Andrey Lepikhov @ 2021-07-08 10:32 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On 8/7/21 10:49, Etsuro Fujita wrote:
Important. If you want to get the InstrEndLoop error, you need to enable
timing in the EXPLAIN operation.
--
regards,
Andrey Lepikhov
Postgres Professional
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
@ 2021-07-22 11:14 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-23 06:09 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-23 11:26 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-27 06:22 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
1 sibling, 3 replies; 20+ messages in thread
From: Etsuro Fujita @ 2021-07-22 11:14 UTC (permalink / raw)
To: Andrey Lepikhov <a.lepikhov@postgrespro.ru>; +Cc: pgsql-bugs@lists.postgresql.org
On Fri, Jul 2, 2021 at 10:24 PM Andrey Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> I found one bug. Look at the case:
>
> CREATE TABLE test (x int) PARTITION BY HASH (x);
> CREATE TABLE test_1 (x int);
> CREATE TABLE test_2 (x int);
> CREATE FOREIGN TABLE ftest_1 PARTITION OF test
> FOR VALUES WITH (modulus 2, remainder 0)
> SERVER loopback OPTIONS (table_name 'test_1');
> CREATE FOREIGN TABLE ftest_2 PARTITION OF test
> FOR VALUES WITH (modulus 2, remainder 1)
> SERVER loopback2 OPTIONS (table_name 'test_2');
> INSERT INTO test (SELECT * FROM generate_series(1,10));
> ANALYZE test,test_1,test_2,ftest_1,ftest_2;
>
> EXPLAIN (ANALYZE)
> SELECT * FROM test WHERE x NOT IN (
> SELECT avg(x) FROM test WHERE x > 10
> );
> ERROR: InstrEndLoop called on running node
>
> When I added ', COSTS OFF, SUMMARY OFF, TIMING OFF' this example fall
> down into the SEGFAULT.
>
> I prepared quick fix for this problem (see patch in attachment). Maybe
> it is'nt a best solution but it can speedup search of it.
@@ -7015,6 +7015,21 @@ process_pending_request(AsyncRequest *areq)
fetch_more_data(node);
+ /*
+ * If the request are made by another append we will only prepare connection
+ * for the next query and don't take a tuple immediately. It is needed to
+ * prevent possible recursion into a qual subplan.
+ */
+ if (!fetch)
+ {
+ AppendState *node = (AppendState *) areq->requestor;
+
+ ExecAsyncRequestDone(areq, NULL);
+ node->as_needrequest = bms_add_member(node->as_needrequest,
+ areq->request_index);
+ return;
+ }
I don’t think this is a good idea, because it is pretty inconsistent,
as doing ExecAsyncRequestDone(areq, NULL) means that there are no more
tuples while changing as_needrequest like that means that there is at
least one tuple to return. This would happen to work, but for
example, if we add to the core more sanity checks on AsyncRequests,
this would not work well anymore. I tried to devise a consistent
solution for this issue, but I couldn’t. So I feel inclined to
disable async execution in cases where async-capable nodes access to
subplans (or initplans), for now.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2021-07-23 06:09 ` Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-25 08:55 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2 siblings, 1 reply; 20+ messages in thread
From: Andrey V. Lepikhov @ 2021-07-23 06:09 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On 7/22/21 4:14 PM, Etsuro Fujita wrote:
> On Fri, Jul 2, 2021 at 10:24 PM Andrey Lepikhov
> @@ -7015,6 +7015,21 @@ process_pending_request(AsyncRequest *areq)
>
> fetch_more_data(node);
>
> + /*
> + * If the request are made by another append we will only prepare connection
> + * for the next query and don't take a tuple immediately. It is needed to
> + * prevent possible recursion into a qual subplan.
> + */
> + if (!fetch)
> + {
> + AppendState *node = (AppendState *) areq->requestor;
> +
> + ExecAsyncRequestDone(areq, NULL);
> + node->as_needrequest = bms_add_member(node->as_needrequest,
> + areq->request_index);
> + return;
> + }
>
> I don’t think this is a good idea, because it is pretty inconsistent,
> as doing ExecAsyncRequestDone(areq, NULL) means that there are no more
> tuples while changing as_needrequest like that means that there is at
> least one tuple to return. This would happen to work, but for
> example, if we add to the core more sanity checks on AsyncRequests,
> this would not work well anymore.
I agree.
> I tried to devise a consistent
> solution for this issue, but I couldn’t. So I feel inclined to
> disable async execution in cases where async-capable nodes access to
> subplans (or initplans), for now.
I think it can be done, but only as a temporary solution. InitPlan is a
common planning utility.
Maybe we can split async logic into:
- receiving stage, when we only fetch and store tuples,
- evaluating stage, when we form resulting tuple and return by a scan node.
I will think about such solution more.
Also, may be you tell your opinion about an additional optimization of
Async Append [1]?
[1]
https://www.postgresql.org/message-id/edb1331c-e861-0c53-9fdb-f7ca7dfd884d%40postgrespro.ru
--
regards,
Andrey Lepikhov
Postgres Professional
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-23 06:09 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
@ 2021-07-25 08:55 ` Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Etsuro Fujita @ 2021-07-25 08:55 UTC (permalink / raw)
To: Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, Jul 23, 2021 at 3:09 PM Andrey V. Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> On 7/22/21 4:14 PM, Etsuro Fujita wrote:
> > On Fri, Jul 2, 2021 at 10:24 PM Andrey Lepikhov
> > @@ -7015,6 +7015,21 @@ process_pending_request(AsyncRequest *areq)
> >
> > fetch_more_data(node);
> >
> > + /*
> > + * If the request are made by another append we will only prepare connection
> > + * for the next query and don't take a tuple immediately. It is needed to
> > + * prevent possible recursion into a qual subplan.
> > + */
> > + if (!fetch)
> > + {
> > + AppendState *node = (AppendState *) areq->requestor;
> > +
> > + ExecAsyncRequestDone(areq, NULL);
> > + node->as_needrequest = bms_add_member(node->as_needrequest,
> > + areq->request_index);
> > + return;
> > + }
> >
> > I don’t think this is a good idea, because it is pretty inconsistent,
> > as doing ExecAsyncRequestDone(areq, NULL) means that there are no more
> > tuples while changing as_needrequest like that means that there is at
> > least one tuple to return. This would happen to work, but for
> > example, if we add to the core more sanity checks on AsyncRequests,
> > this would not work well anymore.
> > So I feel inclined to
> > disable async execution in cases where async-capable nodes access to
> > subplans (or initplans), for now.
> I think it can be done, but only as a temporary solution.
My concern about that is that such an inconsistency would make the
code complicated, and thus make the maintenance hard.
> Maybe we can split async logic into:
> - receiving stage, when we only fetch and store tuples,
> - evaluating stage, when we form resulting tuple and return by a scan node.
> I will think about such solution more.
One simple solution along this line I came up with, which is not the
rewrite, is to 1) split process_pending_request() into the two steps,
and 2) postpone the second step until we are called from
postgresForeignAsyncConfigureWait(), like the attached, which I think
would be much consistent with the existing logic.
> Also, may be you tell your opinion about an additional optimization of
> Async Append [1]?
Is the optimization related to this issue? (Sorry, I didn’t have time
for reviewing the patch in [1] than expected. I plan to do so next
month.)
Best regards,
Etsuro Fujita
Attachments:
reconsider-process_pending_request.patch (application/octet-stream, 8.8K) download
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index ed25e7a743..931b941e9f 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10341,9 +10341,60 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
3505 | 505 | 0505
(1 row)
--- Check with foreign modify
CREATE TABLE local_tbl (a int, b int, c text);
INSERT INTO local_tbl VALUES (1505, 505, 'foo');
+ANALYZE local_tbl;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ($0)
+ Join Filter: (t1.a = async_pt.a)
+ InitPlan 1 (returns $0)
+ -> Aggregate
+ Output: count(*)
+ -> Append
+ -> Async Foreign Scan on public.async_p1 async_pt_4
+ Remote SQL: SELECT NULL FROM public.base_tbl1 WHERE ((a < 3000))
+ -> Async Foreign Scan on public.async_p2 async_pt_5
+ Remote SQL: SELECT NULL FROM public.base_tbl2 WHERE ((a < 3000))
+ -> Seq Scan on public.local_tbl t1
+ Output: t1.a, t1.b, t1.c
+ -> Append
+ -> Async Foreign Scan on public.async_p1 async_pt_1
+ Output: async_pt_1.a, async_pt_1.b, async_pt_1.c, $0
+ Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((a < 3000))
+ -> Async Foreign Scan on public.async_p2 async_pt_2
+ Output: async_pt_2.a, async_pt_2.b, async_pt_2.c, $0
+ Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < 3000))
+(20 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Nested Loop Left Join (actual rows=1 loops=1)
+ Join Filter: (t1.a = async_pt.a)
+ Rows Removed by Join Filter: 399
+ InitPlan 1 (returns $0)
+ -> Aggregate (actual rows=1 loops=1)
+ -> Append (actual rows=400 loops=1)
+ -> Async Foreign Scan on async_p1 async_pt_4 (actual rows=200 loops=1)
+ -> Async Foreign Scan on async_p2 async_pt_5 (actual rows=200 loops=1)
+ -> Seq Scan on local_tbl t1 (actual rows=1 loops=1)
+ -> Append (actual rows=400 loops=1)
+ -> Async Foreign Scan on async_p1 async_pt_1 (actual rows=200 loops=1)
+ -> Async Foreign Scan on async_p2 async_pt_2 (actual rows=200 loops=1)
+(12 rows)
+
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+ a | b | c | a | b | c | count
+------+-----+-----+------+-----+------+-------
+ 1505 | 505 | foo | 1505 | 505 | 0505 | 400
+(1 row)
+
+-- Check with foreign modify
CREATE TABLE base_tbl3 (a int, b int, c text);
CREATE FOREIGN TABLE remote_tbl (a int, b int, c text)
SERVER loopback OPTIONS (table_name 'base_tbl3');
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index f15c97ad7a..0443c4e090 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -503,6 +503,7 @@ static void analyze_row_processor(PGresult *res, int row,
PgFdwAnalyzeState *astate);
static void produce_tuple_asynchronously(AsyncRequest *areq, bool fetch);
static void fetch_more_data_begin(AsyncRequest *areq);
+static void complete_pending_request(AsyncRequest *areq);
static HeapTuple make_tuple_from_result_row(PGresult *res,
int row,
Relation rel,
@@ -6829,6 +6830,20 @@ postgresForeignAsyncConfigureWait(AsyncRequest *areq)
/* The core code would have registered postmaster death event */
Assert(GetNumRegisteredWaitEvents(set) >= 1);
+ /*
+ * If process_pending_request() has been invoked on the given request
+ * before we get here, we might have some tuples already; in which case
+ * complete the request
+ */
+ if (fsstate->next_tuple < fsstate->num_tuples)
+ {
+ complete_pending_request(areq);
+ if (areq->request_complete)
+ return;
+ Assert(areq->callback_pending);
+ }
+ Assert(fsstate->next_tuple >= fsstate->num_tuples);
+
/* Begin an asynchronous data fetch if not already done */
if (!pendingAreq)
fetch_more_data_begin(areq);
@@ -6844,6 +6859,8 @@ postgresForeignAsyncConfigureWait(AsyncRequest *areq)
* we might end up with no configured events other than the postmaster
* death event.
*/
+ if (!bms_is_empty(requestor->as_needrequest))
+ return;
if (GetNumRegisteredWaitEvents(set) > 1)
return;
process_pending_request(pendingAreq);
@@ -6995,23 +7012,40 @@ process_pending_request(AsyncRequest *areq)
{
ForeignScanState *node = (ForeignScanState *) areq->requestee;
PgFdwScanState *fsstate PG_USED_FOR_ASSERTS_ONLY = (PgFdwScanState *) node->fdw_state;
- EState *estate = node->ss.ps.state;
- MemoryContext oldcontext;
+
+ /* The request would have been pending for a callback */
+ Assert(areq->callback_pending);
/* The request should be currently in-process */
Assert(fsstate->conn_state->pendingAreq == areq);
- oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
+ fetch_more_data(node);
+
+ /* If we didn't get any tuples, must be end of data; nothing more to do */
+ if (fsstate->next_tuple >= fsstate->num_tuples)
+ {
+ /* Unlike AsyncNotify, we unset callback_pending ourselves */
+ areq->callback_pending = false;
+ /* Mark the request as complete */
+ ExecAsyncRequestDone(areq, NULL);
+ /* Unlike AsyncNotify, we call ExecAsyncResponse ourselves */
+ ExecAsyncResponse(areq);
+ }
+}
+/*
+ * Complete a pending asynchronous request.
+ */
+static void
+complete_pending_request(AsyncRequest *areq)
+{
/* The request would have been pending for a callback */
Assert(areq->callback_pending);
/* Unlike AsyncNotify, we unset callback_pending ourselves */
areq->callback_pending = false;
- fetch_more_data(node);
-
- /* We need to send a new query afterwards; don't fetch */
+ /* We begin a fetch afterwards if necessary; don't fetch */
produce_tuple_asynchronously(areq, false);
/* Unlike AsyncNotify, we call ExecAsyncResponse ourselves */
@@ -7021,8 +7055,6 @@ process_pending_request(AsyncRequest *areq)
if (areq->requestee->instrument)
InstrUpdateTupleCount(areq->requestee->instrument,
TupIsNull(areq->result) ? 0.0 : 1.0);
-
- MemoryContextSwitchTo(oldcontext);
}
/*
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 02a6b15a13..a4bc15186e 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3280,10 +3280,17 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
--- Check with foreign modify
CREATE TABLE local_tbl (a int, b int, c text);
INSERT INTO local_tbl VALUES (1505, 505, 'foo');
+ANALYZE local_tbl;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+-- Check with foreign modify
CREATE TABLE base_tbl3 (a int, b int, c text);
CREATE FOREIGN TABLE remote_tbl (a int, b int, c text)
SERVER loopback OPTIONS (table_name 'base_tbl3');
diff --git a/src/backend/executor/nodeAppend.c b/src/backend/executor/nodeAppend.c
index 755c1392f0..a4eef19d7f 100644
--- a/src/backend/executor/nodeAppend.c
+++ b/src/backend/executor/nodeAppend.c
@@ -1043,6 +1043,17 @@ ExecAppendAsyncEventWait(AppendState *node)
ExecAsyncConfigureWait(areq);
}
+ /*
+ * No need for further processing if there are no configured events other
+ * than the postmaster death event.
+ */
+ if (GetNumRegisteredWaitEvents(node->as_eventset) == 1)
+ {
+ FreeWaitEventSet(node->as_eventset);
+ node->as_eventset = NULL;
+ return;
+ }
+
/* We wait on at most EVENT_BUFFER_SIZE events. */
if (nevents > EVENT_BUFFER_SIZE)
nevents = EVENT_BUFFER_SIZE;
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2021-07-23 11:26 ` Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2 siblings, 0 replies; 20+ messages in thread
From: Andrey V. Lepikhov @ 2021-07-23 11:26 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On 7/22/21 4:14 PM, Etsuro Fujita wrote:
> On Fri, Jul 2, 2021 at 10:24 PM Andrey Lepikhov
> @@ -7015,6 +7015,21 @@ process_pending_request(AsyncRequest *areq)
>
> fetch_more_data(node);
>
> + /*
> + * If the request are made by another append we will only prepare connection
> + * for the next query and don't take a tuple immediately. It is needed to
> + * prevent possible recursion into a qual subplan.
> + */
> + if (!fetch)
> + {
> + AppendState *node = (AppendState *) areq->requestor;
> +
> + ExecAsyncRequestDone(areq, NULL);
> + node->as_needrequest = bms_add_member(node->as_needrequest,
> + areq->request_index);
> + return;
> + }
>
> I don’t think this is a good idea, because it is pretty inconsistent,
> as doing ExecAsyncRequestDone(areq, NULL) means that there are no more
> tuples while changing as_needrequest like that means that there is at
> least one tuple to return. This would happen to work, but for
> example, if we add to the core more sanity checks on AsyncRequests,
> this would not work well anymore. I tried to devise a consistent
> solution for this issue, but I couldn’t. So I feel inclined to
> disable async execution in cases where async-capable nodes access to
> subplans (or initplans), for now.
That do you think, if (in addition to the patch) we will give
ExecAsyncAppendResponse a chance to fill the result slot? Code:
--- a/src/backend/executor/nodeAppend.c
+++ b/src/backend/executor/nodeAppend.c
@@ -1108,6 +1108,9 @@ ExecAsyncAppendResponse(AsyncRequest *areq)
return;
}
+ if (areq->result == NULL)
+ areq->result =
areq->requestee->ExecProcNodeReal(areq->requestee);
+
/* If the result is NULL or an empty slot, there's nothing more
to do. */
if (TupIsNull(slot))
{
--
regards,
Andrey Lepikhov
Postgres Professional
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2021-07-27 06:22 ` Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-27 10:50 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2 siblings, 1 reply; 20+ messages in thread
From: Andrey V. Lepikhov @ 2021-07-27 06:22 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
>> I think it can be done, but only as a temporary solution.
> My concern about that is that such an inconsistency would make the
> code complicated, and thus make the maintenance hard.
Agree, but your new patch is quite understandable.
>> Maybe we can split async logic into:
>> - receiving stage, when we only fetch and store tuples,
>> - evaluating stage, when we form resulting tuple and return by a
scan >> node.
>> I will think about such solution more.
> One simple solution along this line I came up with, which is not the
> rewrite, is to 1) split process_pending_request() into the two steps,
> and 2) postpone the second step until we are called from
> postgresForeignAsyncConfigureWait(), like the attached, which I think
> would be much consistent with the existing logic.
Good idea. Are you planning to commit this patch?
>> Also, may be you tell your opinion about an additional optimization
>> of Async Append [1]?
> Is the optimization related to this issue? (Sorry, I didn’t have time
> for reviewing the patch in [1] than expected. I plan to do so next
> month.)
This optimization tries to postpone choice of async subplans. It allows
us to make a decision on async capable subplans after all plan
flattening operations.
--
regards,
Andrey Lepikhov
Postgres Professional
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-27 06:22 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
@ 2021-07-27 10:50 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-27 11:00 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-28 06:39 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-30 08:11 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 3 replies; 20+ messages in thread
From: Etsuro Fujita @ 2021-07-27 10:50 UTC (permalink / raw)
To: CAPmGK15eY_P5AEC6-HjZusx2euftT0KD94ywaeJ=f3NGTLJEFw@mail.gmail.com; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Jul 27, 2021 at 3:22 PM Andrey V. Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> >> Maybe we can split async logic into:
> >> - receiving stage, when we only fetch and store tuples,
> >> - evaluating stage, when we form resulting tuple and return by a
> >> scan node.
> >> I will think about such solution more.
> > One simple solution along this line I came up with, which is not the
> > rewrite, is to 1) split process_pending_request() into the two steps,
> > and 2) postpone the second step until we are called from
> > postgresForeignAsyncConfigureWait(), like the attached, which I think
> > would be much consistent with the existing logic.
> Good idea. Are you planning to commit this patch?
Cool!
Here is an updated version of the patch, in which I added/tweaked
comments a bit further. I'm planning to push this version if there
are no objections from others.
> >> Also, may be you tell your opinion about an additional optimization
> >> of Async Append [1]?
> > Is the optimization related to this issue?
> This optimization tries to postpone choice of async subplans. It allows
> us to make a decision on async capable subplans after all plan
> flattening operations.
Thanks for the explanation! My understanding is that the optimization
isn’t related to this issue. Right?
Best regards,
Etsuro Fujita
Attachments:
reconsider-process_pending_request-2.patch (application/octet-stream, 10.5K) download
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index ed25e7a743..78c05ee5ea 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10300,6 +10300,59 @@ SELECT * FROM async_pt t1, async_p2 t2 WHERE t1.a = t2.a AND t1.b === 505;
2505 | 505 | 0505 | 2505 | 505 | 0505
(1 row)
+CREATE TABLE local_tbl (a int, b int, c text);
+INSERT INTO local_tbl VALUES (1505, 505, 'foo');
+ANALYZE local_tbl;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Nested Loop Left Join
+ Output: t1.a, t1.b, t1.c, async_pt.a, async_pt.b, async_pt.c, ($0)
+ Join Filter: (t1.a = async_pt.a)
+ InitPlan 1 (returns $0)
+ -> Aggregate
+ Output: count(*)
+ -> Append
+ -> Async Foreign Scan on public.async_p1 async_pt_4
+ Remote SQL: SELECT NULL FROM public.base_tbl1 WHERE ((a < 3000))
+ -> Async Foreign Scan on public.async_p2 async_pt_5
+ Remote SQL: SELECT NULL FROM public.base_tbl2 WHERE ((a < 3000))
+ -> Seq Scan on public.local_tbl t1
+ Output: t1.a, t1.b, t1.c
+ -> Append
+ -> Async Foreign Scan on public.async_p1 async_pt_1
+ Output: async_pt_1.a, async_pt_1.b, async_pt_1.c, $0
+ Remote SQL: SELECT a, b, c FROM public.base_tbl1 WHERE ((a < 3000))
+ -> Async Foreign Scan on public.async_p2 async_pt_2
+ Output: async_pt_2.a, async_pt_2.b, async_pt_2.c, $0
+ Remote SQL: SELECT a, b, c FROM public.base_tbl2 WHERE ((a < 3000))
+(20 rows)
+
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Nested Loop Left Join (actual rows=1 loops=1)
+ Join Filter: (t1.a = async_pt.a)
+ Rows Removed by Join Filter: 399
+ InitPlan 1 (returns $0)
+ -> Aggregate (actual rows=1 loops=1)
+ -> Append (actual rows=400 loops=1)
+ -> Async Foreign Scan on async_p1 async_pt_4 (actual rows=200 loops=1)
+ -> Async Foreign Scan on async_p2 async_pt_5 (actual rows=200 loops=1)
+ -> Seq Scan on local_tbl t1 (actual rows=1 loops=1)
+ -> Append (actual rows=400 loops=1)
+ -> Async Foreign Scan on async_p1 async_pt_1 (actual rows=200 loops=1)
+ -> Async Foreign Scan on async_p2 async_pt_2 (actual rows=200 loops=1)
+(12 rows)
+
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+ a | b | c | a | b | c | count
+------+-----+-----+------+-----+------+-------
+ 1505 | 505 | foo | 1505 | 505 | 0505 | 400
+(1 row)
+
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
QUERY PLAN
@@ -10342,8 +10395,6 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
(1 row)
-- Check with foreign modify
-CREATE TABLE local_tbl (a int, b int, c text);
-INSERT INTO local_tbl VALUES (1505, 505, 'foo');
CREATE TABLE base_tbl3 (a int, b int, c text);
CREATE FOREIGN TABLE remote_tbl (a int, b int, c text)
SERVER loopback OPTIONS (table_name 'base_tbl3');
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index f15c97ad7a..bd8fda9e91 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -503,6 +503,7 @@ static void analyze_row_processor(PGresult *res, int row,
PgFdwAnalyzeState *astate);
static void produce_tuple_asynchronously(AsyncRequest *areq, bool fetch);
static void fetch_more_data_begin(AsyncRequest *areq);
+static void complete_pending_request(AsyncRequest *areq);
static HeapTuple make_tuple_from_result_row(PGresult *res,
int row,
Relation rel,
@@ -6826,6 +6827,22 @@ postgresForeignAsyncConfigureWait(AsyncRequest *areq)
/* This should not be called unless callback_pending */
Assert(areq->callback_pending);
+ /*
+ * If process_pending_request() has been invoked on the given request
+ * before we get here, we might have some tuples already; in which case
+ * complete the request
+ */
+ if (fsstate->next_tuple < fsstate->num_tuples)
+ {
+ complete_pending_request(areq);
+ if (areq->request_complete)
+ return;
+ Assert(areq->callback_pending);
+ }
+
+ /* We must have run out of tuples */
+ Assert(fsstate->next_tuple >= fsstate->num_tuples);
+
/* The core code would have registered postmaster death event */
Assert(GetNumRegisteredWaitEvents(set) >= 1);
@@ -6838,12 +6855,15 @@ postgresForeignAsyncConfigureWait(AsyncRequest *areq)
* This is the case when the in-process request was made by another
* Append. Note that it might be useless to process the request,
* because the query might not need tuples from that Append anymore.
- * Skip the given request if there are any configured events other
- * than the postmaster death event; otherwise process the request,
- * then begin a fetch to configure the event below, because otherwise
- * we might end up with no configured events other than the postmaster
- * death event.
+ * If there are any child subplans of the same parent that are ready
+ * for new requests, skip the given request. Likewise, if there are
+ * any configured events other than the postmaster death event, skip
+ * it. Otherwise, process the in-process request, then begin a fetch
+ * to configure the event below, because we might otherwise end up
+ * with no configured events other than the postmaster death event.
*/
+ if (!bms_is_empty(requestor->as_needrequest))
+ return;
if (GetNumRegisteredWaitEvents(set) > 1)
return;
process_pending_request(pendingAreq);
@@ -6995,23 +7015,44 @@ process_pending_request(AsyncRequest *areq)
{
ForeignScanState *node = (ForeignScanState *) areq->requestee;
PgFdwScanState *fsstate PG_USED_FOR_ASSERTS_ONLY = (PgFdwScanState *) node->fdw_state;
- EState *estate = node->ss.ps.state;
- MemoryContext oldcontext;
+
+ /* The request would have been pending for a callback */
+ Assert(areq->callback_pending);
/* The request should be currently in-process */
Assert(fsstate->conn_state->pendingAreq == areq);
- oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
+ fetch_more_data(node);
+ /*
+ * If we didn't get any tuples, must be end of data; complete the request
+ * now. Otherwise, we postpone completing the request until we are called
+ * from postgresForeignAsyncConfigureWait().
+ */
+ if (fsstate->next_tuple >= fsstate->num_tuples)
+ {
+ /* Unlike AsyncNotify, we unset callback_pending ourselves */
+ areq->callback_pending = false;
+ /* Mark the request as complete */
+ ExecAsyncRequestDone(areq, NULL);
+ /* Unlike AsyncNotify, we call ExecAsyncResponse ourselves */
+ ExecAsyncResponse(areq);
+ }
+}
+
+/*
+ * Complete a pending asynchronous request.
+ */
+static void
+complete_pending_request(AsyncRequest *areq)
+{
/* The request would have been pending for a callback */
Assert(areq->callback_pending);
/* Unlike AsyncNotify, we unset callback_pending ourselves */
areq->callback_pending = false;
- fetch_more_data(node);
-
- /* We need to send a new query afterwards; don't fetch */
+ /* We begin a fetch afterwards if necessary; don't fetch */
produce_tuple_asynchronously(areq, false);
/* Unlike AsyncNotify, we call ExecAsyncResponse ourselves */
@@ -7021,8 +7062,6 @@ process_pending_request(AsyncRequest *areq)
if (areq->requestee->instrument)
InstrUpdateTupleCount(areq->requestee->instrument,
TupIsNull(areq->result) ? 0.0 : 1.0);
-
- MemoryContextSwitchTo(oldcontext);
}
/*
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 02a6b15a13..75fff9bad0 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3274,6 +3274,16 @@ EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM async_pt t1, async_p2 t2 WHERE t1.a = t2.a AND t1.b === 505;
SELECT * FROM async_pt t1, async_p2 t2 WHERE t1.a = t2.a AND t1.b === 505;
+CREATE TABLE local_tbl (a int, b int, c text);
+INSERT INTO local_tbl VALUES (1505, 505, 'foo');
+ANALYZE local_tbl;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+SELECT * FROM local_tbl t1 LEFT JOIN (SELECT *, (SELECT count(*) FROM async_pt WHERE a < 3000) FROM async_pt WHERE a < 3000) t2 ON t1.a = t2.a;
+
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
@@ -3281,9 +3291,6 @@ SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
SELECT * FROM async_pt t1 WHERE t1.b === 505 LIMIT 1;
-- Check with foreign modify
-CREATE TABLE local_tbl (a int, b int, c text);
-INSERT INTO local_tbl VALUES (1505, 505, 'foo');
-
CREATE TABLE base_tbl3 (a int, b int, c text);
CREATE FOREIGN TABLE remote_tbl (a int, b int, c text)
SERVER loopback OPTIONS (table_name 'base_tbl3');
diff --git a/src/backend/executor/nodeAppend.c b/src/backend/executor/nodeAppend.c
index 755c1392f0..a4eef19d7f 100644
--- a/src/backend/executor/nodeAppend.c
+++ b/src/backend/executor/nodeAppend.c
@@ -1043,6 +1043,17 @@ ExecAppendAsyncEventWait(AppendState *node)
ExecAsyncConfigureWait(areq);
}
+ /*
+ * No need for further processing if there are no configured events other
+ * than the postmaster death event.
+ */
+ if (GetNumRegisteredWaitEvents(node->as_eventset) == 1)
+ {
+ FreeWaitEventSet(node->as_eventset);
+ node->as_eventset = NULL;
+ return;
+ }
+
/* We wait on at most EVENT_BUFFER_SIZE events. */
if (nevents > EVENT_BUFFER_SIZE)
nevents = EVENT_BUFFER_SIZE;
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-27 06:22 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-27 10:50 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2021-07-27 11:00 ` Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-28 03:35 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2 siblings, 1 reply; 20+ messages in thread
From: Andrey V. Lepikhov @ 2021-07-27 11:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
On 7/27/21 3:50 PM, Etsuro Fujita wrote:
> On Tue, Jul 27, 2021 at 3:22 PM Andrey V. Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
>> >> Maybe we can split async logic into:
>> >> - receiving stage, when we only fetch and store tuples,
>> >> - evaluating stage, when we form resulting tuple and return by a
>> >> scan node.
>> >> I will think about such solution more.
>
>> > One simple solution along this line I came up with, which is not the
>> > rewrite, is to 1) split process_pending_request() into the two steps,
>> > and 2) postpone the second step until we are called from
>> > postgresForeignAsyncConfigureWait(), like the attached, which I think
>> > would be much consistent with the existing logic.
>
>> Good idea. Are you planning to commit this patch?
>
> Cool!
>
> Here is an updated version of the patch, in which I added/tweaked
> comments a bit further. I'm planning to push this version if there
> are no objections from others.
Thanks. In addition, I will use this patch in TPC-H benchmark tomorrow.
>
>> >> Also, may be you tell your opinion about an additional optimization
>> >> of Async Append [1]?
>
>> > Is the optimization related to this issue?
>
>> This optimization tries to postpone choice of async subplans. It allows
>> us to make a decision on async capable subplans after all plan
>> flattening operations.
>
> Thanks for the explanation! My understanding is that the optimization
> isn’t related to this issue. Right?
Right
--
regards,
Andrey Lepikhov
Postgres Professional
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-27 06:22 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-27 10:50 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-27 11:00 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
@ 2021-07-28 03:35 ` Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Etsuro Fujita @ 2021-07-28 03:35 UTC (permalink / raw)
To: Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Jul 27, 2021 at 8:01 PM Andrey V. Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> On 7/27/21 3:50 PM, Etsuro Fujita wrote:
> > On Tue, Jul 27, 2021 at 3:22 PM Andrey V. Lepikhov
> > <a.lepikhov@postgrespro.ru> wrote:
> >> >> Also, may be you tell your opinion about an additional optimization
> >> >> of Async Append [1]?
> >
> >> > Is the optimization related to this issue?
> >
> >> This optimization tries to postpone choice of async subplans. It allows
> >> us to make a decision on async capable subplans after all plan
> >> flattening operations.
> >
> > Thanks for the explanation! My understanding is that the optimization
> > isn’t related to this issue. Right?
> Right
OK
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-27 06:22 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-27 10:50 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2021-07-28 06:39 ` Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-28 08:28 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2 siblings, 1 reply; 20+ messages in thread
From: Andrey V. Lepikhov @ 2021-07-28 06:39 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; CAPmGK15eY_P5AEC6-HjZusx2euftT0KD94ywaeJ=f3NGTLJEFw@mail.gmail.com; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On 7/27/21 3:50 PM, Etsuro Fujita wrote:
> On Tue, Jul 27, 2021 at 3:22 PM Andrey V. Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
> Here is an updated version of the patch, in which I added/tweaked
> comments a bit further. I'm planning to push this version if there
> are no objections from others.
I used this patch in TPC-H benchmark (that caused initial error) and it
finished without problems.
Queries and their explains are attached. You could look for situations
when async append was not used.
--
regards,
Andrey Lepikhov
Postgres Professional
SET
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=541790.95..541790.97 rows=6 width=236) (actual time=25244.764..25244.766 rows=4 loops=1)
Sort Key: lineitem.l_returnflag, lineitem.l_linestatus
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=541790.71..541790.87 rows=6 width=236) (actual time=25244.723..25244.737 rows=4 loops=1)
Group Key: lineitem.l_returnflag, lineitem.l_linestatus
Batches: 1 Memory Usage: 32kB
-> Append (cost=100.00..335245.49 rows=5901292 width=25) (actual time=2.342..12993.013 rows=5900882 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..152623.94 rows=2945467 width=25) (actual time=1.322..6075.492 rows=2945324 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..153115.09 rows=2955825 width=25) (actual time=1.194..6083.693 rows=2955558 loops=1)
Planning Time: 2.485 ms
Execution Time: 25250.010 ms
(11 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=130984.64..130984.64 rows=3 width=193) (actual time=2538.957..2538.985 rows=100 loops=1)
-> Sort (cost=130984.64..130984.64 rows=3 width=193) (actual time=2538.956..2538.978 rows=100 loops=1)
Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
Sort Method: top-N heapsort Memory: 70kB
-> Hash Join (cost=122359.58..130984.61 rows=3 width=193) (actual time=2423.360..2538.383 rows=492 loops=1)
Hash Cond: ((partsupp_3.ps_partkey = part.p_partkey) AND ((min(partsupp_3.ps_supplycost)) = partsupp.ps_supplycost))
-> HashAggregate (cost=64908.83..70033.83 rows=200000 width=36) (actual time=1220.950..1312.846 rows=116165 loops=1)
Group Key: partsupp_3.ps_partkey
Planned Partitions: 16 Batches: 17 Memory Usage: 4241kB Disk Usage: 6952kB
-> Hash Join (cost=1405.83..53158.83 rows=320000 width=10) (actual time=13.600..1147.781 rows=156400 loops=1)
Hash Cond: (partsupp_3.ps_suppkey = supplier_3.s_suppkey)
-> Append (cost=100.00..45653.00 rows=800000 width=14) (actual time=0.557..1015.506 rows=800000 loops=1)
-> Async Foreign Scan on partsupp_1 partsupp_4 (cost=100.00..20745.92 rows=398464 width=14) (actual time=0.189..404.946 rows=398464 loops=1)
-> Async Foreign Scan on partsupp_2 partsupp_5 (cost=100.00..20907.08 rows=401536 width=14) (actual time=0.217..405.543 rows=401536 loops=1)
-> Hash (cost=1255.83..1255.83 rows=4000 width=4) (actual time=13.016..13.022 rows=1955 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 101kB
-> Hash Join (cost=505.33..1255.83 rows=4000 width=4) (actual time=1.034..12.635 rows=1955 loops=1)
Hash Cond: (supplier_3.s_nationkey = nation_3.n_nationkey)
-> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.344..10.612 rows=10000 loops=1)
-> Async Foreign Scan on supplier_1 supplier_4 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.214..3.460 rows=5019 loops=1)
-> Async Foreign Scan on supplier_2 supplier_5 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.128..3.265 rows=4981 loops=1)
-> Hash (cost=405.21..405.21 rows=10 width=4) (actual time=0.680..0.684 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=302.14..405.21 rows=10 width=4) (actual time=0.664..0.681 rows=5 loops=1)
Hash Cond: (nation_3.n_regionkey = region_3.r_regionkey)
-> Append (cost=100.00..202.88 rows=25 width=8) (actual time=0.282..0.293 rows=25 loops=1)
-> Async Foreign Scan on nation_1 nation_4 (cost=100.00..101.30 rows=10 width=8) (actual time=0.193..0.196 rows=10 loops=1)
-> Async Foreign Scan on nation_2 nation_5 (cost=100.00..101.45 rows=15 width=8) (actual time=0.086..0.089 rows=15 loops=1)
-> Hash (cost=202.11..202.11 rows=2 width=4) (actual time=0.373..0.375 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Append (cost=100.00..202.11 rows=2 width=4) (actual time=0.370..0.372 rows=1 loops=1)
-> Async Foreign Scan on region_1 region_4 (cost=100.00..101.06 rows=1 width=4) (actual time=0.166..0.167 rows=1 loops=1)
-> Async Foreign Scan on region_2 region_5 (cost=100.00..101.05 rows=1 width=4) (actual time=0.116..0.116 rows=0 loops=1)
-> Hash (cost=57431.31..57431.31 rows=1296 width=203) (actual time=1202.205..1202.215 rows=670 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 172kB
-> Hash Join (cost=8731.70..57431.31 rows=1296 width=203) (actual time=98.251..1201.510 rows=670 loops=1)
Hash Cond: (nation.n_regionkey = region.r_regionkey)
-> Hash Join (cost=8529.56..57204.06 rows=3240 width=207) (actual time=96.874..1199.782 rows=3292 loops=1)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Hash Join (cost=8326.38..56956.33 rows=3240 width=181) (actual time=96.217..1197.800 rows=3292 loops=1)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Hash Join (cost=7428.38..56013.78 rows=3240 width=44) (actual time=74.400..1173.755 rows=3292 loops=1)
Hash Cond: (partsupp.ps_partkey = part.p_partkey)
-> Append (cost=100.00..45653.00 rows=800000 width=14) (actual time=0.869..1011.801 rows=800000 loops=1)
-> Async Foreign Scan on partsupp_1 (cost=100.00..20745.92 rows=398464 width=14) (actual time=0.453..434.194 rows=398464 loops=1)
-> Async Foreign Scan on partsupp_2 (cost=100.00..20907.08 rows=401536 width=14) (actual time=0.413..413.732 rows=401536 loops=1)
-> Hash (cost=7318.25..7318.25 rows=810 width=30) (actual time=72.484..72.485 rows=823 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 58kB
-> Append (cost=100.00..7318.25 rows=810 width=30) (actual time=9.539..72.134 rows=823 loops=1)
-> Async Foreign Scan on part_1 (cost=100.00..3643.28 rows=402 width=30) (actual time=0.648..1.192 rows=407 loops=1)
-> Async Foreign Scan on part_2 (cost=100.00..3670.92 rows=408 width=30) (actual time=0.895..1.389 rows=416 loops=1)
-> Hash (cost=773.00..773.00 rows=10000 width=145) (actual time=21.777..21.778 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 1887kB
-> Append (cost=100.00..773.00 rows=10000 width=145) (actual time=1.536..18.268 rows=10000 loops=1)
-> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=145) (actual time=0.937..8.694 rows=5019 loops=1)
-> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=144) (actual time=0.728..8.309 rows=4981 loops=1)
-> Hash (cost=202.88..202.88 rows=25 width=34) (actual time=0.648..0.649 rows=25 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Append (cost=100.00..202.88 rows=25 width=34) (actual time=0.554..0.641 rows=25 loops=1)
-> Async Foreign Scan on nation_1 (cost=100.00..101.30 rows=10 width=34) (actual time=0.334..0.336 rows=10 loops=1)
-> Async Foreign Scan on nation_2 (cost=100.00..101.45 rows=15 width=34) (actual time=0.230..0.233 rows=15 loops=1)
-> Hash (cost=202.11..202.11 rows=2 width=4) (actual time=1.085..1.086 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Append (cost=100.00..202.11 rows=2 width=4) (actual time=1.017..1.082 rows=1 loops=1)
-> Async Foreign Scan on region_1 (cost=100.00..101.06 rows=1 width=4) (actual time=0.618..0.618 rows=1 loops=1)
-> Async Foreign Scan on region_2 (cost=100.00..101.05 rows=1 width=4) (actual time=0.398..0.398 rows=0 loops=1)
Planning Time: 3.578 ms
Execution Time: 2541.580 ms
(68 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=497159.36..497159.38 rows=10 width=44) (actual time=8096.572..8096.580 rows=10 loops=1)
Output: lineitem.l_orderkey, (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))), orders.o_orderdate, orders.o_shippriority
-> Sort (cost=497159.36..499634.08 rows=989888 width=44) (actual time=8096.569..8096.576 rows=10 loops=1)
Output: lineitem.l_orderkey, (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))), orders.o_orderdate, orders.o_shippriority
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
Sort Method: top-N heapsort Memory: 26kB
-> HashAggregate (cost=451794.38..475768.23 rows=989888 width=44) (actual time=8069.424..8092.794 rows=11426 loops=1)
Output: lineitem.l_orderkey, sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))), orders.o_orderdate, orders.o_shippriority
Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
Planned Partitions: 128 Batches: 129 Memory Usage: 4305kB Disk Usage: 8136kB
-> Hash Join (cost=78641.75..403228.00 rows=989888 width=24) (actual time=1360.433..8030.329 rows=30268 loops=1)
Output: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority, lineitem.l_extendedprice, lineitem.l_discount
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Append (cost=100.00..269502.91 rows=3271589 width=16) (actual time=0.925..5389.498 rows=3266925 loops=1)
-> Async Foreign Scan on public.lineitem_1 (cost=100.00..126428.18 rows=1635679 width=16) (actual time=0.555..2192.645 rows=1631510 loops=1)
Output: lineitem_1.l_orderkey, lineitem_1.l_extendedprice, lineitem_1.l_discount
Remote SQL: SELECT l_orderkey, l_extendedprice, l_discount FROM public.lineitem_1 WHERE ((l_shipdate > '1995-03-05'::date))
-> Async Foreign Scan on public.lineitem_2 (cost=100.00..126716.79 rows=1635910 width=16) (actual time=0.366..2220.283 rows=1635415 loops=1)
Output: lineitem_2.l_orderkey, lineitem_2.l_extendedprice, lineitem_2.l_discount
Remote SQL: SELECT l_orderkey, l_extendedprice, l_discount FROM public.lineitem_2 WHERE ((l_shipdate > '1995-03-05'::date))
-> Hash (cost=74748.68..74748.68 rows=218166 width=12) (actual time=1358.493..1358.497 rows=143880 loops=1)
Output: orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
Buckets: 131072 Batches: 4 Memory Usage: 2559kB
-> Hash Join (cost=6892.09..74748.68 rows=218166 width=12) (actual time=114.062..1318.307 rows=143880 loops=1)
Output: orders.o_orderdate, orders.o_shippriority, orders.o_orderkey
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Append (cost=100.00..63071.03 rows=721041 width=16) (actual time=0.824..1080.903 rows=721026 loops=1)
-> Async Foreign Scan on public.orders_1 (cost=100.00..29691.65 rows=359777 width=16) (actual time=0.552..465.150 rows=359777 loops=1)
Output: orders_1.o_orderdate, orders_1.o_shippriority, orders_1.o_custkey, orders_1.o_orderkey
Remote SQL: SELECT o_orderkey, o_custkey, o_orderdate, o_shippriority FROM public.orders_1 WHERE ((o_orderdate < '1995-03-05'::date))
-> Async Foreign Scan on public.orders_2 (cost=100.00..29774.17 rows=361264 width=16) (actual time=0.555..463.040 rows=361249 loops=1)
Output: orders_2.o_orderdate, orders_2.o_shippriority, orders_2.o_custkey, orders_2.o_orderkey
Remote SQL: SELECT o_orderkey, o_custkey, o_orderdate, o_shippriority FROM public.orders_2 WHERE ((o_orderdate < '1995-03-05'::date))
-> Hash (cost=6414.72..6414.72 rows=30189 width=4) (actual time=113.214..113.216 rows=30189 loops=1)
Output: customer.c_custkey
Buckets: 32768 Batches: 1 Memory Usage: 1318kB
-> Append (cost=100.00..6414.72 rows=30189 width=4) (actual time=0.639..104.931 rows=30189 loops=1)
-> Async Foreign Scan on public.customer_1 (cost=100.00..3130.05 rows=15058 width=4) (actual time=0.333..12.782 rows=15058 loops=1)
Output: customer_1.c_custkey
Remote SQL: SELECT c_custkey FROM public.customer_1 WHERE ((c_mktsegment = 'HOUSEHOLD'::bpchar))
-> Async Foreign Scan on public.customer_2 (cost=100.00..3133.73 rows=15131 width=4) (actual time=0.711..13.153 rows=15131 loops=1)
Output: customer_2.c_custkey
Remote SQL: SELECT c_custkey FROM public.customer_2 WHERE ((c_mktsegment = 'HOUSEHOLD'::bpchar))
Planning Time: 1.536 ms
Execution Time: 8099.098 ms
(45 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=330646.98..330647.00 rows=5 width=24) (actual time=8999.679..8999.683 rows=5 loops=1)
Sort Key: orders.o_orderpriority
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=330646.87..330646.92 rows=5 width=24) (actual time=8999.666..8999.671 rows=5 loops=1)
Group Key: orders.o_orderpriority
Batches: 1 Memory Usage: 24kB
-> Hash Semi Join (cost=270643.38..330363.92 rows=56590 width=16) (actual time=8193.905..8979.742 rows=51942 loops=1)
Hash Cond: (orders.o_orderkey = lineitem.l_orderkey)
-> Append (cost=100.00..50209.75 rows=56590 width=20) (actual time=1.731..214.789 rows=56592 loops=1)
-> Async Foreign Scan on orders_1 (cost=100.00..24934.46 rows=28286 width=20) (actual time=0.562..32.222 rows=28286 loops=1)
-> Async Foreign Scan on orders_2 (cost=100.00..24992.35 rows=28304 width=20) (actual time=0.534..29.683 rows=28306 loops=1)
-> Hash (cost=237723.31..237723.31 rows=2000405 width=4) (actual time=8191.939..8191.941 rows=3793296 loops=1)
Buckets: 131072 (originally 131072) Batches: 64 (originally 32) Memory Usage: 3098kB
-> Append (cost=100.00..237723.31 rows=2000405 width=4) (actual time=0.499..7352.728 rows=3793296 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..113688.26 rows=998683 width=4) (actual time=0.275..1242.253 rows=1894002 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..114033.03 rows=1001722 width=4) (actual time=1.898..1312.056 rows=1899294 loops=1)
Planning Time: 0.787 ms
Execution Time: 9000.667 ms
(18 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=498693.90..498693.97 rows=25 width=58) (actual time=12498.406..12498.420 rows=5 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: quicksort Memory: 25kB
-> GroupAggregate (cost=498473.70..498693.32 rows=25 width=58) (actual time=12494.016..12498.406 rows=5 loops=1)
Group Key: nation.n_name
-> Sort (cost=498473.70..498517.56 rows=17545 width=38) (actual time=12492.744..12493.191 rows=6991 loops=1)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 793kB
-> Hash Join (cost=73200.38..497236.88 rows=17545 width=38) (actual time=597.255..12487.603 rows=6991 loops=1)
Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
-> Hash Join (cost=72277.38..492848.72 rows=438628 width=50) (actual time=574.241..12438.067 rows=181249 loops=1)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Append (cost=100.00..322740.53 rows=6001215 width=20) (actual time=0.525..9967.582 rows=6001215 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..146145.44 rows=2996048 width=20) (actual time=0.346..4327.560 rows=2996048 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..146589.01 rows=3005167 width=20) (actual time=0.146..4328.614 rows=3005167 loops=1)
-> Hash (cost=70326.43..70326.43 rows=91116 width=38) (actual time=572.345..572.353 rows=45450 loops=1)
Buckets: 65536 Batches: 2 Memory Usage: 2094kB
-> Hash Join (cost=11922.28..70326.43 rows=91116 width=38) (actual time=180.940..559.798 rows=45450 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Append (cost=100.00..54489.78 rows=227791 width=8) (actual time=0.571..310.404 rows=227783 loops=1)
-> Async Foreign Scan on orders_1 (cost=100.00..26646.11 rows=113869 width=8) (actual time=0.264..80.716 rows=113869 loops=1)
-> Async Foreign Scan on orders_2 (cost=100.00..26704.71 rows=113922 width=8) (actual time=0.308..80.121 rows=113914 loops=1)
-> Hash (cost=10603.28..10603.28 rows=60000 width=38) (actual time=180.257..180.263 rows=29764 loops=1)
Buckets: 65536 Batches: 2 Memory Usage: 1616kB
-> Hash Join (cost=505.78..10603.28 rows=60000 width=38) (actual time=1.479..170.724 rows=29764 loops=1)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Append (cost=100.00..9035.00 rows=150000 width=8) (actual time=0.261..145.165 rows=150000 loops=1)
-> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=8) (actual time=0.145..59.515 rows=74951 loops=1)
-> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=8) (actual time=0.159..55.517 rows=75049 loops=1)
-> Hash (cost=405.66..405.66 rows=10 width=30) (actual time=1.214..1.218 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=302.14..405.66 rows=10 width=30) (actual time=0.834..1.215 rows=5 loops=1)
Hash Cond: (nation.n_regionkey = region.r_regionkey)
-> Append (cost=100.00..203.32 rows=25 width=34) (actual time=0.394..0.767 rows=25 loops=1)
-> Async Foreign Scan on nation_1 (cost=100.00..101.52 rows=10 width=34) (actual time=0.307..0.310 rows=10 loops=1)
-> Async Foreign Scan on nation_2 (cost=100.00..101.68 rows=15 width=34) (actual time=0.273..0.276 rows=15 loops=1)
-> Hash (cost=202.11..202.11 rows=2 width=4) (actual time=0.434..0.436 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Append (cost=100.00..202.11 rows=2 width=4) (actual time=0.155..0.434 rows=1 loops=1)
-> Async Foreign Scan on region_1 (cost=100.00..101.06 rows=1 width=4) (actual time=0.102..0.102 rows=1 loops=1)
-> Async Foreign Scan on region_2 (cost=100.00..101.05 rows=1 width=4) (actual time=0.229..0.229 rows=0 loops=1)
-> Hash (cost=773.00..773.00 rows=10000 width=8) (actual time=15.796..15.797 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 519kB
-> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.580..12.859 rows=10000 loops=1)
-> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.377..5.767 rows=5019 loops=1)
-> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.286..3.970 rows=4981 loops=1)
Planning Time: 3.607 ms
Execution Time: 12500.664 ms
(48 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=251311.72..251311.73 rows=1 width=32) (actual time=1028.392..1028.394 rows=1 loops=1)
-> Append (cost=100.00..250713.99 rows=119546 width=12) (actual time=4.906..951.524 rows=119466 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..124871.34 rows=59813 width=12) (actual time=0.967..107.461 rows=59780 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..125244.92 rows=59733 width=12) (actual time=0.949..107.852 rows=59686 loops=1)
Planning Time: 0.477 ms
Execution Time: 1028.805 ms
(6 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=371693.19..372458.21 rows=23539 width=116) (actual time=6308.687..6313.391 rows=4 loops=1)
Group Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate))
-> Sort (cost=371693.19..371752.04 rows=23539 width=96) (actual time=6307.089..6307.468 rows=5996 loops=1)
Sort Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate))
Sort Method: quicksort Memory: 1083kB
-> Hash Join (cost=102950.05..369983.94 rows=23539 width=96) (actual time=1874.259..6300.937 rows=5996 loops=1)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
Join Filter: (((n1.n_name = 'FRANCE'::bpchar) AND (n2.n_name = 'ROMANIA'::bpchar)) OR ((n1.n_name = 'ROMANIA'::bpchar) AND (n2.n_name = 'FRANCE'::bpchar)))
Rows Removed by Join Filter: 5931
-> Hash Join (cost=1149.03..259300.31 rows=292771 width=46) (actual time=16.039..4345.394 rows=145583 loops=1)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Append (cost=100.00..248461.75 rows=1829821 width=24) (actual time=0.916..4091.607 rows=1828450 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..119485.96 rows=914062 width=24) (actual time=0.576..1838.677 rows=912708 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..119826.68 rows=915759 width=24) (actual time=0.336..1801.642 rows=915742 loops=1)
-> Hash (cost=1029.03..1029.03 rows=1600 width=30) (actual time=15.110..15.114 rows=800 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 65kB
-> Hash Join (cost=302.53..1029.03 rows=1600 width=30) (actual time=1.362..14.927 rows=800 loops=1)
Hash Cond: (supplier.s_nationkey = n1.n_nationkey)
-> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.403..12.829 rows=10000 loops=1)
-> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.161..3.401 rows=5019 loops=1)
-> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.192..3.414 rows=4981 loops=1)
-> Hash (cost=202.47..202.47 rows=4 width=30) (actual time=0.947..0.949 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Append (cost=100.00..202.47 rows=4 width=30) (actual time=0.942..0.944 rows=2 loops=1)
-> Async Foreign Scan on nation_1 n1_1 (cost=100.00..101.19 rows=2 width=30) (actual time=0.262..0.262 rows=0 loops=1)
-> Async Foreign Scan on nation_2 n1_2 (cost=100.00..101.26 rows=2 width=30) (actual time=0.458..0.459 rows=2 loops=1)
-> Hash (cost=97160.02..97160.02 rows=240000 width=30) (actual time=1852.199..1852.204 rows=122612 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 1437kB
-> Hash Join (cost=10440.02..97160.02 rows=240000 width=30) (actual time=164.806..1815.875 rows=122612 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Append (cost=100.00..78795.00 rows=1500000 width=8) (actual time=0.269..1449.405 rows=1500000 loops=1)
-> Async Foreign Scan on orders_1 (cost=100.00..35604.47 rows=749049 width=8) (actual time=0.146..565.476 rows=749049 loops=1)
-> Async Foreign Scan on orders_2 (cost=100.00..35690.53 rows=750951 width=8) (actual time=0.093..547.274 rows=750951 loops=1)
-> Hash (cost=10040.02..10040.02 rows=24000 width=30) (actual time=164.507..164.510 rows=12200 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 995kB
-> Hash Join (cost=302.53..10040.02 rows=24000 width=30) (actual time=0.625..161.416 rows=12200 loops=1)
Hash Cond: (customer.c_nationkey = n2.n_nationkey)
-> Append (cost=100.00..9035.00 rows=150000 width=8) (actual time=0.237..143.225 rows=150000 loops=1)
-> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=8) (actual time=0.123..51.478 rows=74951 loops=1)
-> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=8) (actual time=0.126..51.130 rows=75049 loops=1)
-> Hash (cost=202.47..202.47 rows=4 width=30) (actual time=0.380..0.382 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Append (cost=100.00..202.47 rows=4 width=30) (actual time=0.377..0.379 rows=2 loops=1)
-> Async Foreign Scan on nation_1 n2_1 (cost=100.00..101.19 rows=2 width=30) (actual time=0.118..0.118 rows=0 loops=1)
-> Async Foreign Scan on nation_2 n2_2 (cost=100.00..101.26 rows=2 width=30) (actual time=0.163..0.164 rows=2 loops=1)
Planning Time: 2.529 ms
Execution Time: 6314.653 ms
(47 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=433678.24..433860.26 rows=2406 width=64) (actual time=12489.940..12491.218 rows=2 loops=1)
Group Key: (EXTRACT(year FROM orders.o_orderdate))
-> Sort (cost=433678.24..433693.12 rows=5951 width=70) (actual time=12488.592..12488.765 rows=2599 loops=1)
Sort Key: (EXTRACT(year FROM orders.o_orderdate))
Sort Method: quicksort Memory: 320kB
-> Hash Join (cost=86173.27..433305.15 rows=5951 width=70) (actual time=986.484..12487.115 rows=2599 loops=1)
Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
-> Hash Join (cost=85970.08..433005.25 rows=5951 width=20) (actual time=986.113..12485.007 rows=2599 loops=1)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Hash Join (cost=85072.08..432025.43 rows=5951 width=20) (actual time=973.354..12470.924 rows=2599 loops=1)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Hash Join (cost=6948.55..352499.27 rows=40564 width=20) (actual time=48.158..11526.713 rows=40752 loops=1)
Hash Cond: (lineitem.l_partkey = part.p_partkey)
-> Append (cost=100.00..322740.53 rows=6001215 width=24) (actual time=0.545..10824.585 rows=6001215 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..146145.44 rows=2996048 width=24) (actual time=0.298..4673.747 rows=2996048 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..146589.01 rows=3005167 width=24) (actual time=0.180..4657.524 rows=3005167 loops=1)
-> Hash (cost=6831.70..6831.70 rows=1348 width=4) (actual time=46.655..46.656 rows=1348 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 64kB
-> Append (cost=100.00..6831.70 rows=1348 width=4) (actual time=3.699..46.228 rows=1348 loops=1)
-> Async Foreign Scan on part_1 (cost=100.00..3399.64 rows=672 width=4) (actual time=0.717..1.290 rows=672 loops=1)
-> Async Foreign Scan on part_2 (cost=100.00..3425.32 rows=676 width=4) (actual time=1.099..1.816 rows=676 loops=1)
-> Hash (cost=75122.34..75122.34 rows=182895 width=8) (actual time=912.628..912.636 rows=92715 loops=1)
Buckets: 131072 Batches: 4 Memory Usage: 1923kB
-> Hash Join (cost=11452.83..75122.34 rows=182895 width=8) (actual time=161.443..889.333 rows=92715 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Append (cost=100.00..60225.92 rows=457237 width=12) (actual time=1.023..657.041 rows=457263 loops=1)
-> Async Foreign Scan on orders_1 (cost=100.00..28933.29 rows=228228 width=12) (actual time=0.624..266.960 rows=228228 loops=1)
-> Async Foreign Scan on orders_2 (cost=100.00..29006.44 rows=229009 width=12) (actual time=0.216..267.366 rows=229035 loops=1)
-> Hash (cost=10602.83..10602.83 rows=60000 width=4) (actual time=160.369..160.374 rows=30197 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 1574kB
-> Hash Join (cost=505.33..10602.83 rows=60000 width=4) (actual time=1.584..154.393 rows=30197 loops=1)
Hash Cond: (customer.c_nationkey = n1.n_nationkey)
-> Append (cost=100.00..9035.00 rows=150000 width=8) (actual time=0.469..131.462 rows=150000 loops=1)
-> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=8) (actual time=0.248..52.895 rows=74951 loops=1)
-> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=8) (actual time=0.171..52.690 rows=75049 loops=1)
-> Hash (cost=405.21..405.21 rows=10 width=4) (actual time=1.098..1.101 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=302.14..405.21 rows=10 width=4) (actual time=0.911..1.098 rows=5 loops=1)
Hash Cond: (n1.n_regionkey = region.r_regionkey)
-> Append (cost=100.00..202.88 rows=25 width=8) (actual time=0.267..0.451 rows=25 loops=1)
-> Async Foreign Scan on nation_1 n1_1 (cost=100.00..101.30 rows=10 width=8) (actual time=0.163..0.167 rows=10 loops=1)
-> Async Foreign Scan on nation_2 n1_2 (cost=100.00..101.45 rows=15 width=8) (actual time=0.106..0.111 rows=15 loops=1)
-> Hash (cost=202.11..202.11 rows=2 width=4) (actual time=0.627..0.628 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Append (cost=100.00..202.11 rows=2 width=4) (actual time=0.624..0.625 rows=1 loops=1)
-> Async Foreign Scan on region_1 (cost=100.00..101.06 rows=1 width=4) (actual time=0.182..0.182 rows=0 loops=1)
-> Async Foreign Scan on region_2 (cost=100.00..101.05 rows=1 width=4) (actual time=0.291..0.292 rows=1 loops=1)
-> Hash (cost=773.00..773.00 rows=10000 width=8) (actual time=12.746..12.748 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 519kB
-> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.326..10.876 rows=10000 loops=1)
-> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.164..3.426 rows=5019 loops=1)
-> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.125..3.330 rows=4981 loops=1)
-> Hash (cost=202.88..202.88 rows=25 width=30) (actual time=0.361..0.362 rows=25 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Append (cost=100.00..202.88 rows=25 width=30) (actual time=0.298..0.355 rows=25 loops=1)
-> Async Foreign Scan on nation_1 n2_1 (cost=100.00..101.30 rows=10 width=30) (actual time=0.189..0.192 rows=10 loops=1)
-> Async Foreign Scan on nation_2 n2_2 (cost=100.00..101.45 rows=15 width=30) (actual time=0.115..0.118 rows=15 loops=1)
Planning Time: 2.054 ms
Execution Time: 12492.715 ms
(59 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=510075.80..510079.93 rows=118 width=90) (actual time=17731.007..18339.274 rows=175 loops=1)
Group Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate))
-> Sort (cost=510075.80..510076.10 rows=118 width=81) (actual time=17728.562..17922.589 rows=322637 loops=1)
Sort Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate)) DESC
Sort Method: external merge Disk: 21016kB
-> Hash Join (cost=425747.02..510071.74 rows=118 width=81) (actual time=14626.225..17345.337 rows=322637 loops=1)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Hash Join (cost=425543.83..509866.63 rows=118 width=31) (actual time=14625.809..17182.229 rows=322637 loops=1)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Hash Join (cost=424645.83..508967.01 rows=118 width=35) (actual time=14609.559..17064.342 rows=322637 loops=1)
Hash Cond: (orders.o_orderkey = lineitem.l_orderkey)
-> Append (cost=100.00..78795.00 rows=1500000 width=8) (actual time=0.419..1798.362 rows=1500000 loops=1)
-> Async Foreign Scan on orders_1 (cost=100.00..35604.47 rows=749049 width=8) (actual time=0.263..781.063 rows=749049 loops=1)
-> Async Foreign Scan on orders_2 (cost=100.00..35690.53 rows=750951 width=8) (actual time=0.094..781.654 rows=750951 loops=1)
-> Hash (cost=424544.36..424544.36 rows=118 width=35) (actual time=14609.078..14609.085 rows=322637 loops=1)
Buckets: 65536 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3585kB
-> Hash Join (cost=56893.55..424544.36 rows=118 width=35) (actual time=1148.708..14479.650 rows=322637 loops=1)
Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey))
-> Append (cost=100.00..322740.53 rows=6001215 width=29) (actual time=0.617..12315.100 rows=6001215 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..146145.44 rows=2996048 width=29) (actual time=0.424..5488.474 rows=2996048 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..146589.01 rows=3005167 width=29) (actual time=0.509..5536.754 rows=3005167 loops=1)
-> Hash (cost=56207.71..56207.71 rows=39056 width=18) (actual time=1148.040..1148.044 rows=43100 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 2701kB
-> Hash Join (cost=7264.15..56207.71 rows=39056 width=18) (actual time=92.726..1134.963 rows=43100 loops=1)
Hash Cond: (partsupp.ps_partkey = part.p_partkey)
-> Append (cost=100.00..45653.00 rows=800000 width=14) (actual time=0.321..947.782 rows=800000 loops=1)
-> Async Foreign Scan on partsupp_1 (cost=100.00..20745.92 rows=398464 width=14) (actual time=0.170..402.864 rows=398464 loops=1)
-> Async Foreign Scan on partsupp_2 (cost=100.00..20907.08 rows=401536 width=14) (actual time=0.251..405.320 rows=401536 loops=1)
-> Hash (cost=7042.10..7042.10 rows=9764 width=4) (actual time=92.345..92.347 rows=10775 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 507kB
-> Append (cost=100.00..7042.10 rows=9764 width=4) (actual time=1.690..89.275 rows=10775 loops=1)
-> Async Foreign Scan on part_1 (cost=100.00..3482.66 rows=4823 width=4) (actual time=0.715..5.719 rows=5374 loops=1)
-> Async Foreign Scan on part_2 (cost=100.00..3510.62 rows=4941 width=4) (actual time=1.641..6.196 rows=5401 loops=1)
-> Hash (cost=773.00..773.00 rows=10000 width=8) (actual time=16.239..16.241 rows=10000 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 519kB
-> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.280..13.510 rows=10000 loops=1)
-> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.145..4.787 rows=5019 loops=1)
-> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.151..4.680 rows=4981 loops=1)
-> Hash (cost=202.88..202.88 rows=25 width=30) (actual time=0.405..0.406 rows=25 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Append (cost=100.00..202.88 rows=25 width=30) (actual time=0.317..0.399 rows=25 loops=1)
-> Async Foreign Scan on nation_1 (cost=100.00..101.30 rows=10 width=30) (actual time=0.175..0.177 rows=10 loops=1)
-> Async Foreign Scan on nation_2 (cost=100.00..101.45 rows=15 width=30) (actual time=0.149..0.152 rows=15 loops=1)
Planning Time: 2.903 ms
Execution Time: 18343.507 ms
(45 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=365043.50..365043.55 rows=20 width=202) (actual time=4131.852..4131.863 rows=20 loops=1)
-> Sort (cost=365043.50..365734.17 rows=276267 width=202) (actual time=4131.851..4131.860 rows=20 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 35kB
-> HashAggregate (cost=340209.62..357692.14 rows=276267 width=202) (actual time=3950.770..4114.517 rows=38263 loops=1)
Group Key: customer.c_custkey, customer.c_name, customer.c_acctbal, customer.c_phone, nation.n_name, customer.c_address, customer.c_comment
Planned Partitions: 64 Batches: 65 Memory Usage: 4313kB Disk Usage: 28504kB
-> Hash Join (cost=67885.77..310942.58 rows=276267 width=182) (actual time=816.952..3759.652 rows=114665 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Hash Join (cost=51047.09..283944.22 rows=276267 width=16) (actual time=437.957..3265.990 rows=114665 loops=1)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Append (cost=100.00..224688.21 rows=1479001 width=16) (actual time=0.873..2630.635 rows=1478870 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..108469.62 rows=737751 width=16) (actual time=0.352..1009.803 rows=738327 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..108823.59 rows=741250 width=16) (actual time=0.199..989.762 rows=740543 loops=1)
-> Hash (cost=50229.72..50229.72 rows=57389 width=8) (actual time=436.965..436.967 rows=57384 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 2754kB
-> Append (cost=100.00..50229.72 rows=57389 width=8) (actual time=1.491..419.850 rows=57384 loops=1)
-> Async Foreign Scan on orders_1 (cost=100.00..24940.92 rows=28609 width=8) (actual time=0.342..30.189 rows=28609 loops=1)
-> Async Foreign Scan on orders_2 (cost=100.00..25001.86 rows=28780 width=8) (actual time=0.533..31.017 rows=28775 loops=1)
-> Hash (cost=11300.69..11300.69 rows=150000 width=170) (actual time=378.245..378.249 rows=150000 loops=1)
Buckets: 32768 Batches: 16 Memory Usage: 2147kB
-> Hash Join (cost=303.19..11300.69 rows=150000 width=170) (actual time=1.959..314.514 rows=150000 loops=1)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Append (cost=100.00..9035.00 rows=150000 width=148) (actual time=1.337..265.914 rows=150000 loops=1)
-> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=148) (actual time=0.738..122.404 rows=74951 loops=1)
-> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=148) (actual time=0.596..122.293 rows=75049 loops=1)
-> Hash (cost=202.88..202.88 rows=25 width=30) (actual time=0.617..0.619 rows=25 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Append (cost=100.00..202.88 rows=25 width=30) (actual time=0.258..0.612 rows=25 loops=1)
-> Async Foreign Scan on nation_1 (cost=100.00..101.30 rows=10 width=30) (actual time=0.167..0.169 rows=10 loops=1)
-> Async Foreign Scan on nation_2 (cost=100.00..101.45 rows=15 width=30) (actual time=0.261..0.264 rows=15 loops=1)
Planning Time: 1.581 ms
Execution Time: 4135.998 ms
(33 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=106916.72..106970.05 rows=21333 width=36) (actual time=2310.374..2310.431 rows=919 loops=1)
Sort Key: (sum((partsupp.ps_supplycost * (partsupp.ps_availqty)::numeric))) DESC
Sort Method: quicksort Memory: 75kB
InitPlan 1 (returns $0)
-> Aggregate (cost=50803.89..50803.90 rows=1 width=32) (actual time=1083.909..1083.914 rows=1 loops=1)
-> Hash Join (cost=1130.89..50323.89 rows=64000 width=10) (actual time=12.670..1066.241 rows=32080 loops=1)
Hash Cond: (partsupp_3.ps_suppkey = supplier_3.s_suppkey)
-> Append (cost=100.00..45653.00 rows=800000 width=14) (actual time=0.564..963.091 rows=800000 loops=1)
-> Async Foreign Scan on partsupp_1 partsupp_4 (cost=100.00..20745.92 rows=398464 width=14) (actual time=0.182..413.416 rows=398464 loops=1)
-> Async Foreign Scan on partsupp_2 partsupp_5 (cost=100.00..20907.08 rows=401536 width=14) (actual time=0.225..405.205 rows=401536 loops=1)
-> Hash (cost=1020.89..1020.89 rows=800 width=4) (actual time=12.086..12.090 rows=401 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 23kB
-> Hash Join (cost=302.39..1020.89 rows=800 width=4) (actual time=0.706..12.005 rows=401 loops=1)
Hash Cond: (supplier_3.s_nationkey = nation_3.n_nationkey)
-> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.321..10.580 rows=10000 loops=1)
-> Async Foreign Scan on supplier_1 supplier_4 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.188..3.336 rows=5019 loops=1)
-> Async Foreign Scan on supplier_2 supplier_5 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.130..3.300 rows=4981 loops=1)
-> Hash (cost=202.36..202.36 rows=2 width=4) (actual time=0.372..0.374 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Append (cost=100.00..202.36 rows=2 width=4) (actual time=0.306..0.371 rows=1 loops=1)
-> Async Foreign Scan on nation_1 nation_4 (cost=100.00..101.15 rows=1 width=4) (actual time=0.186..0.186 rows=0 loops=1)
-> Async Foreign Scan on nation_2 nation_5 (cost=100.00..101.21 rows=1 width=4) (actual time=0.120..0.120 rows=1 loops=1)
-> HashAggregate (cost=52993.89..54578.89 rows=21333 width=36) (actual time=2272.513..2310.043 rows=919 loops=1)
Group Key: partsupp.ps_partkey
Filter: (sum((partsupp.ps_supplycost * (partsupp.ps_availqty)::numeric)) > $0)
Planned Partitions: 8 Batches: 9 Memory Usage: 4305kB Disk Usage: 1448kB
Rows Removed by Filter: 29289
-> Hash Join (cost=1130.89..50323.89 rows=64000 width=14) (actual time=14.908..1158.935 rows=32080 loops=1)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Append (cost=100.00..45653.00 rows=800000 width=18) (actual time=0.775..1053.692 rows=800000 loops=1)
-> Async Foreign Scan on partsupp_1 (cost=100.00..20745.92 rows=398464 width=18) (actual time=0.469..444.911 rows=398464 loops=1)
-> Async Foreign Scan on partsupp_2 (cost=100.00..20907.08 rows=401536 width=18) (actual time=0.298..449.623 rows=401536 loops=1)
-> Hash (cost=1020.89..1020.89 rows=800 width=4) (actual time=14.124..14.127 rows=401 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 23kB
-> Hash Join (cost=302.39..1020.89 rows=800 width=4) (actual time=1.206..14.043 rows=401 loops=1)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.476..12.289 rows=10000 loops=1)
-> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.254..3.522 rows=5019 loops=1)
-> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.179..3.423 rows=4981 loops=1)
-> Hash (cost=202.36..202.36 rows=2 width=4) (actual time=0.724..0.726 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Append (cost=100.00..202.36 rows=2 width=4) (actual time=0.721..0.723 rows=1 loops=1)
-> Async Foreign Scan on nation_1 (cost=100.00..101.15 rows=1 width=4) (actual time=0.253..0.253 rows=0 loops=1)
-> Async Foreign Scan on nation_2 (cost=100.00..101.21 rows=1 width=4) (actual time=0.270..0.270 rows=1 loops=1)
Planning Time: 0.763 ms
Execution Time: 2311.861 ms
(46 rows)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=343142.27..343717.68 rows=7 width=27) (actual time=3888.817..3896.078 rows=2 loops=1)
Group Key: lineitem.l_shipmode
-> Sort (cost=343142.27..343214.19 rows=28767 width=27) (actual time=3881.413..3883.079 rows=31186 loops=1)
Sort Key: lineitem.l_shipmode
Sort Method: quicksort Memory: 3449kB
-> Hash Join (cost=248904.10..341011.77 rows=28767 width=27) (actual time=2183.522..3868.589 rows=31186 loops=1)
Hash Cond: (orders.o_orderkey = lineitem.l_orderkey)
-> Append (cost=100.00..78795.00 rows=1500000 width=20) (actual time=0.479..1484.239 rows=1500000 loops=1)
-> Async Foreign Scan on orders_1 (cost=100.00..35604.47 rows=749049 width=20) (actual time=0.236..585.157 rows=749049 loops=1)
-> Async Foreign Scan on orders_2 (cost=100.00..35690.53 rows=750951 width=20) (actual time=0.218..576.020 rows=750951 loops=1)
-> Hash (cost=248444.51..248444.51 rows=28767 width=15) (actual time=2182.943..2182.945 rows=31186 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1718kB
-> Append (cost=100.00..248444.51 rows=28767 width=15) (actual time=10.230..2170.895 rows=31186 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..123961.50 rows=14321 width=15) (actual time=0.636..25.634 rows=15548 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..124339.18 rows=14446 width=15) (actual time=1.066..25.764 rows=15638 loops=1)
Planning Time: 0.695 ms
Execution Time: 3897.201 ms
(17 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=192609.76..192610.26 rows=200 width=16) (actual time=3380.250..3380.256 rows=42 loops=1)
Sort Key: (count(*)) DESC, (count(orders.o_orderkey)) DESC
Sort Method: quicksort Memory: 27kB
-> HashAggregate (cost=192600.11..192602.11 rows=200 width=16) (actual time=3380.218..3380.230 rows=42 loops=1)
Group Key: count(orders.o_orderkey)
Batches: 1 Memory Usage: 40kB
-> HashAggregate (cost=177254.83..190350.11 rows=150000 width=12) (actual time=2922.466..3341.806 rows=150000 loops=1)
Group Key: customer.c_custkey
Planned Partitions: 4 Batches: 21 Memory Usage: 4153kB Disk Usage: 31968kB
-> Hash Right Join (cost=11596.00..126235.60 rows=1484196 width=8) (actual time=158.047..2355.235 rows=1533791 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Append (cost=100.00..82149.90 rows=1484196 width=8) (actual time=3.662..1494.543 rows=1483787 loops=1)
-> Async Foreign Scan on orders_1 (cost=100.00..37310.31 rows=740710 width=8) (actual time=2.184..546.274 rows=740960 loops=1)
-> Async Foreign Scan on orders_2 (cost=100.00..37418.61 rows=743486 width=8) (actual time=1.523..547.115 rows=742827 loops=1)
-> Hash (cost=9035.00..9035.00 rows=150000 width=4) (actual time=154.144..154.146 rows=150000 loops=1)
Buckets: 131072 Batches: 2 Memory Usage: 3720kB
-> Append (cost=100.00..9035.00 rows=150000 width=4) (actual time=0.377..122.678 rows=150000 loops=1)
-> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=4) (actual time=0.222..43.296 rows=74951 loops=1)
-> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=4) (actual time=0.177..42.983 rows=75049 loops=1)
Planning Time: 2.754 ms
Execution Time: 3384.378 ms
(21 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=224364.96..224364.98 rows=1 width=32) (actual time=1248.208..1248.212 rows=1 loops=1)
-> Hash Join (cost=15266.00..223009.53 rows=77453 width=33) (actual time=246.778..1177.942 rows=77819 loops=1)
Hash Cond: (lineitem.l_partkey = part.p_partkey)
-> Append (cost=100.00..204652.55 rows=77453 width=16) (actual time=5.918..854.963 rows=77819 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..101982.44 rows=38886 width=16) (actual time=0.455..77.278 rows=38927 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..102282.85 rows=38567 width=16) (actual time=0.677..77.336 rows=38892 loops=1)
-> Hash (cost=11298.00..11298.00 rows=200000 width=25) (actual time=240.773..240.775 rows=200000 loops=1)
Buckets: 65536 Batches: 4 Memory Usage: 3406kB
-> Append (cost=100.00..11298.00 rows=200000 width=25) (actual time=0.381..188.983 rows=200000 loops=1)
-> Async Foreign Scan on part_1 (cost=100.00..5129.48 rows=99616 width=25) (actual time=0.245..77.463 rows=99616 loops=1)
-> Async Foreign Scan on part_2 (cost=100.00..5168.52 rows=100384 width=25) (actual time=0.184..78.740 rows=100384 loops=1)
Planning Time: 0.527 ms
Execution Time: 1248.789 ms
(13 rows)
CREATE VIEW
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=421604.90..422318.25 rows=50 width=104) (actual time=2506.593..2506.599 rows=1 loops=1)
Merge Cond: (supplier.s_suppkey = revenue0.supplier_no)
InitPlan 1 (returns $0)
-> Aggregate (cost=210751.48..210751.49 rows=1 width=32) (actual time=1252.201..1252.203 rows=1 loops=1)
-> HashAggregate (cost=210501.48..210626.48 rows=10000 width=36) (actual time=1205.858..1250.479 rows=10000 loops=1)
Group Key: lineitem_3.l_suppkey
Batches: 5 Memory Usage: 4273kB Disk Usage: 1696kB
-> Append (cost=100.00..208277.12 rows=222436 width=16) (actual time=1.441..976.819 rows=223616 loops=1)
-> Async Foreign Scan on lineitem_1 lineitem_4 (cost=100.00..103413.38 rows=110433 width=16) (actual time=0.532..172.564 rows=111275 loops=1)
-> Async Foreign Scan on lineitem_2 lineitem_5 (cost=100.00..103751.57 rows=112003 width=16) (actual time=0.489..174.695 rows=112341 loops=1)
-> Merge Append (cost=200.01..887.61 rows=10000 width=72) (actual time=7.482..22.094 rows=5450 loops=1)
Sort Key: supplier.s_suppkey
-> Foreign Scan on supplier_1 (cost=100.00..395.01 rows=5019 width=72) (actual time=3.017..9.161 rows=2729 loops=1)
-> Foreign Scan on supplier_2 (cost=100.00..392.59 rows=4981 width=71) (actual time=4.463..12.318 rows=2722 loops=1)
-> Sort (cost=210653.40..210653.52 rows=50 width=36) (actual time=2484.033..2484.035 rows=1 loops=1)
Sort Key: revenue0.supplier_no
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on revenue0 (cost=210501.48..210651.98 rows=50 width=36) (actual time=2483.968..2484.025 rows=1 loops=1)
-> HashAggregate (cost=210501.48..210651.48 rows=50 width=36) (actual time=2483.967..2484.023 rows=1 loops=1)
Group Key: lineitem.l_suppkey
Filter: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))) = $0)
Batches: 5 Memory Usage: 4273kB Disk Usage: 1696kB
Rows Removed by Filter: 9999
-> Append (cost=100.00..208277.12 rows=222436 width=16) (actual time=1.906..970.218 rows=223616 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..103413.38 rows=110433 width=16) (actual time=0.786..167.972 rows=111275 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..103751.57 rows=112003 width=16) (actual time=0.653..169.821 rows=112341 loops=1)
Planning Time: 1.135 ms
Execution Time: 2508.563 ms
(28 rows)
DROP VIEW
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=73362.13..73369.50 rows=2946 width=44) (actual time=1430.034..1430.913 rows=18243 loops=1)
Sort Key: (count(DISTINCT partsupp.ps_suppkey)) DESC, part.p_brand, part.p_type, part.p_size
Sort Method: quicksort Memory: 2351kB
-> GroupAggregate (cost=71689.97..73192.38 rows=2946 width=44) (actual time=1282.151..1402.153 rows=18243 loops=1)
Group Key: part.p_brand, part.p_type, part.p_size
-> Sort (cost=71689.97..71984.56 rows=117836 width=40) (actual time=1282.129..1332.299 rows=118012 loops=1)
Sort Key: part.p_brand, part.p_type, part.p_size
Sort Method: external merge Disk: 6024kB
-> Hash Join (cost=11050.82..59830.29 rows=117836 width=40) (actual time=89.256..1096.015 rows=118012 loops=1)
Hash Cond: (partsupp.ps_partkey = part.p_partkey)
-> Append (cost=648.11..46749.22 rows=400000 width=8) (actual time=9.627..884.679 rows=799680 loops=1)
-> Async Foreign Scan on partsupp_1 (cost=648.11..22290.19 rows=199232 width=8) (actual time=9.435..354.366 rows=398294 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 170
SubPlan 1
-> Append (cost=100.00..548.10 rows=4 width=4) (actual time=2.624..4.603 rows=4 loops=2)
-> Async Foreign Scan on supplier_1 (cost=100.00..274.78 rows=2 width=4) (actual time=0.656..0.657 rows=1 loops=2)
-> Async Foreign Scan on supplier_2 (cost=100.00..273.30 rows=2 width=4) (actual time=0.615..0.616 rows=3 loops=2)
-> Async Foreign Scan on partsupp_2 (cost=648.11..22459.03 rows=200768 width=8) (actual time=0.183..347.226 rows=401386 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 150
-> Hash (cost=10034.48..10034.48 rows=29459 width=40) (actual time=79.595..79.597 rows=29514 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 2390kB
-> Append (cost=100.00..10034.48 rows=29459 width=40) (actual time=1.581..72.414 rows=29514 loops=1)
-> Async Foreign Scan on part_1 (cost=100.00..4925.02 rows=14681 width=40) (actual time=0.864..15.128 rows=14658 loops=1)
-> Async Foreign Scan on part_2 (cost=100.00..4962.16 rows=14778 width=40) (actual time=0.473..15.048 rows=14856 loops=1)
Planning Time: 1.877 ms
Execution Time: 1433.416 ms
(28 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=785525.21..785525.22 rows=1 width=32) (actual time=30476.615..30476.623 rows=1 loops=1)
-> Hash Join (cost=433697.69..780549.51 rows=1990281 width=8) (actual time=20830.945..30476.400 rows=644 loops=1)
Hash Cond: (part.p_partkey = pre.x1)
Join Filter: (lineitem.l_quantity < pre.x2)
Rows Removed by Join Filter: 5869
-> Hash Join (cost=7405.46..352610.42 rows=5988 width=21) (actual time=32.506..9712.837 rows=6513 loops=1)
Hash Cond: (lineitem.l_partkey = part.p_partkey)
-> Append (cost=100.00..322740.53 rows=6001215 width=17) (actual time=0.571..9067.430 rows=6001215 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..146145.44 rows=2996048 width=17) (actual time=0.152..3952.077 rows=2996048 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..146589.01 rows=3005167 width=17) (actual time=0.261..3981.652 rows=3005167 loops=1)
-> Hash (cost=7302.97..7302.97 rows=199 width=4) (actual time=29.579..29.582 rows=213 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
-> Append (cost=100.00..7302.97 rows=199 width=4) (actual time=22.479..29.492 rows=213 loops=1)
-> Async Foreign Scan on part_1 (cost=100.00..3637.28 rows=102 width=4) (actual time=0.467..0.520 rows=104 loops=1)
-> Async Foreign Scan on part_2 (cost=100.00..3664.70 rows=97 width=4) (actual time=0.257..0.317 rows=109 loops=1)
-> Hash (cost=422240.39..422240.39 rows=199427 width=36) (actual time=20737.488..20737.490 rows=200000 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 1743kB
-> Subquery Scan on pre (cost=200.01..422240.39 rows=199427 width=36) (actual time=5155.633..20671.380 rows=200000 loops=1)
-> GroupAggregate (cost=200.01..420246.12 rows=199427 width=36) (actual time=5155.631..20648.839 rows=200000 loops=1)
Group Key: lineitem_3.l_partkey
-> Merge Append (cost=200.01..387248.64 rows=6001215 width=9) (actual time=5155.577..18941.018 rows=6001215 loops=1)
Sort Key: lineitem_3.l_partkey
-> Foreign Scan on lineitem_1 lineitem_4 (cost=100.00..163370.34 rows=2996048 width=9) (actual time=2579.362..9381.250 rows=2996048 loops=1)
-> Foreign Scan on lineitem_2 lineitem_5 (cost=100.00..163866.14 rows=3005167 width=9) (actual time=2576.209..8988.836 rows=3005167 loops=1)
Planning Time: 0.542 ms
Execution Time: 30487.130 ms
(26 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1238242.26..1238245.26 rows=100 width=71) (actual time=33250.208..33250.264 rows=10 loops=1)
-> GroupAggregate (cost=1238242.26..1288117.77 rows=1662517 width=71) (actual time=33250.206..33250.260 rows=10 loops=1)
Group Key: orders.o_totalprice, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey
-> Sort (cost=1238242.26..1242398.55 rows=1662517 width=44) (actual time=33250.187..33250.200 rows=70 loops=1)
Sort Key: orders.o_totalprice DESC, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey
Sort Method: quicksort Memory: 31kB
-> Hash Join (cost=581063.55..1005092.81 rows=1662517 width=44) (actual time=28361.133..33250.094 rows=70 loops=1)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Append (cost=100.00..322740.53 rows=6001215 width=9) (actual time=0.808..7349.339 rows=6001215 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..146145.44 rows=2996048 width=9) (actual time=0.382..3015.045 rows=2996048 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..146589.01 rows=3005167 width=9) (actual time=0.280..3080.052 rows=3005167 loops=1)
-> Hash (cost=572116.24..572116.24 rows=415545 width=43) (actual time=23961.761..23961.767 rows=10 loops=1)
Buckets: 65536 Batches: 16 Memory Usage: 513kB
-> Hash Join (cost=458816.99..572116.24 rows=415545 width=43) (actual time=21326.975..23961.645 rows=10 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Hash Join (cost=447027.99..548864.50 rows=415545 width=24) (actual time=21147.464..23770.420 rows=10 loops=1)
Hash Cond: (orders.o_orderkey = lineitem_3.l_orderkey)
-> Append (cost=100.00..78795.00 rows=1500000 width=20) (actual time=0.611..2671.277 rows=1500000 loops=1)
-> Async Foreign Scan on orders_1 (cost=100.00..35604.47 rows=749049 width=20) (actual time=0.392..1203.158 rows=749049 loops=1)
-> Async Foreign Scan on orders_2 (cost=100.00..35690.53 rows=750951 width=20) (actual time=0.196..1204.969 rows=750951 loops=1)
-> Hash (cost=440109.68..440109.68 rows=415545 width=4) (actual time=20490.578..20490.580 rows=10 loops=1)
Buckets: 131072 Batches: 8 Memory Usage: 1025kB
-> GroupAggregate (cost=200.01..435954.23 rows=415545 width=4) (actual time=7538.737..20490.321 rows=10 loops=1)
Group Key: lineitem_3.l_orderkey
Filter: (sum(lineitem_3.l_quantity) > '312'::numeric)
Rows Removed by Filter: 1499990
-> Merge Append (cost=200.01..387248.64 rows=6001215 width=9) (actual time=4136.763..17797.673 rows=6001215 loops=1)
Sort Key: lineitem_3.l_orderkey
-> Foreign Scan on lineitem_1 lineitem_4 (cost=100.00..163370.34 rows=2996048 width=9) (actual time=2059.370..8548.500 rows=2996048 loops=1)
-> Foreign Scan on lineitem_2 lineitem_5 (cost=100.00..163866.14 rows=3005167 width=9) (actual time=2077.388..8660.137 rows=3005167 loops=1)
-> Hash (cost=9035.00..9035.00 rows=150000 width=23) (actual time=179.374..179.375 rows=150000 loops=1)
Buckets: 65536 Batches: 4 Memory Usage: 2637kB
-> Append (cost=100.00..9035.00 rows=150000 width=23) (actual time=0.499..141.104 rows=150000 loops=1)
-> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=23) (actual time=0.272..55.989 rows=74951 loops=1)
-> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=23) (actual time=0.215..56.286 rows=75049 loops=1)
Planning Time: 2.117 ms
Execution Time: 33269.123 ms
(37 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=308802.85..308802.86 rows=1 width=32) (actual time=1470.797..1470.802 rows=1 loops=1)
-> Hash Join (cost=12916.23..308801.99 rows=115 width=12) (actual time=48.012..1470.525 rows=133 loops=1)
Hash Cond: (lineitem.l_partkey = part.p_partkey)
Join Filter: (((part.p_brand = 'Brand#41'::bpchar) AND (part.p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (lineitem.l_quantity >= '5'::numeric) AND (lineitem.l_quantity <= '15'::numeric) AND (part.p_size <= 5)) OR ((part.p_brand = 'Brand#53'::bpchar) AND (part.p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (lineitem.l_quantity >= '17'::numeric) AND (lineitem.l_quantity <= '27'::numeric) AND (part.p_size <= 10)) OR ((part.p_brand = 'Brand#32'::bpchar) AND (part.p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (lineitem.l_quantity >= '30'::numeric) AND (lineitem.l_quantity <= '40'::numeric) AND (part.p_size <= 15)))
Rows Removed by Join Filter: 235
-> Append (cost=100.00..295548.58 rows=112565 width=21) (actual time=3.102..1405.475 rows=141319 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..147266.40 rows=56048 width=21) (actual time=0.491..161.037 rows=70695 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..147719.35 rows=56517 width=21) (actual time=0.717..166.270 rows=70624 loops=1)
-> Hash (cost=12810.15..12810.15 rows=486 width=30) (actual time=43.013..43.015 rows=507 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 40kB
-> Append (cost=100.00..12810.15 rows=486 width=30) (actual time=15.291..42.863 rows=507 loops=1)
-> Async Foreign Scan on part_1 (cost=100.00..6379.52 rows=242 width=30) (actual time=0.703..0.887 rows=258 loops=1)
-> Async Foreign Scan on part_2 (cost=100.00..6428.20 rows=244 width=30) (actual time=0.569..0.799 rows=249 loops=1)
Planning Time: 1.807 ms
Execution Time: 1471.382 ms
(15 rows)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=332645.88..332645.88 rows=1 width=52) (actual time=4145.639..4145.655 rows=167 loops=1)
Sort Key: supplier.s_name
Sort Method: quicksort Memory: 45kB
-> Hash Join (cost=332543.49..332645.87 rows=1 width=52) (actual time=4145.474..4145.514 rows=167 loops=1)
Hash Cond: (nation.n_nationkey = supplier.s_nationkey)
-> Append (cost=100.00..202.36 rows=2 width=4) (actual time=0.318..0.320 rows=1 loops=1)
-> Async Foreign Scan on nation_1 (cost=100.00..101.15 rows=1 width=4) (actual time=0.143..0.144 rows=1 loops=1)
-> Async Foreign Scan on nation_2 (cost=100.00..101.21 rows=1 width=4) (actual time=0.170..0.170 rows=0 loops=1)
-> Hash (cost=332443.47..332443.47 rows=1 width=56) (actual time=4145.150..4145.156 rows=4555 loops=1)
Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 463kB
-> Hash Semi Join (cost=331744.21..332443.47 rows=1 width=56) (actual time=4127.884..4144.108 rows=4555 loops=1)
Hash Cond: (supplier.s_suppkey = partsupp.ps_suppkey)
-> Append (cost=100.00..773.00 rows=10000 width=60) (actual time=0.335..14.573 rows=10000 loops=1)
-> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=60) (actual time=0.194..4.768 rows=5019 loops=1)
-> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=59) (actual time=0.491..4.906 rows=4981 loops=1)
-> Hash (cost=331644.20..331644.20 rows=1 width=8) (actual time=4127.536..4127.541 rows=5989 loops=1)
Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 298kB
-> Hash Join (cost=316241.38..331644.20 rows=1 width=8) (actual time=3010.848..4126.160 rows=5989 loops=1)
Hash Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey))
Join Filter: ((partsupp.ps_availqty)::numeric > ((0.5 * sum(lineitem.l_quantity))))
Rows Removed by Join Filter: 10
-> HashAggregate (cost=261383.52..273296.35 rows=199427 width=40) (actual time=1998.286..3036.919 rows=543808 loops=1)
Group Key: lineitem.l_partkey, lineitem.l_suppkey
Planned Partitions: 16 Batches: 153 Memory Usage: 4121kB Disk Usage: 31560kB
-> Append (cost=100.00..225555.08 rows=913554 width=13) (actual time=0.737..1601.268 rows=911395 loops=1)
-> Async Foreign Scan on lineitem_1 (cost=100.00..110328.82 rows=456205 width=13) (actual time=0.390..514.763 rows=455324 loops=1)
-> Async Foreign Scan on lineitem_2 (cost=100.00..110658.49 rows=457349 width=13) (actual time=0.392..514.112 rows=456071 loops=1)
-> Hash (cost=54728.20..54728.20 rows=8644 width=16) (actual time=1012.132..1012.136 rows=8848 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 543kB
-> Hash Semi Join (cost=6979.04..54728.20 rows=8644 width=16) (actual time=54.895..1009.128 rows=8848 loops=1)
Hash Cond: (partsupp.ps_partkey = part.p_partkey)
-> Append (cost=100.00..45653.00 rows=800000 width=12) (actual time=0.321..865.743 rows=800000 loops=1)
-> Async Foreign Scan on partsupp_1 (cost=100.00..20745.92 rows=398464 width=12) (actual time=0.166..303.859 rows=398464 loops=1)
-> Async Foreign Scan on partsupp_2 (cost=100.00..20907.08 rows=401536 width=12) (actual time=0.246..346.767 rows=401536 loops=1)
-> Hash (cost=6852.03..6852.03 rows=2161 width=4) (actual time=51.991..51.992 rows=2212 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 110kB
-> Append (cost=100.00..6852.03 rows=2161 width=4) (actual time=3.384..51.241 rows=2212 loops=1)
-> Async Foreign Scan on part_1 (cost=100.00..3407.72 rows=1076 width=4) (actual time=0.403..1.467 rows=1108 loops=1)
-> Async Foreign Scan on part_2 (cost=100.00..3433.50 rows=1085 width=4) (actual time=0.714..1.921 rows=1104 loops=1)
Planning Time: 2.116 ms
Execution Time: 4149.343 ms
(41 rows)
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1040976.51..1040976.52 rows=1 width=34) (actual time=18351.578..18351.601 rows=100 loops=1)
-> Sort (cost=1040976.51..1040976.52 rows=1 width=34) (actual time=18351.577..18351.594 rows=100 loops=1)
Sort Key: (count(*)) DESC, supplier.s_name
Sort Method: top-N heapsort Memory: 38kB
-> GroupAggregate (cost=1040976.48..1040976.50 rows=1 width=34) (actual time=18350.347..18351.428 rows=393 loops=1)
Group Key: supplier.s_name
-> Sort (cost=1040976.48..1040976.49 rows=1 width=26) (actual time=18350.329..18350.656 rows=3918 loops=1)
Sort Key: supplier.s_name
Sort Method: quicksort Memory: 311kB
-> Hash Semi Join (cost=951615.46..1040976.47 rows=1 width=26) (actual time=16937.366..18348.092 rows=3918 loops=1)
Hash Cond: (orders.o_orderkey = l2.l_orderkey)
Join Filter: (l2.l_suppkey <> l1.l_suppkey)
Rows Removed by Join Filter: 4130
-> Hash Join (cost=530416.75..596332.72 rows=1 width=38) (actual time=9688.130..10481.556 rows=6540 loops=1)
Hash Cond: (orders.o_orderkey = l1.l_orderkey)
-> Append (cost=100.00..63280.62 rows=729425 width=4) (actual time=0.615..706.791 rows=729413 loops=1)
-> Async Foreign Scan on orders_1 (cost=100.00..29776.43 rows=364016 width=4) (actual time=0.356..214.117 rows=364016 loops=1)
-> Async Foreign Scan on orders_2 (cost=100.00..29857.07 rows=365409 width=4) (actual time=0.254..224.783 rows=365397 loops=1)
-> Hash (cost=530316.73..530316.73 rows=1 width=34) (actual time=9686.896..9686.904 rows=13416 loops=1)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1019kB
-> Hash Anti Join (cost=271674.26..530316.73 rows=1 width=34) (actual time=4734.460..9683.675 rows=13416 loops=1)
Hash Cond: (l1.l_orderkey = l3.l_orderkey)
Join Filter: (l3.l_suppkey <> l1.l_suppkey)
Rows Removed by Join Filter: 54358
-> Hash Join (cost=1130.89..247856.04 rows=160032 width=34) (actual time=15.166..4379.082 rows=149442 loops=1)
Hash Cond: (l1.l_suppkey = supplier.s_suppkey)
-> Append (cost=100.00..237723.31 rows=2000405 width=8) (actual time=0.505..3936.640 rows=3793296 loops=1)
-> Async Foreign Scan on lineitem_1 l1_1 (cost=100.00..113688.26 rows=998683 width=8) (actual time=0.231..1430.615 rows=1894002 loops=1)
-> Async Foreign Scan on lineitem_2 l1_2 (cost=100.00..114033.03 rows=1001722 width=8) (actual time=0.229..1425.376 rows=1899294 loops=1)
-> Hash (cost=1020.89..1020.89 rows=800 width=30) (actual time=14.656..14.660 rows=393 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 33kB
-> Hash Join (cost=302.39..1020.89 rows=800 width=30) (actual time=1.195..14.568 rows=393 loops=1)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Append (cost=100.00..773.00 rows=10000 width=34) (actual time=0.486..12.780 rows=10000 loops=1)
-> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=34) (actual time=0.248..4.243 rows=5019 loops=1)
-> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=34) (actual time=0.258..4.075 rows=4981 loops=1)
-> Hash (cost=202.36..202.36 rows=2 width=4) (actual time=0.689..0.691 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Append (cost=100.00..202.36 rows=2 width=4) (actual time=0.686..0.688 rows=1 loops=1)
-> Async Foreign Scan on nation_1 (cost=100.00..101.15 rows=1 width=4) (actual time=0.180..0.180 rows=0 loops=1)
-> Async Foreign Scan on nation_2 (cost=100.00..101.21 rows=1 width=4) (actual time=0.296..0.296 rows=1 loops=1)
-> Hash (cost=237723.31..237723.31 rows=2000405 width=8) (actual time=4676.888..4676.889 rows=3793296 loops=1)
Buckets: 131072 (originally 131072) Batches: 64 (originally 32) Memory Usage: 3328kB
-> Append (cost=100.00..237723.31 rows=2000405 width=8) (actual time=0.291..3803.896 rows=3793296 loops=1)
-> Async Foreign Scan on lineitem_1 l3_1 (cost=100.00..113688.26 rows=998683 width=8) (actual time=0.155..1434.767 rows=1894002 loops=1)
-> Async Foreign Scan on lineitem_2 l3_2 (cost=100.00..114033.03 rows=1001722 width=8) (actual time=0.155..1453.430 rows=1899294 loops=1)
-> Hash (cost=322740.53..322740.53 rows=6001215 width=8) (actual time=7240.565..7240.566 rows=6001215 loops=1)
Buckets: 131072 Batches: 128 Memory Usage: 2838kB
-> Append (cost=100.00..322740.53 rows=6001215 width=8) (actual time=0.540..5893.590 rows=6001215 loops=1)
-> Async Foreign Scan on lineitem_1 l2_1 (cost=100.00..146145.44 rows=2996048 width=8) (actual time=0.292..2325.692 rows=2996048 loops=1)
-> Async Foreign Scan on lineitem_2 l2_2 (cost=100.00..146589.01 rows=3005167 width=8) (actual time=0.176..2252.629 rows=3005167 loops=1)
Planning Time: 1.433 ms
Execution Time: 18353.481 ms
(53 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=124926.45..124926.48 rows=1 width=72) (actual time=3127.335..3129.487 rows=7 loops=1)
Group Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2))
InitPlan 1 (returns $0)
-> Aggregate (cost=7853.68..7853.69 rows=1 width=32) (actual time=107.705..107.707 rows=1 loops=1)
-> Append (cost=100.00..7841.75 rows=4770 width=6) (actual time=1.945..98.412 rows=38221 loops=1)
-> Async Foreign Scan on customer_1 customer_4 (cost=100.00..3907.12 rows=2383 width=6) (actual time=0.777..16.216 rows=19022 loops=1)
-> Async Foreign Scan on customer_2 customer_5 (cost=100.00..3910.78 rows=2387 width=6) (actual time=1.258..24.490 rows=19199 loops=1)
-> Sort (cost=117072.76..117072.77 rows=1 width=38) (actual time=3126.973..3127.354 rows=6364 loops=1)
Sort Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2))
Sort Method: quicksort Memory: 541kB
-> Hash Anti Join (cost=103505.00..117072.76 rows=1 width=38) (actual time=2902.642..3124.800 rows=6364 loops=1)
Hash Cond: (customer.c_custkey = orders.o_custkey)
-> Append (cost=100.00..7766.25 rows=1750 width=26) (actual time=108.858..173.567 rows=19009 loops=1)
-> Async Foreign Scan on customer_1 (cost=100.00..3876.94 rows=874 width=26) (actual time=108.219..119.499 rows=9439 loops=1)
-> Async Foreign Scan on customer_2 (cost=100.00..3880.56 rows=876 width=26) (actual time=0.400..11.942 rows=9570 loops=1)
-> Hash (cost=78795.00..78795.00 rows=1500000 width=4) (actual time=2792.199..2792.200 rows=1500000 loops=1)
Buckets: 131072 Batches: 32 Memory Usage: 2697kB
-> Append (cost=100.00..78795.00 rows=1500000 width=4) (actual time=0.329..2457.260 rows=1500000 loops=1)
-> Async Foreign Scan on orders_1 (cost=100.00..35604.47 rows=749049 width=4) (actual time=0.157..455.400 rows=749049 loops=1)
-> Async Foreign Scan on orders_2 (cost=100.00..35690.53 rows=750951 width=4) (actual time=1.016..462.740 rows=750951 loops=1)
Planning Time: 0.893 ms
Execution Time: 3130.506 ms
(22 rows)
Attachments:
res.txt (text/plain, 91.6K) download
tpchq_rewritten.sql (application/sql, 14.5K) download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-27 06:22 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-27 10:50 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-28 06:39 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
@ 2021-07-28 08:28 ` Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Etsuro Fujita @ 2021-07-28 08:28 UTC (permalink / raw)
To: Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>; +Cc: CAPmGK15eY_P5AEC6-HjZusx2euftT0KD94ywaeJ=f3NGTLJEFw@mail.gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Jul 28, 2021 at 3:39 PM Andrey V. Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> On 7/27/21 3:50 PM, Etsuro Fujita wrote:
> > On Tue, Jul 27, 2021 at 3:22 PM Andrey V. Lepikhov
> > <a.lepikhov@postgrespro.ru> wrote:
> > Here is an updated version of the patch, in which I added/tweaked
> > comments a bit further. I'm planning to push this version if there
> > are no objections from others.
> I used this patch in TPC-H benchmark (that caused initial error) and it
> finished without problems.
Good to know!
> Queries and their explains are attached. You could look for situations
> when async append was not used.
Interesting! Yeah, async exection doesn't apply to Merge Append, so
let's improve to support async-aware Merge Append.
Thanks for the testing!
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-27 06:22 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-27 10:50 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2021-07-30 08:11 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-30 12:25 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2 siblings, 1 reply; 20+ messages in thread
From: Etsuro Fujita @ 2021-07-30 08:11 UTC (permalink / raw)
To: CAPmGK15eY_P5AEC6-HjZusx2euftT0KD94ywaeJ=f3NGTLJEFw@mail.gmail.com; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Jul 27, 2021 at 7:50 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> Here is an updated version of the patch, in which I added/tweaked
> comments a bit further. I'm planning to push this version if there
> are no objections from others.
Done.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: The case when AsyncAppend exists also in the qual of Async ForeignScan
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-27 06:22 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-27 10:50 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-30 08:11 ` Re: The case when AsyncAppend exists also in the qual of Async ForeignScan Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2021-07-30 12:25 ` Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Etsuro Fujita @ 2021-07-30 12:25 UTC (permalink / raw)
To: CAPmGK15eY_P5AEC6-HjZusx2euftT0KD94ywaeJ=f3NGTLJEFw@mail.gmail.com; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, Jul 30, 2021 at 5:11 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Tue, Jul 27, 2021 at 7:50 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> > Here is an updated version of the patch, in which I added/tweaked
> > comments a bit further. I'm planning to push this version if there
> > are no objections from others.
>
> Done.
I noticed that some buildfarm members cause this assertion failure:
TRAP: FailedAssertion("fsstate->conn_state->pendingAreq == areq",
File: "postgres_fdw.c", Line: 6900, PID: 25840)
I will look into this.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2021-07-30 12:25 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-02 13:24 The case when AsyncAppend exists also in the qual of Async ForeignScan Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-02 23:35 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 07:49 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 08:06 ` Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-08 08:32 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 10:18 ` Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-08 17:10 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-08 10:32 ` Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-22 11:14 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-23 06:09 ` Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-25 08:55 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-23 11:26 ` Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-27 06:22 ` Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-27 10:50 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-27 11:00 ` Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-28 03:35 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-28 06:39 ` Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
2021-07-28 08:28 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-30 08:11 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2021-07-30 12:25 ` Etsuro Fujita <etsuro.fujita@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox