5 create or replace function parallel_restricted(int) returns int as
6 $$begin return $1; end$$ language plpgsql parallel restricted;
8 -- Serializable isolation would disable parallel query, so explicitly use an
9 -- arbitrary other level.
10 begin isolation level repeatable read;
12 -- encourage use of parallel plans
13 set parallel_setup_cost=0;
14 set parallel_tuple_cost=0;
15 set min_parallel_table_scan_size=0;
16 set max_parallel_workers_per_gather=4;
19 select count(*) from a_star;
20 select count(*) from a_star;
22 -- test that parallel_restricted function doesn't run in worker
23 alter table tenk1 set (parallel_workers = 4);
24 explain (verbose, costs off)
25 select parallel_restricted(unique1) from tenk1
26 where stringu1 = 'GRAAAA' order by 1;
28 -- test parallel plan when group by expression is in target list.
30 select length(stringu1) from tenk1 group by length(stringu1);
31 select length(stringu1) from tenk1 group by length(stringu1);
34 select stringu1, count(*) from tenk1 group by stringu1 order by stringu1;
36 -- test that parallel plan for aggregates is not selected when
37 -- target list contains parallel restricted clause.
39 select sum(parallel_restricted(unique1)) from tenk1
40 group by(parallel_restricted(unique1));
42 -- test parallel plans for queries containing un-correlated subplans.
43 alter table tenk2 set (parallel_workers = 0);
45 select count(*) from tenk1 where (two, four) not in
46 (select hundred, thousand from tenk2 where thousand > 100);
47 select count(*) from tenk1 where (two, four) not in
48 (select hundred, thousand from tenk2 where thousand > 100);
49 alter table tenk2 reset (parallel_workers);
51 -- test parallel index scans.
52 set enable_seqscan to off;
53 set enable_bitmapscan to off;
56 select count((unique1)) from tenk1 where hundred > 1;
57 select count((unique1)) from tenk1 where hundred > 1;
59 -- test parallel index-only scans.
61 select count(*) from tenk1 where thousand > 95;
62 select count(*) from tenk1 where thousand > 95;
65 reset enable_bitmapscan;
67 -- test parallel bitmap heap scan.
68 set enable_seqscan to off;
69 set enable_indexscan to off;
70 set enable_hashjoin to off;
71 set enable_mergejoin to off;
72 set enable_material to off;
73 -- test prefetching, if the platform allows it
76 SET effective_io_concurrency = 50;
77 EXCEPTION WHEN invalid_parameter_value THEN
79 set work_mem='64kB'; --set small work mem to force lossy pages
81 select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
82 select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
84 create table bmscantest (a int, t text);
85 insert into bmscantest select r, 'fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r;
86 create index i_bmtest ON bmscantest(a);
87 select count(*) from bmscantest where a>1;
90 reset enable_indexscan;
91 reset enable_hashjoin;
92 reset enable_mergejoin;
93 reset enable_material;
94 reset effective_io_concurrency;
96 drop table bmscantest;
98 -- test parallel merge join path.
99 set enable_hashjoin to off;
100 set enable_nestloop to off;
103 select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
104 select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
106 reset enable_hashjoin;
107 reset enable_nestloop;
110 set enable_hashagg to off;
113 select string4, count((unique2)) from tenk1 group by string4 order by string4;
115 select string4, count((unique2)) from tenk1 group by string4 order by string4;
117 reset enable_hashagg;
119 set force_parallel_mode=1;
122 select stringu1::int2 from tenk1 where unique1 = 1;
124 -- to increase the parallel query test coverage
125 EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
127 -- provoke error in worker
128 select stringu1::int2 from tenk1 where unique1 = 1;