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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
|
LOAD 'pg_hint_plan';
SET pg_hint_plan.enable_hint TO on;
SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO LOG;
-- Queries on ordinary tables with default setting
EXPLAIN (COSTS false) SELECT * FROM s1.t1;
-- Note that parallel is not enforced on a single relation without
-- the GUCs related to parallelism reset.
/*+Parallel(t1 5 hard)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1;
-- Still it works for multiple relations.
/*+Parallel(t11 5 hard)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 as t11, s1.t1 as t12;
SET parallel_setup_cost to 0;
SET parallel_tuple_cost to 0;
SET min_parallel_table_scan_size to 0;
SET min_parallel_index_scan_size to 0;
SET max_parallel_workers_per_gather to DEFAULT;
/*+Parallel(t1 8)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1;
/*+Parallel(t1 8 soft)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1;
/*+Parallel(t1 8 hard)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1;
/*+Parallel(t1 4 hard) */ /* to be gather merge*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 ORDER BY s1.t1.c1 LIMIT 4;
-- Queries on inheritance tables
SET parallel_setup_cost to 0;
SET parallel_tuple_cost to 0;
SET min_parallel_table_scan_size to 0;
SET min_parallel_index_scan_size to 0;
SET enable_parallel_append to false;
/*+Parallel(p1 8)*/
EXPLAIN (COSTS false) SELECT * FROM p1;
SET enable_parallel_append to true;
/*+Parallel(p1 8)*/
EXPLAIN (COSTS false) SELECT * FROM p1;
SET parallel_setup_cost to DEFAULT;
SET parallel_tuple_cost to DEFAULT;
SET min_parallel_table_scan_size to DEFAULT;
SET min_parallel_index_scan_size to DEFAULT;
SET enable_parallel_append to false;
/*+Parallel(p1 8 hard)*/
EXPLAIN (COSTS false) SELECT * FROM p1;
SET enable_parallel_append to true;
/*+Parallel(p1 8 hard)*/
EXPLAIN (COSTS false) SELECT * FROM p1;
-- hinting on children doesn't work (changed as of pg_hint_plan 10)
SET enable_parallel_append to false;
/*+Parallel(p1_c1 8 hard)*/
EXPLAIN (COSTS false) SELECT * FROM p1;
SET enable_parallel_append to true;
/*+Parallel(p1_c1 8 hard)*/
EXPLAIN (COSTS false) SELECT * FROM p1;
-- Joins
EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
/*+Parallel(p1_c1_c1 8 hard)*/
EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
SET parallel_setup_cost to 0;
SET parallel_tuple_cost to 0;
SET min_parallel_table_scan_size to 0;
SET min_parallel_index_scan_size to 0;
/*+Parallel(p1_c1_c1 8 soft) Parallel(p2_c1_c1 0)*/
EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
/*+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 0)*/
EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
/*+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 8 hard)*/
EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
-- Joins on inheritance tables
SET parallel_setup_cost to 0;
SET parallel_tuple_cost to 0;
SET min_parallel_table_scan_size to 0;
SET min_parallel_index_scan_size to 0;
SET enable_parallel_append to false;
/*+Parallel(p1 8)*/
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
SET enable_parallel_append to true;
/*+Parallel(p1 8)*/
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
SET enable_parallel_append to false;
/*+Parallel(p1 8)Parallel(p2 0)*/
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
SET enable_parallel_append to true;
/*+Parallel(p1 8)Parallel(p2 0)*/
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
SET parallel_setup_cost to DEFAULT;
SET parallel_tuple_cost to DEFAULT;
SET min_parallel_table_scan_size to DEFAULT;
SET min_parallel_index_scan_size to DEFAULT;
/*+Parallel(p2 8 soft)*/
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
/*+Parallel(p2 8 hard)*/
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
-- Number of workers results to the largest number
SET enable_parallel_append to false;
/*+Parallel(p2 8 hard) Parallel(p1 5 hard) */
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
SET enable_parallel_append to true;
/*+Parallel(p2 8 hard) Parallel(p1 5 hard) */
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
-- Mixture with scan hints
-- p1 can be parallel
SET enable_parallel_append to false;
/*+Parallel(p1 8 hard) IndexScan(p2) */
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
SET enable_parallel_append to true;
/*+Parallel(p1 8 hard) IndexScan(p2) */
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
-- Parallel sequential scan
SET enable_parallel_append to false;
/*+Parallel(p1 8 hard) SeqScan(p1) */
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
SET enable_parallel_append to true;
/*+Parallel(p1 8 hard) SeqScan(p1) */
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
-- Parallel index scan
SET enable_parallel_append to false;
/*+Parallel(p1 8 hard) IndexScan(p1) */
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
SET enable_parallel_append to true;
/*+Parallel(p1 8 hard) IndexScan(p1) */
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
-- This hint doesn't turn on parallel, so the Parallel hint is ignored
set max_parallel_workers_per_gather TO 0;
/*+Parallel(p1 0 hard) IndexScan(p1) */
EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
-- Parallel on UNION
EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
-- parallel hinting on any relation enables parallel
SET parallel_setup_cost to 0;
SET parallel_tuple_cost to 0;
SET min_parallel_table_scan_size to 0;
SET min_parallel_index_scan_size to 0;
SET max_parallel_workers_per_gather to 0;
/*+Parallel(p1 8) */
EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
-- set hint has the same effect
/*+Set(max_parallel_workers_per_gather 1)*/
EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
-- applies largest number of workers on merged parallel paths
SET parallel_setup_cost to DEFAULT;
SET parallel_tuple_cost to DEFAULT;
SET min_parallel_table_scan_size to DEFAULT;
SET min_parallel_index_scan_size to DEFAULT;
SET max_parallel_workers_per_gather to 8;
/*+Parallel(p1 5 hard)Parallel(p2 6 hard) */
EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
-- On empty tables, parallel hints can only be enforced for index scans
-- and not sequential scans. Adding a single row allows a parallel
-- hint to be enforced on a sequential scan. It is a bit weird that
-- having no rows controls how parallel workers are triggered, but
-- at the same time we have nothing to query, and this is an old
-- historical (and accidental) behavior.
/*+Parallel(t5 4 hard) Parallel(t6 2 hard)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t5 NATURAL JOIN s1.t6;
/*+Parallel(t5 4 hard) Parallel(t6 2 hard) NoSeqScan(t5) NoSeqScan(t6) */
EXPLAIN (COSTS false) SELECT * FROM s1.t5 NATURAL JOIN s1.t6;
INSERT INTO s1.t5 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 1) i) t;
INSERT INTO s1.t6 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 1) i) t;
ANALYZE s1.t5;
ANALYZE s1.t6;
/*+Parallel(t5 4 hard) Parallel(t6 2 hard)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t5 NATURAL JOIN s1.t6;
-- Negative hints
SET enable_indexscan to DEFAULT;
SET parallel_setup_cost to 0;
SET parallel_tuple_cost to 0;
SET min_parallel_table_scan_size to 0;
SET min_parallel_index_scan_size to 0;
SET max_parallel_workers_per_gather to 5;
EXPLAIN (COSTS false) SELECT * FROM p1;
SET enable_parallel_append to false;
/*+Parallel(p1 0 hard)*/
EXPLAIN (COSTS false) SELECT * FROM p1;
SET enable_parallel_append to true;
/*+Parallel(p1 0 hard)*/
EXPLAIN (COSTS false) SELECT * FROM p1;
-- Errors
/*+Parallel(p1 100x hard)Parallel(p1 -1000 hard)Parallel(p1 1000000 hard)
Parallel(p1 8 hoge)Parallel(p1)Parallel(p1 100 soft x)*/
EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
-- Hints on unhintable relations are just ignored
SELECT explain_filter('
/*+Parallel(p1 5 hard) Parallel(s1 3 hard) IndexScan(ft1) SeqScan(cte1)
IndexScan(t) IndexScan(*VALUES*) */
EXPLAIN (COSTS false) SELECT id FROM p1_c1_c1 as s1 TABLESAMPLE SYSTEM(10)
UNION ALL
SELECT id FROM ft1
UNION ALL
(WITH cte1 AS (SELECT id FROM p1 WHERE id % 2 = 0) SELECT id FROM cte1)
UNION ALL
SELECT x FROM (VALUES (1), (2), (3)) t(x);
');
|
