src/test/regress/expected/select_parallel.out - master - githubmirror/postgres

src/test/regress/expected/select_parallel.out
1 436 строк65 KB
Robert Haas
Assign each subquery a unique name prior to planning it.
07 окт 2025, 16:18
07 окт 2025, 16:188c49a48
--
-- PARALLEL
--
-- Save parallel worker stats, used for comparison at the end
select pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

select parallel_workers_to_launch as parallel_workers_to_launch_before,
       parallel_workers_launched as parallel_workers_launched_before
  from pg_stat_database
  where datname = current_database() \gset
create function sp_parallel_restricted(int) returns int as
  $$begin return $1; end$$ language plpgsql parallel restricted;
begin;
-- encourage use of parallel plans
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
set min_parallel_table_scan_size=0;
set max_parallel_workers_per_gather=4;
-- Parallel Append with partial-subplans
explain (costs off)
  select round(avg(aa)), sum(aa) from a_star;
                          QUERY PLAN                          
--------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 3
         ->  Partial Aggregate
               ->  Parallel Append
                     ->  Parallel Seq Scan on d_star a_star_4
                     ->  Parallel Seq Scan on f_star a_star_6
                     ->  Parallel Seq Scan on e_star a_star_5
                     ->  Parallel Seq Scan on b_star a_star_2
                     ->  Parallel Seq Scan on c_star a_star_3
                     ->  Parallel Seq Scan on a_star a_star_1
(11 rows)

select round(avg(aa)), sum(aa) from a_star a1;
 round | sum 
-------+-----
    14 | 355
(1 row)

-- Parallel Append with both partial and non-partial subplans
alter table c_star set (parallel_workers = 0);
alter table d_star set (parallel_workers = 0);
explain (costs off)
  select round(avg(aa)), sum(aa) from a_star;
                          QUERY PLAN                          
--------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 3
         ->  Partial Aggregate
               ->  Parallel Append
                     ->  Seq Scan on d_star a_star_4
                     ->  Seq Scan on c_star a_star_3
                     ->  Parallel Seq Scan on f_star a_star_6
                     ->  Parallel Seq Scan on e_star a_star_5
                     ->  Parallel Seq Scan on b_star a_star_2
                     ->  Parallel Seq Scan on a_star a_star_1
(11 rows)

select round(avg(aa)), sum(aa) from a_star a2;
 round | sum 
-------+-----
    14 | 355
(1 row)

-- Parallel Append with only non-partial subplans
alter table a_star set (parallel_workers = 0);
alter table b_star set (parallel_workers = 0);
alter table e_star set (parallel_workers = 0);
alter table f_star set (parallel_workers = 0);
explain (costs off)
  select round(avg(aa)), sum(aa) from a_star;
                     QUERY PLAN                      
-----------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 3
         ->  Partial Aggregate
               ->  Parallel Append
                     ->  Seq Scan on d_star a_star_4
                     ->  Seq Scan on f_star a_star_6
                     ->  Seq Scan on e_star a_star_5
                     ->  Seq Scan on b_star a_star_2
                     ->  Seq Scan on c_star a_star_3
                     ->  Seq Scan on a_star a_star_1
(11 rows)

select round(avg(aa)), sum(aa) from a_star a3;
 round | sum 
-------+-----
    14 | 355
(1 row)

-- Disable Parallel Append
alter table a_star reset (parallel_workers);
alter table b_star reset (parallel_workers);
alter table c_star reset (parallel_workers);
alter table d_star reset (parallel_workers);
alter table e_star reset (parallel_workers);
alter table f_star reset (parallel_workers);
set enable_parallel_append to off;
explain (costs off)
  select round(avg(aa)), sum(aa) from a_star;
                          QUERY PLAN                          
--------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 1
         ->  Partial Aggregate
               ->  Append
                     ->  Parallel Seq Scan on a_star a_star_1
                     ->  Parallel Seq Scan on b_star a_star_2
                     ->  Parallel Seq Scan on c_star a_star_3
                     ->  Parallel Seq Scan on d_star a_star_4
                     ->  Parallel Seq Scan on e_star a_star_5
                     ->  Parallel Seq Scan on f_star a_star_6
(11 rows)

select round(avg(aa)), sum(aa) from a_star a4;
 round | sum 
-------+-----
    14 | 355
(1 row)

reset enable_parallel_append;
-- Parallel Append that runs serially
create function sp_test_func() returns setof text as
$$ select 'foo'::varchar union all select 'bar'::varchar $$
language sql stable;
select sp_test_func() order by 1;
 sp_test_func 
--------------
 bar
 foo
(2 rows)

-- Parallel Append is not to be used when the subpath depends on the outer param
create table part_pa_test(a int, b int) partition by range(a);
create table part_pa_test_p1 partition of part_pa_test for values from (minvalue) to (0);
create table part_pa_test_p2 partition of part_pa_test for values from (0) to (maxvalue);
explain (costs off)
	select (select max((select pa1.b from part_pa_test pa1 where pa1.a = pa2.a)))
	from part_pa_test pa2;
                          QUERY PLAN                          
