how to get top plan of GatherMerge in OSS10

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?

Responses

Browse pgsql-general by date

  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?