path: root/src/test/regress/expected/select_parallel.out

--
-- PARALLEL
--
create or replace function parallel_restricted(int) returns int as
  $$begin return $1; end$$ language plpgsql parallel restricted;
-- Serializable isolation would disable parallel query, so explicitly use an
-- arbitrary other level.
begin isolation level repeatable read;
-- 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;
explain (costs off)
  select count(*) from a_star;
                     QUERY PLAN                      
-----------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 1
         ->  Partial Aggregate
               ->  Append
                     ->  Parallel Seq Scan on a_star
                     ->  Parallel Seq Scan on b_star
                     ->  Parallel Seq Scan on c_star
                     ->  Parallel Seq Scan on d_star
                     ->  Parallel Seq Scan on e_star
                     ->  Parallel Seq Scan on f_star
(11 rows)

select count(*) from a_star;
 count 
-------
    50
(1 row)

-- 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 parallel_restricted(unique1) from tenk1
  where stringu1 = 'GRAAAA' order by 1;
                       QUERY PLAN                        
---------------------------------------------------------
 Sort
   Output: (parallel_restricted(unique1))
   Sort Key: (parallel_restricted(tenk1.unique1))
   ->  Gather
         Output: 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(parallel_restricted(unique1)) from tenk1
	group by(parallel_restricted(unique1));
                            QUERY PLAN                             
-------------------------------------------------------------------
 HashAggregate
   Group Key: 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 (hashed SubPlan 1))
                     SubPlan 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 (hashed SubPlan 1))
   SubPlan 1
     ->  Seq Scan on tenk2
(4 rows)

alter table tenk2 reset (parallel_workers);
-- test parallel index scans.
set enable_seqscan to off;
set enable_bitmapscan to off;
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)

-- 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)

reset enable_material;
reset enable_seqscan;
reset enable_bitmapscan;
-- 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
         ->  Seq Scan on tenk2
               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)
(10 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)

reset enable_seqscan;
reset enable_indexscan;
reset enable_hashjoin;
reset enable_mergejoin;
reset enable_material;
reset effective_io_concurrency;
reset work_mem;
drop table bmscantest;
-- 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 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 or replace function simple_func(var1 integer) returns integer
as $$
begin
        return var1 + 10;
end;
$$ language plpgsql PARALLEL SAFE;
explain (costs off, verbose)
    select ten, simple_func(ten) from tenk1 where ten < 100 order by ten;
                     QUERY PLAN                      
-----------------------------------------------------
 Gather Merge
   Output: ten, (simple_func(ten))
   Workers Planned: 4
   ->  Result
         Output: ten, 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 simple_func(integer);
-- 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;
SAVEPOINT settings;
SET LOCAL force_parallel_mode = 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 OR REPLACE 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 force_parallel_mode = 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 force_parallel_mode = 1;
select count(*) from tenk1;
 count 
-------
 10000
(1 row)

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

ROLLBACK TO SAVEPOINT settings;
-- provoke error in worker
SAVEPOINT settings;
SET LOCAL force_parallel_mode = 1;
select stringu1::int2 from tenk1 where unique1 = 1;
ERROR:  invalid input syntax for integer: "BAAAAA"
CONTEXT:  parallel worker
ROLLBACK TO SAVEPOINT settings;
rollback;