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_table_scan_size to 0;
17 SET min_parallel_index_scan_size to 0;
18 SET max_parallel_workers_per_gather to DEFAULT;
21 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
23 /*+Parallel(t1 8 soft)*/
24 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
26 /*+Parallel(t1 8 hard)*/
27 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
29 /*+Parallel(t1 4 hard) */ /* to be gather merge*/
30 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ORDER BY s1.t1.c1 LIMIT 4;
32 -- Queries on inheritance tables
33 SET parallel_setup_cost to 0;
34 SET parallel_tuple_cost to 0;
35 SET min_parallel_table_scan_size to 0;
36 SET min_parallel_index_scan_size to 0;
38 EXPLAIN (COSTS false) SELECT * FROM p1;
39 SET parallel_setup_cost to DEFAULT;
40 SET parallel_tuple_cost to DEFAULT;
41 SET min_parallel_table_scan_size to DEFAULT;
42 SET min_parallel_index_scan_size to DEFAULT;
44 /*+Parallel(p1 8 hard)*/
45 EXPLAIN (COSTS false) SELECT * FROM p1;
47 -- hinting on children makes the whole inheritance parallel
48 /*+Parallel(p1_c1 8 hard)*/
49 EXPLAIN (COSTS false) SELECT * FROM p1;
53 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
55 /*+Parallel(p1_c1_c1 8 hard)*/
56 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
58 SET parallel_setup_cost to 0;
59 SET parallel_tuple_cost to 0;
60 SET min_parallel_table_scan_size to 0;
61 SET min_parallel_index_scan_size to 0;
63 /*+Parallel(p1_c1_c1 8 soft) Parallel(p2_c1_c1 0)*/
64 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
66 /*+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 0)*/
67 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
69 /*+Parallel(p1_c1_c1 8 hard) Parallel(p2_c1_c1 8 hard)*/
70 EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
73 -- Joins on inheritance tables
74 SET parallel_setup_cost to 0;
75 SET parallel_tuple_cost to 0;
76 SET min_parallel_table_scan_size to 0;
77 SET min_parallel_index_scan_size to 0;
79 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
81 /*+Parallel(p1 8)Parallel(p2 0)*/
82 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
84 SET parallel_setup_cost to DEFAULT;
85 SET parallel_tuple_cost to DEFAULT;
86 SET min_parallel_table_scan_size to DEFAULT;
87 SET min_parallel_index_scan_size to DEFAULT;
89 /*+Parallel(p2 8 soft)*/
90 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
92 /*+Parallel(p2 8 hard)*/
93 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
95 /*+Parallel(p2 8 hard) Parallel(p1 5 hard) */
96 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
99 -- Mixture with a scan hint
100 -- p1 can be parallel
101 /*+Parallel(p1 8 hard) IndexScan(p2) */
102 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
104 -- Parallel sequential scan
105 /*+Parallel(p1 8 hard) SeqScan(p1) */
106 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
108 -- Parallel index scan
109 /*+Parallel(p1 8 hard) IndexScan(p1) */
110 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
112 -- This hint doesn't turn on parallel, so the Parallel hint is ignored
113 set max_parallel_workers_per_gather TO 0;
114 /*+Parallel(p1 0 hard) IndexScan(p1) */
115 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
119 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
121 -- parallel hinting on any relation enables parallel
122 SET parallel_setup_cost to 0;
123 SET parallel_tuple_cost to 0;
124 SET min_parallel_table_scan_size to 0;
125 SET min_parallel_index_scan_size to 0;
126 SET max_parallel_workers_per_gather to 0;
129 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
131 -- set hint does the same thing
132 /*+Set(max_parallel_workers_per_gather 1)*/
133 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
135 -- applies largest number of workers on merged parallel paths
136 SET parallel_setup_cost to DEFAULT;
137 SET parallel_tuple_cost to DEFAULT;
138 SET min_parallel_table_scan_size to DEFAULT;
139 SET min_parallel_index_scan_size to DEFAULT;
140 SET max_parallel_workers_per_gather to 8;
141 /*+Parallel(p1 5 hard)Parallel(p2 6 hard) */
142 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
145 -- num of workers of non-hinted relations should be default value
146 SET parallel_setup_cost to 0;
147 SET parallel_tuple_cost to 0;
148 SET min_parallel_table_scan_size to 0;
149 SET min_parallel_index_scan_size to 0;
150 SET max_parallel_workers_per_gather to 3;
151 SET enable_indexscan to false;
153 /*+Parallel(p1 8 hard) */
154 EXPLAIN (COSTS false) SELECT * FROM p1 join t1 on p1.id = t1.id;
157 SET enable_indexscan to DEFAULT;
158 SET parallel_setup_cost to 0;
159 SET parallel_tuple_cost to 0;
160 SET min_parallel_table_scan_size to 0;
161 SET min_parallel_index_scan_size to 0;
162 SET max_parallel_workers_per_gather to 5;
163 EXPLAIN (COSTS false) SELECT * FROM p1;
165 /*+Parallel(p1 0 hard)*/
166 EXPLAIN (COSTS false) SELECT * FROM p1;
169 /*+Parallel(p1 100x hard)Parallel(p1 -1000 hard)Parallel(p1 1000000 hard)
170 Parallel(p1 8 hoge)Parallel(p1)Parallel(p1 100 soft x)*/
171 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
173 -- Hints on unhintable relations are just ignored
174 /*+Parallel(p1 5 hard) Parallel(s1 3 hard) IndexScan(ft1) SeqScan(cte1)
175 TidScan(fs1) IndexScan(t) IndexScan(*VALUES*) */
176 EXPLAIN (COSTS false) SELECT id FROM p1_c1_c1 as s1 TABLESAMPLE SYSTEM(10)
180 (WITH cte1 AS (SELECT id FROM p1 WHERE id % 2 = 0) SELECT id FROM cte1)
182 SELECT userid FROM pg_stat_statements fs1
184 SELECT x FROM (VALUES (1), (2), (3)) t(x);
187 ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
188 SELECT pg_reload_conf();