From: | "Yang, Rong" <yangr(dot)fnst(at)cn(dot)fujitsu(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | how to get top plan of GatherMerge in OSS10 |
Date: | 2020-09-04 08:20:56 |
Message-ID: | 1599207928905.13720@cn.fujitsu.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2020-09-04 08:41:16 | Re: How to enumerate partitions from a window function? |
Previous Message | Michael Lewis | 2020-09-03 18:58:52 | Re: How to enumerate partitions from a window function? |