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;
8 CREATE TABLE s1.tl (a int);
9 INSERT INTO s1.tl (SELECT a FROM generate_series(0, 100000) a);
11 -- Queries on ordinary tables with default setting
12 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
14 SET parallel_setup_cost to 0;
15 SET parallel_tuple_cost to 0;
16 SET min_parallel_relation_size to 0;
17 SET max_parallel_workers_per_gather to DEFAULT;
20 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
22 /*+Parallel(t1 10 soft)*/
23 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
25 /*+Parallel(t1 10 hard)*/
26 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
28 -- Queries on inheritance tables
29 SET parallel_setup_cost to 0;
30 SET parallel_tuple_cost to 0;
31 SET min_parallel_relation_size to 0;
33 EXPLAIN (COSTS false) SELECT * FROM p1;
34 SET parallel_setup_cost to DEFAULT;
35 SET parallel_tuple_cost to DEFAULT;
36 SET min_parallel_relation_size to DEFAULT;
38 /*+Parallel(p1 10 hard)*/
39 EXPLAIN (COSTS false) SELECT * FROM p1;
41 -- hinting on children makes the whole inheritance parallel
42 /*+Parallel(p1_c1 10 hard)*/
43 EXPLAIN (COSTS false) SELECT * FROM p1;
47 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
49 /*+Parallel(p1_c1_c1 10 hard)*/
50 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
52 SET parallel_setup_cost to 0;
53 SET parallel_tuple_cost to 0;
54 SET min_parallel_relation_size to 0;
56 /*+Parallel(p1_c1_c1 10 soft) Parallel(p2_c1_c1 0)*/
57 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
59 /*+Parallel(p1_c1_c1 10 hard) Parallel(p2_c1_c1 0)*/
60 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
62 /*+Parallel(p1_c1_c1 10 hard) Parallel(p2_c1_c1 10 hard)*/
63 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
66 -- Joins on inheritance tables
67 SET parallel_setup_cost to 0;
68 SET parallel_tuple_cost to 0;
69 SET min_parallel_relation_size to 0;
71 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
73 /*+Parallel(p1 10)Parallel(p2 0)*/
74 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
76 SET parallel_setup_cost to DEFAULT;
77 SET parallel_tuple_cost to DEFAULT;
78 SET min_parallel_relation_size to DEFAULT;
80 /*+Parallel(p2 10 hard)*/
81 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
83 /*+Parallel(p2 10 hard) Parallel(p1 5 hard) */
84 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
87 -- Mixture with a scan hint
89 /*+Parallel(p1 10 hard) IndexScan(p2) */
90 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
92 -- seqscan doesn't harm parallelism
93 /*+Parallel(p1 10 hard) SeqScan(p1) */
94 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
96 -- parallel overrides index scan
97 /*+Parallel(p1 10 hard) IndexScan(p1) */
98 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
99 /*+Parallel(p1 0 hard) IndexScan(p1) */
100 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
104 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
106 -- parallel hinting on any relation enables parallel
107 SET parallel_setup_cost to 0;
108 SET parallel_tuple_cost to 0;
109 SET min_parallel_relation_size to 0;
110 SET max_parallel_workers_per_gather to 0;
112 /*+Parallel(p1 10) */
113 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
115 -- set hint also does
116 /*+Set(max_parallel_workers_per_gather 1)*/
117 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
119 -- applies largest number of workers on merged parallel paths
120 SET parallel_setup_cost to DEFAULT;
121 SET parallel_tuple_cost to DEFAULT;
122 SET min_parallel_relation_size to DEFAULT;
123 SET max_parallel_workers_per_gather to 10;
124 /*+Parallel(p1 5 hard)Parallel(p2 6 hard) */
125 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
128 -- num of workers of non-hinted relations should be default value
129 SET parallel_setup_cost to 0;
130 SET parallel_tuple_cost to 0;
131 SET min_parallel_relation_size to 0;
132 SET max_parallel_workers_per_gather to 3;
134 /*+Parallel(p1 10 hard) */
135 EXPLAIN (COSTS false) SELECT * FROM p1 join t1 on p1.id = t1.id;
138 SET parallel_setup_cost to 0;
139 SET parallel_tuple_cost to 0;
140 SET min_parallel_relation_size to 0;
141 SET max_parallel_workers_per_gather to 5;
142 EXPLAIN (COSTS false) SELECT * FROM p1;
144 /*+Parallel(p1 0 hard)*/
145 EXPLAIN (COSTS false) SELECT * FROM p1;
148 /*+Parallel(p1 100x hard)Parallel(p1 -1000 hard)Parallel(p1 1000000 hard)
149 Parallel(p1 10 hoge)Parallel(p1)Parallel(p1 100 soft x)*/
150 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
152 ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
153 SELECT pg_reload_conf();