1-- 2-- PARALLEL 3-- 4 5create function sp_parallel_restricted(int) returns int as 6 $$begin return $1; end$$ language plpgsql parallel restricted; 7 8begin; 9 10-- encourage use of parallel plans 11set parallel_setup_cost=0; 12set parallel_tuple_cost=0; 13set min_parallel_table_scan_size=0; 14set max_parallel_workers_per_gather=4; 15 16-- Parallel Append with partial-subplans 17explain (costs off) 18 select round(avg(aa)), sum(aa) from a_star; 19select round(avg(aa)), sum(aa) from a_star a1; 20 21-- Parallel Append with both partial and non-partial subplans 22alter table c_star set (parallel_workers = 0); 23alter table d_star set (parallel_workers = 0); 24explain (costs off) 25 select round(avg(aa)), sum(aa) from a_star; 26select round(avg(aa)), sum(aa) from a_star a2; 27 28-- Parallel Append with only non-partial subplans 29alter table a_star set (parallel_workers = 0); 30alter table b_star set (parallel_workers = 0); 31alter table e_star set (parallel_workers = 0); 32alter table f_star set (parallel_workers = 0); 33explain (costs off) 34 select round(avg(aa)), sum(aa) from a_star; 35select round(avg(aa)), sum(aa) from a_star a3; 36 37-- Disable Parallel Append 38alter table a_star reset (parallel_workers); 39alter table b_star reset (parallel_workers); 40alter table c_star reset (parallel_workers); 41alter table d_star reset (parallel_workers); 42alter table e_star reset (parallel_workers); 43alter table f_star reset (parallel_workers); 44set enable_parallel_append to off; 45explain (costs off) 46 select round(avg(aa)), sum(aa) from a_star; 47select round(avg(aa)), sum(aa) from a_star a4; 48reset enable_parallel_append; 49 50-- Parallel Append that runs serially 51create function sp_test_func() returns setof text as 52$$ select 'foo'::varchar union all select 'bar'::varchar $$ 53language sql stable; 54select sp_test_func() order by 1; 55 56-- Parallel Append is not to be used when the subpath depends on the outer param 57create table part_pa_test(a int, b int) partition by range(a); 58create table part_pa_test_p1 partition of part_pa_test for values from (minvalue) to (0); 59create table part_pa_test_p2 partition of part_pa_test for values from (0) to (maxvalue); 60explain (costs off) 61 select (select max((select pa1.b from part_pa_test pa1 where pa1.a = pa2.a))) 62 from part_pa_test pa2; 63drop table part_pa_test; 64 65-- test with leader participation disabled 66set parallel_leader_participation = off; 67explain (costs off) 68 select count(*) from tenk1 where stringu1 = 'GRAAAA'; 69select count(*) from tenk1 where stringu1 = 'GRAAAA'; 70 71-- test with leader participation disabled, but no workers available (so 72-- the leader will have to run the plan despite the setting) 73set max_parallel_workers = 0; 74explain (costs off) 75 select count(*) from tenk1 where stringu1 = 'GRAAAA'; 76select count(*) from tenk1 where stringu1 = 'GRAAAA'; 77 78reset max_parallel_workers; 79reset parallel_leader_participation; 80 81-- test that parallel_restricted function doesn't run in worker 82alter table tenk1 set (parallel_workers = 4); 83explain (verbose, costs off) 84select sp_parallel_restricted(unique1) from tenk1 85 where stringu1 = 'GRAAAA' order by 1; 86 87-- test parallel plan when group by expression is in target list. 88explain (costs off) 89 select length(stringu1) from tenk1 group by length(stringu1); 90select length(stringu1) from tenk1 group by length(stringu1); 91 92explain (costs off) 93 select stringu1, count(*) from tenk1 group by stringu1 order by stringu1; 94 95-- test that parallel plan for aggregates is not selected when 96-- target list contains parallel restricted clause. 97explain (costs off) 98 select sum(sp_parallel_restricted(unique1)) from tenk1 99 group by(sp_parallel_restricted(unique1)); 100 101-- test prepared statement 102prepare tenk1_count(integer) As select count((unique1)) from tenk1 where hundred > $1; 103explain (costs off) execute tenk1_count(1); 104execute tenk1_count(1); 105deallocate tenk1_count; 106 107-- test parallel plans for queries containing un-correlated subplans. 108alter table tenk2 set (parallel_workers = 0); 109explain (costs off) 110 select count(*) from tenk1 where (two, four) not in 111 (select hundred, thousand from tenk2 where thousand > 100); 112select count(*) from tenk1 where (two, four) not in 113 (select hundred, thousand from tenk2 where thousand > 100); 114-- this is not parallel-safe due to use of random() within SubLink's testexpr: 115explain (costs off) 116 select * from tenk1 where (unique1 + random())::integer not in 117 (select ten from tenk2); 118alter table tenk2 reset (parallel_workers); 119 120-- test parallel plan for a query containing initplan. 121set enable_indexscan = off; 122set enable_indexonlyscan = off; 123set enable_bitmapscan = off; 124alter table tenk2 set (parallel_workers = 2); 125 126explain (costs off) 127 select count(*) from tenk1 128 where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2); 129select count(*) from tenk1 130 where tenk1.unique1 = (Select max(tenk2.unique1) from tenk2); 131 132reset enable_indexscan; 133reset enable_indexonlyscan; 134reset enable_bitmapscan; 135alter table tenk2 reset (parallel_workers); 136 137-- test parallel index scans. 138set enable_seqscan to off; 139set enable_bitmapscan to off; 140 141explain (costs off) 142 select count((unique1)) from tenk1 where hundred > 1; 143select count((unique1)) from tenk1 where hundred > 1; 144 145-- test parallel index-only scans. 146explain (costs off) 147 select count(*) from tenk1 where thousand > 95; 148select count(*) from tenk1 where thousand > 95; 149 150-- test rescan cases too 151set enable_material = false; 152 153explain (costs off) 154select * from 155 (select count(unique1) from tenk1 where hundred > 10) ss 156 right join (values (1),(2),(3)) v(x) on true; 157select * from 158 (select count(unique1) from tenk1 where hundred > 10) ss 159 right join (values (1),(2),(3)) v(x) on true; 160 161explain (costs off) 162select * from 163 (select count(*) from tenk1 where thousand > 99) ss 164 right join (values (1),(2),(3)) v(x) on true; 165select * from 166 (select count(*) from tenk1 where thousand > 99) ss 167 right join (values (1),(2),(3)) v(x) on true; 168 169-- test rescans for a Limit node with a parallel node beneath it. 170reset enable_seqscan; 171set enable_indexonlyscan to off; 172set enable_indexscan to off; 173alter table tenk1 set (parallel_workers = 0); 174alter table tenk2 set (parallel_workers = 1); 175explain (costs off) 176select count(*) from tenk1 177 left join (select tenk2.unique1 from tenk2 order by 1 limit 1000) ss 178 on tenk1.unique1 < ss.unique1 + 1 179 where tenk1.unique1 < 2; 180select count(*) from tenk1 181 left join (select tenk2.unique1 from tenk2 order by 1 limit 1000) ss 182 on tenk1.unique1 < ss.unique1 + 1 183 where tenk1.unique1 < 2; 184--reset the value of workers for each table as it was before this test. 185alter table tenk1 set (parallel_workers = 4); 186alter table tenk2 reset (parallel_workers); 187 188reset enable_material; 189reset enable_bitmapscan; 190reset enable_indexonlyscan; 191reset enable_indexscan; 192 193-- test parallel bitmap heap scan. 194set enable_seqscan to off; 195set enable_indexscan to off; 196set enable_hashjoin to off; 197set enable_mergejoin to off; 198set enable_material to off; 199-- test prefetching, if the platform allows it 200DO $$ 201BEGIN 202 SET effective_io_concurrency = 50; 203EXCEPTION WHEN invalid_parameter_value THEN 204END $$; 205set work_mem='64kB'; --set small work mem to force lossy pages 206explain (costs off) 207 select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; 208select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; 209 210create table bmscantest (a int, t text); 211insert into bmscantest select r, 'fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' FROM generate_series(1,100000) r; 212create index i_bmtest ON bmscantest(a); 213select count(*) from bmscantest where a>1; 214 215-- test accumulation of stats for parallel nodes 216reset enable_seqscan; 217alter table tenk2 set (parallel_workers = 0); 218explain (analyze, timing off, summary off, costs off) 219 select count(*) from tenk1, tenk2 where tenk1.hundred > 1 220 and tenk2.thousand=0; 221alter table tenk2 reset (parallel_workers); 222 223reset work_mem; 224create function explain_parallel_sort_stats() returns setof text 225language plpgsql as 226$$ 227declare ln text; 228begin 229 for ln in 230 explain (analyze, timing off, summary off, costs off) 231 select * from 232 (select ten from tenk1 where ten < 100 order by ten) ss 233 right join (values (1),(2),(3)) v(x) on true 234 loop 235 ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); 236 return next ln; 237 end loop; 238end; 239$$; 240select * from explain_parallel_sort_stats(); 241 242reset enable_indexscan; 243reset enable_hashjoin; 244reset enable_mergejoin; 245reset enable_material; 246reset effective_io_concurrency; 247drop table bmscantest; 248drop function explain_parallel_sort_stats(); 249 250-- test parallel merge join path. 251set enable_hashjoin to off; 252set enable_nestloop to off; 253 254explain (costs off) 255 select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1; 256select count(*) from tenk1, tenk2 where tenk1.unique1 = tenk2.unique1; 257 258reset enable_hashjoin; 259reset enable_nestloop; 260 261-- test gather merge 262set enable_hashagg = false; 263 264explain (costs off) 265 select count(*) from tenk1 group by twenty; 266 267select count(*) from tenk1 group by twenty; 268 269--test expressions in targetlist are pushed down for gather merge 270create function sp_simple_func(var1 integer) returns integer 271as $$ 272begin 273 return var1 + 10; 274end; 275$$ language plpgsql PARALLEL SAFE; 276 277explain (costs off, verbose) 278 select ten, sp_simple_func(ten) from tenk1 where ten < 100 order by ten; 279 280drop function sp_simple_func(integer); 281 282-- test handling of SRFs in targetlist (bug in 10.0) 283 284explain (costs off) 285 select count(*), generate_series(1,2) from tenk1 group by twenty; 286 287select count(*), generate_series(1,2) from tenk1 group by twenty; 288 289-- test gather merge with parallel leader participation disabled 290set parallel_leader_participation = off; 291 292explain (costs off) 293 select count(*) from tenk1 group by twenty; 294 295select count(*) from tenk1 group by twenty; 296 297reset parallel_leader_participation; 298 299--test rescan behavior of gather merge 300set enable_material = false; 301 302explain (costs off) 303select * from 304 (select string4, count(unique2) 305 from tenk1 group by string4 order by string4) ss 306 right join (values (1),(2),(3)) v(x) on true; 307 308select * from 309 (select string4, count(unique2) 310 from tenk1 group by string4 order by string4) ss 311 right join (values (1),(2),(3)) v(x) on true; 312 313reset enable_material; 314 315reset enable_hashagg; 316 317-- check parallelized int8 aggregate (bug #14897) 318explain (costs off) 319select avg(unique1::int8) from tenk1; 320 321select avg(unique1::int8) from tenk1; 322 323-- gather merge test with a LIMIT 324explain (costs off) 325 select fivethous from tenk1 order by fivethous limit 4; 326 327select fivethous from tenk1 order by fivethous limit 4; 328 329-- gather merge test with 0 worker 330set max_parallel_workers = 0; 331explain (costs off) 332 select string4 from tenk1 order by string4 limit 5; 333select string4 from tenk1 order by string4 limit 5; 334 335-- gather merge test with 0 workers, with parallel leader 336-- participation disabled (the leader will have to run the plan 337-- despite the setting) 338set parallel_leader_participation = off; 339explain (costs off) 340 select string4 from tenk1 order by string4 limit 5; 341select string4 from tenk1 order by string4 limit 5; 342 343reset parallel_leader_participation; 344reset max_parallel_workers; 345 346SAVEPOINT settings; 347SET LOCAL force_parallel_mode = 1; 348explain (costs off) 349 select stringu1::int2 from tenk1 where unique1 = 1; 350ROLLBACK TO SAVEPOINT settings; 351 352-- exercise record typmod remapping between backends 353CREATE FUNCTION make_record(n int) 354 RETURNS RECORD LANGUAGE plpgsql PARALLEL SAFE AS 355$$ 356BEGIN 357 RETURN CASE n 358 WHEN 1 THEN ROW(1) 359 WHEN 2 THEN ROW(1, 2) 360 WHEN 3 THEN ROW(1, 2, 3) 361 WHEN 4 THEN ROW(1, 2, 3, 4) 362 ELSE ROW(1, 2, 3, 4, 5) 363 END; 364END; 365$$; 366SAVEPOINT settings; 367SET LOCAL force_parallel_mode = 1; 368SELECT make_record(x) FROM (SELECT generate_series(1, 5) x) ss ORDER BY x; 369ROLLBACK TO SAVEPOINT settings; 370DROP function make_record(n int); 371 372-- test the sanity of parallel query after the active role is dropped. 373drop role if exists regress_parallel_worker; 374create role regress_parallel_worker; 375set role regress_parallel_worker; 376reset session authorization; 377drop role regress_parallel_worker; 378set force_parallel_mode = 1; 379select count(*) from tenk1; 380reset force_parallel_mode; 381reset role; 382 383-- Window function calculation can't be pushed to workers. 384explain (costs off, verbose) 385 select count(*) from tenk1 a where (unique1, two) in 386 (select unique1, row_number() over() from tenk1 b); 387 388 389-- LIMIT/OFFSET within sub-selects can't be pushed to workers. 390explain (costs off) 391 select * from tenk1 a where two in 392 (select two from tenk1 b where stringu1 like '%AAAA' limit 3); 393 394-- to increase the parallel query test coverage 395SAVEPOINT settings; 396SET LOCAL force_parallel_mode = 1; 397EXPLAIN (analyze, timing off, summary off, costs off) SELECT * FROM tenk1; 398ROLLBACK TO SAVEPOINT settings; 399 400-- provoke error in worker 401-- (make the error message long enough to require multiple bufferloads) 402SAVEPOINT settings; 403SET LOCAL force_parallel_mode = 1; 404select (stringu1 || repeat('abcd', 5000))::int2 from tenk1 where unique1 = 1; 405ROLLBACK TO SAVEPOINT settings; 406 407-- test interaction with set-returning functions 408SAVEPOINT settings; 409 410-- multiple subqueries under a single Gather node 411-- must set parallel_setup_cost > 0 to discourage multiple Gather nodes 412SET LOCAL parallel_setup_cost = 10; 413EXPLAIN (COSTS OFF) 414SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1 415UNION ALL 416SELECT unique1 FROM tenk1 WHERE fivethous = tenthous + 1; 417ROLLBACK TO SAVEPOINT settings; 418 419-- can't use multiple subqueries under a single Gather node due to initPlans 420EXPLAIN (COSTS OFF) 421SELECT unique1 FROM tenk1 WHERE fivethous = 422 (SELECT unique1 FROM tenk1 WHERE fivethous = 1 LIMIT 1) 423UNION ALL 424SELECT unique1 FROM tenk1 WHERE fivethous = 425 (SELECT unique2 FROM tenk1 WHERE fivethous = 1 LIMIT 1) 426ORDER BY 1; 427 428-- test interaction with SRFs 429SELECT * FROM information_schema.foreign_data_wrapper_options 430ORDER BY 1, 2, 3; 431 432EXPLAIN (VERBOSE, COSTS OFF) 433SELECT generate_series(1, two), array(select generate_series(1, two)) 434 FROM tenk1 ORDER BY tenthous; 435 436-- test passing expanded-value representations to workers 437CREATE FUNCTION make_some_array(int,int) returns int[] as 438$$declare x int[]; 439 begin 440 x[1] := $1; 441 x[2] := $2; 442 return x; 443 end$$ language plpgsql parallel safe; 444CREATE TABLE fooarr(f1 text, f2 int[], f3 text); 445INSERT INTO fooarr VALUES('1', ARRAY[1,2], 'one'); 446 447PREPARE pstmt(text, int[]) AS SELECT * FROM fooarr WHERE f1 = $1 AND f2 = $2; 448EXPLAIN (COSTS OFF) EXECUTE pstmt('1', make_some_array(1,2)); 449EXECUTE pstmt('1', make_some_array(1,2)); 450DEALLOCATE pstmt; 451 452-- test interaction between subquery and partial_paths 453CREATE VIEW tenk1_vw_sec WITH (security_barrier) AS SELECT * FROM tenk1; 454EXPLAIN (COSTS OFF) 455SELECT 1 FROM tenk1_vw_sec 456 WHERE (SELECT sum(f1) FROM int4_tbl WHERE f1 < unique1) < 100; 457 458rollback; 459