2 ALTER SYSTEM SET session_preload_libraries TO 'pg_hint_plan';
3 SET pg_hint_plan.enable_hint TO on;
4 SET pg_hint_plan.debug_print TO on;
5 SET client_min_messages TO LOG;
6 SET search_path TO public;
7 SET max_parallel_workers_per_gather TO 0;
8 SET enable_indexscan to false;
9 SET enable_bitmapscan to false;
10 SET parallel_setup_cost to 0;
11 SET parallel_tuple_cost to 0;
12 SET min_parallel_relation_size to 0;
13 SET max_parallel_workers_per_gather to 0;
14 CREATE TABLE s1.tl (a int);
15 INSERT INTO s1.tl (SELECT a FROM generate_series(0, 100000) a);
16 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
23 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
32 -------------------------------
35 -> Parallel Seq Scan on t1
38 /*+Parallel(t1 10 soft)*/
39 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
48 -------------------------------
51 -> Parallel Seq Scan on t1
54 /*+Parallel(t1 10 hard)*/
55 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
64 -------------------------------
67 -> Parallel Seq Scan on t1
71 /*+Parallel(p1 10 soft)*/
72 EXPLAIN (COSTS false) SELECT * FROM p1;
81 -------------------------------------------
85 -> Parallel Seq Scan on p1
86 -> Parallel Seq Scan on p1_c1
87 -> Parallel Seq Scan on p1_c2
88 -> Parallel Seq Scan on p1_c3
89 -> Parallel Seq Scan on p1_c4
90 -> Parallel Seq Scan on p1_c1_c1
91 -> Parallel Seq Scan on p1_c1_c2
92 -> Parallel Seq Scan on p1_c3_c1
93 -> Parallel Seq Scan on p1_c3_c2
96 /*+Parallel(p1 10 hard)*/
97 EXPLAIN (COSTS false) SELECT * FROM p1;
106 -------------------------------------------
110 -> Parallel Seq Scan on p1
111 -> Parallel Seq Scan on p1_c1
112 -> Parallel Seq Scan on p1_c2
113 -> Parallel Seq Scan on p1_c3
114 -> Parallel Seq Scan on p1_c4
115 -> Parallel Seq Scan on p1_c1_c1
116 -> Parallel Seq Scan on p1_c1_c2
117 -> Parallel Seq Scan on p1_c3_c1
118 -> Parallel Seq Scan on p1_c3_c2
122 EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
124 ----------------------------------------
126 Hash Cond: (p1_c1.id = p2_c1.id)
129 -> Seq Scan on p1_c1_c1
130 -> Seq Scan on p1_c1_c2
134 -> Seq Scan on p2_c1_c1
135 -> Seq Scan on p2_c1_c2
138 /*+Parallel(p1_c1 10 hard)*/
139 EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
142 Parallel(p1_c1 10 hard)
148 -------------------------------------------------
152 Hash Cond: (p1_c1.id = p2_c1.id)
154 -> Parallel Seq Scan on p1_c1
155 -> Parallel Seq Scan on p1_c1_c1
156 -> Parallel Seq Scan on p1_c1_c2
160 -> Seq Scan on p2_c1_c1
161 -> Seq Scan on p2_c1_c2
164 /*+Parallel(p2_c1 10 hard)*/
165 EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
168 Parallel(p2_c1 10 hard)
174 -------------------------------------------------
178 Hash Cond: (p2_c1.id = p1_c1.id)
180 -> Parallel Seq Scan on p2_c1
181 -> Parallel Seq Scan on p2_c1_c1
182 -> Parallel Seq Scan on p2_c1_c2
186 -> Seq Scan on p1_c1_c1
187 -> Seq Scan on p1_c1_c2
190 /*+Parallel(p1_c1 10 hard) Parallel(p2_c1 10 hard)*/
191 EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
194 Parallel(p1_c1 10 hard)
195 Parallel(p2_c1 10 hard)
201 -------------------------------------------------------
203 Hash Cond: (p1_c1.id = p2_c1.id)
207 -> Parallel Seq Scan on p1_c1
208 -> Parallel Seq Scan on p1_c1_c1
209 -> Parallel Seq Scan on p1_c1_c2
214 -> Parallel Seq Scan on p2_c1
215 -> Parallel Seq Scan on p2_c1_c1
216 -> Parallel Seq Scan on p2_c1_c2
219 -- Joins on inheritance tables
221 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
230 -------------------------------------------------
234 Hash Cond: (p1.id = p2.id)
236 -> Parallel Seq Scan on p1
237 -> Parallel Seq Scan on p1_c1
238 -> Parallel Seq Scan on p1_c2
239 -> Parallel Seq Scan on p1_c3
240 -> Parallel Seq Scan on p1_c4
241 -> Parallel Seq Scan on p1_c1_c1
242 -> Parallel Seq Scan on p1_c1_c2
243 -> Parallel Seq Scan on p1_c3_c1
244 -> Parallel Seq Scan on p1_c3_c2
252 -> Seq Scan on p2_c1_c1
253 -> Seq Scan on p2_c1_c2
254 -> Seq Scan on p2_c3_c1
255 -> Seq Scan on p2_c3_c2
258 /*+Parallel(p2 10 hard)*/
259 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
268 -------------------------------------------------
272 Hash Cond: (p2.id = p1.id)
274 -> Parallel Seq Scan on p2
275 -> Parallel Seq Scan on p2_c1
276 -> Parallel Seq Scan on p2_c2
277 -> Parallel Seq Scan on p2_c3
278 -> Parallel Seq Scan on p2_c4
279 -> Parallel Seq Scan on p2_c1_c1
280 -> Parallel Seq Scan on p2_c1_c2
281 -> Parallel Seq Scan on p2_c3_c1
282 -> Parallel Seq Scan on p2_c3_c2
290 -> Seq Scan on p1_c1_c1
291 -> Seq Scan on p1_c1_c2
292 -> Seq Scan on p1_c3_c1
293 -> Seq Scan on p1_c3_c2
296 /*+Parallel(p2 10 hard) Parallel(p1 5 hard) */
297 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
307 -------------------------------------------------------
309 Hash Cond: (p1.id = p2.id)
313 -> Parallel Seq Scan on p1
314 -> Parallel Seq Scan on p1_c1
315 -> Parallel Seq Scan on p1_c2
316 -> Parallel Seq Scan on p1_c3
317 -> Parallel Seq Scan on p1_c4
318 -> Parallel Seq Scan on p1_c1_c1
319 -> Parallel Seq Scan on p1_c1_c2
320 -> Parallel Seq Scan on p1_c3_c1
321 -> Parallel Seq Scan on p1_c3_c2
326 -> Parallel Seq Scan on p2
327 -> Parallel Seq Scan on p2_c1
328 -> Parallel Seq Scan on p2_c2
329 -> Parallel Seq Scan on p2_c3
330 -> Parallel Seq Scan on p2_c4
331 -> Parallel Seq Scan on p2_c1_c1
332 -> Parallel Seq Scan on p2_c1_c2
333 -> Parallel Seq Scan on p2_c3_c1
334 -> Parallel Seq Scan on p2_c3_c2
338 SET max_parallel_workers_per_gather to 5;
339 EXPLAIN (COSTS false) SELECT * FROM p1;
341 -------------------------------------------
345 -> Parallel Seq Scan on p1
346 -> Parallel Seq Scan on p1_c1
347 -> Parallel Seq Scan on p1_c2
348 -> Parallel Seq Scan on p1_c3
349 -> Parallel Seq Scan on p1_c4
350 -> Parallel Seq Scan on p1_c1_c1
351 -> Parallel Seq Scan on p1_c1_c2
352 -> Parallel Seq Scan on p1_c3_c1
353 -> Parallel Seq Scan on p1_c3_c2
356 /*+Parallel(p1 0 hard)*/
357 EXPLAIN (COSTS false) SELECT * FROM p1;
366 ----------------------------
373 -> Seq Scan on p1_c1_c1
374 -> Seq Scan on p1_c1_c2
375 -> Seq Scan on p1_c3_c1
376 -> Seq Scan on p1_c3_c2
379 ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
380 SELECT pg_reload_conf();