|
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?
|
