-- -- 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 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) -- 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) select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; QUERY PLAN -------------------------------------------------------------------------- Aggregate (actual rows=1 loops=1) -> Nested Loop (actual rows=98000 loops=1) -> Seq Scan on tenk2 (actual rows=10 loops=1) Filter: (thousand = 0) Rows Removed by Filter: 9990 -> Gather (actual rows=9800 loops=10) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on tenk1 (actual rows=1960 loops=50) Filter: (hundred > 1) Rows Removed by Filter: 40 (11 rows) alter table tenk2 reset (parallel_workers); 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 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 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; -- 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) reset max_parallel_workers; 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) -- 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; set 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) -- 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 Semi Join Hash Cond: ((a.unique1 = b.unique1) AND (a.two = (row_number() OVER (?)))) -> Gather Output: a.unique1, a.two Workers Planned: 4 -> Parallel Seq Scan on public.tenk1 a Output: a.unique1, a.two -> Hash Output: b.unique1, (row_number() OVER (?)) -> WindowAgg Output: b.unique1, row_number() OVER (?) -> Gather Output: b.unique1 Workers Planned: 4 -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 b Output: b.unique1 (18 rows) -- to increase the parallel query test coverage 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) -- provoke error in worker select stringu1::int2 from tenk1 where unique1 = 1; ERROR: invalid input syntax for integer: "BAAAAA" CONTEXT: parallel worker rollback;