From: | Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Multi-level hierarchy with parallel append can lead to an extra subplan. |
Date: | 2018-01-09 10:09:03 |
Message-ID: | CAKcux6mBF-NiddyEe9LwymoUC5+wh8bQJ=uk2gGkOE+L8cv=LA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
I am getting extra subplan when using parallel append with multi-level
hierarchy, leading to data corruption.
Please see below test case.
-- set below parameters to encourage use of parallel plans
SET parallel_setup_cost=0;
SET parallel_tuple_cost=0;
SET min_parallel_table_scan_size=0;
SET max_parallel_workers_per_gather=4;
--create below data set
CREATE TABLE RM38941_inherit (c1 INTEGER PRIMARY KEY,c2 INTEGER,c3
CHAR(10));
INSERT INTO RM38941_inherit SELECT i, i % 125, to_char(i % 4, 'FM0000')
FROM generate_series(0, 499,2) i;
CREATE TABLE RM38941_inherit_t1 () INHERITS (RM38941_inherit);
INSERT INTO RM38941_inherit_t1 SELECT i, i % 125, to_char(i % 4, 'FM0000')
FROM generate_series(0, 499,3) i;
CREATE TABLE RM38941_inherit_t2 () INHERITS (RM38941_inherit);
INSERT INTO RM38941_inherit_t2 SELECT i, i % 125, to_char(i % 4, 'FM0000')
FROM generate_series(0, 499,5) i;
CREATE TABLE RM38941_union_t1 (c1 INTEGER PRIMARY KEY,c2 INTEGER,c3
CHAR(10));
INSERT INTO RM38941_union_t1 SELECT i, i % 125, to_char(i % 4, 'FM0000')
FROM generate_series(0, 499,2) i;
CREATE TABLE RM38941_union_t2 (c1 INTEGER PRIMARY KEY,c2 INTEGER,c3
CHAR(10));
INSERT INTO RM38941_union_t2 SELECT i, i % 125, to_char(i % 4, 'FM0000')
FROM generate_series(0, 499,4) i;
ALTER TABLE RM38941_union_t1 SET (parallel_workers = 0);
ALTER TABLE RM38941_inherit_t1 SET (parallel_workers = 0);
--with parallel_append
SET enable_parallel_append=on;
postgres=# EXPLAIN (COSTS OFF)
postgres-# SELECT AVG(c1),SUM(c2) FROM (SELECT c1,c2 FROM RM38941_union_t2
UNION ALL SELECT c1,c2 FROM RM38941_inherit UNION ALL SELECT c1,c2 FROM
RM38941_union_t1)UA;
QUERY PLAN
-----------------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 3
-> Partial Aggregate
-> Parallel Append
-> Seq Scan on rm38941_inherit_t1
-> Seq Scan on rm38941_union_t1
-> Parallel Seq Scan on rm38941_union_t2
-> Parallel Seq Scan on rm38941_inherit
-> Parallel Seq Scan on rm38941_inherit_t2
-> Parallel Append
-> Seq Scan on rm38941_inherit_t1
-> Parallel Seq Scan on rm38941_inherit
-> Parallel Seq Scan on rm38941_inherit_t2
(14 rows)
postgres=# SELECT AVG(c1),SUM(c2) FROM (SELECT c1,c2 FROM RM38941_union_t2
UNION ALL SELECT c1,c2 FROM RM38941_inherit UNION ALL SELECT c1,c2 FROM
RM38941_union_t1)UA;
avg | sum
----------------------+-------
248.6983676366217175 | 86916
(1 row)
--without parallel_append
SET enable_parallel_append=off;
postgres=# EXPLAIN (COSTS OFF)
postgres-# SELECT AVG(c1),SUM(c2) FROM (SELECT c1,c2 FROM RM38941_union_t2
UNION ALL SELECT c1,c2 FROM RM38941_inherit UNION ALL SELECT c1,c2 FROM
RM38941_union_t1)UA;
QUERY PLAN
--------------------------------------------
Aggregate
-> Append
-> Seq Scan on rm38941_union_t2
-> Seq Scan on rm38941_inherit
-> Seq Scan on rm38941_inherit_t1
-> Seq Scan on rm38941_inherit_t2
-> Seq Scan on rm38941_union_t1
(7 rows)
postgres=# SELECT AVG(c1),SUM(c2) FROM (SELECT c1,c2 FROM RM38941_union_t2
UNION ALL SELECT c1,c2 FROM RM38941_inherit UNION ALL SELECT c1,c2 FROM
RM38941_union_t1)UA;
avg | sum
----------------------+-------
248.6917040358744395 | 55083
(1 row)
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
From | Date | Subject | |
---|---|---|---|
Next Message | David Gould | 2018-01-09 10:28:42 | Re: PL/Python SD dict wiped? |
Previous Message | Beena Emerson | 2018-01-09 09:22:07 | Re: [HACKERS] Runtime Partition Pruning |