how to get top plan of GatherMerge in OSS10 - Mailing list pgsql-general

From Yang, Rong
Subject how to get top plan of GatherMerge in OSS10
Date
Msg-id 1599207928905.13720@cn.fujitsu.com
Whole thread Raw
Responses Re: how to get top plan of GatherMerge in OSS10
List 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?


pgsql-general by date:

Previous
From: Michael Lewis
Date:
Subject: Re: How to enumerate partitions from a window function?
Next
From: Alban Hertroys
Date:
Subject: Re: How to enumerate partitions from a window function?