--------------------------------------------------------------
 Aggregate
   ->  Gather
         Workers Planned: 3
         ->  Parallel Append
               ->  Parallel Seq Scan on part_pa_test_p1 pa2_1
               ->  Parallel Seq Scan on part_pa_test_p2 pa2_2
   SubPlan expr_1
     ->  Result
   SubPlan expr_2
     ->  Append
           ->  Seq Scan on part_pa_test_p1 pa1_1
                 Filter: (a = pa2.a)
           ->  Seq Scan on part_pa_test_p2 pa1_2
                 Filter: (a = pa2.a)
(14 rows)

drop table part_pa_test;
-- test with leader participation disabled
set parallel_leader_participation = off;
explain (costs off)
  select count(*) from tenk1 where stringu1 = 'GRAAAA';
                       QUERY PLAN                        
---------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Seq Scan on tenk1
                     Filter: (stringu1 = 'GRAAAA'::name)
(6 rows)

select count(*) from tenk1 where stringu1 = 'GRAAAA';
 count 
-------
    15
(1 row)

-- test with leader participation disabled, but no workers available (so
-- the leader will have to run the plan despite the setting)
set max_parallel_workers = 0;
explain (costs off)
  select count(*) from tenk1 where stringu1 = 'GRAAAA';
                       QUERY PLAN                        
---------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Seq Scan on tenk1
                     Filter: (stringu1 = 'GRAAAA'::name)
(6 rows)

select count(*) from tenk1 where stringu1 = 'GRAAAA';
 count 
-------
    15
(1 row)

reset max_parallel_workers;
reset parallel_leader_participation;
-- test that parallel_restricted function doesn't run in worker
alter table tenk1 set (parallel_workers = 4);
explain (verbose, costs off)
select sp_parallel_restricted(unique1) from tenk1
  where stringu1 = 'GRAAAA' order by 1;
                       QUERY PLAN                        
---------------------------------------------------------
 Sort
   Output: (sp_parallel_restricted(unique1))
   Sort Key: (sp_parallel_restricted(tenk1.unique1))
   ->  Gather
         Output: sp_parallel_restricted(unique1)
         Workers Planned: 4
         ->  Parallel Seq Scan on public.tenk1
               Output: unique1
               Filter: (tenk1.stringu1 = 'GRAAAA'::name)
(9 rows)

-- test parallel plan when group by expression is in target list.
explain (costs off)
	select length(stringu1) from tenk1 group by length(stringu1);
                    QUERY PLAN                     
---------------------------------------------------
 Finalize HashAggregate
   Group Key: (length((stringu1)::text))
   ->  Gather
         Workers Planned: 4
         ->  Partial HashAggregate
               Group Key: length((stringu1)::text)
               ->  Parallel Seq Scan on tenk1
(7 rows)

select length(stringu1) from tenk1 group by length(stringu1);
 length 
--------
      6
(1 row)

explain (costs off)
	select stringu1, count(*) from tenk1 group by stringu1 order by stringu1;
                     QUERY PLAN                     
----------------------------------------------------
 Sort
   Sort Key: stringu1
   ->  Finalize HashAggregate
         Group Key: stringu1
         ->  Gather
               Workers Planned: 4
               ->  Partial HashAggregate
                     Group Key: stringu1
                     ->  Parallel Seq Scan on tenk1
(9 rows)

-- test that parallel plan for aggregates is not selected when
-- target list contains parallel restricted clause.
explain (costs off)
	select  sum(sp_parallel_restricted(unique1)) from tenk1
	group by(sp_parallel_restricted(unique1));
                            QUERY PLAN                             
-------------------------------------------------------------------
 HashAggregate
   Group Key: sp_parallel_restricted(unique1)
   ->  Gather
         Workers Planned: 4
         ->  Parallel Index Only Scan using tenk1_unique1 on tenk1
(5 rows)

-- test prepared statement
prepare tenk1_count(integer) As select  count((unique1)) from tenk1 where hundred > $1;
explain (costs off) execute tenk1_count(1);
                  QUERY PLAN                  
----------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Seq Scan on tenk1
                     Filter: (hundred > 1)
(6 rows)

execute tenk1_count(1);
 count 
-------
  9800
(1 row)

deallocate tenk1_count;
-- test parallel plans for queries containing un-correlated subplans.
alter table tenk2 set (parallel_workers = 0);
explain (costs off)
	select count(*) from tenk1 where (two, four) not in
	(select hundred, thousand from tenk2 where thousand > 100);
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Seq Scan on tenk1
                     Filter: (NOT (ANY ((two = (hashed SubPlan any_1).col1) AND (four = (hashed SubPlan any_1).col2))))
                     SubPlan any_1
                       ->  Seq Scan on tenk2
                             Filter: (thousand > 100)
(9 rows)

select count(*) from tenk1 where (two, four) not in
	(select hundred, thousand from tenk2 where thousand > 100);
 count 
-------
 10000
(1 row)

-- this is not parallel-safe due to use of random() within SubLink's testexpr:
explain (costs off)
	select * from tenk1 where (unique1 + random())::integer not in
	(select ten from tenk2);
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Seq Scan on tenk1
   Filter: (NOT (ANY ((((unique1)::double precision + random()))::integer = (hashed SubPlan any_1).col1)))
   SubPlan any_1
     ->  Seq Scan on tenk2
(4 rows)

alter table tenk2 reset (parallel_workers);
-- test parallel plan for a query containing initplan.
set enable_indexscan = off;
set enable_indexonlyscan = off;
set enable_bitmapscan = off;
alter table tenk2 set (parallel_workers = 2);
explain (costs off)
	select count(*) from tenk1
        where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2);
                        QUERY PLAN                        
