blob: 2286fafab399f72397440f82d8fff7a85a37ad5e (
plain)
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
|
--
-- 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;
QUERY PLAN
-----------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 1
-> Partial Aggregate
-> Append
-> Parallel Seq Scan on a_star
-> Parallel Seq Scan on b_star
-> Parallel Seq Scan on c_star
-> Parallel Seq Scan on d_star
-> Parallel Seq Scan on e_star
-> Parallel Seq Scan on f_star
(11 rows)
select count(*) from a_star;
count
-------
50
(1 row)
-- 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;
QUERY PLAN
---------------------------------------------------------
Sort
Output: (parallel_restricted(unique1))
Sort Key: (parallel_restricted(tenk1.unique1))
-> Gather
Output: parallel_restricted(unique1)
Workers Planned: 4
-> Parallel Seq Scan on public.tenk1
Output: unique1
Filter: (tenk1.stringu1 = 'GRAAAA'::name)
(9 rows)
-- test parallel plan when group by expression is in target list.
explain (costs off)
select length(stringu1) from tenk1 group by length(stringu1);
QUERY PLAN
---------------------------------------------------
Finalize HashAggregate
Group Key: (length((stringu1)::text))
-> Gather
Workers Planned: 4
-> Partial HashAggregate
Group Key: length((stringu1)::text)
-> Parallel Seq Scan on tenk1
(7 rows)
select length(stringu1) from tenk1 group by length(stringu1);
length
--------
6
(1 row)
explain (costs off)
select stringu1, count(*) from tenk1 group by stringu1 order by stringu1;
QUERY PLAN
----------------------------------------------------
Sort
Sort Key: stringu1
-> Finalize HashAggregate
Group Key: stringu1
-> Gather
Workers Planned: 4
-> Partial HashAggregate
Group Key: stringu1
-> Parallel Seq Scan on tenk1
(9 rows)
-- 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));
QUERY PLAN
----------------------------------------------------
HashAggregate
Group Key: parallel_restricted(unique1)
-> Index Only Scan using tenk1_unique1 on tenk1
(3 rows)
set force_parallel_mode=1;
explain (costs off)
select stringu1::int2 from tenk1 where unique1 = 1;
QUERY PLAN
-----------------------------------------------
Gather
Workers Planned: 1
Single Copy: true
-> Index Scan using tenk1_unique1 on tenk1
Index Cond: (unique1 = 1)
(5 rows)
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.
perform stringu1::int2 from tenk1 where unique1 = 1;
exception
when others then
raise 'SQLERRM: %', sqlerrm;
end$$;
ERROR: SQLERRM: invalid input syntax for integer: "BAAAAA"
CONTEXT: PL/pgSQL function inline_code_block line 7 at RAISE
rollback;
|
