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