----------------------------------------------------------
 Aggregate
   InitPlan expr_1
     ->  Finalize Aggregate
           ->  Gather
                 Workers Planned: 2
                 ->  Partial Aggregate
                       ->  Parallel Seq Scan on tenk2
   ->  Gather
         Workers Planned: 4
         ->  Parallel Seq Scan on tenk1
               Filter: (unique1 = (InitPlan expr_1).col1)
(11 rows)

select count(*) from tenk1
    where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2);
 count 
-------
     1
(1 row)

reset enable_indexscan;
reset enable_indexonlyscan;
reset enable_bitmapscan;
alter table tenk2 reset (parallel_workers);
-- test parallel index scans.
set enable_seqscan to off;
set enable_bitmapscan to off;
set random_page_cost = 2;
explain (costs off)
	select  count((unique1)) from tenk1 where hundred > 1;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Index Scan using tenk1_hundred on tenk1
                     Index Cond: (hundred > 1)
(6 rows)

select  count((unique1)) from tenk1 where hundred > 1;
 count 
-------
  9800
(1 row)

-- Parallel ScalarArrayOp index scan
explain (costs off)
  select count((unique1)) from tenk1
  where hundred = any ((select array_agg(i) from generate_series(1, 100, 15) i)::int[]);
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Finalize Aggregate
   InitPlan expr_1
     ->  Aggregate
           ->  Function Scan on generate_series i
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Index Scan using tenk1_hundred on tenk1
                     Index Cond: (hundred = ANY ((InitPlan expr_1).col1))
(9 rows)

select count((unique1)) from tenk1
where hundred = any ((select array_agg(i) from generate_series(1, 100, 15) i)::int[]);
 count 
-------
   700
(1 row)

-- test parallel index-only scans.
explain (costs off)
	select  count(*) from tenk1 where thousand > 95;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Index Only Scan using tenk1_thous_tenthous on tenk1
                     Index Cond: (thousand > 95)
(6 rows)

select  count(*) from tenk1 where thousand > 95;
 count 
-------
  9040
(1 row)

-- test rescan cases too
set enable_material = false;
explain (costs off)
select * from
  (select count(unique1) from tenk1 where hundred > 10) ss
  right join (values (1),(2),(3)) v(x) on true;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Nested Loop Left Join
   ->  Values Scan on "*VALUES*"
   ->  Finalize Aggregate
         ->  Gather
               Workers Planned: 4
               ->  Partial Aggregate
                     ->  Parallel Index Scan using tenk1_hundred on tenk1
                           Index Cond: (hundred > 10)
(8 rows)

select * from
  (select count(unique1) from tenk1 where hundred > 10) ss
  right join (values (1),(2),(3)) v(x) on true;
 count | x 
-------+---
  8900 | 1
  8900 | 2
  8900 | 3
(3 rows)

explain (costs off)
select * from
  (select count(*) from tenk1 where thousand > 99) ss
  right join (values (1),(2),(3)) v(x) on true;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Nested Loop Left Join
   ->  Values Scan on "*VALUES*"
   ->  Finalize Aggregate
         ->  Gather
               Workers Planned: 4
               ->  Partial Aggregate
                     ->  Parallel Index Only Scan using tenk1_thous_tenthous on tenk1
                           Index Cond: (thousand > 99)
(8 rows)

select * from
  (select count(*) from tenk1 where thousand > 99) ss
  right join (values (1),(2),(3)) v(x) on true;
 count | x 
-------+---
  9000 | 1
  9000 | 2
  9000 | 3
(3 rows)

-- test rescans for a Limit node with a parallel node beneath it.
reset enable_seqscan;
set enable_indexonlyscan to off;
set enable_indexscan to off;
alter table tenk1 set (parallel_workers = 0);
alter table tenk2 set (parallel_workers = 1);
explain (costs off)
select count(*) from tenk1
  left join (select tenk2.unique1 from tenk2 order by 1 limit 1000) ss
  on tenk1.unique1 < ss.unique1 + 1
  where tenk1.unique1 < 2;
                         QUERY PLAN                         
------------------------------------------------------------
 Aggregate
   ->  Nested Loop Left Join
         Join Filter: (tenk1.unique1 < (tenk2.unique1 + 1))
         ->  Seq Scan on tenk1
               Filter: (unique1 < 2)
         ->  Limit
               ->  Gather Merge
                     Workers Planned: 1
                     ->  Sort
                           Sort Key: tenk2.unique1
                           ->  Parallel Seq Scan on tenk2
(11 rows)

select count(*) from tenk1
  left join (select tenk2.unique1 from tenk2 order by 1 limit 1000) ss
  on tenk1.unique1 < ss.unique1 + 1
  where tenk1.unique1 < 2;
 count 
-------
  1999
(1 row)

--reset the value of workers for each table as it was before this test.
alter table tenk1 set (parallel_workers = 4);
alter table tenk2 reset (parallel_workers);
reset enable_material;
reset enable_bitmapscan;
reset enable_indexonlyscan;
reset enable_indexscan;
-- test parallel bitmap heap scan.
set enable_seqscan to off;
set enable_indexscan to off;
set enable_hashjoin to off;
set enable_mergejoin to off;
set enable_material to off;
-- test prefetching, if the platform allows it
DO $$
BEGIN
 SET effective_io_concurrency = 50;
