1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
--
-- PARALLEL
--
create or replace function parallel_restricted(int) returns int as
$$begin return $1; end$$ language plpgsql parallel restricted;
-- Serializable isolation would disable parallel query, so explicitly use an
-- arbitrary other level.
begin isolation level repeatable read;
-- encourage use of parallel plans
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
set min_parallel_relation_size=0;
set max_parallel_workers_per_gather=4;
explain (costs off)
select count(*) from a_star;
select count(*) from a_star;
-- test that parallel_restricted function doesn't run in worker
alter table tenk1 set (parallel_workers = 4);
explain (verbose, costs off)
select parallel_restricted(unique1) from tenk1
where stringu1 = 'GRAAAA' order by 1;
-- test parallel plan when group by expression is in target list.
explain (costs off)
select length(stringu1) from tenk1 group by length(stringu1);
select length(stringu1) from tenk1 group by length(stringu1);
explain (costs off)
select stringu1, count(*) from tenk1 group by stringu1 order by stringu1;
-- test that parallel plan for aggregates is not selected when
-- target list contains parallel restricted clause.
explain (costs off)
select sum(parallel_restricted(unique1)) from tenk1
group by(parallel_restricted(unique1));
-- check parallelized int8 aggregate (bug #14897)
explain (costs off)
select avg(aa::int8) from a_star;
select avg(aa::int8) from a_star;
-- test accumulation of stats for parallel nodes
set enable_indexscan to off;
set enable_bitmapscan to off;
set enable_material to off;
alter table tenk2 set (parallel_workers = 0);
create function explain_parallel_stats() returns setof text
language plpgsql as
$$
declare ln text;
begin
for ln in
explain (analyze, timing off, costs off)
select count(*) from tenk1, tenk2 where
tenk1.hundred > 1 and tenk2.thousand=0
loop
ln := regexp_replace(ln, 'Planning time: \S*', 'Planning time: xxx');
ln := regexp_replace(ln, 'Execution time: \S*', 'Execution time: xxx');
return next ln;
end loop;
end;
$$;
select * from explain_parallel_stats();
reset enable_indexscan;
reset enable_bitmapscan;
reset enable_material;
alter table tenk2 reset (parallel_workers);
drop function explain_parallel_stats();
-- test the sanity of parallel query after the active role is dropped.
set force_parallel_mode=1;
drop role if exists regress_parallel_worker;
create role regress_parallel_worker;
set role regress_parallel_worker;
reset session authorization;
drop role regress_parallel_worker;
select count(*) from tenk1;
reset role;
-- Window function calculation can't be pushed to workers.
explain (costs off, verbose)
select count(*) from tenk1 a where (unique1, two) in
(select unique1, row_number() over() from tenk1 b);
-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
explain (costs off)
select * from tenk1 a where two in
(select two from tenk1 b where stringu1 like '%AAAA' limit 3);
explain (costs off)
select stringu1::int2 from tenk1 where unique1 = 1;
-- test passing expanded-value representations to workers
CREATE FUNCTION make_some_array(int,int) returns int[] as
$$declare x int[];
begin
x[1] := $1;
x[2] := $2;
return x;
end$$ language plpgsql parallel safe;
CREATE TABLE fooarr(f1 text, f2 int[], f3 text);
INSERT INTO fooarr VALUES('1', ARRAY[1,2], 'one');
PREPARE pstmt(text, int[]) AS SELECT * FROM fooarr WHERE f1 = $1 AND f2 = $2;
EXPLAIN (COSTS OFF) EXECUTE pstmt('1', make_some_array(1,2));
EXECUTE pstmt('1', make_some_array(1,2));
DEALLOCATE pstmt;
do $$begin
-- Provoke error, possibly in worker. If this error happens to occur in
-- the worker, there will be a CONTEXT line which must be hidden.
-- (make the error message long enough to require multiple bufferloads)
perform (stringu1 || repeat('abcd', 5000))::int2 from tenk1 where unique1 = 1;
exception
when others then
raise 'SQLERRM: %', sqlerrm;
end$$;
rollback;
|
