Thread: how to get top plan of GatherMerge in OSS10

how to get top plan of GatherMerge in OSS10

From
"Yang, Rong"
Date:

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?


Re: how to get top plan of GatherMerge in OSS10

From
David Rowley
Date:
On Fri, 4 Sep 2020 at 20:21, Yang, Rong <yangr.fnst@cn.fujitsu.com> wrote:
> in OSS 10, how  to make the top plan of the plan tree to GatherMerge with ‘except all’ in sql?

(I guess you're talking about PostgreSQL 10. I'm not sure what OSS 10 is.)

The ability for Gather Merge to work with Init Plan values was only
added in [1], which was new to PostgreSQL 11. So you're not going to
make that work in PostgreSQL 10.

You could try crafting the query in such a way that an Init plan is
not used. e.g a CROSS JOIN, but not sure if that'll improve your
performance any. So you could try reducing the parallel_tuple_cost a
bit, which might give you a Gather, so at least the Seq Scan will be
done in parallel. The sort will still be serial though.

A btree index on table1 (c) looks like it might be worth considering.

David

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e89a71fb44