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?
