5 create function sp_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;
18 -- Parallel Append with partial-subplans
20 select round(avg(aa)), sum(aa) from a_star;
21 select round(avg(aa)), sum(aa) from a_star a1;
23 -- Parallel Append with both partial and non-partial subplans
24 alter table c_star set (parallel_workers = 0);
25 alter table d_star set (parallel_workers = 0);
27 select round(avg(aa)), sum(aa) from a_star;
28 select round(avg(aa)), sum(aa) from a_star a2;
30 -- Parallel Append with only non-partial subplans
31 alter table a_star set (parallel_workers = 0);
32 alter table b_star set (parallel_workers = 0);
33 alter table e_star set (parallel_workers = 0);
34 alter table f_star set (parallel_workers = 0);
36 select round(avg(aa)), sum(aa) from a_star;
37 select round(avg(aa)), sum(aa) from a_star a3;
39 -- Disable Parallel Append
40 alter table a_star reset (parallel_workers);
41 alter table b_star reset (parallel_workers);
42 alter table c_star reset (parallel_workers);
43 alter table d_star reset (parallel_workers);
44 alter table e_star reset (parallel_workers);
45 alter table f_star reset (parallel_workers);
46 set enable_parallel_append to off;
48 select round(avg(aa)), sum(aa) from a_star;
49 select round(avg(aa)), sum(aa) from a_star a4;
50 reset enable_parallel_append;
52 -- Parallel Append that runs serially
53 create function sp_test_func() returns setof text as
54 $$ select 'foo'::varchar union all select 'bar'::varchar $$
56 select sp_test_func() order by 1;
58 -- test with leader participation disabled
59 set parallel_leader_participation = off;
61 select count(*) from tenk1 where stringu1 = 'GRAAAA';
62 select count(*) from tenk1 where stringu1 = 'GRAAAA';
64 -- test with leader participation disabled, but no workers available (so
65 -- the leader will have to run the plan despite the setting)
66 set max_parallel_workers = 0;
68 select count(*) from tenk1 where stringu1 = 'GRAAAA';
69 select count(*) from tenk1 where stringu1 = 'GRAAAA';
71 reset max_parallel_workers;
72 reset parallel_leader_participation;
74 -- test that parallel_restricted function doesn't run in worker
75 alter table tenk1 set (parallel_workers = 4);
76 explain (verbose, costs off)
77 select sp_parallel_restricted(unique1) from tenk1
78 where stringu1 = 'GRAAAA' order by 1;
80 -- test parallel plan when group by expression is in target list.
82 select length(stringu1) from tenk1 group by length(stringu1);
83 select length(stringu1) from tenk1 group by length(stringu1);
86 select stringu1, count(*) from tenk1 group by stringu1 order by stringu1;
88 -- test that parallel plan for aggregates is not selected when
89 -- target list contains parallel restricted clause.
91 select sum(sp_parallel_restricted(unique1)) from tenk1
92 group by(sp_parallel_restricted(unique1));
94 -- test prepared statement
95 prepare tenk1_count(integer) As select count((unique1)) from tenk1 where hundred > $1;
96 explain (costs off) execute tenk1_count(1);
97 execute tenk1_count(1);
98 deallocate tenk1_count;
100 -- test parallel plans for queries containing un-correlated subplans.
101 alter table tenk2 set (parallel_workers = 0);
103 select count(*) from tenk1 where (two, four) not in
104 (select hundred, thousand from tenk2 where thousand > 100);
105 select count(*) from tenk1 where (two, four) not in
106 (select hundred, thousand from tenk2 where thousand > 100);
107 -- this is not parallel-safe due to use of random() within SubLink's testexpr:
109 select * from tenk1 where (unique1 + random())::integer not in
110 (select ten from tenk2);
111 alter table tenk2 reset (parallel_workers);
113 -- test parallel plan for a query containing initplan.
114 set enable_indexscan = off;
115 set enable_indexonlyscan = off;
116 set enable_bitmapscan = off;
117 alter table tenk2 set (parallel_workers = 2);
120 select count(*) from tenk1
121 where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2);
122 select count(*) from tenk1
123 where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2);
125 reset enable_indexscan;
126 reset enable_indexonlyscan;
127 reset enable_bitmapscan;
128 alter table tenk2 reset (parallel_workers);
130 -- test parallel index scans.
131 set enable_seqscan to off;
132 set enable_bitmapscan to off;
135 select count((unique1)) from tenk1 where hundred > 1;
136 select count((unique1)) from tenk1 where hundred > 1;
138 -- test parallel index-only scans.
140 select count(*) from tenk1 where thousand > 95;
141 select count(*) from tenk1 where thousand > 95;
143 -- test rescan cases too
144 set enable_material = false;
148 (select count(unique1) from tenk1 where hundred > 10) ss
149 right join (values (1),(2),(3)) v(x) on true;
151 (select count(unique1) from tenk1 where hundred > 10) ss
152 right join (values (1),(2),(3)) v(x) on true;
156 (select count(*) from tenk1 where thousand > 99) ss
157 right join (values (1),(2),(3)) v(x) on true;
159 (select count(*) from tenk1 where thousand > 99) ss
160 right join (values (1),(2),(3)) v(x) on true;
162 reset enable_material;
163 reset enable_seqscan;
164 reset enable_bitmapscan;
166 -- test parallel bitmap heap scan.
167 set enable_seqscan to off;
168 set enable_indexscan to off;
169 set enable_hashjoin to off;
170 set enable_mergejoin to off;
171 set enable_material to off;
172 -- test prefetching, if the platform allows it
175 SET effective_io_concurrency = 50;
176 EXCEPTION WHEN invalid_parameter_value THEN
178 set work_mem='64kB'; --set small work mem to force lossy pages
180 select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
181 select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
183 create table bmscantest (a int, t text);
184 insert into bmscantest select r, 'fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r;
185 create index i_bmtest ON bmscantest(a);
186 select count(*) from bmscantest where a>1;
188 -- test accumulation of stats for parallel nodes
189 reset enable_seqscan;
190 alter table tenk2 set (parallel_workers = 0);
191 explain (analyze, timing off, summary off, costs off)
192 select count(*) from tenk1, tenk2 where tenk1.hundred > 1
193 and tenk2.thousand=0;
194 alter table tenk2 reset (parallel_workers);
197 create function explain_parallel_sort_stats() returns setof text
203 explain (analyze, timing off, summary off, costs off)
205 (select ten from tenk1 where ten < 100 order by ten) ss
206 right join (values (1),(2),(3)) v(x) on true
208 ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx');
213 select * from explain_parallel_sort_stats();
215 reset enable_indexscan;
216 reset enable_hashjoin;
217 reset enable_mergejoin;
218 reset enable_material;
219 reset effective_io_concurrency;
220 drop table bmscantest;
221 drop function explain_parallel_sort_stats();
223 -- test parallel merge join path.
224 set enable_hashjoin to off;
225 set enable_nestloop to off;
228 select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
229 select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
231 reset enable_hashjoin;
232 reset enable_nestloop;
235 set enable_hashagg = false;
238 select count(*) from tenk1 group by twenty;
240 select count(*) from tenk1 group by twenty;
242 --test expressions in targetlist are pushed down for gather merge
243 create function sp_simple_func(var1 integer) returns integer
248 $$ language plpgsql PARALLEL SAFE;
250 explain (costs off, verbose)
251 select ten, sp_simple_func(ten) from tenk1 where ten < 100 order by ten;
253 drop function sp_simple_func(integer);
255 -- test gather merge with parallel leader participation disabled
256 set parallel_leader_participation = off;
259 select count(*) from tenk1 group by twenty;
261 select count(*) from tenk1 group by twenty;
263 reset parallel_leader_participation;
265 --test rescan behavior of gather merge
266 set enable_material = false;
270 (select string4, count(unique2)
271 from tenk1 group by string4 order by string4) ss
272 right join (values (1),(2),(3)) v(x) on true;
275 (select string4, count(unique2)
276 from tenk1 group by string4 order by string4) ss
277 right join (values (1),(2),(3)) v(x) on true;
279 reset enable_material;
281 reset enable_hashagg;
283 -- check parallelized int8 aggregate (bug #14897)
285 select avg(unique1::int8) from tenk1;
287 select avg(unique1::int8) from tenk1;
289 -- gather merge test with a LIMIT
291 select fivethous from tenk1 order by fivethous limit 4;
293 select fivethous from tenk1 order by fivethous limit 4;
295 -- gather merge test with 0 worker
296 set max_parallel_workers = 0;
298 select string4 from tenk1 order by string4 limit 5;
299 select string4 from tenk1 order by string4 limit 5;
301 -- gather merge test with 0 workers, with parallel leader
302 -- participation disabled (the leader will have to run the plan
303 -- despite the setting)
304 set parallel_leader_participation = off;
306 select string4 from tenk1 order by string4 limit 5;
307 select string4 from tenk1 order by string4 limit 5;
309 reset parallel_leader_participation;
310 reset max_parallel_workers;
313 SET LOCAL force_parallel_mode = 1;
315 select stringu1::int2 from tenk1 where unique1 = 1;
316 ROLLBACK TO SAVEPOINT settings;
318 -- exercise record typmod remapping between backends
319 CREATE FUNCTION make_record(n int)
320 RETURNS RECORD LANGUAGE plpgsql PARALLEL SAFE AS
325 WHEN 2 THEN ROW(1, 2)
326 WHEN 3 THEN ROW(1, 2, 3)
327 WHEN 4 THEN ROW(1, 2, 3, 4)
328 ELSE ROW(1, 2, 3, 4, 5)
333 SET LOCAL force_parallel_mode = 1;
334 SELECT make_record(x) FROM (SELECT generate_series(1, 5) x) ss ORDER BY x;
335 ROLLBACK TO SAVEPOINT settings;
336 DROP function make_record(n int);
338 -- test the sanity of parallel query after the active role is dropped.
339 drop role if exists regress_parallel_worker;
340 create role regress_parallel_worker;
341 set role regress_parallel_worker;
342 reset session authorization;
343 drop role regress_parallel_worker;
344 set force_parallel_mode = 1;
345 select count(*) from tenk1;
346 reset force_parallel_mode;
349 -- to increase the parallel query test coverage
351 SET LOCAL force_parallel_mode = 1;
352 EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
353 ROLLBACK TO SAVEPOINT settings;
355 -- provoke error in worker
357 SET LOCAL force_parallel_mode = 1;
358 select stringu1::int2 from tenk1 where unique1 = 1;
359 ROLLBACK TO SAVEPOINT settings;
361 -- test interaction with set-returning functions
364 -- multiple subqueries under a single Gather node
365 -- must set parallel_setup_cost > 0 to discourage multiple Gather nodes
366 SET LOCAL parallel_setup_cost = 10;
368 SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1
370 SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1;
371 ROLLBACK TO SAVEPOINT settings;
373 -- can't use multiple subqueries under a single Gather node due to initPlans
375 SELECT unique1 FROM tenk1 WHERE fivethous =
376 (SELECT unique1 FROM tenk1 WHERE fivethous = 1 LIMIT 1)
378 SELECT unique1 FROM tenk1 WHERE fivethous =
379 (SELECT unique2 FROM tenk1 WHERE fivethous = 1 LIMIT 1)
382 -- test interaction with SRFs
383 SELECT * FROM information_schema.foreign_data_wrapper_options
386 -- test interation between subquery and partial_paths
387 SET LOCAL min_parallel_table_scan_size TO 0;
388 CREATE VIEW tenk1_vw_sec WITH (security_barrier) AS SELECT * FROM tenk1;
390 SELECT 1 FROM tenk1_vw_sec WHERE EXISTS (SELECT 1 WHERE unique1 = 0);