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
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_relation_size to 0;
19 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
28 -------------------------------
31 -> Parallel Seq Scan on t1
34 /*+Parallel(t1 10 soft)*/
35 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
44 -------------------------------
47 -> Parallel Seq Scan on t1
50 SET parallel_setup_cost to DEFAULT;
51 SET parallel_tuple_cost to DEFAULT;
52 SET min_parallel_relation_size to DEFAULT;
53 /*+Parallel(t1 10 hard)*/
54 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
63 -------------------------------
66 -> Parallel Seq Scan on t1
69 -- Queries on inheritance tables
70 SET parallel_setup_cost to 0;
71 SET parallel_tuple_cost to 0;
72 SET min_parallel_relation_size to 0;
74 EXPLAIN (COSTS false) SELECT * FROM p1;
83 -------------------------------------------
87 -> Parallel Seq Scan on p1
88 -> Parallel Seq Scan on p1_c1
89 -> Parallel Seq Scan on p1_c2
90 -> Parallel Seq Scan on p1_c3
91 -> Parallel Seq Scan on p1_c4
92 -> Parallel Seq Scan on p1_c1_c1
93 -> Parallel Seq Scan on p1_c1_c2
94 -> Parallel Seq Scan on p1_c3_c1
95 -> Parallel Seq Scan on p1_c3_c2
98 SET parallel_setup_cost to DEFAULT;
99 SET parallel_tuple_cost to DEFAULT;
100 SET min_parallel_relation_size to DEFAULT;
101 /*+Parallel(p1 10 hard)*/
102 EXPLAIN (COSTS false) SELECT * FROM p1;
111 -------------------------------------------
115 -> Parallel Seq Scan on p1
116 -> Parallel Seq Scan on p1_c1
117 -> Parallel Seq Scan on p1_c2
118 -> Parallel Seq Scan on p1_c3
119 -> Parallel Seq Scan on p1_c4
120 -> Parallel Seq Scan on p1_c1_c1
121 -> Parallel Seq Scan on p1_c1_c2
122 -> Parallel Seq Scan on p1_c3_c1
123 -> Parallel Seq Scan on p1_c3_c2
126 -- hinting on children don't work but enables parallel
127 /*+Parallel(p1_c1 10 hard)*/
128 EXPLAIN (COSTS false) SELECT * FROM p1;
132 Parallel(p1_c1 10 hard)
137 -------------------------------------------
141 -> Parallel Seq Scan on p1
142 -> Parallel Seq Scan on p1_c1
143 -> Parallel Seq Scan on p1_c2
144 -> Parallel Seq Scan on p1_c3
145 -> Parallel Seq Scan on p1_c4
146 -> Parallel Seq Scan on p1_c1_c1
147 -> Parallel Seq Scan on p1_c1_c2
148 -> Parallel Seq Scan on p1_c3_c1
149 -> Parallel Seq Scan on p1_c3_c2
153 EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
155 ----------------------------------------
157 Hash Cond: (p1_c1.id = p2_c1.id)
160 -> Seq Scan on p1_c1_c1
161 -> Seq Scan on p1_c1_c2
165 -> Seq Scan on p2_c1_c1
166 -> Seq Scan on p2_c1_c2
169 /*+Parallel(p1_c1 10 hard)*/
170 EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
173 Parallel(p1_c1 10 hard)
179 -------------------------------------------------------
181 Hash Cond: (p1_c1.id = p2_c1.id)
185 -> Parallel Seq Scan on p1_c1
186 -> Parallel Seq Scan on p1_c1_c1
187 -> Parallel Seq Scan on p1_c1_c2
192 -> Parallel Seq Scan on p2_c1
193 -> Parallel Seq Scan on p2_c1_c1
194 -> Parallel Seq Scan on p2_c1_c2
197 /*+Parallel(p2_c1 10 hard)*/
198 EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
201 Parallel(p2_c1 10 hard)
207 -------------------------------------------------------
209 Hash Cond: (p1_c1.id = p2_c1.id)
213 -> Parallel Seq Scan on p1_c1
214 -> Parallel Seq Scan on p1_c1_c1
215 -> Parallel Seq Scan on p1_c1_c2
220 -> Parallel Seq Scan on p2_c1
221 -> Parallel Seq Scan on p2_c1_c1
222 -> Parallel Seq Scan on p2_c1_c2
225 /*+Parallel(p1_c1 10 hard) Parallel(p2_c1 10 hard)*/
226 EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
229 Parallel(p1_c1 10 hard)
230 Parallel(p2_c1 10 hard)
236 -------------------------------------------------------
238 Hash Cond: (p1_c1.id = p2_c1.id)
242 -> Parallel Seq Scan on p1_c1
243 -> Parallel Seq Scan on p1_c1_c1
244 -> Parallel Seq Scan on p1_c1_c2
249 -> Parallel Seq Scan on p2_c1
250 -> Parallel Seq Scan on p2_c1_c1
251 -> Parallel Seq Scan on p2_c1_c2
254 -- Joins on inheritance tables
255 SET parallel_setup_cost to 0;
256 SET parallel_tuple_cost to 0;
257 SET min_parallel_relation_size to 0;
259 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
268 -------------------------------------------------------
270 Hash Cond: (p1.id = p2.id)
274 -> Parallel Seq Scan on p1
275 -> Parallel Seq Scan on p1_c1
276 -> Parallel Seq Scan on p1_c2
277 -> Parallel Seq Scan on p1_c3
278 -> Parallel Seq Scan on p1_c4
279 -> Parallel Seq Scan on p1_c1_c1
280 -> Parallel Seq Scan on p1_c1_c2
281 -> Parallel Seq Scan on p1_c3_c1
282 -> Parallel Seq Scan on p1_c3_c2
287 -> Parallel Seq Scan on p2
288 -> Parallel Seq Scan on p2_c1
289 -> Parallel Seq Scan on p2_c2
290 -> Parallel Seq Scan on p2_c3
291 -> Parallel Seq Scan on p2_c4
292 -> Parallel Seq Scan on p2_c1_c1
293 -> Parallel Seq Scan on p2_c1_c2
294 -> Parallel Seq Scan on p2_c3_c1
295 -> Parallel Seq Scan on p2_c3_c2
298 SET parallel_setup_cost to DEFAULT;
299 SET parallel_tuple_cost to DEFAULT;
300 SET min_parallel_relation_size to DEFAULT;
301 /*+Parallel(p2 10 hard)*/
302 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
311 -------------------------------------------------------
313 Hash Cond: (p1.id = p2.id)
317 -> Parallel Seq Scan on p1
318 -> Parallel Seq Scan on p1_c1
319 -> Parallel Seq Scan on p1_c2
320 -> Parallel Seq Scan on p1_c3
321 -> Parallel Seq Scan on p1_c4
322 -> Parallel Seq Scan on p1_c1_c1
323 -> Parallel Seq Scan on p1_c1_c2
324 -> Parallel Seq Scan on p1_c3_c1
325 -> Parallel Seq Scan on p1_c3_c2
330 -> Parallel Seq Scan on p2
331 -> Parallel Seq Scan on p2_c1
332 -> Parallel Seq Scan on p2_c2
333 -> Parallel Seq Scan on p2_c3
334 -> Parallel Seq Scan on p2_c4
335 -> Parallel Seq Scan on p2_c1_c1
336 -> Parallel Seq Scan on p2_c1_c2
337 -> Parallel Seq Scan on p2_c3_c1
338 -> Parallel Seq Scan on p2_c3_c2
341 /*+Parallel(p2 10 hard) Parallel(p1 5 hard) */
342 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
352 -------------------------------------------------------
354 Hash Cond: (p1.id = p2.id)
358 -> Parallel Seq Scan on p1
359 -> Parallel Seq Scan on p1_c1
360 -> Parallel Seq Scan on p1_c2
361 -> Parallel Seq Scan on p1_c3
362 -> Parallel Seq Scan on p1_c4
363 -> Parallel Seq Scan on p1_c1_c1
364 -> Parallel Seq Scan on p1_c1_c2
365 -> Parallel Seq Scan on p1_c3_c1
366 -> Parallel Seq Scan on p1_c3_c2
371 -> Parallel Seq Scan on p2
372 -> Parallel Seq Scan on p2_c1
373 -> Parallel Seq Scan on p2_c2
374 -> Parallel Seq Scan on p2_c3
375 -> Parallel Seq Scan on p2_c4
376 -> Parallel Seq Scan on p2_c1_c1
377 -> Parallel Seq Scan on p2_c1_c2
378 -> Parallel Seq Scan on p2_c3_c1
379 -> Parallel Seq Scan on p2_c3_c2
382 -- Mixture with a scan hint
383 -- p1 can be parallel
384 /*+Parallel(p1 10 hard) IndexScan(p2) */
385 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
395 -------------------------------------------------------
397 Hash Cond: (p1.id = p2.id)
401 -> Parallel Seq Scan on p1
402 -> Parallel Seq Scan on p1_c1
403 -> Parallel Seq Scan on p1_c2
404 -> Parallel Seq Scan on p1_c3
405 -> Parallel Seq Scan on p1_c4
406 -> Parallel Seq Scan on p1_c1_c1
407 -> Parallel Seq Scan on p1_c1_c2
408 -> Parallel Seq Scan on p1_c3_c1
409 -> Parallel Seq Scan on p1_c3_c2
414 -> Parallel Seq Scan on p2
415 -> Parallel Seq Scan on p2_c1
416 -> Parallel Seq Scan on p2_c2
417 -> Parallel Seq Scan on p2_c3
418 -> Parallel Seq Scan on p2_c4
419 -> Parallel Seq Scan on p2_c1_c1
420 -> Parallel Seq Scan on p2_c1_c2
421 -> Parallel Seq Scan on p2_c3_c1
422 -> Parallel Seq Scan on p2_c3_c2
425 -- seqscan doesn't harm parallelism
426 /*+Parallel(p1 10 hard) SeqScan(p1) */
427 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
437 -------------------------------------------------------
439 Hash Cond: (p1.id = p2.id)
443 -> Parallel Seq Scan on p1
444 -> Parallel Seq Scan on p1_c1
445 -> Parallel Seq Scan on p1_c2
446 -> Parallel Seq Scan on p1_c3
447 -> Parallel Seq Scan on p1_c4
448 -> Parallel Seq Scan on p1_c1_c1
449 -> Parallel Seq Scan on p1_c1_c2
450 -> Parallel Seq Scan on p1_c3_c1
451 -> Parallel Seq Scan on p1_c3_c2
456 -> Parallel Seq Scan on p2
457 -> Parallel Seq Scan on p2_c1
458 -> Parallel Seq Scan on p2_c2
459 -> Parallel Seq Scan on p2_c3
460 -> Parallel Seq Scan on p2_c4
461 -> Parallel Seq Scan on p2_c1_c1
462 -> Parallel Seq Scan on p2_c1_c2
463 -> Parallel Seq Scan on p2_c3_c1
464 -> Parallel Seq Scan on p2_c3_c2
467 -- parallelism is not available for the case
468 /*+Parallel(p1 10 hard) IndexScan(p1) */
469 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
479 -------------------------------------------------------
481 Hash Cond: (p1.id = p2.id)
485 -> Parallel Seq Scan on p1
486 -> Parallel Seq Scan on p1_c1
487 -> Parallel Seq Scan on p1_c2
488 -> Parallel Seq Scan on p1_c3
489 -> Parallel Seq Scan on p1_c4
490 -> Parallel Seq Scan on p1_c1_c1
491 -> Parallel Seq Scan on p1_c1_c2
492 -> Parallel Seq Scan on p1_c3_c1
493 -> Parallel Seq Scan on p1_c3_c2
498 -> Parallel Seq Scan on p2
499 -> Parallel Seq Scan on p2_c1
500 -> Parallel Seq Scan on p2_c2
501 -> Parallel Seq Scan on p2_c3
502 -> Parallel Seq Scan on p2_c4
503 -> Parallel Seq Scan on p2_c1_c1
504 -> Parallel Seq Scan on p2_c1_c2
505 -> Parallel Seq Scan on p2_c3_c1
506 -> Parallel Seq Scan on p2_c3_c2
510 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
512 ----------------------------
519 -> Seq Scan on p1_c1_c1
520 -> Seq Scan on p1_c1_c2
521 -> Seq Scan on p1_c3_c1
522 -> Seq Scan on p1_c3_c2
528 -> Seq Scan on p2_c1_c1
529 -> Seq Scan on p2_c1_c2
530 -> Seq Scan on p2_c3_c1
531 -> Seq Scan on p2_c3_c2
534 -- some of the scans are not parallel, so this cannot be parallel
535 SET parallel_setup_cost to 0;
536 SET parallel_tuple_cost to 0;
537 SET min_parallel_relation_size to 0;
538 SET max_parallel_workers_per_gather to 0;
539 /*+Parallel(p1 10) */
540 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
549 -------------------------------------------
553 -> Parallel Seq Scan on p1
554 -> Parallel Seq Scan on p1_c1
555 -> Parallel Seq Scan on p1_c2
556 -> Parallel Seq Scan on p1_c3
557 -> Parallel Seq Scan on p1_c4
558 -> Parallel Seq Scan on p1_c1_c1
559 -> Parallel Seq Scan on p1_c1_c2
560 -> Parallel Seq Scan on p1_c3_c1
561 -> Parallel Seq Scan on p1_c3_c2
562 -> Parallel Seq Scan on p2
563 -> Parallel Seq Scan on p2_c1
564 -> Parallel Seq Scan on p2_c2
565 -> Parallel Seq Scan on p2_c3
566 -> Parallel Seq Scan on p2_c4
567 -> Parallel Seq Scan on p2_c1_c1
568 -> Parallel Seq Scan on p2_c1_c2
569 -> Parallel Seq Scan on p2_c3_c1
570 -> Parallel Seq Scan on p2_c3_c2
573 -- all children are parallel, so this can be parallel
574 /*+Parallel(p1 10) Parallel(p2 10) */
575 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
585 -------------------------------------------
589 -> Parallel Seq Scan on p1
590 -> Parallel Seq Scan on p1_c1
591 -> Parallel Seq Scan on p1_c2
592 -> Parallel Seq Scan on p1_c3
593 -> Parallel Seq Scan on p1_c4
594 -> Parallel Seq Scan on p1_c1_c1
595 -> Parallel Seq Scan on p1_c1_c2
596 -> Parallel Seq Scan on p1_c3_c1
597 -> Parallel Seq Scan on p1_c3_c2
598 -> Parallel Seq Scan on p2
599 -> Parallel Seq Scan on p2_c1
600 -> Parallel Seq Scan on p2_c2
601 -> Parallel Seq Scan on p2_c3
602 -> Parallel Seq Scan on p2_c4
603 -> Parallel Seq Scan on p2_c1_c1
604 -> Parallel Seq Scan on p2_c1_c2
605 -> Parallel Seq Scan on p2_c3_c1
606 -> Parallel Seq Scan on p2_c3_c2
609 SET parallel_setup_cost to DEFAULT;
610 SET parallel_tuple_cost to DEFAULT;
611 SET min_parallel_relation_size to DEFAULT;
612 SET max_parallel_workers_per_gather to DEFAULT;
613 /*+Parallel(p1 10 hard)Parallel(p2 10 hard) */
614 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
624 -------------------------------------------
628 -> Parallel Seq Scan on p1
629 -> Parallel Seq Scan on p1_c1
630 -> Parallel Seq Scan on p1_c2
631 -> Parallel Seq Scan on p1_c3
632 -> Parallel Seq Scan on p1_c4
633 -> Parallel Seq Scan on p1_c1_c1
634 -> Parallel Seq Scan on p1_c1_c2
635 -> Parallel Seq Scan on p1_c3_c1
636 -> Parallel Seq Scan on p1_c3_c2
637 -> Parallel Seq Scan on p2
638 -> Parallel Seq Scan on p2_c1
639 -> Parallel Seq Scan on p2_c2
640 -> Parallel Seq Scan on p2_c3
641 -> Parallel Seq Scan on p2_c4
642 -> Parallel Seq Scan on p2_c1_c1
643 -> Parallel Seq Scan on p2_c1_c2
644 -> Parallel Seq Scan on p2_c3_c1
645 -> Parallel Seq Scan on p2_c3_c2
648 -- num of workers of non-hinted relations should be default value
649 SET parallel_setup_cost to 0;
650 SET parallel_tuple_cost to 0;
651 SET min_parallel_relation_size to 0;
652 SET max_parallel_workers_per_gather to 3;
653 /*+Parallel(p1 10 hard) */
654 EXPLAIN (COSTS false) SELECT * FROM p1 join t1 on p1.id = t1.id;
663 -------------------------------------------------------
665 Hash Cond: (t1.id = p1.id)
668 -> Parallel Seq Scan on t1
673 -> Parallel Seq Scan on p1
674 -> Parallel Seq Scan on p1_c1
675 -> Parallel Seq Scan on p1_c2
676 -> Parallel Seq Scan on p1_c3
677 -> Parallel Seq Scan on p1_c4
678 -> Parallel Seq Scan on p1_c1_c1
679 -> Parallel Seq Scan on p1_c1_c2
680 -> Parallel Seq Scan on p1_c3_c1
681 -> Parallel Seq Scan on p1_c3_c2
685 SET parallel_setup_cost to 0;
686 SET parallel_tuple_cost to 0;
687 SET min_parallel_relation_size to 0;
688 SET max_parallel_workers_per_gather to 5;
689 EXPLAIN (COSTS false) SELECT * FROM p1;
691 -------------------------------------------
695 -> Parallel Seq Scan on p1
696 -> Parallel Seq Scan on p1_c1
697 -> Parallel Seq Scan on p1_c2
698 -> Parallel Seq Scan on p1_c3
699 -> Parallel Seq Scan on p1_c4
700 -> Parallel Seq Scan on p1_c1_c1
701 -> Parallel Seq Scan on p1_c1_c2
702 -> Parallel Seq Scan on p1_c3_c1
703 -> Parallel Seq Scan on p1_c3_c2
706 /*+Parallel(p1 0 hard)*/
707 EXPLAIN (COSTS false) SELECT * FROM p1;
716 ----------------------------
723 -> Seq Scan on p1_c1_c1
724 -> Seq Scan on p1_c1_c2
725 -> Seq Scan on p1_c3_c1
726 -> Seq Scan on p1_c3_c2
730 /*+Parallel(p1 100x hard)Parallel(p1 -1000 hard)Parallel(p1 1000000 hard)
731 Parallel(p1 10 hoge)Parallel(p1)Parallel(p1 100 soft x)*/
732 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
733 INFO: pg_hint_plan: hint syntax error at or near "100x"
734 DETAIL: number of workers must be a number: Parallel
735 INFO: pg_hint_plan: hint syntax error at or near "-1000"
736 DETAIL: number of workers must be positive: Parallel
737 INFO: pg_hint_plan: hint syntax error at or near "1000000"
738 DETAIL: number of workers = 1000000 is larger than max_worker_processes(100): Parallel
739 INFO: pg_hint_plan: hint syntax error at or near "hoge"
740 DETAIL: enforcement must be soft or hard: Parallel
741 INFO: pg_hint_plan: hint syntax error at or near ")"
742 DETAIL: wrong number of arguments (1): Parallel
743 INFO: pg_hint_plan: hint syntax error at or near ")"
744 DETAIL: wrong number of arguments (4): Parallel
750 Parallel(p1 100x hard)
751 Parallel(p1 -1000 hard)
752 Parallel(p1 1000000 hard)
758 -------------------------------------------
762 -> Parallel Seq Scan on p1
763 -> Parallel Seq Scan on p1_c1
764 -> Parallel Seq Scan on p1_c2
765 -> Parallel Seq Scan on p1_c3
766 -> Parallel Seq Scan on p1_c4
767 -> Parallel Seq Scan on p1_c1_c1
768 -> Parallel Seq Scan on p1_c1_c2
769 -> Parallel Seq Scan on p1_c3_c1
770 -> Parallel Seq Scan on p1_c3_c2
771 -> Parallel Seq Scan on p2
772 -> Parallel Seq Scan on p2_c1
773 -> Parallel Seq Scan on p2_c2
774 -> Parallel Seq Scan on p2_c3
775 -> Parallel Seq Scan on p2_c4
776 -> Parallel Seq Scan on p2_c1_c1
777 -> Parallel Seq Scan on p2_c1_c2
778 -> Parallel Seq Scan on p2_c3_c1
779 -> Parallel Seq Scan on p2_c3_c2
782 ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
783 SELECT pg_reload_conf();