Обсуждение: how to get top plan of GatherMerge in OSS10
Hello~
When I look at the plan for a query in OSS11 and OSS10, the plan tree structure is different.
OSS10.13:
postgres=# explain (costs off) select C,((select c_int from table1 where C<=2)except all(select c_int from table1 where C=2)) from table1 where C < 100 order by C;
QUERY PLAN
----------------------------------------------------------------------
Sort
Sort Key: table1.c
InitPlan 1 (returns $2)
-> HashSetOp Except All
-> Append
-> Subquery Scan on "*SELECT* 1"
-> Gather
Workers Planned: 2
-> Parallel Seq Scan on table1 table1_1
Filter: (c <= 2)
-> Subquery Scan on "*SELECT* 2"
-> Gather
Workers Planned: 2
-> Parallel Seq Scan on table1 table1_2
Filter: (c = 2)
-> Gather
Workers Planned: 2
-> Parallel Seq Scan on table1
Filter: (c < 100)
(19 rows)
OSS11.8:
postgres=# explain (costs off) select C,((select c_int from table1 where C<=2)except all(select c_int from table1 where C=2)) from table1 where C < 100 order by C;
QUERY PLAN
----------------------------------------------------------------------
Gather Merge
Workers Planned: 2
Params Evaluated: $2
InitPlan 1 (returns $2)
-> HashSetOp Except All
-> Append
-> Subquery Scan on "*SELECT* 1"
-> Gather
Workers Planned: 2
-> Parallel Seq Scan on table1 table1_1
Filter: (c <= 2)
-> Subquery Scan on "*SELECT* 2"
-> Gather
Workers Planned: 2
-> Parallel Seq Scan on table1 table1_2
Filter: (c = 2)
-> Sort
Sort Key: table1.c
-> Parallel Seq Scan on table1
Filter: (c < 100)
(20 rows)
Data in a table and Database configuration are the same,as follows:
create table table1(C int, c_int int, c_varchar varchar(1024),c_bigint bigint, c_varchar2 varchar(1024),c_time timestamp);
insert into table1 values(1,10,'10000tt1111t',123456789,'012-3456-7890','2018-05-06 10:00:00');
insert into table1 values(2,20,'20000tt2t222t',223456789,'023-3486-2342','2019-05-06 10:00:00');
insert into table1 values(3,30,'30000tt3t333tt',323456789,'021-9823-8821','2020-05-06 10:00:00');
insert into table1 values(4,20,'20000tt2t222t',223456789,'023-3486-2342','2019-05-06 10:00:00');
set enable_sort=off;
set force_parallel_mode to on;
set parallel_setup_cost to 0.1;
set min_parallel_table_scan_size to 0;
set min_parallel_index_scan_size to 0;
insert into table1 select generate_series(10,1000000),generate_series(100,100000),'aaa',777777777,'012-3456-7890','2018-05-06 10:00:00';
question:
in OSS 10, how to make the top plan of the plan tree to GatherMerge with ‘except all’ in sql?
On Fri, 4 Sep 2020 at 20:21, Yang, Rong <yangr.fnst@cn.fujitsu.com> wrote: > in OSS 10, how to make the top plan of the plan tree to GatherMerge with ‘except all’ in sql? (I guess you're talking about PostgreSQL 10. I'm not sure what OSS 10 is.) The ability for Gather Merge to work with Init Plan values was only added in [1], which was new to PostgreSQL 11. So you're not going to make that work in PostgreSQL 10. You could try crafting the query in such a way that an Init plan is not used. e.g a CROSS JOIN, but not sure if that'll improve your performance any. So you could try reducing the parallel_tuple_cost a bit, which might give you a Gather, so at least the Seq Scan will be done in parallel. The sort will still be serial though. A btree index on table1 (c) looks like it might be worth considering. David [1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e89a71fb44