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 CREATE TABLE s1.tl (a int);
7 INSERT INTO s1.tl (SELECT a FROM generate_series(0, 100000) a);
8 -- Queries on ordinary tables with default setting
9 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
15 SET parallel_setup_cost to 0;
16 SET parallel_tuple_cost to 0;
17 SET min_parallel_table_scan_size to 0;
18 SET min_parallel_index_scan_size to 0;
19 SET max_parallel_workers_per_gather to DEFAULT;
21 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
30 -------------------------------
33 -> Parallel Seq Scan on t1
36 /*+Parallel(t1 8 soft)*/
37 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
46 -------------------------------
49 -> Parallel Seq Scan on t1
52 /*+Parallel(t1 8 hard)*/
53 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
62 -------------------------------
65 -> Parallel Seq Scan on t1
68 /*+Parallel(t1 4 hard) */ /* to be gather merge*/
69 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ORDER BY s1.t1.c1 LIMIT 4;
78 ---------------------------------------------------
82 -> Parallel Index Scan using t1_i1 on t1
85 -- Queries on inheritance tables
86 SET parallel_setup_cost to 0;
87 SET parallel_tuple_cost to 0;
88 SET min_parallel_table_scan_size to 0;
89 SET min_parallel_index_scan_size to 0;
90 SET enable_parallel_append to false;
92 EXPLAIN (COSTS false) SELECT * FROM p1;
101 ------------------------------------------------
105 -> Parallel Seq Scan on p1 p1_1
106 -> Parallel Seq Scan on p1_c1 p1_2
107 -> Parallel Seq Scan on p1_c2 p1_3
108 -> Parallel Seq Scan on p1_c3 p1_4
109 -> Parallel Seq Scan on p1_c4 p1_5
110 -> Parallel Seq Scan on p1_c1_c1 p1_6
111 -> Parallel Seq Scan on p1_c1_c2 p1_7
112 -> Parallel Seq Scan on p1_c3_c1 p1_8
113 -> Parallel Seq Scan on p1_c3_c2 p1_9
116 SET enable_parallel_append to true;
118 EXPLAIN (COSTS false) SELECT * FROM p1;
127 ------------------------------------------------
131 -> Seq Scan on p1 p1_1
132 -> Seq Scan on p1_c1 p1_2
133 -> Seq Scan on p1_c3 p1_4
134 -> Parallel Seq Scan on p1_c2 p1_3
135 -> Parallel Seq Scan on p1_c4 p1_5
136 -> Parallel Seq Scan on p1_c1_c1 p1_6
137 -> Parallel Seq Scan on p1_c1_c2 p1_7
138 -> Parallel Seq Scan on p1_c3_c1 p1_8
139 -> Parallel Seq Scan on p1_c3_c2 p1_9
142 SET parallel_setup_cost to DEFAULT;
143 SET parallel_tuple_cost to DEFAULT;
144 SET min_parallel_table_scan_size to DEFAULT;
145 SET min_parallel_index_scan_size to DEFAULT;
146 SET enable_parallel_append to false;
147 /*+Parallel(p1 8 hard)*/
148 EXPLAIN (COSTS false) SELECT * FROM p1;
157 ------------------------------------------------
161 -> Parallel Seq Scan on p1 p1_1
162 -> Parallel Seq Scan on p1_c1 p1_2
163 -> Parallel Seq Scan on p1_c2 p1_3
164 -> Parallel Seq Scan on p1_c3 p1_4
165 -> Parallel Seq Scan on p1_c4 p1_5
166 -> Parallel Seq Scan on p1_c1_c1 p1_6
167 -> Parallel Seq Scan on p1_c1_c2 p1_7
168 -> Parallel Seq Scan on p1_c3_c1 p1_8
169 -> Parallel Seq Scan on p1_c3_c2 p1_9
172 SET enable_parallel_append to true;
173 /*+Parallel(p1 8 hard)*/
174 EXPLAIN (COSTS false) SELECT * FROM p1;
183 ------------------------------------------------
187 -> Seq Scan on p1 p1_1
188 -> Seq Scan on p1_c1 p1_2
189 -> Seq Scan on p1_c3 p1_4
190 -> Parallel Seq Scan on p1_c2 p1_3
191 -> Parallel Seq Scan on p1_c4 p1_5
192 -> Parallel Seq Scan on p1_c1_c1 p1_6
193 -> Parallel Seq Scan on p1_c1_c2 p1_7
194 -> Parallel Seq Scan on p1_c3_c1 p1_8
195 -> Parallel Seq Scan on p1_c3_c2 p1_9
198 -- hinting on children doesn't work (changed as of pg_hint_plan 10)
199 SET enable_parallel_append to false;
200 /*+Parallel(p1_c1 8 hard)*/
201 EXPLAIN (COSTS false) SELECT * FROM p1;
204 Parallel(p1_c1 8 hard)
210 ---------------------------------
212 -> Seq Scan on p1 p1_1
213 -> Seq Scan on p1_c1 p1_2
214 -> Seq Scan on p1_c2 p1_3
215 -> Seq Scan on p1_c3 p1_4
216 -> Seq Scan on p1_c4 p1_5
217 -> Seq Scan on p1_c1_c1 p1_6
218 -> Seq Scan on p1_c1_c2 p1_7
219 -> Seq Scan on p1_c3_c1 p1_8
220 -> Seq Scan on p1_c3_c2 p1_9
223 SET enable_parallel_append to true;
224 /*+Parallel(p1_c1 8 hard)*/
225 EXPLAIN (COSTS false) SELECT * FROM p1;
228 Parallel(p1_c1 8 hard)
234 ---------------------------------
236 -> Seq Scan on p1 p1_1
237 -> Seq Scan on p1_c1 p1_2
238 -> Seq Scan on p1_c2 p1_3
239 -> Seq Scan on p1_c3 p1_4
240 -> Seq Scan on p1_c4 p1_5
241 -> Seq Scan on p1_c1_c1 p1_6
242 -> Seq Scan on p1_c1_c2 p1_7
243 -> Seq Scan on p1_c3_c1 p1_8
244 -> Seq Scan on p1_c3_c2 p1_9
248 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
250 ------------------------------------------
252 Hash Cond: (p1_c1_c1.id = p2_c1_c1.id)
253 -> Seq Scan on p1_c1_c1
255 -> Seq Scan on p2_c1_c1
258 /*+Parallel(p1_c1_c1 8 hard)*/
259 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
262 Parallel(p1_c1_c1 8 hard)
268 -------------------------------------------------
270 Hash Cond: (p2_c1_c1.id = p1_c1_c1.id)
271 -> Seq Scan on p2_c1_c1
275 -> Parallel Seq Scan on p1_c1_c1
278 SET parallel_setup_cost to 0;
279 SET parallel_tuple_cost to 0;
280 SET min_parallel_table_scan_size to 0;
281 SET min_parallel_index_scan_size to 0;
282 /*+Parallel(p1_c1_c1 8 soft) Parallel(p2_c1_c1 0)*/
283 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
286 Parallel(p1_c1_c1 8 soft)
287 Parallel(p2_c1_c1 0 soft)
293 ------------------------------------------------
297 Hash Cond: (p1_c1_c1.id = p2_c1_c1.id)
298 -> Parallel Seq Scan on p1_c1_c1
300 -> Seq Scan on p2_c1_c1
303 /*+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 0)*/
304 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
307 Parallel(p1_c1_c1 8 hard)
308 Parallel(p2_c1_c1 0 soft)
314 ------------------------------------------------
318 Hash Cond: (p1_c1_c1.id = p2_c1_c1.id)
319 -> Parallel Seq Scan on p1_c1_c1
321 -> Seq Scan on p2_c1_c1
324 /*+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 8 hard)*/
325 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
328 Parallel(p1_c1_c1 8 hard)
329 Parallel(p2_c1_c1 8 hard)
335 -------------------------------------------------
338 -> Parallel Hash Join
339 Hash Cond: (p1_c1_c1.id = p2_c1_c1.id)
340 -> Parallel Seq Scan on p1_c1_c1
342 -> Parallel Seq Scan on p2_c1_c1
345 -- Joins on inheritance tables
346 SET parallel_setup_cost to 0;
347 SET parallel_tuple_cost to 0;
348 SET min_parallel_table_scan_size to 0;
349 SET min_parallel_index_scan_size to 0;
350 SET enable_parallel_append to false;
352 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
361 ------------------------------------------------------------
364 -> Parallel Hash Join
365 Hash Cond: (p1.id = p2.id)
367 -> Parallel Seq Scan on p1 p1_1
368 -> Parallel Seq Scan on p1_c1 p1_2
369 -> Parallel Seq Scan on p1_c2 p1_3
370 -> Parallel Seq Scan on p1_c3 p1_4
371 -> Parallel Seq Scan on p1_c4 p1_5
372 -> Parallel Seq Scan on p1_c1_c1 p1_6
373 -> Parallel Seq Scan on p1_c1_c2 p1_7
374 -> Parallel Seq Scan on p1_c3_c1 p1_8
375 -> Parallel Seq Scan on p1_c3_c2 p1_9
378 -> Parallel Seq Scan on p2 p2_1
379 -> Parallel Seq Scan on p2_c1 p2_2
380 -> Parallel Seq Scan on p2_c2 p2_3
381 -> Parallel Seq Scan on p2_c3 p2_4
382 -> Parallel Seq Scan on p2_c4 p2_5
383 -> Parallel Seq Scan on p2_c1_c1 p2_6
384 -> Parallel Seq Scan on p2_c1_c2 p2_7
385 -> Parallel Seq Scan on p2_c3_c1 p2_8
386 -> Parallel Seq Scan on p2_c3_c2 p2_9
389 SET enable_parallel_append to true;
391 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
400 ------------------------------------------------------------
403 -> Parallel Hash Join
404 Hash Cond: (p1.id = p2.id)
406 -> Seq Scan on p1 p1_1
407 -> Seq Scan on p1_c1 p1_2
408 -> Seq Scan on p1_c3 p1_4
409 -> Parallel Seq Scan on p1_c2 p1_3
410 -> Parallel Seq Scan on p1_c4 p1_5
411 -> Parallel Seq Scan on p1_c1_c1 p1_6
412 -> Parallel Seq Scan on p1_c1_c2 p1_7
413 -> Parallel Seq Scan on p1_c3_c1 p1_8
414 -> Parallel Seq Scan on p1_c3_c2 p1_9
417 -> Seq Scan on p2 p2_1
418 -> Seq Scan on p2_c1 p2_2
419 -> Seq Scan on p2_c3 p2_4
420 -> Parallel Seq Scan on p2_c2 p2_3
421 -> Parallel Seq Scan on p2_c4 p2_5
422 -> Parallel Seq Scan on p2_c1_c1 p2_6
423 -> Parallel Seq Scan on p2_c1_c2 p2_7
424 -> Parallel Seq Scan on p2_c3_c1 p2_8
425 -> Parallel Seq Scan on p2_c3_c2 p2_9
428 SET enable_parallel_append to false;
429 /*+Parallel(p1 8)Parallel(p2 0)*/
430 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
440 ------------------------------------------------------
444 Hash Cond: (p1.id = p2.id)
446 -> Parallel Seq Scan on p1 p1_1
447 -> Parallel Seq Scan on p1_c1 p1_2
448 -> Parallel Seq Scan on p1_c2 p1_3
449 -> Parallel Seq Scan on p1_c3 p1_4
450 -> Parallel Seq Scan on p1_c4 p1_5
451 -> Parallel Seq Scan on p1_c1_c1 p1_6
452 -> Parallel Seq Scan on p1_c1_c2 p1_7
453 -> Parallel Seq Scan on p1_c3_c1 p1_8
454 -> Parallel Seq Scan on p1_c3_c2 p1_9
457 -> Seq Scan on p2 p2_1
458 -> Seq Scan on p2_c1 p2_2
459 -> Seq Scan on p2_c2 p2_3
460 -> Seq Scan on p2_c3 p2_4
461 -> Seq Scan on p2_c4 p2_5
462 -> Seq Scan on p2_c1_c1 p2_6
463 -> Seq Scan on p2_c1_c2 p2_7
464 -> Seq Scan on p2_c3_c1 p2_8
465 -> Seq Scan on p2_c3_c2 p2_9
468 SET enable_parallel_append to true;
469 /*+Parallel(p1 8)Parallel(p2 0)*/
470 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
480 ------------------------------------------------------
483 -> Parallel Hash Join
484 Hash Cond: (p1.id = p2.id)
486 -> Seq Scan on p1 p1_1
487 -> Seq Scan on p1_c1 p1_2
488 -> Seq Scan on p1_c3 p1_4
489 -> Parallel Seq Scan on p1_c2 p1_3
490 -> Parallel Seq Scan on p1_c4 p1_5
491 -> Parallel Seq Scan on p1_c1_c1 p1_6
492 -> Parallel Seq Scan on p1_c1_c2 p1_7
493 -> Parallel Seq Scan on p1_c3_c1 p1_8
494 -> Parallel Seq Scan on p1_c3_c2 p1_9
497 -> Seq Scan on p2_c2 p2_3
498 -> Seq Scan on p2_c4 p2_5
499 -> Seq Scan on p2_c1_c1 p2_6
500 -> Seq Scan on p2_c1_c2 p2_7
501 -> Seq Scan on p2_c3_c1 p2_8
502 -> Seq Scan on p2_c3_c2 p2_9
503 -> Seq Scan on p2 p2_1
504 -> Seq Scan on p2_c1 p2_2
505 -> Seq Scan on p2_c3 p2_4
508 SET parallel_setup_cost to DEFAULT;
509 SET parallel_tuple_cost to DEFAULT;
510 SET min_parallel_table_scan_size to DEFAULT;
511 SET min_parallel_index_scan_size to DEFAULT;
512 /*+Parallel(p2 8 soft)*/
513 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
522 ---------------------------------------------
524 Hash Cond: (p1.id = p2.id)
526 -> Seq Scan on p1 p1_1
527 -> Seq Scan on p1_c1 p1_2
528 -> Seq Scan on p1_c2 p1_3
529 -> Seq Scan on p1_c3 p1_4
530 -> Seq Scan on p1_c4 p1_5
531 -> Seq Scan on p1_c1_c1 p1_6
532 -> Seq Scan on p1_c1_c2 p1_7
533 -> Seq Scan on p1_c3_c1 p1_8
534 -> Seq Scan on p1_c3_c2 p1_9
537 -> Seq Scan on p2 p2_1
538 -> Seq Scan on p2_c1 p2_2
539 -> Seq Scan on p2_c2 p2_3
540 -> Seq Scan on p2_c3 p2_4
541 -> Seq Scan on p2_c4 p2_5
542 -> Seq Scan on p2_c1_c1 p2_6
543 -> Seq Scan on p2_c1_c2 p2_7
544 -> Seq Scan on p2_c3_c1 p2_8
545 -> Seq Scan on p2_c3_c2 p2_9
548 /*+Parallel(p2 8 hard)*/
549 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
558 ------------------------------------------------------------
561 -> Parallel Hash Join
562 Hash Cond: (p2.id = p1.id)
564 -> Seq Scan on p2 p2_1
565 -> Seq Scan on p2_c1 p2_2
566 -> Seq Scan on p2_c3 p2_4
567 -> Parallel Seq Scan on p2_c2 p2_3
568 -> Parallel Seq Scan on p2_c4 p2_5
569 -> Parallel Seq Scan on p2_c1_c1 p2_6
570 -> Parallel Seq Scan on p2_c1_c2 p2_7
571 -> Parallel Seq Scan on p2_c3_c1 p2_8
572 -> Parallel Seq Scan on p2_c3_c2 p2_9
575 -> Seq Scan on p1 p1_1
576 -> Seq Scan on p1_c1 p1_2
577 -> Seq Scan on p1_c3 p1_4
578 -> Parallel Seq Scan on p1_c2 p1_3
579 -> Parallel Seq Scan on p1_c4 p1_5
580 -> Parallel Seq Scan on p1_c1_c1 p1_6
581 -> Parallel Seq Scan on p1_c1_c2 p1_7
582 -> Parallel Seq Scan on p1_c3_c1 p1_8
583 -> Parallel Seq Scan on p1_c3_c2 p1_9
586 -- Number of workers results to the largest number
587 SET enable_parallel_append to false;
588 /*+Parallel(p2 8 hard) Parallel(p1 5 hard) */
589 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
599 ------------------------------------------------------------
602 -> Parallel Hash Join
603 Hash Cond: (p1.id = p2.id)
605 -> Parallel Seq Scan on p1 p1_1
606 -> Parallel Seq Scan on p1_c1 p1_2
607 -> Parallel Seq Scan on p1_c2 p1_3
608 -> Parallel Seq Scan on p1_c3 p1_4
609 -> Parallel Seq Scan on p1_c4 p1_5
610 -> Parallel Seq Scan on p1_c1_c1 p1_6
611 -> Parallel Seq Scan on p1_c1_c2 p1_7
612 -> Parallel Seq Scan on p1_c3_c1 p1_8
613 -> Parallel Seq Scan on p1_c3_c2 p1_9
616 -> Parallel Seq Scan on p2 p2_1
617 -> Parallel Seq Scan on p2_c1 p2_2
618 -> Parallel Seq Scan on p2_c2 p2_3
619 -> Parallel Seq Scan on p2_c3 p2_4
620 -> Parallel Seq Scan on p2_c4 p2_5
621 -> Parallel Seq Scan on p2_c1_c1 p2_6
622 -> Parallel Seq Scan on p2_c1_c2 p2_7
623 -> Parallel Seq Scan on p2_c3_c1 p2_8
624 -> Parallel Seq Scan on p2_c3_c2 p2_9
627 SET enable_parallel_append to true;
628 /*+Parallel(p2 8 hard) Parallel(p1 5 hard) */
629 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
639 ------------------------------------------------------------
642 -> Parallel Hash Join
643 Hash Cond: (p2.id = p1.id)
645 -> Seq Scan on p2 p2_1
646 -> Seq Scan on p2_c1 p2_2
647 -> Seq Scan on p2_c3 p2_4
648 -> Parallel Seq Scan on p2_c2 p2_3
649 -> Parallel Seq Scan on p2_c4 p2_5
650 -> Parallel Seq Scan on p2_c1_c1 p2_6
651 -> Parallel Seq Scan on p2_c1_c2 p2_7
652 -> Parallel Seq Scan on p2_c3_c1 p2_8
653 -> Parallel Seq Scan on p2_c3_c2 p2_9
656 -> Seq Scan on p1 p1_1
657 -> Seq Scan on p1_c1 p1_2
658 -> Seq Scan on p1_c3 p1_4
659 -> Parallel Seq Scan on p1_c2 p1_3
660 -> Parallel Seq Scan on p1_c4 p1_5
661 -> Parallel Seq Scan on p1_c1_c1 p1_6
662 -> Parallel Seq Scan on p1_c1_c2 p1_7
663 -> Parallel Seq Scan on p1_c3_c1 p1_8
664 -> Parallel Seq Scan on p1_c3_c2 p1_9
667 -- Mixture with scan hints
668 -- p1 can be parallel
669 SET enable_parallel_append to false;
670 /*+Parallel(p1 8 hard) IndexScan(p2) */
671 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
681 -------------------------------------------------------------------
683 Hash Cond: (p2.id = p1.id)
685 -> Index Scan using p2_id2_val on p2 p2_1
686 -> Index Scan using p2_c1_id2_val on p2_c1 p2_2
687 -> Index Scan using p2_c2_id2_val on p2_c2 p2_3
688 -> Index Scan using p2_c3_id_val_idx on p2_c3 p2_4
689 -> Index Scan using p2_c4_id_val_idx on p2_c4 p2_5
690 -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2_6
691 -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2_7
692 -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 p2_8
693 -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 p2_9
698 -> Parallel Seq Scan on p1 p1_1
699 -> Parallel Seq Scan on p1_c1 p1_2
700 -> Parallel Seq Scan on p1_c2 p1_3
701 -> Parallel Seq Scan on p1_c3 p1_4
702 -> Parallel Seq Scan on p1_c4 p1_5
703 -> Parallel Seq Scan on p1_c1_c1 p1_6
704 -> Parallel Seq Scan on p1_c1_c2 p1_7
705 -> Parallel Seq Scan on p1_c3_c1 p1_8
706 -> Parallel Seq Scan on p1_c3_c2 p1_9
709 SET enable_parallel_append to true;
710 /*+Parallel(p1 8 hard) IndexScan(p2) */
711 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
721 -------------------------------------------------------------------
723 Hash Cond: (p2.id = p1.id)
725 -> Index Scan using p2_id2_val on p2 p2_1
726 -> Index Scan using p2_c1_id2_val on p2_c1 p2_2
727 -> Index Scan using p2_c2_id2_val on p2_c2 p2_3
728 -> Index Scan using p2_c3_id_val_idx on p2_c3 p2_4
729 -> Index Scan using p2_c4_id_val_idx on p2_c4 p2_5
730 -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2_6
731 -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2_7
732 -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 p2_8
733 -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 p2_9
738 -> Seq Scan on p1 p1_1
739 -> Seq Scan on p1_c1 p1_2
740 -> Seq Scan on p1_c3 p1_4
741 -> Parallel Seq Scan on p1_c2 p1_3
742 -> Parallel Seq Scan on p1_c4 p1_5
743 -> Parallel Seq Scan on p1_c1_c1 p1_6
744 -> Parallel Seq Scan on p1_c1_c2 p1_7
745 -> Parallel Seq Scan on p1_c3_c1 p1_8
746 -> Parallel Seq Scan on p1_c3_c2 p1_9
749 -- Parallel sequential scan
750 SET enable_parallel_append to false;
751 /*+Parallel(p1 8 hard) SeqScan(p1) */
752 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
762 ------------------------------------------------------------
765 -> Parallel Hash Join
766 Hash Cond: (p1.id = p2.id)
768 -> Parallel Seq Scan on p1 p1_1
769 -> Parallel Seq Scan on p1_c1 p1_2
770 -> Parallel Seq Scan on p1_c2 p1_3
771 -> Parallel Seq Scan on p1_c3 p1_4
772 -> Parallel Seq Scan on p1_c4 p1_5
773 -> Parallel Seq Scan on p1_c1_c1 p1_6
774 -> Parallel Seq Scan on p1_c1_c2 p1_7
775 -> Parallel Seq Scan on p1_c3_c1 p1_8
776 -> Parallel Seq Scan on p1_c3_c2 p1_9
779 -> Parallel Seq Scan on p2 p2_1
780 -> Parallel Seq Scan on p2_c1 p2_2
781 -> Parallel Seq Scan on p2_c2 p2_3
782 -> Parallel Seq Scan on p2_c3 p2_4
783 -> Parallel Seq Scan on p2_c4 p2_5
784 -> Parallel Seq Scan on p2_c1_c1 p2_6
785 -> Parallel Seq Scan on p2_c1_c2 p2_7
786 -> Parallel Seq Scan on p2_c3_c1 p2_8
787 -> Parallel Seq Scan on p2_c3_c2 p2_9
790 SET enable_parallel_append to true;
791 /*+Parallel(p1 8 hard) SeqScan(p1) */
792 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
802 ------------------------------------------------------------
805 -> Parallel Hash Join
806 Hash Cond: (p1.id = p2.id)
808 -> Seq Scan on p1 p1_1
809 -> Seq Scan on p1_c1 p1_2
810 -> Seq Scan on p1_c3 p1_4
811 -> Parallel Seq Scan on p1_c2 p1_3
812 -> Parallel Seq Scan on p1_c4 p1_5
813 -> Parallel Seq Scan on p1_c1_c1 p1_6
814 -> Parallel Seq Scan on p1_c1_c2 p1_7
815 -> Parallel Seq Scan on p1_c3_c1 p1_8
816 -> Parallel Seq Scan on p1_c3_c2 p1_9
819 -> Seq Scan on p2 p2_1
820 -> Seq Scan on p2_c1 p2_2
821 -> Seq Scan on p2_c3 p2_4
822 -> Parallel Seq Scan on p2_c2 p2_3
823 -> Parallel Seq Scan on p2_c4 p2_5
824 -> Parallel Seq Scan on p2_c1_c1 p2_6
825 -> Parallel Seq Scan on p2_c1_c2 p2_7
826 -> Parallel Seq Scan on p2_c3_c1 p2_8
827 -> Parallel Seq Scan on p2_c3_c2 p2_9
830 -- Parallel index scan
831 SET enable_parallel_append to false;
832 /*+Parallel(p1 8 hard) IndexScan(p1) */
833 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
843 ----------------------------------------------------------------------------
846 -> Parallel Hash Join
847 Hash Cond: (p1.id = p2.id)
849 -> Parallel Index Scan using p1_pkey on p1 p1_1
850 -> Parallel Index Scan using p1_c1_pkey on p1_c1 p1_2
851 -> Parallel Index Scan using p1_c2_pkey on p1_c2 p1_3
852 -> Parallel Index Scan using p1_c3_pkey on p1_c3 p1_4
853 -> Parallel Index Scan using p1_c4_pkey on p1_c4 p1_5
854 -> Parallel Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1_6
855 -> Parallel Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1_7
856 -> Parallel Index Scan using p1_c3_c1_pkey on p1_c3_c1 p1_8
857 -> Parallel Index Scan using p1_c3_c2_pkey on p1_c3_c2 p1_9
860 -> Parallel Seq Scan on p2 p2_1
861 -> Parallel Seq Scan on p2_c1 p2_2
862 -> Parallel Seq Scan on p2_c2 p2_3
863 -> Parallel Seq Scan on p2_c3 p2_4
864 -> Parallel Seq Scan on p2_c4 p2_5
865 -> Parallel Seq Scan on p2_c1_c1 p2_6
866 -> Parallel Seq Scan on p2_c1_c2 p2_7
867 -> Parallel Seq Scan on p2_c3_c1 p2_8
868 -> Parallel Seq Scan on p2_c3_c2 p2_9
871 SET enable_parallel_append to true;
872 /*+Parallel(p1 8 hard) IndexScan(p1) */
873 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
883 ----------------------------------------------------------------------------
886 -> Parallel Hash Join
887 Hash Cond: (p1.id = p2.id)
889 -> Parallel Index Scan using p1_pkey on p1 p1_1
890 -> Parallel Index Scan using p1_c1_pkey on p1_c1 p1_2
891 -> Parallel Index Scan using p1_c2_pkey on p1_c2 p1_3
892 -> Parallel Index Scan using p1_c3_pkey on p1_c3 p1_4
893 -> Parallel Index Scan using p1_c4_pkey on p1_c4 p1_5
894 -> Parallel Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1_6
895 -> Parallel Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1_7
896 -> Parallel Index Scan using p1_c3_c1_pkey on p1_c3_c1 p1_8
897 -> Parallel Index Scan using p1_c3_c2_pkey on p1_c3_c2 p1_9
900 -> Seq Scan on p2 p2_1
901 -> Seq Scan on p2_c1 p2_2
902 -> Seq Scan on p2_c3 p2_4
903 -> Parallel Seq Scan on p2_c2 p2_3
904 -> Parallel Seq Scan on p2_c4 p2_5
905 -> Parallel Seq Scan on p2_c1_c1 p2_6
906 -> Parallel Seq Scan on p2_c1_c2 p2_7
907 -> Parallel Seq Scan on p2_c3_c1 p2_8
908 -> Parallel Seq Scan on p2_c3_c2 p2_9
911 -- This hint doesn't turn on parallel, so the Parallel hint is ignored
912 set max_parallel_workers_per_gather TO 0;
913 /*+Parallel(p1 0 hard) IndexScan(p1) */
914 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
924 -------------------------------------------------------------
926 Hash Cond: (p1.id = p2.id)
928 -> Index Scan using p1_pkey on p1 p1_1
929 -> Index Scan using p1_c1_pkey on p1_c1 p1_2
930 -> Index Scan using p1_c2_pkey on p1_c2 p1_3
931 -> Index Scan using p1_c3_pkey on p1_c3 p1_4
932 -> Index Scan using p1_c4_pkey on p1_c4 p1_5
933 -> Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1_6
934 -> Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1_7
935 -> Index Scan using p1_c3_c1_pkey on p1_c3_c1 p1_8
936 -> Index Scan using p1_c3_c2_pkey on p1_c3_c2 p1_9
939 -> Seq Scan on p2 p2_1
940 -> Seq Scan on p2_c1 p2_2
941 -> Seq Scan on p2_c2 p2_3
942 -> Seq Scan on p2_c3 p2_4
943 -> Seq Scan on p2_c4 p2_5
944 -> Seq Scan on p2_c1_c1 p2_6
945 -> Seq Scan on p2_c1_c2 p2_7
946 -> Seq Scan on p2_c3_c1 p2_8
947 -> Seq Scan on p2_c3_c2 p2_9
951 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
953 ---------------------------------
956 -> Seq Scan on p1_c1 p1_1
957 -> Seq Scan on p1_c2 p1_2
958 -> Seq Scan on p1_c3 p1_3
959 -> Seq Scan on p1_c4 p1_4
960 -> Seq Scan on p1_c1_c1 p1_5
961 -> Seq Scan on p1_c1_c2 p1_6
962 -> Seq Scan on p1_c3_c1 p1_7
963 -> Seq Scan on p1_c3_c2 p1_8
965 -> Seq Scan on p2_c1 p2_1
966 -> Seq Scan on p2_c2 p2_2
967 -> Seq Scan on p2_c3 p2_3
968 -> Seq Scan on p2_c4 p2_4
969 -> Seq Scan on p2_c1_c1 p2_5
970 -> Seq Scan on p2_c1_c2 p2_6
971 -> Seq Scan on p2_c3_c1 p2_7
972 -> Seq Scan on p2_c3_c2 p2_8
975 -- parallel hinting on any relation enables parallel
976 SET parallel_setup_cost to 0;
977 SET parallel_tuple_cost to 0;
978 SET min_parallel_table_scan_size to 0;
979 SET min_parallel_index_scan_size to 0;
980 SET max_parallel_workers_per_gather to 0;
982 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
991 ------------------------------------------------
995 -> Parallel Seq Scan on p2_c2 p2_2
996 -> Parallel Seq Scan on p2_c4 p2_4
997 -> Parallel Seq Scan on p2_c1_c1 p2_5
998 -> Parallel Seq Scan on p2_c1_c2 p2_6
999 -> Parallel Seq Scan on p2_c3_c1 p2_7
1000 -> Parallel Seq Scan on p2_c3_c2 p2_8
1001 -> Parallel Seq Scan on p1
1002 -> Parallel Seq Scan on p1_c1 p1_1
1003 -> Parallel Seq Scan on p1_c2 p1_2
1004 -> Parallel Seq Scan on p1_c3 p1_3
1005 -> Parallel Seq Scan on p1_c4 p1_4
1006 -> Parallel Seq Scan on p1_c1_c1 p1_5
1007 -> Parallel Seq Scan on p1_c1_c2 p1_6
1008 -> Parallel Seq Scan on p1_c3_c1 p1_7
1009 -> Parallel Seq Scan on p1_c3_c2 p1_8
1010 -> Parallel Seq Scan on p2
1011 -> Parallel Seq Scan on p2_c1 p2_1
1012 -> Parallel Seq Scan on p2_c3 p2_3
1015 -- set hint has the same effect
1016 /*+Set(max_parallel_workers_per_gather 1)*/
1017 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
1020 Set(max_parallel_workers_per_gather 1)
1026 ------------------------------------------------
1030 -> Parallel Seq Scan on p1_c2 p1_2
1031 -> Parallel Seq Scan on p1_c4 p1_4
1032 -> Parallel Seq Scan on p2_c2 p2_2
1033 -> Parallel Seq Scan on p2_c4 p2_4
1034 -> Parallel Seq Scan on p1_c1_c1 p1_5
1035 -> Parallel Seq Scan on p1_c1_c2 p1_6
1036 -> Parallel Seq Scan on p1_c3_c1 p1_7
1037 -> Parallel Seq Scan on p1_c3_c2 p1_8
1038 -> Parallel Seq Scan on p2_c1_c1 p2_5
1039 -> Parallel Seq Scan on p2_c1_c2 p2_6
1040 -> Parallel Seq Scan on p2_c3_c1 p2_7
1041 -> Parallel Seq Scan on p2_c3_c2 p2_8
1042 -> Parallel Seq Scan on p1
1043 -> Parallel Seq Scan on p1_c1 p1_1
1044 -> Parallel Seq Scan on p1_c3 p1_3
1045 -> Parallel Seq Scan on p2
1046 -> Parallel Seq Scan on p2_c1 p2_1
1047 -> Parallel Seq Scan on p2_c3 p2_3
1050 -- applies largest number of workers on merged parallel paths
1051 SET parallel_setup_cost to DEFAULT;
1052 SET parallel_tuple_cost to DEFAULT;
1053 SET min_parallel_table_scan_size to DEFAULT;
1054 SET min_parallel_index_scan_size to DEFAULT;
1055 SET max_parallel_workers_per_gather to 8;
1056 /*+Parallel(p1 5 hard)Parallel(p2 6 hard) */
1057 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
1067 ------------------------------------------------
1072 -> Seq Scan on p1_c1 p1_1
1073 -> Seq Scan on p1_c3 p1_3
1075 -> Seq Scan on p2_c1 p2_1
1076 -> Seq Scan on p2_c3 p2_3
1077 -> Parallel Seq Scan on p1_c2 p1_2
1078 -> Parallel Seq Scan on p1_c4 p1_4
1079 -> Parallel Seq Scan on p1_c1_c1 p1_5
1080 -> Parallel Seq Scan on p1_c1_c2 p1_6
1081 -> Parallel Seq Scan on p1_c3_c1 p1_7
1082 -> Parallel Seq Scan on p1_c3_c2 p1_8
1083 -> Parallel Seq Scan on p2_c2 p2_2
1084 -> Parallel Seq Scan on p2_c4 p2_4
1085 -> Parallel Seq Scan on p2_c1_c1 p2_5
1086 -> Parallel Seq Scan on p2_c1_c2 p2_6
1087 -> Parallel Seq Scan on p2_c3_c1 p2_7
1088 -> Parallel Seq Scan on p2_c3_c2 p2_8
1092 SET enable_indexscan to DEFAULT;
1093 SET parallel_setup_cost to 0;
1094 SET parallel_tuple_cost to 0;
1095 SET min_parallel_table_scan_size to 0;
1096 SET min_parallel_index_scan_size to 0;
1097 SET max_parallel_workers_per_gather to 5;
1098 EXPLAIN (COSTS false) SELECT * FROM p1;
1100 ------------------------------------------------
1104 -> Parallel Seq Scan on p1_c2 p1_3
1105 -> Parallel Seq Scan on p1_c4 p1_5
1106 -> Parallel Seq Scan on p1_c1_c1 p1_6
1107 -> Parallel Seq Scan on p1_c1_c2 p1_7
1108 -> Parallel Seq Scan on p1_c3_c1 p1_8
1109 -> Parallel Seq Scan on p1_c3_c2 p1_9
1110 -> Parallel Seq Scan on p1 p1_1
1111 -> Parallel Seq Scan on p1_c1 p1_2
1112 -> Parallel Seq Scan on p1_c3 p1_4
1115 SET enable_parallel_append to false;
1116 /*+Parallel(p1 0 hard)*/
1117 EXPLAIN (COSTS false) SELECT * FROM p1;
1126 ---------------------------------
1128 -> Seq Scan on p1 p1_1
1129 -> Seq Scan on p1_c1 p1_2
1130 -> Seq Scan on p1_c2 p1_3
1131 -> Seq Scan on p1_c3 p1_4
1132 -> Seq Scan on p1_c4 p1_5
1133 -> Seq Scan on p1_c1_c1 p1_6
1134 -> Seq Scan on p1_c1_c2 p1_7
1135 -> Seq Scan on p1_c3_c1 p1_8
1136 -> Seq Scan on p1_c3_c2 p1_9
1139 SET enable_parallel_append to true;
1140 /*+Parallel(p1 0 hard)*/
1141 EXPLAIN (COSTS false) SELECT * FROM p1;
1150 ---------------------------------
1152 -> Seq Scan on p1 p1_1
1153 -> Seq Scan on p1_c1 p1_2
1154 -> Seq Scan on p1_c2 p1_3
1155 -> Seq Scan on p1_c3 p1_4
1156 -> Seq Scan on p1_c4 p1_5
1157 -> Seq Scan on p1_c1_c1 p1_6
1158 -> Seq Scan on p1_c1_c2 p1_7
1159 -> Seq Scan on p1_c3_c1 p1_8
1160 -> Seq Scan on p1_c3_c2 p1_9
1164 /*+Parallel(p1 100x hard)Parallel(p1 -1000 hard)Parallel(p1 1000000 hard)
1165 Parallel(p1 8 hoge)Parallel(p1)Parallel(p1 100 soft x)*/
1166 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
1167 INFO: pg_hint_plan: hint syntax error at or near "100x"
1168 DETAIL: number of workers must be a number: Parallel
1169 INFO: pg_hint_plan: hint syntax error at or near "-1000"
1170 DETAIL: number of workers must be positive: Parallel
1171 INFO: pg_hint_plan: hint syntax error at or near "1000000"
1172 DETAIL: number of workers = 1000000 is larger than max_worker_processes(8): Parallel
1173 INFO: pg_hint_plan: hint syntax error at or near "hoge"
1174 DETAIL: enforcement must be soft or hard: Parallel
1175 INFO: pg_hint_plan: hint syntax error at or near ")"
1176 DETAIL: wrong number of arguments (1): Parallel
1177 INFO: pg_hint_plan: hint syntax error at or near ")"
1178 DETAIL: wrong number of arguments (4): Parallel
1184 Parallel(p1 100x hard)
1185 Parallel(p1 -1000 hard)
1186 Parallel(p1 1000000 hard)
1192 ------------------------------------------------
1196 -> Parallel Seq Scan on p1_c2 p1_2
1197 -> Parallel Seq Scan on p1_c4 p1_4
1198 -> Parallel Seq Scan on p2_c2 p2_2
1199 -> Parallel Seq Scan on p2_c4 p2_4
1200 -> Parallel Seq Scan on p1_c1_c1 p1_5
1201 -> Parallel Seq Scan on p1_c1_c2 p1_6
1202 -> Parallel Seq Scan on p1_c3_c1 p1_7
1203 -> Parallel Seq Scan on p1_c3_c2 p1_8
1204 -> Parallel Seq Scan on p2_c1_c1 p2_5
1205 -> Parallel Seq Scan on p2_c1_c2 p2_6
1206 -> Parallel Seq Scan on p2_c3_c1 p2_7
1207 -> Parallel Seq Scan on p2_c3_c2 p2_8
1208 -> Parallel Seq Scan on p1
1209 -> Parallel Seq Scan on p1_c1 p1_1
1210 -> Parallel Seq Scan on p1_c3 p1_3
1211 -> Parallel Seq Scan on p2
1212 -> Parallel Seq Scan on p2_c1 p2_1
1213 -> Parallel Seq Scan on p2_c3 p2_3
1216 -- Hints on unhintable relations are just ignored
1217 /*+Parallel(p1 5 hard) Parallel(s1 3 hard) IndexScan(ft1) SeqScan(cte1)
1218 TidScan(fs1) IndexScan(t) IndexScan(*VALUES*) */
1219 \o results/ut-W.tmpout
1220 EXPLAIN (COSTS false) SELECT id FROM p1_c1_c1 as s1 TABLESAMPLE SYSTEM(10)
1224 (WITH cte1 AS (SELECT id FROM p1 WHERE id % 2 = 0) SELECT id FROM cte1)
1226 SELECT userid FROM pg_stat_statements fs1
1228 SELECT x FROM (VALUES (1), (2), (3)) t(x);
1243 \! sql/maskout2.sh results/ut-W.tmpout
1249 -> Sample Scan on p1_c1_c1 s1
1250 Sampling: system ('10'::real)
1251 -> Foreign Scan on ft1
1252 Foreign File: (snip..)
1256 -> Seq Scan on p1 p1_1
1257 Filter: ((id % 2) = 0)
1258 -> Seq Scan on p1_c1 p1_2
1259 Filter: ((id % 2) = 0)
1260 -> Seq Scan on p1_c3 p1_4
1261 Filter: ((id % 2) = 0)
1262 -> Parallel Seq Scan on p1_c2 p1_3
1263 Filter: ((id % 2) = 0)
1264 -> Parallel Seq Scan on p1_c4 p1_5
1265 Filter: ((id % 2) = 0)
1266 -> Parallel Seq Scan on p1_c1_c1 p1_6
1267 Filter: ((id % 2) = 0)
1268 -> Parallel Seq Scan on p1_c1_c2 p1_7
1269 Filter: ((id % 2) = 0)
1270 -> Parallel Seq Scan on p1_c3_c1 p1_8
1271 Filter: ((id % 2) = 0)
1272 -> Parallel Seq Scan on p1_c3_c2 p1_9
1273 Filter: ((id % 2) = 0)
1274 -> Function Scan on pg_stat_statements
1275 -> Subquery Scan on "*SELECT* 5"
1276 -> Values Scan on "*VALUES*"
1279 ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
1280 SELECT pg_reload_conf();