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
106 -> Parallel Seq Scan on p1_c1
107 -> Parallel Seq Scan on p1_c2
108 -> Parallel Seq Scan on p1_c3
109 -> Parallel Seq Scan on p1_c4
110 -> Parallel Seq Scan on p1_c1_c1
111 -> Parallel Seq Scan on p1_c1_c2
112 -> Parallel Seq Scan on p1_c3_c1
113 -> Parallel Seq Scan on p1_c3_c2
116 SET enable_parallel_append to true;
118 EXPLAIN (COSTS false) SELECT * FROM p1;
127 -------------------------------------------
134 -> Parallel Seq Scan on p1_c2
135 -> Parallel Seq Scan on p1_c4
136 -> Parallel Seq Scan on p1_c1_c1
137 -> Parallel Seq Scan on p1_c1_c2
138 -> Parallel Seq Scan on p1_c3_c1
139 -> Parallel Seq Scan on p1_c3_c2
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
162 -> Parallel Seq Scan on p1_c1
163 -> Parallel Seq Scan on p1_c2
164 -> Parallel Seq Scan on p1_c3
165 -> Parallel Seq Scan on p1_c4
166 -> Parallel Seq Scan on p1_c1_c1
167 -> Parallel Seq Scan on p1_c1_c2
168 -> Parallel Seq Scan on p1_c3_c1
169 -> Parallel Seq Scan on p1_c3_c2
172 SET enable_parallel_append to true;
173 /*+Parallel(p1 8 hard)*/
174 EXPLAIN (COSTS false) SELECT * FROM p1;
183 -------------------------------------------
190 -> Parallel Seq Scan on p1_c2
191 -> Parallel Seq Scan on p1_c4
192 -> Parallel Seq Scan on p1_c1_c1
193 -> Parallel Seq Scan on p1_c1_c2
194 -> Parallel Seq Scan on p1_c3_c1
195 -> Parallel Seq Scan on p1_c3_c2
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 ----------------------------
217 -> Seq Scan on p1_c1_c1
218 -> Seq Scan on p1_c1_c2
219 -> Seq Scan on p1_c3_c1
220 -> Seq Scan on p1_c3_c2
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 ----------------------------
241 -> Seq Scan on p1_c1_c1
242 -> Seq Scan on p1_c1_c2
243 -> Seq Scan on p1_c3_c1
244 -> Seq Scan on p1_c3_c2
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
368 -> Parallel Seq Scan on p1_c1
369 -> Parallel Seq Scan on p1_c2
370 -> Parallel Seq Scan on p1_c3
371 -> Parallel Seq Scan on p1_c4
372 -> Parallel Seq Scan on p1_c1_c1
373 -> Parallel Seq Scan on p1_c1_c2
374 -> Parallel Seq Scan on p1_c3_c1
375 -> Parallel Seq Scan on p1_c3_c2
378 -> Parallel Seq Scan on p2
379 -> Parallel Seq Scan on p2_c1
380 -> Parallel Seq Scan on p2_c2
381 -> Parallel Seq Scan on p2_c3
382 -> Parallel Seq Scan on p2_c4
383 -> Parallel Seq Scan on p2_c1_c1
384 -> Parallel Seq Scan on p2_c1_c2
385 -> Parallel Seq Scan on p2_c3_c1
386 -> Parallel Seq Scan on p2_c3_c2
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)
409 -> Parallel Seq Scan on p1_c2
410 -> Parallel Seq Scan on p1_c4
411 -> Parallel Seq Scan on p1_c1_c1
412 -> Parallel Seq Scan on p1_c1_c2
413 -> Parallel Seq Scan on p1_c3_c1
414 -> Parallel Seq Scan on p1_c3_c2
420 -> Parallel Seq Scan on p2_c2
421 -> Parallel Seq Scan on p2_c4
422 -> Parallel Seq Scan on p2_c1_c1
423 -> Parallel Seq Scan on p2_c1_c2
424 -> Parallel Seq Scan on p2_c3_c1
425 -> Parallel Seq Scan on p2_c3_c2
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
447 -> Parallel Seq Scan on p1_c1
448 -> Parallel Seq Scan on p1_c2
449 -> Parallel Seq Scan on p1_c3
450 -> Parallel Seq Scan on p1_c4
451 -> Parallel Seq Scan on p1_c1_c1
452 -> Parallel Seq Scan on p1_c1_c2
453 -> Parallel Seq Scan on p1_c3_c1
454 -> Parallel Seq Scan on p1_c3_c2
462 -> Seq Scan on p2_c1_c1
463 -> Seq Scan on p2_c1_c2
464 -> Seq Scan on p2_c3_c1
465 -> Seq Scan on p2_c3_c2
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_c2.id)
489 -> Parallel Seq Scan on p1_c2
490 -> Parallel Seq Scan on p1_c4
491 -> Parallel Seq Scan on p1_c1_c1
492 -> Parallel Seq Scan on p1_c1_c2
493 -> Parallel Seq Scan on p1_c3_c1
494 -> Parallel Seq Scan on p1_c3_c2
499 -> Seq Scan on p2_c1_c1
500 -> Seq Scan on p2_c1_c2
501 -> Seq Scan on p2_c3_c1
502 -> Seq Scan on p2_c3_c2
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)
531 -> Seq Scan on p1_c1_c1
532 -> Seq Scan on p1_c1_c2
533 -> Seq Scan on p1_c3_c1
534 -> Seq Scan on p1_c3_c2
542 -> Seq Scan on p2_c1_c1
543 -> Seq Scan on p2_c1_c2
544 -> Seq Scan on p2_c3_c1
545 -> Seq Scan on p2_c3_c2
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)
567 -> Parallel Seq Scan on p2_c2
568 -> Parallel Seq Scan on p2_c4
569 -> Parallel Seq Scan on p2_c1_c1
570 -> Parallel Seq Scan on p2_c1_c2
571 -> Parallel Seq Scan on p2_c3_c1
572 -> Parallel Seq Scan on p2_c3_c2
578 -> Parallel Seq Scan on p1_c2
579 -> Parallel Seq Scan on p1_c4
580 -> Parallel Seq Scan on p1_c1_c1
581 -> Parallel Seq Scan on p1_c1_c2
582 -> Parallel Seq Scan on p1_c3_c1
583 -> Parallel Seq Scan on p1_c3_c2
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
606 -> Parallel Seq Scan on p1_c1
607 -> Parallel Seq Scan on p1_c2
608 -> Parallel Seq Scan on p1_c3
609 -> Parallel Seq Scan on p1_c4
610 -> Parallel Seq Scan on p1_c1_c1
611 -> Parallel Seq Scan on p1_c1_c2
612 -> Parallel Seq Scan on p1_c3_c1
613 -> Parallel Seq Scan on p1_c3_c2
616 -> Parallel Seq Scan on p2
617 -> Parallel Seq Scan on p2_c1
618 -> Parallel Seq Scan on p2_c2
619 -> Parallel Seq Scan on p2_c3
620 -> Parallel Seq Scan on p2_c4
621 -> Parallel Seq Scan on p2_c1_c1
622 -> Parallel Seq Scan on p2_c1_c2
623 -> Parallel Seq Scan on p2_c3_c1
624 -> Parallel Seq Scan on p2_c3_c2
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)
648 -> Parallel Seq Scan on p2_c2
649 -> Parallel Seq Scan on p2_c4
650 -> Parallel Seq Scan on p2_c1_c1
651 -> Parallel Seq Scan on p2_c1_c2
652 -> Parallel Seq Scan on p2_c3_c1
653 -> Parallel Seq Scan on p2_c3_c2
659 -> Parallel Seq Scan on p1_c2
660 -> Parallel Seq Scan on p1_c4
661 -> Parallel Seq Scan on p1_c1_c1
662 -> Parallel Seq Scan on p1_c1_c2
663 -> Parallel Seq Scan on p1_c3_c1
664 -> Parallel Seq Scan on p1_c3_c2
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
686 -> Index Scan using p2_c1_id2_val on p2_c1
687 -> Index Scan using p2_c2_id2_val on p2_c2
688 -> Index Scan using p2_c3_id_val_idx on p2_c3
689 -> Index Scan using p2_c4_id_val_idx on p2_c4
690 -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
691 -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
692 -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
693 -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
698 -> Parallel Seq Scan on p1
699 -> Parallel Seq Scan on p1_c1
700 -> Parallel Seq Scan on p1_c2
701 -> Parallel Seq Scan on p1_c3
702 -> Parallel Seq Scan on p1_c4
703 -> Parallel Seq Scan on p1_c1_c1
704 -> Parallel Seq Scan on p1_c1_c2
705 -> Parallel Seq Scan on p1_c3_c1
706 -> Parallel Seq Scan on p1_c3_c2
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
726 -> Index Scan using p2_c1_id2_val on p2_c1
727 -> Index Scan using p2_c2_id2_val on p2_c2
728 -> Index Scan using p2_c3_id_val_idx on p2_c3
729 -> Index Scan using p2_c4_id_val_idx on p2_c4
730 -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
731 -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
732 -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
733 -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
741 -> Parallel Seq Scan on p1_c2
742 -> Parallel Seq Scan on p1_c4
743 -> Parallel Seq Scan on p1_c1_c1
744 -> Parallel Seq Scan on p1_c1_c2
745 -> Parallel Seq Scan on p1_c3_c1
746 -> Parallel Seq Scan on p1_c3_c2
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
769 -> Parallel Seq Scan on p1_c1
770 -> Parallel Seq Scan on p1_c2
771 -> Parallel Seq Scan on p1_c3
772 -> Parallel Seq Scan on p1_c4
773 -> Parallel Seq Scan on p1_c1_c1
774 -> Parallel Seq Scan on p1_c1_c2
775 -> Parallel Seq Scan on p1_c3_c1
776 -> Parallel Seq Scan on p1_c3_c2
779 -> Parallel Seq Scan on p2
780 -> Parallel Seq Scan on p2_c1
781 -> Parallel Seq Scan on p2_c2
782 -> Parallel Seq Scan on p2_c3
783 -> Parallel Seq Scan on p2_c4
784 -> Parallel Seq Scan on p2_c1_c1
785 -> Parallel Seq Scan on p2_c1_c2
786 -> Parallel Seq Scan on p2_c3_c1
787 -> Parallel Seq Scan on p2_c3_c2
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)
811 -> Parallel Seq Scan on p1_c2
812 -> Parallel Seq Scan on p1_c4
813 -> Parallel Seq Scan on p1_c1_c1
814 -> Parallel Seq Scan on p1_c1_c2
815 -> Parallel Seq Scan on p1_c3_c1
816 -> Parallel Seq Scan on p1_c3_c2
822 -> Parallel Seq Scan on p2_c2
823 -> Parallel Seq Scan on p2_c4
824 -> Parallel Seq Scan on p2_c1_c1
825 -> Parallel Seq Scan on p2_c1_c2
826 -> Parallel Seq Scan on p2_c3_c1
827 -> Parallel Seq Scan on p2_c3_c2
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
850 -> Parallel Index Scan using p1_c1_pkey on p1_c1
851 -> Parallel Index Scan using p1_c2_pkey on p1_c2
852 -> Parallel Index Scan using p1_c3_pkey on p1_c3
853 -> Parallel Index Scan using p1_c4_pkey on p1_c4
854 -> Parallel Index Scan using p1_c1_c1_pkey on p1_c1_c1
855 -> Parallel Index Scan using p1_c1_c2_pkey on p1_c1_c2
856 -> Parallel Index Scan using p1_c3_c1_pkey on p1_c3_c1
857 -> Parallel Index Scan using p1_c3_c2_pkey on p1_c3_c2
860 -> Parallel Seq Scan on p2
861 -> Parallel Seq Scan on p2_c1
862 -> Parallel Seq Scan on p2_c2
863 -> Parallel Seq Scan on p2_c3
864 -> Parallel Seq Scan on p2_c4
865 -> Parallel Seq Scan on p2_c1_c1
866 -> Parallel Seq Scan on p2_c1_c2
867 -> Parallel Seq Scan on p2_c3_c1
868 -> Parallel Seq Scan on p2_c3_c2
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
890 -> Parallel Index Scan using p1_c1_pkey on p1_c1
891 -> Parallel Index Scan using p1_c2_pkey on p1_c2
892 -> Parallel Index Scan using p1_c3_pkey on p1_c3
893 -> Parallel Index Scan using p1_c4_pkey on p1_c4
894 -> Parallel Index Scan using p1_c1_c1_pkey on p1_c1_c1
895 -> Parallel Index Scan using p1_c1_c2_pkey on p1_c1_c2
896 -> Parallel Index Scan using p1_c3_c1_pkey on p1_c3_c1
897 -> Parallel Index Scan using p1_c3_c2_pkey on p1_c3_c2
903 -> Parallel Seq Scan on p2_c2
904 -> Parallel Seq Scan on p2_c4
905 -> Parallel Seq Scan on p2_c1_c1
906 -> Parallel Seq Scan on p2_c1_c2
907 -> Parallel Seq Scan on p2_c3_c1
908 -> Parallel Seq Scan on p2_c3_c2
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
929 -> Index Scan using p1_c1_pkey on p1_c1
930 -> Index Scan using p1_c2_pkey on p1_c2
931 -> Index Scan using p1_c3_pkey on p1_c3
932 -> Index Scan using p1_c4_pkey on p1_c4
933 -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
934 -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
935 -> Index Scan using p1_c3_c1_pkey on p1_c3_c1
936 -> Index Scan using p1_c3_c2_pkey on p1_c3_c2
944 -> Seq Scan on p2_c1_c1
945 -> Seq Scan on p2_c1_c2
946 -> Seq Scan on p2_c3_c1
947 -> Seq Scan on p2_c3_c2
951 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
953 ----------------------------
960 -> Seq Scan on p1_c1_c1
961 -> Seq Scan on p1_c1_c2
962 -> Seq Scan on p1_c3_c1
963 -> Seq Scan on p1_c3_c2
969 -> Seq Scan on p2_c1_c1
970 -> Seq Scan on p2_c1_c2
971 -> Seq Scan on p2_c3_c1
972 -> Seq Scan on p2_c3_c2
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
996 -> Parallel Seq Scan on p2_c4
997 -> Parallel Seq Scan on p2_c1_c1
998 -> Parallel Seq Scan on p2_c1_c2
999 -> Parallel Seq Scan on p2_c3_c1
1000 -> Parallel Seq Scan on p2_c3_c2
1001 -> Parallel Seq Scan on p1
1002 -> Parallel Seq Scan on p1_c1
1003 -> Parallel Seq Scan on p1_c2
1004 -> Parallel Seq Scan on p1_c3
1005 -> Parallel Seq Scan on p1_c4
1006 -> Parallel Seq Scan on p1_c1_c1
1007 -> Parallel Seq Scan on p1_c1_c2
1008 -> Parallel Seq Scan on p1_c3_c1
1009 -> Parallel Seq Scan on p1_c3_c2
1010 -> Parallel Seq Scan on p2
1011 -> Parallel Seq Scan on p2_c1
1012 -> Parallel Seq Scan on p2_c3
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
1031 -> Parallel Seq Scan on p1_c4
1032 -> Parallel Seq Scan on p2_c2
1033 -> Parallel Seq Scan on p2_c4
1034 -> Parallel Seq Scan on p1_c1_c1
1035 -> Parallel Seq Scan on p1_c1_c2
1036 -> Parallel Seq Scan on p1_c3_c1
1037 -> Parallel Seq Scan on p1_c3_c2
1038 -> Parallel Seq Scan on p2_c1_c1
1039 -> Parallel Seq Scan on p2_c1_c2
1040 -> Parallel Seq Scan on p2_c3_c1
1041 -> Parallel Seq Scan on p2_c3_c2
1042 -> Parallel Seq Scan on p1
1043 -> Parallel Seq Scan on p1_c1
1044 -> Parallel Seq Scan on p1_c3
1045 -> Parallel Seq Scan on p2
1046 -> Parallel Seq Scan on p2_c1
1047 -> Parallel Seq Scan on p2_c3
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
1073 -> Seq Scan on p1_c3
1075 -> Seq Scan on p2_c1
1076 -> Seq Scan on p2_c3
1077 -> Parallel Seq Scan on p1_c2
1078 -> Parallel Seq Scan on p1_c4
1079 -> Parallel Seq Scan on p1_c1_c1
1080 -> Parallel Seq Scan on p1_c1_c2
1081 -> Parallel Seq Scan on p1_c3_c1
1082 -> Parallel Seq Scan on p1_c3_c2
1083 -> Parallel Seq Scan on p2_c2
1084 -> Parallel Seq Scan on p2_c4
1085 -> Parallel Seq Scan on p2_c1_c1
1086 -> Parallel Seq Scan on p2_c1_c2
1087 -> Parallel Seq Scan on p2_c3_c1
1088 -> Parallel Seq Scan on p2_c3_c2
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
1105 -> Parallel Seq Scan on p1_c4
1106 -> Parallel Seq Scan on p1_c1_c1
1107 -> Parallel Seq Scan on p1_c1_c2
1108 -> Parallel Seq Scan on p1_c3_c1
1109 -> Parallel Seq Scan on p1_c3_c2
1110 -> Parallel Seq Scan on p1
1111 -> Parallel Seq Scan on p1_c1
1112 -> Parallel Seq Scan on p1_c3
1115 SET enable_parallel_append to false;
1116 /*+Parallel(p1 0 hard)*/
1117 EXPLAIN (COSTS false) SELECT * FROM p1;
1126 ----------------------------
1129 -> Seq Scan on p1_c1
1130 -> Seq Scan on p1_c2
1131 -> Seq Scan on p1_c3
1132 -> Seq Scan on p1_c4
1133 -> Seq Scan on p1_c1_c1
1134 -> Seq Scan on p1_c1_c2
1135 -> Seq Scan on p1_c3_c1
1136 -> Seq Scan on p1_c3_c2
1139 SET enable_parallel_append to true;
1140 /*+Parallel(p1 0 hard)*/
1141 EXPLAIN (COSTS false) SELECT * FROM p1;
1150 ----------------------------
1153 -> Seq Scan on p1_c1
1154 -> Seq Scan on p1_c2
1155 -> Seq Scan on p1_c3
1156 -> Seq Scan on p1_c4
1157 -> Seq Scan on p1_c1_c1
1158 -> Seq Scan on p1_c1_c2
1159 -> Seq Scan on p1_c3_c1
1160 -> Seq Scan on p1_c3_c2
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
1197 -> Parallel Seq Scan on p1_c4
1198 -> Parallel Seq Scan on p2_c2
1199 -> Parallel Seq Scan on p2_c4
1200 -> Parallel Seq Scan on p1_c1_c1
1201 -> Parallel Seq Scan on p1_c1_c2
1202 -> Parallel Seq Scan on p1_c3_c1
1203 -> Parallel Seq Scan on p1_c3_c2
1204 -> Parallel Seq Scan on p2_c1_c1
1205 -> Parallel Seq Scan on p2_c1_c2
1206 -> Parallel Seq Scan on p2_c3_c1
1207 -> Parallel Seq Scan on p2_c3_c2
1208 -> Parallel Seq Scan on p1
1209 -> Parallel Seq Scan on p1_c1
1210 -> Parallel Seq Scan on p1_c3
1211 -> Parallel Seq Scan on p2
1212 -> Parallel Seq Scan on p2_c1
1213 -> Parallel Seq Scan on p2_c3
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 EXPLAIN (COSTS false) SELECT id FROM p1_c1_c1 as s1 TABLESAMPLE SYSTEM(10)
1223 (WITH cte1 AS (SELECT id FROM p1 WHERE id % 2 = 0) SELECT id FROM cte1)
1225 SELECT userid FROM pg_stat_statements fs1
1227 SELECT x FROM (VALUES (1), (2), (3)) t(x);
1242 -----------------------------------------------------------------------------------------------
1246 -> Sample Scan on p1_c1_c1 s1
1247 Sampling: system ('10'::real)
1248 -> Foreign Scan on ft1
1249 Foreign File: /home/horiguti/work/pg_hint_plan/pg_hint_plan/data/data.csv
1254 Filter: ((id % 2) = 0)
1255 -> Seq Scan on p1_c1
1256 Filter: ((id % 2) = 0)
1257 -> Seq Scan on p1_c3
1258 Filter: ((id % 2) = 0)
1259 -> Parallel Seq Scan on p1_c2
1260 Filter: ((id % 2) = 0)
1261 -> Parallel Seq Scan on p1_c4
1262 Filter: ((id % 2) = 0)
1263 -> Parallel Seq Scan on p1_c1_c1
1264 Filter: ((id % 2) = 0)
1265 -> Parallel Seq Scan on p1_c1_c2
1266 Filter: ((id % 2) = 0)
1267 -> Parallel Seq Scan on p1_c3_c1
1268 Filter: ((id % 2) = 0)
1269 -> Parallel Seq Scan on p1_c3_c2
1270 Filter: ((id % 2) = 0)
1271 -> Function Scan on pg_stat_statements
1272 -> Subquery Scan on "*SELECT* 5"
1273 -> Values Scan on "*VALUES*"
1276 ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
1277 SELECT pg_reload_conf();