EXCEPTION WHEN invalid_parameter_value THEN
END $$;
set work_mem='64kB';  --set small work mem to force lossy pages
explain (costs off)
	select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
                         QUERY PLAN                         
------------------------------------------------------------
 Aggregate
   ->  Nested Loop
         ->  Gather
               Workers Planned: 4
               ->  Parallel Seq Scan on tenk2
                     Disabled: true
                     Filter: (thousand = 0)
         ->  Gather
               Workers Planned: 4
               ->  Parallel Bitmap Heap Scan on tenk1
                     Recheck Cond: (hundred > 1)
                     ->  Bitmap Index Scan on tenk1_hundred
                           Index Cond: (hundred > 1)
(13 rows)

select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
 count 
-------
 98000
(1 row)

create table bmscantest (a int, t text);
insert into bmscantest select r, 'fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r;
create index i_bmtest ON bmscantest(a);
select count(*) from bmscantest where a>1;
 count 
-------
 99999
(1 row)

-- test accumulation of stats for parallel nodes
reset enable_seqscan;
alter table tenk2 set (parallel_workers = 0);
explain (analyze, timing off, summary off, costs off, buffers off)
   select count(*) from tenk1, tenk2 where tenk1.hundred > 1
        and tenk2.thousand=0;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Nested Loop (actual rows=98000.00 loops=1)
         ->  Seq Scan on tenk2 (actual rows=10.00 loops=1)
               Filter: (thousand = 0)
               Rows Removed by Filter: 9990
         ->  Gather (actual rows=9800.00 loops=10)
               Workers Planned: 4
               Workers Launched: 4
               ->  Parallel Seq Scan on tenk1 (actual rows=1960.00 loops=50)
                     Filter: (hundred > 1)
                     Rows Removed by Filter: 40
(11 rows)

alter table tenk2 reset (parallel_workers);
reset work_mem;
create function explain_parallel_sort_stats() returns setof text
language plpgsql as
$$
declare ln text;
begin
    for ln in
        explain (analyze, timing off, summary off, costs off, buffers off)
          select * from
          (select ten from tenk1 where ten < 100 order by ten) ss
          right join (values (1),(2),(3)) v(x) on true
    loop
        ln := regexp_replace(ln, 'Memory: \S*',  'Memory: xxx');
        return next ln;
    end loop;
end;
$$;
select * from explain_parallel_sort_stats();
                         explain_parallel_sort_stats                         
-----------------------------------------------------------------------------
 Nested Loop Left Join (actual rows=30000.00 loops=1)
   ->  Values Scan on "*VALUES*" (actual rows=3.00 loops=1)
   ->  Gather Merge (actual rows=10000.00 loops=3)
         Workers Planned: 4
         Workers Launched: 4
         ->  Sort (actual rows=2000.00 loops=15)
               Sort Key: tenk1.ten
               Sort Method: quicksort  Memory: xxx
               Worker 0:  Sort Method: quicksort  Memory: xxx
               Worker 1:  Sort Method: quicksort  Memory: xxx
               Worker 2:  Sort Method: quicksort  Memory: xxx
               Worker 3:  Sort Method: quicksort  Memory: xxx
               ->  Parallel Seq Scan on tenk1 (actual rows=2000.00 loops=15)
                     Filter: (ten < 100)
(14 rows)

reset enable_indexscan;
reset enable_hashjoin;
reset enable_mergejoin;
reset enable_material;
reset effective_io_concurrency;
drop table bmscantest;
drop function explain_parallel_sort_stats();
-- test parallel merge join path.
set enable_hashjoin to off;
set enable_nestloop to off;
explain (costs off)
	select  count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Merge Join
                     Merge Cond: (tenk1.unique1 = tenk2.unique1)
                     ->  Parallel Index Only Scan using tenk1_unique1 on tenk1
                     ->  Index Only Scan using tenk2_unique1 on tenk2
(8 rows)

select  count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
 count 
-------
 10000
(1 row)

reset enable_hashjoin;
reset enable_nestloop;
-- test parallel nestloop join path with materialization of the inner path
alter table tenk2 set (parallel_workers = 0);
explain (costs off)
select * from tenk1 t1, tenk2 t2 where t1.two > t2.two;
                QUERY PLAN                 
-------------------------------------------
 Gather
   Workers Planned: 4
   ->  Nested Loop
         Join Filter: (t1.two > t2.two)
         ->  Parallel Seq Scan on tenk1 t1
         ->  Materialize
               ->  Seq Scan on tenk2 t2
(7 rows)

-- test that parallel nestloop join is not generated if the inner path is
-- not parallel-safe
explain (costs off)
select * from tenk1 t1
    left join lateral
      (select t1.unique1 as x, * from tenk2 t2 order by 1) t2
    on true
where t1.two > t2.two;
                QUERY PLAN                 
-------------------------------------------
 Nested Loop
   ->  Gather
         Workers Planned: 4
         ->  Parallel Seq Scan on tenk1 t1
   ->  Subquery Scan on t2
         Filter: (t1.two > t2.two)
         ->  Seq Scan on tenk2 t2_1
(7 rows)

alter table tenk2 reset (parallel_workers);
-- test gather merge
set enable_hashagg = false;
explain (costs off)
   select count(*) from tenk1 group by twenty;
                     QUERY PLAN                     
