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 -- Temporary hack to investigate whether extra vacuum/analyze is happening
40 select relname, relpages, reltuples
42 where relname like '__star' order by relname;
44 -- Disable Parallel Append
45 alter table a_star reset (parallel_workers);
46 alter table b_star reset (parallel_workers);
47 alter table c_star reset (parallel_workers);
48 alter table d_star reset (parallel_workers);
49 alter table e_star reset (parallel_workers);
50 alter table f_star reset (parallel_workers);
51 set enable_parallel_append to off;
53 select round(avg(aa)), sum(aa) from a_star;
54 select round(avg(aa)), sum(aa) from a_star a4;
55 reset enable_parallel_append;
57 -- Parallel Append that runs serially
58 create function sp_test_func() returns setof text as
59 $$ select 'foo'::varchar union all select 'bar'::varchar $$
61 select sp_test_func() order by 1;
63 -- Parallel Append is not to be used when the subpath depends on the outer param
64 create table part_pa_test(a int, b int) partition by range(a);
65 create table part_pa_test_p1 partition of part_pa_test for values from (minvalue) to (0);
66 create table part_pa_test_p2 partition of part_pa_test for values from (0) to (maxvalue);
68 select (select max((select pa1.b from part_pa_test pa1 where pa1.a = pa2.a)))
69 from part_pa_test pa2;
70 drop table part_pa_test;
72 -- test with leader participation disabled
73 set parallel_leader_participation = off;
75 select count(*) from tenk1 where stringu1 = 'GRAAAA';
76 select count(*) from tenk1 where stringu1 = 'GRAAAA';
78 -- test with leader participation disabled, but no workers available (so
79 -- the leader will have to run the plan despite the setting)
80 set max_parallel_workers = 0;
82 select count(*) from tenk1 where stringu1 = 'GRAAAA';
83 select count(*) from tenk1 where stringu1 = 'GRAAAA';
85 reset max_parallel_workers;
86 reset parallel_leader_participation;
88 -- test that parallel_restricted function doesn't run in worker
89 alter table tenk1 set (parallel_workers = 4);
90 explain (verbose, costs off)
91 select sp_parallel_restricted(unique1) from tenk1
92 where stringu1 = 'GRAAAA' order by 1;
94 -- test parallel plan when group by expression is in target list.
96 select length(stringu1) from tenk1 group by length(stringu1);
97 select length(stringu1) from tenk1 group by length(stringu1);
100 select stringu1, count(*) from tenk1 group by stringu1 order by stringu1;
102 -- test that parallel plan for aggregates is not selected when
103 -- target list contains parallel restricted clause.
105 select sum(sp_parallel_restricted(unique1)) from tenk1
106 group by(sp_parallel_restricted(unique1));
108 -- test prepared statement
109 prepare tenk1_count(integer) As select count((unique1)) from tenk1 where hundred > $1;
110 explain (costs off) execute tenk1_count(1);
111 execute tenk1_count(1);
112 deallocate tenk1_count;
114 -- test parallel plans for queries containing un-correlated subplans.
115 alter table tenk2 set (parallel_workers = 0);
117 select count(*) from tenk1 where (two, four) not in
118 (select hundred, thousand from tenk2 where thousand > 100);
119 select count(*) from tenk1 where (two, four) not in
120 (select hundred, thousand from tenk2 where thousand > 100);
121 -- this is not parallel-safe due to use of random() within SubLink's testexpr:
123 select * from tenk1 where (unique1 + random())::integer not in
124 (select ten from tenk2);
125 alter table tenk2 reset (parallel_workers);
127 -- test parallel plan for a query containing initplan.
128 set enable_indexscan = off;
129 set enable_indexonlyscan = off;
130 set enable_bitmapscan = off;
131 alter table tenk2 set (parallel_workers = 2);
134 select count(*) from tenk1
135 where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2);
136 select count(*) from tenk1
137 where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2);
139 reset enable_indexscan;
140 reset enable_indexonlyscan;
141 reset enable_bitmapscan;
142 alter table tenk2 reset (parallel_workers);
144 -- test parallel index scans.
145 set enable_seqscan to off;
146 set enable_bitmapscan to off;
149 select count((unique1)) from tenk1 where hundred > 1;
150 select count((unique1)) from tenk1 where hundred > 1;
152 -- test parallel index-only scans.
154 select count(*) from tenk1 where thousand > 95;
155 select count(*) from tenk1 where thousand > 95;
157 -- test rescan cases too
158 set enable_material = false;
162 (select count(unique1) from tenk1 where hundred > 10) ss
163 right join (values (1),(2),(3)) v(x) on true;
165 (select count(unique1) from tenk1 where hundred > 10) ss
166 right join (values (1),(2),(3)) v(x) on true;
170 (select count(*) from tenk1 where thousand > 99) ss
171 right join (values (1),(2),(3)) v(x) on true;
173 (select count(*) from tenk1 where thousand > 99) ss
174 right join (values (1),(2),(3)) v(x) on true;
176 reset enable_material;
177 reset enable_seqscan;
178 reset enable_bitmapscan;
180 -- test parallel bitmap heap scan.
181 set enable_seqscan to off;
182 set enable_indexscan to off;
183 set enable_hashjoin to off;
184 set enable_mergejoin to off;
185 set enable_material to off;
186 -- test prefetching, if the platform allows it
189 SET effective_io_concurrency = 50;
190 EXCEPTION WHEN invalid_parameter_value THEN
192 set work_mem='64kB'; --set small work mem to force lossy pages
194 select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
195 select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0;
197 create table bmscantest (a int, t text);
198 insert into bmscantest select r, 'fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r;
199 create index i_bmtest ON bmscantest(a);
200 select count(*) from bmscantest where a>1;
202 -- test accumulation of stats for parallel nodes
203 reset enable_seqscan;
204 alter table tenk2 set (parallel_workers = 0);
205 explain (analyze, timing off, summary off, costs off)
206 select count(*) from tenk1, tenk2 where tenk1.hundred > 1
207 and tenk2.thousand=0;
208 alter table tenk2 reset (parallel_workers);
211 create function explain_parallel_sort_stats() returns setof text
217 explain (analyze, timing off, summary off, costs off)
219 (select ten from tenk1 where ten < 100 order by ten) ss
220 right join (values (1),(2),(3)) v(x) on true
222 ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx');
227 select * from explain_parallel_sort_stats();
229 reset enable_indexscan;
230 reset enable_hashjoin;
231 reset enable_mergejoin;
232 reset enable_material;
233 reset effective_io_concurrency;
234 drop table bmscantest;
235 drop function explain_parallel_sort_stats();
237 -- test parallel merge join path.
238 set enable_hashjoin to off;
239 set enable_nestloop to off;
242 select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
243 select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1;
245 reset enable_hashjoin;
246 reset enable_nestloop;
249 set enable_hashagg = false;
252 select count(*) from tenk1 group by twenty;
254 select count(*) from tenk1 group by twenty;
256 --test expressions in targetlist are pushed down for gather merge
257 create function sp_simple_func(var1 integer) returns integer
262 $$ language plpgsql PARALLEL SAFE;
264 explain (costs off, verbose)
265 select ten, sp_simple_func(ten) from tenk1 where ten < 100 order by ten;
267 drop function sp_simple_func(integer);
269 -- test handling of SRFs in targetlist (bug in 10.0)
272 select count(*), generate_series(1,2) from tenk1 group by twenty;
274 select count(*), generate_series(1,2) from tenk1 group by twenty;
276 -- test gather merge with parallel leader participation disabled
277 set parallel_leader_participation = off;
280 select count(*) from tenk1 group by twenty;
282 select count(*) from tenk1 group by twenty;
284 reset parallel_leader_participation;
286 --test rescan behavior of gather merge
287 set enable_material = false;
291 (select string4, count(unique2)
292 from tenk1 group by string4 order by string4) ss
293 right join (values (1),(2),(3)) v(x) on true;
296 (select string4, count(unique2)
297 from tenk1 group by string4 order by string4) ss
298 right join (values (1),(2),(3)) v(x) on true;
300 reset enable_material;
302 reset enable_hashagg;
304 -- check parallelized int8 aggregate (bug #14897)
306 select avg(unique1::int8) from tenk1;
308 select avg(unique1::int8) from tenk1;
310 -- gather merge test with a LIMIT
312 select fivethous from tenk1 order by fivethous limit 4;
314 select fivethous from tenk1 order by fivethous limit 4;
316 -- gather merge test with 0 worker
317 set max_parallel_workers = 0;
319 select string4 from tenk1 order by string4 limit 5;
320 select string4 from tenk1 order by string4 limit 5;
322 -- gather merge test with 0 workers, with parallel leader
323 -- participation disabled (the leader will have to run the plan
324 -- despite the setting)
325 set parallel_leader_participation = off;
327 select string4 from tenk1 order by string4 limit 5;
328 select string4 from tenk1 order by string4 limit 5;
330 reset parallel_leader_participation;
331 reset max_parallel_workers;
334 SET LOCAL force_parallel_mode = 1;
336 select stringu1::int2 from tenk1 where unique1 = 1;
337 ROLLBACK TO SAVEPOINT settings;
339 -- exercise record typmod remapping between backends
340 CREATE FUNCTION make_record(n int)
341 RETURNS RECORD LANGUAGE plpgsql PARALLEL SAFE AS
346 WHEN 2 THEN ROW(1, 2)
347 WHEN 3 THEN ROW(1, 2, 3)
348 WHEN 4 THEN ROW(1, 2, 3, 4)
349 ELSE ROW(1, 2, 3, 4, 5)
354 SET LOCAL force_parallel_mode = 1;
355 SELECT make_record(x) FROM (SELECT generate_series(1, 5) x) ss ORDER BY x;
356 ROLLBACK TO SAVEPOINT settings;
357 DROP function make_record(n int);
359 -- test the sanity of parallel query after the active role is dropped.
360 drop role if exists regress_parallel_worker;
361 create role regress_parallel_worker;
362 set role regress_parallel_worker;
363 reset session authorization;
364 drop role regress_parallel_worker;
365 set force_parallel_mode = 1;
366 select count(*) from tenk1;
367 reset force_parallel_mode;
370 -- Window function calculation can't be pushed to workers.
371 explain (costs off, verbose)
372 select count(*) from tenk1 a where (unique1, two) in
373 (select unique1, row_number() over() from tenk1 b);
376 -- LIMIT/OFFSET within sub-selects can't be pushed to workers.
378 select * from tenk1 a where two in
379 (select two from tenk1 b where stringu1 like '%AAAA' limit 3);
381 -- to increase the parallel query test coverage
383 SET LOCAL force_parallel_mode = 1;
384 EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1;
385 ROLLBACK TO SAVEPOINT settings;
387 -- provoke error in worker
389 SET LOCAL force_parallel_mode = 1;
390 select stringu1::int2 from tenk1 where unique1 = 1;
391 ROLLBACK TO SAVEPOINT settings;
393 -- test interaction with set-returning functions
396 -- multiple subqueries under a single Gather node
397 -- must set parallel_setup_cost > 0 to discourage multiple Gather nodes
398 SET LOCAL parallel_setup_cost = 10;
400 SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1
402 SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1;
403 ROLLBACK TO SAVEPOINT settings;
405 -- can't use multiple subqueries under a single Gather node due to initPlans
407 SELECT unique1 FROM tenk1 WHERE fivethous =
408 (SELECT unique1 FROM tenk1 WHERE fivethous = 1 LIMIT 1)
410 SELECT unique1 FROM tenk1 WHERE fivethous =
411 (SELECT unique2 FROM tenk1 WHERE fivethous = 1 LIMIT 1)
414 -- test interaction with SRFs
415 SELECT * FROM information_schema.foreign_data_wrapper_options
418 -- test passing expanded-value representations to workers
419 CREATE FUNCTION make_some_array(int,int) returns int[] as
425 end$$ language plpgsql parallel safe;
426 CREATE TABLE fooarr(f1 text, f2 int[], f3 text);
427 INSERT INTO fooarr VALUES('1', ARRAY[1,2], 'one');
429 PREPARE pstmt(text, int[]) AS SELECT * FROM fooarr WHERE f1 = $1 AND f2 = $2;
430 EXPLAIN (COSTS OFF) EXECUTE pstmt('1', make_some_array(1,2));
431 EXECUTE pstmt('1', make_some_array(1,2));
434 -- test interaction between subquery and partial_paths
435 CREATE VIEW tenk1_vw_sec WITH (security_barrier) AS SELECT * FROM tenk1;
437 SELECT 1 FROM tenk1_vw_sec
438 WHERE (SELECT sum(f1) FROM int4_tbl WHERE f1 < unique1) < 100;