----------------------------------------------------
 Finalize GroupAggregate
   Group Key: twenty
   ->  Gather Merge
         Workers Planned: 4
         ->  Partial GroupAggregate
               Group Key: twenty
               ->  Sort
                     Sort Key: twenty
                     ->  Parallel Seq Scan on tenk1
(9 rows)

select count(*) from tenk1 group by twenty;
 count 
-------
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
(20 rows)

--test expressions in targetlist are pushed down for gather merge
create function sp_simple_func(var1 integer) returns integer
as $$
begin
        return var1 + 10;
end;
$$ language plpgsql PARALLEL SAFE;
explain (costs off, verbose)
    select ten, sp_simple_func(ten) from tenk1 where ten < 100 order by ten;
                     QUERY PLAN                      
-----------------------------------------------------
 Gather Merge
   Output: ten, (sp_simple_func(ten))
   Workers Planned: 4
   ->  Result
         Output: ten, sp_simple_func(ten)
         ->  Sort
               Output: ten
               Sort Key: tenk1.ten
               ->  Parallel Seq Scan on public.tenk1
                     Output: ten
                     Filter: (tenk1.ten < 100)
(11 rows)

drop function sp_simple_func(integer);
-- test handling of SRFs in targetlist (bug in 10.0)
explain (costs off)
   select count(*), generate_series(1,2) from tenk1 group by twenty;
                        QUERY PLAN                        
----------------------------------------------------------
 ProjectSet
   ->  Finalize GroupAggregate
         Group Key: twenty
         ->  Gather Merge
               Workers Planned: 4
               ->  Partial GroupAggregate
                     Group Key: twenty
                     ->  Sort
                           Sort Key: twenty
                           ->  Parallel Seq Scan on tenk1
(10 rows)

select count(*), generate_series(1,2) from tenk1 group by twenty;
 count | generate_series 
-------+-----------------
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
   500 |               1
   500 |               2
(40 rows)

-- test gather merge with parallel leader participation disabled
set parallel_leader_participation = off;
explain (costs off)
   select count(*) from tenk1 group by twenty;
                     QUERY PLAN                     
----------------------------------------------------
 Finalize GroupAggregate
   Group Key: twenty
   ->  Gather Merge
         Workers Planned: 4
         ->  Partial GroupAggregate
               Group Key: twenty
               ->  Sort
                     Sort Key: twenty
                     ->  Parallel Seq Scan on tenk1
(9 rows)

select count(*) from tenk1 group by twenty;
 count 
-------
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
   500
(20 rows)

reset parallel_leader_participation;
--test rescan behavior of gather merge
set enable_material = false;
explain (costs off)
select * from
  (select string4, count(unique2)
   from tenk1 group by string4 order by string4) ss
  right join (values (1),(2),(3)) v(x) on true;
                        QUERY PLAN                        
----------------------------------------------------------
 Nested Loop Left Join
   ->  Values Scan on "*VALUES*"
   ->  Finalize GroupAggregate
         Group Key: tenk1.string4
         ->  Gather Merge
               Workers Planned: 4
               ->  Partial GroupAggregate
                     Group Key: tenk1.string4
                     ->  Sort
                           Sort Key: tenk1.string4
                           ->  Parallel Seq Scan on tenk1
(11 rows)

select * from
  (select string4, count(unique2)
   from tenk1 group by string4 order by string4) ss
  right join (values (1),(2),(3)) v(x) on true;
 string4 | count | x 
---------+-------+---
 AAAAxx  |  2500 | 1
 HHHHxx  |  2500 | 1
 OOOOxx  |  2500 | 1
 VVVVxx  |  2500 | 1
 AAAAxx  |  2500 | 2
 HHHHxx  |  2500 | 2
 OOOOxx  |  2500 | 2
 VVVVxx  |  2500 | 2
 AAAAxx  |  2500 | 3
 HHHHxx  |  2500 | 3
 OOOOxx  |  2500 | 3
 VVVVxx  |  2500 | 3
(12 rows)

reset enable_material;
reset enable_hashagg;
-- check parallelized int8 aggregate (bug #14897)
explain (costs off)
select avg(unique1::int8) from tenk1;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Index Only Scan using tenk1_unique1 on tenk1
(5 rows)

select avg(unique1::int8) from tenk1;
          avg          
-----------------------
 4999.5000000000000000
(1 row)

-- gather merge test with a LIMIT
explain (costs off)
  select fivethous from tenk1 order by fivethous limit 4;
                  QUERY PLAN                  
----------------------------------------------
 Limit
   ->  Gather Merge
         Workers Planned: 4
         ->  Sort
               Sort Key: fivethous
               ->  Parallel Seq Scan on tenk1
(6 rows)

select fivethous from tenk1 order by fivethous limit 4;
 fivethous 
-----------
         0
         0
         1
         1
(4 rows)

-- gather merge test with 0 worker
set max_parallel_workers = 0;
explain (costs off)
   select string4 from tenk1 order by string4 limit 5;
                  QUERY PLAN                  
----------------------------------------------
 Limit
   ->  Gather Merge
         Workers Planned: 4
         ->  Sort
               Sort Key: string4
               ->  Parallel Seq Scan on tenk1
(6 rows)

select string4 from tenk1 order by string4 limit 5;
 string4 
---------
 AAAAxx
 AAAAxx
 AAAAxx
 AAAAxx
 AAAAxx
(5 rows)

-- gather merge test with 0 workers, with parallel leader
-- participation disabled (the leader will have to run the plan
-- despite the setting)
set parallel_leader_participation = off;
explain (costs off)
   select string4 from tenk1 order by string4 limit 5;
                  QUERY PLAN                  
----------------------------------------------
 Limit
   ->  Gather Merge
         Workers Planned: 4
         ->  Sort
               Sort Key: string4
               ->  Parallel Seq Scan on tenk1
(6 rows)

select string4 from tenk1 order by string4 limit 5;
 string4 
---------
 AAAAxx
 AAAAxx
 AAAAxx
 AAAAxx
 AAAAxx
(5 rows)

reset parallel_leader_participation;
reset max_parallel_workers;
create function parallel_safe_volatile(a int) returns int as
  $$ begin return a; end; $$ parallel safe volatile language plpgsql;
-- Test gather merge atop of a sort of a partial path
explain (costs off)
select * from tenk1 where four = 2
order by four, hundred, parallel_safe_volatile(thousand);
                          QUERY PLAN                           
---------------------------------------------------------------
 Gather Merge
   Workers Planned: 4
   ->  Sort
         Sort Key: hundred, (parallel_safe_volatile(thousand))
         ->  Parallel Seq Scan on tenk1
               Filter: (four = 2)
(6 rows)

-- Test gather merge atop of an incremental sort a of partial path
set min_parallel_index_scan_size = 0;
set enable_seqscan = off;
explain (costs off)
select * from tenk1 where four = 2
order by four, hundred, parallel_safe_volatile(thousand);
                          QUERY PLAN                           
---------------------------------------------------------------
 Gather Merge
   Workers Planned: 4
   ->  Incremental Sort
         Sort Key: hundred, (parallel_safe_volatile(thousand))
         Presorted Key: hundred
         ->  Parallel Index Scan using tenk1_hundred on tenk1
               Filter: (four = 2)
(7 rows)

reset min_parallel_index_scan_size;
reset enable_seqscan;
-- Test GROUP BY with a gather merge path atop of a sort of a partial path
explain (costs off)
select count(*) from tenk1
group by twenty, parallel_safe_volatile(two);
                             QUERY PLAN                             
--------------------------------------------------------------------
 Finalize GroupAggregate
   Group Key: twenty, (parallel_safe_volatile(two))
   ->  Gather Merge
         Workers Planned: 4
         ->  Sort
               Sort Key: twenty, (parallel_safe_volatile(two))
               ->  Partial HashAggregate
                     Group Key: twenty, parallel_safe_volatile(two)
                     ->  Parallel Seq Scan on tenk1
(9 rows)

drop function parallel_safe_volatile(int);
SAVEPOINT settings;
SET LOCAL debug_parallel_query = 1;
explain (costs off)
  select stringu1::int2 from tenk1 where unique1 = 1;
                  QUERY PLAN                   
-----------------------------------------------
 Gather
   Workers Planned: 1
   Single Copy: true
   ->  Index Scan using tenk1_unique1 on tenk1
         Index Cond: (unique1 = 1)
(5 rows)

ROLLBACK TO SAVEPOINT settings;
-- exercise record typmod remapping between backends
CREATE FUNCTION make_record(n int)
  RETURNS RECORD LANGUAGE plpgsql PARALLEL SAFE AS
$$
BEGIN
  RETURN CASE n
           WHEN 1 THEN ROW(1)
           WHEN 2 THEN ROW(1, 2)
           WHEN 3 THEN ROW(1, 2, 3)
           WHEN 4 THEN ROW(1, 2, 3, 4)
           ELSE ROW(1, 2, 3, 4, 5)
         END;
END;
$$;
SAVEPOINT settings;
SET LOCAL debug_parallel_query = 1;
SELECT make_record(x) FROM (SELECT generate_series(1, 5) x) ss ORDER BY x;
 make_record 
-------------
 (1)
 (1,2)
 (1,2,3)
 (1,2,3,4)
 (1,2,3,4,5)
(5 rows)

ROLLBACK TO SAVEPOINT settings;
DROP function make_record(n int);
-- test the sanity of parallel query after the active role is dropped.
drop role if exists regress_parallel_worker;
NOTICE:  role "regress_parallel_worker" does not exist, skipping
create role regress_parallel_worker;
set role regress_parallel_worker;
reset session authorization;
drop role regress_parallel_worker;
set debug_parallel_query = 1;
select count(*) from tenk1;
 count 
-------
 10000
(1 row)

reset debug_parallel_query;
reset role;
-- Window function calculation can't be pushed to workers.
explain (costs off, verbose)
  select count(*) from tenk1 a where (unique1, two) in
    (select unique1, row_number() over() from tenk1 b);
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Aggregate
   Output: count(*)
   ->  Hash Right Semi Join
         Hash Cond: ((b.unique1 = a.unique1) AND ((row_number() OVER w1) = a.two))
         ->  WindowAgg
               Output: b.unique1, row_number() OVER w1
               Window: w1 AS (ROWS UNBOUNDED PRECEDING)
               ->  Gather
                     Output: b.unique1
                     Workers Planned: 4
                     ->  Parallel Index Only Scan using tenk1_unique1 on public.tenk1 b
                           Output: b.unique1
         ->  Hash
               Output: a.unique1, a.two
               ->  Gather
                     Output: a.unique1, a.two
                     Workers Planned: 4
                     ->  Parallel Seq Scan on public.tenk1 a
                           Output: a.unique1, a.two
(19 rows)

-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
explain (costs off)
  select * from tenk1 a where two in
    (select two from tenk1 b where stringu1 like '%AAAA' limit 3);
                          QUERY PLAN                           
---------------------------------------------------------------
 Hash Semi Join
   Hash Cond: (a.two = b.two)
   ->  Gather
         Workers Planned: 4
         ->  Parallel Seq Scan on tenk1 a
   ->  Hash
         ->  Limit
               ->  Gather
                     Workers Planned: 4
                     ->  Parallel Seq Scan on tenk1 b
                           Filter: (stringu1 ~~ '%AAAA'::text)
(11 rows)

-- to increase the parallel query test coverage
SAVEPOINT settings;
SET LOCAL debug_parallel_query = 1;
EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1;
                           QUERY PLAN                           
----------------------------------------------------------------
 Gather (actual rows=10000.00 loops=1)
   Workers Planned: 4
   Workers Launched: 4
   ->  Parallel Seq Scan on tenk1 (actual rows=2000.00 loops=5)
(4 rows)

ROLLBACK TO SAVEPOINT settings;
-- provoke error in worker
-- (make the error message long enough to require multiple bufferloads)
SAVEPOINT settings;
SET LOCAL debug_parallel_query = 1;
select (stringu1 || repeat('abcd', 5000))::int2 from tenk1 where unique1 = 1;
ERROR:  invalid input syntax for type smallint: "BAAAAAabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcda … [Строка слишком длинная. Вы можете скачать файл]
CONTEXT:  parallel worker
ROLLBACK TO SAVEPOINT settings;
-- test interaction with set-returning functions
SAVEPOINT settings;
-- multiple subqueries under a single Gather node
-- must set parallel_setup_cost > 0 to discourage multiple Gather nodes
SET LOCAL parallel_setup_cost = 10;
EXPLAIN (COSTS OFF)
SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1
UNION ALL
SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1;
                     QUERY PLAN                     
----------------------------------------------------
 Gather
   Workers Planned: 4
   ->  Parallel Append
         ->  Parallel Seq Scan on tenk1
               Filter: (fivethous = (tenthous + 1))
         ->  Parallel Seq Scan on tenk1 tenk1_1
               Filter: (fivethous = (tenthous + 1))
(7 rows)

ROLLBACK TO SAVEPOINT settings;
-- can't use multiple subqueries under a single Gather node due to initPlans
EXPLAIN (COSTS OFF)
SELECT unique1 FROM tenk1 WHERE fivethous =
	(SELECT unique1 FROM tenk1 WHERE fivethous = 1 LIMIT 1)
UNION ALL
SELECT unique1 FROM tenk1 WHERE fivethous =
	(SELECT unique2 FROM tenk1 WHERE fivethous = 1 LIMIT 1)
ORDER BY 1;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Sort
   Sort Key: tenk1.unique1
   ->  Append
         ->  Gather
               Workers Planned: 4
               InitPlan expr_1
                 ->  Limit
                       ->  Gather
                             Workers Planned: 4
                             ->  Parallel Seq Scan on tenk1 tenk1_2
                                   Filter: (fivethous = 1)
               ->  Parallel Seq Scan on tenk1
                     Filter: (fivethous = (InitPlan expr_1).col1)
         ->  Gather
               Workers Planned: 4
               InitPlan expr_2
                 ->  Limit
                       ->  Gather
                             Workers Planned: 4
                             ->  Parallel Seq Scan on tenk1 tenk1_3
                                   Filter: (fivethous = 1)
               ->  Parallel Seq Scan on tenk1 tenk1_1
                     Filter: (fivethous = (InitPlan expr_2).col1)
(23 rows)

-- test interaction with SRFs
SELECT * FROM information_schema.foreign_data_wrapper_options
ORDER BY 1, 2, 3;
 foreign_data_wrapper_catalog | foreign_data_wrapper_name | option_name | option_value 
------------------------------+---------------------------+-------------+--------------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT generate_series(1, two), array(select generate_series(1, two))
  FROM tenk1 ORDER BY tenthous;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 ProjectSet
   Output: generate_series(1, tenk1.two), ARRAY(SubPlan array_1), tenk1.tenthous
   ->  Gather Merge
         Output: tenk1.two, tenk1.tenthous
         Workers Planned: 4
         ->  Result
               Output: tenk1.two, tenk1.tenthous
               ->  Sort
                     Output: tenk1.tenthous, tenk1.two
                     Sort Key: tenk1.tenthous
                     ->  Parallel Seq Scan on public.tenk1
                           Output: tenk1.tenthous, tenk1.two
   SubPlan array_1
     ->  ProjectSet
           Output: generate_series(1, tenk1.two)
           ->  Result
(16 rows)

-- must disallow pushing sort below gather when pathkey contains an SRF
EXPLAIN (VERBOSE, COSTS OFF)
SELECT unnest(ARRAY[]::integer[]) + 1 AS pathkey
  FROM tenk1 t1 JOIN tenk1 t2 ON TRUE
  ORDER BY pathkey;
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Sort
   Output: (((unnest('{}'::integer[])) + 1))
   Sort Key: (((unnest('{}'::integer[])) + 1))
   ->  Result
         Output: ((unnest('{}'::integer[])) + 1)
         ->  ProjectSet
               Output: unnest('{}'::integer[])
               ->  Nested Loop
                     ->  Gather
                           Workers Planned: 4
                           ->  Parallel Index Only Scan using tenk1_hundred on public.tenk1 t1
                     ->  Materialize
                           ->  Gather
                                 Workers Planned: 4
                                 ->  Parallel Index Only Scan using tenk1_hundred on public.tenk1 t2
(15 rows)

-- test passing expanded-value representations to workers
CREATE FUNCTION make_some_array(int,int) returns int[] as
$$declare x int[];
  begin
    x[1] := $1;
    x[2] := $2;
    return x;
  end$$ language plpgsql parallel safe;
CREATE TABLE fooarr(f1 text, f2 int[], f3 text);
INSERT INTO fooarr VALUES('1', ARRAY[1,2], 'one');
PREPARE pstmt(text, int[]) AS SELECT * FROM fooarr WHERE f1 = $1 AND f2 = $2;
EXPLAIN (COSTS OFF) EXECUTE pstmt('1', make_some_array(1,2));
                            QUERY PLAN                            
------------------------------------------------------------------
 Gather
   Workers Planned: 3
   ->  Parallel Seq Scan on fooarr
         Filter: ((f1 = '1'::text) AND (f2 = '{1,2}'::integer[]))
(4 rows)

EXECUTE pstmt('1', make_some_array(1,2));
 f1 |  f2   | f3  
----+-------+-----
 1  | {1,2} | one
(1 row)

DEALLOCATE pstmt;
-- test interaction between subquery and partial_paths
CREATE VIEW tenk1_vw_sec WITH (security_barrier) AS SELECT * FROM tenk1;
EXPLAIN (COSTS OFF)
SELECT 1 FROM tenk1_vw_sec
  WHERE (SELECT sum(f1) FROM int4_tbl WHERE f1 < unique1) < 100;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Subquery Scan on tenk1_vw_sec
   Filter: ((SubPlan expr_1) < 100)
   ->  Gather
         Workers Planned: 4
         ->  Parallel Index Only Scan using tenk1_unique1 on tenk1
   SubPlan expr_1
     ->  Aggregate
           ->  Seq Scan on int4_tbl
                 Filter: (f1 < tenk1_vw_sec.unique1)
(9 rows)

rollback;
-- test that a newly-created session role propagates to workers.
begin;
create role regress_parallel_worker;
set session authorization regress_parallel_worker;
select current_setting('session_authorization');
     current_setting     
-------------------------
 regress_parallel_worker
(1 row)

set debug_parallel_query = 1;
select current_setting('session_authorization');
     current_setting     
-------------------------
 regress_parallel_worker
(1 row)

rollback;
-- test that function option SET ROLE works in parallel workers.
create role regress_parallel_worker;
create function set_and_report_role() returns text as
  $$ select current_setting('role') $$ language sql parallel safe
  set role = regress_parallel_worker;
create function set_role_and_error(int) returns int as
  $$ select 1 / $1 $$ language sql parallel safe
  set role = regress_parallel_worker;
set debug_parallel_query = 0;
select set_and_report_role();
   set_and_report_role   
-------------------------
 regress_parallel_worker
(1 row)

select set_role_and_error(0);
ERROR:  division by zero
CONTEXT:  SQL function "set_role_and_error" statement 1
set debug_parallel_query = 1;
select set_and_report_role();
   set_and_report_role   
-------------------------
 regress_parallel_worker
(1 row)

select set_role_and_error(0);
ERROR:  division by zero
CONTEXT:  SQL function "set_role_and_error" statement 1
parallel worker
reset debug_parallel_query;
drop function set_and_report_role();
drop function set_role_and_error(int);
drop role regress_parallel_worker;
-- don't freeze in ParallelFinish while holding an LWLock
BEGIN;
CREATE FUNCTION my_cmp (int4, int4)
RETURNS int LANGUAGE sql AS
$$
	SELECT
		CASE WHEN $1 < $2 THEN -1
				WHEN $1 > $2 THEN  1
				ELSE 0
		END;
$$;
CREATE TABLE parallel_hang (i int4);
INSERT INTO parallel_hang
	(SELECT * FROM generate_series(1, 400) gs);
CREATE OPERATOR CLASS int4_custom_ops FOR TYPE int4 USING btree AS
	OPERATOR 1 < (int4, int4), OPERATOR 2 <= (int4, int4),
	OPERATOR 3 = (int4, int4), OPERATOR 4 >= (int4, int4),
	OPERATOR 5 > (int4, int4), FUNCTION 1 my_cmp(int4, int4);
CREATE UNIQUE INDEX parallel_hang_idx
					ON parallel_hang
					USING btree (i int4_custom_ops);
SET debug_parallel_query = on;
DELETE FROM parallel_hang WHERE 380 <= i AND i <= 420;
ROLLBACK;
-- Check parallel worker stats
select pg_stat_force_next_flush();
 pg_stat_force_next_flush 
--------------------------
 
(1 row)

select parallel_workers_to_launch > :'parallel_workers_to_launch_before'  AS wrk_to_launch,
       parallel_workers_launched > :'parallel_workers_launched_before' AS wrk_launched
  from pg_stat_database
  where datname = current_database();
 wrk_to_launch | wrk_launched 
---------------+--------------
 t             | t
(1 row)