• R/O
  • R/O (HTTP)
  • R/W (SSH)
  • R/W (HTTPS)

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revision5a72d7cc92b30c58849def207a027beac6a70930 (tree)
Time2017-02-10 17:19:43
AuthorKyotaro Horiguchi <horiguchi.kyotaro@lab....>
CommiterKyotaro Horiguchi

Log Message

Refactor hint application mechamism next step

I removed useless code forgotten to be removed in
add_paths_to_joinrel_wrapper by the previous refactoring. Addition to
that, find_scan_method_hint and find_parallel_hint are found not to
require the third parameter rel, so removed it. Some inadequate tests
are fixed and changed according to change of unclear behaviors.

Change Summary

Incremental Difference

--- a/expected/ut-S.out
+++ b/expected/ut-S.out
@@ -5227,18 +5227,18 @@ error hint:
52275227 EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
52285228 LOG: pg_hint_plan:
52295229 used hint:
5230-not used hint:
52315230 IndexScan(p2c1)
5231+not used hint:
52325232 duplication hint:
52335233 error hint:
52345234
5235- QUERY PLAN
5236---------------------------
5235+ QUERY PLAN
5236+---------------------------------------
52375237 Append
52385238 -> Seq Scan on p2
52395239 Filter: (c1 = 1)
5240- -> Seq Scan on p2c1
5241- Filter: (c1 = 1)
5240+ -> Index Scan using p2c1_i on p2c1
5241+ Index Cond: (c1 = 1)
52425242 -> Seq Scan on p2c1c1
52435243 Filter: (c1 = 1)
52445244 (7 rows)
--- a/expected/ut-W.out
+++ b/expected/ut-W.out
@@ -5,7 +5,7 @@ SET pg_hint_plan.debug_print TO on;
55 SET client_min_messages TO LOG;
66 CREATE TABLE s1.tl (a int);
77 INSERT INTO s1.tl (SELECT a FROM generate_series(0, 100000) a);
8--- Queries on ordinary tables
8+-- Queries on ordinary tables with default setting
99 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
1010 QUERY PLAN
1111 ----------------
@@ -15,6 +15,7 @@ EXPLAIN (COSTS false) SELECT * FROM s1.t1;
1515 SET parallel_setup_cost to 0;
1616 SET parallel_tuple_cost to 0;
1717 SET min_parallel_relation_size to 0;
18+SET max_parallel_workers_per_gather to DEFAULT;
1819 /*+Parallel(t1 10)*/
1920 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
2021 LOG: pg_hint_plan:
@@ -47,9 +48,6 @@ error hint:
4748 -> Parallel Seq Scan on t1
4849 (3 rows)
4950
50-SET parallel_setup_cost to DEFAULT;
51-SET parallel_tuple_cost to DEFAULT;
52-SET min_parallel_relation_size to DEFAULT;
5351 /*+Parallel(t1 10 hard)*/
5452 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
5553 LOG: pg_hint_plan:
@@ -123,20 +121,20 @@ error hint:
123121 -> Parallel Seq Scan on p1_c3_c2
124122 (12 rows)
125123
126--- hinting on children don't work but enables parallel
124+-- hinting on children makes the whole inheritance parallel
127125 /*+Parallel(p1_c1 10 hard)*/
128126 EXPLAIN (COSTS false) SELECT * FROM p1;
129127 LOG: pg_hint_plan:
130128 used hint:
131-not used hint:
132129 Parallel(p1_c1 10 hard)
130+not used hint:
133131 duplication hint:
134132 error hint:
135133
136134 QUERY PLAN
137135 -------------------------------------------
138136 Gather
139- Workers Planned: 1
137+ Workers Planned: 10
140138 -> Append
141139 -> Parallel Seq Scan on p1
142140 -> Parallel Seq Scan on p1_c1
@@ -150,106 +148,103 @@ error hint:
150148 (12 rows)
151149
152150 -- Joins
153-EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
154- QUERY PLAN
155-----------------------------------------
151+EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
152+ QUERY PLAN
153+------------------------------------------
156154 Hash Join
157- Hash Cond: (p1_c1.id = p2_c1.id)
158- -> Append
159- -> Seq Scan on p1_c1
160- -> Seq Scan on p1_c1_c1
161- -> Seq Scan on p1_c1_c2
155+ Hash Cond: (p1_c1_c1.id = p2_c1_c1.id)
156+ -> Seq Scan on p1_c1_c1
162157 -> Hash
163- -> Append
164- -> Seq Scan on p2_c1
165- -> Seq Scan on p2_c1_c1
166- -> Seq Scan on p2_c1_c2
167-(11 rows)
158+ -> Seq Scan on p2_c1_c1
159+(5 rows)
168160
169-/*+Parallel(p1_c1 10 hard)*/
170-EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
161+/*+Parallel(p1_c1_c1 10 hard)*/
162+EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
171163 LOG: pg_hint_plan:
172164 used hint:
173-Parallel(p1_c1 10 hard)
165+Parallel(p1_c1_c1 10 hard)
174166 not used hint:
175167 duplication hint:
176168 error hint:
177169
178- QUERY PLAN
179--------------------------------------------------------
170+ QUERY PLAN
171+-------------------------------------------------
180172 Hash Join
181- Hash Cond: (p1_c1.id = p2_c1.id)
182- -> Gather
183- Workers Planned: 10
184- -> Append
185- -> Parallel Seq Scan on p1_c1
186- -> Parallel Seq Scan on p1_c1_c1
187- -> Parallel Seq Scan on p1_c1_c2
173+ Hash Cond: (p2_c1_c1.id = p1_c1_c1.id)
174+ -> Seq Scan on p2_c1_c1
188175 -> Hash
189176 -> Gather
190- Workers Planned: 1
191- -> Append
192- -> Parallel Seq Scan on p2_c1
193- -> Parallel Seq Scan on p2_c1_c1
194- -> Parallel Seq Scan on p2_c1_c2
195-(15 rows)
177+ Workers Planned: 10
178+ -> Parallel Seq Scan on p1_c1_c1
179+(7 rows)
196180
197-/*+Parallel(p2_c1 10 hard)*/
198-EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
181+SET parallel_setup_cost to 0;
182+SET parallel_tuple_cost to 0;
183+SET min_parallel_relation_size to 0;
184+/*+Parallel(p1_c1_c1 10 soft) Parallel(p2_c1_c1 0)*/
185+EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
199186 LOG: pg_hint_plan:
200187 used hint:
201-Parallel(p2_c1 10 hard)
188+Parallel(p1_c1_c1 10 soft)
189+Parallel(p2_c1_c1 0 soft)
202190 not used hint:
203191 duplication hint:
204192 error hint:
205193
206- QUERY PLAN
207--------------------------------------------------------
208- Hash Join
209- Hash Cond: (p1_c1.id = p2_c1.id)
210- -> Gather
211- Workers Planned: 1
212- -> Append
213- -> Parallel Seq Scan on p1_c1
214- -> Parallel Seq Scan on p1_c1_c1
215- -> Parallel Seq Scan on p1_c1_c2
216- -> Hash
217- -> Gather
218- Workers Planned: 10
219- -> Append
220- -> Parallel Seq Scan on p2_c1
221- -> Parallel Seq Scan on p2_c1_c1
222- -> Parallel Seq Scan on p2_c1_c2
223-(15 rows)
194+ QUERY PLAN
195+------------------------------------------------
196+ Gather
197+ Workers Planned: 1
198+ -> Hash Join
199+ Hash Cond: (p1_c1_c1.id = p2_c1_c1.id)
200+ -> Parallel Seq Scan on p1_c1_c1
201+ -> Hash
202+ -> Seq Scan on p2_c1_c1
203+(7 rows)
224204
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;
205+/*+Parallel(p1_c1_c1 10 hard) Parallel(p2_c1_c1 0)*/
206+EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
227207 LOG: pg_hint_plan:
228208 used hint:
229-Parallel(p1_c1 10 hard)
230-Parallel(p2_c1 10 hard)
209+Parallel(p1_c1_c1 10 hard)
210+Parallel(p2_c1_c1 0 soft)
231211 not used hint:
232212 duplication hint:
233213 error hint:
234214
235- QUERY PLAN
236--------------------------------------------------------
215+ QUERY PLAN
216+------------------------------------------------
217+ Gather
218+ Workers Planned: 10
219+ -> Hash Join
220+ Hash Cond: (p1_c1_c1.id = p2_c1_c1.id)
221+ -> Parallel Seq Scan on p1_c1_c1
222+ -> Hash
223+ -> Seq Scan on p2_c1_c1
224+(7 rows)
225+
226+/*+Parallel(p1_c1_c1 10 hard) Parallel(p2_c1_c1 10 hard)*/
227+EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
228+LOG: pg_hint_plan:
229+used hint:
230+Parallel(p1_c1_c1 10 hard)
231+Parallel(p2_c1_c1 10 hard)
232+not used hint:
233+duplication hint:
234+error hint:
235+
236+ QUERY PLAN
237+-------------------------------------------------
237238 Hash Join
238- Hash Cond: (p1_c1.id = p2_c1.id)
239+ Hash Cond: (p1_c1_c1.id = p2_c1_c1.id)
239240 -> Gather
240241 Workers Planned: 10
241- -> Append
242- -> Parallel Seq Scan on p1_c1
243- -> Parallel Seq Scan on p1_c1_c1
244- -> Parallel Seq Scan on p1_c1_c2
242+ -> Parallel Seq Scan on p1_c1_c1
245243 -> Hash
246244 -> Gather
247245 Workers Planned: 10
248- -> Append
249- -> Parallel Seq Scan on p2_c1
250- -> Parallel Seq Scan on p2_c1_c1
251- -> Parallel Seq Scan on p2_c1_c2
252-(15 rows)
246+ -> Parallel Seq Scan on p2_c1_c1
247+(9 rows)
253248
254249 -- Joins on inheritance tables
255250 SET parallel_setup_cost to 0;
@@ -295,6 +290,45 @@ error hint:
295290 -> Parallel Seq Scan on p2_c3_c2
296291 (27 rows)
297292
293+/*+Parallel(p1 10)Parallel(p2 0)*/
294+EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
295+LOG: pg_hint_plan:
296+used hint:
297+Parallel(p1 10 soft)
298+Parallel(p2 0 soft)
299+not used hint:
300+duplication hint:
301+error hint:
302+
303+ QUERY PLAN
304+-------------------------------------------------
305+ Gather
306+ Workers Planned: 1
307+ -> Hash Join
308+ Hash Cond: (p1.id = p2.id)
309+ -> Append
310+ -> Parallel Seq Scan on p1
311+ -> Parallel Seq Scan on p1_c1
312+ -> Parallel Seq Scan on p1_c2
313+ -> Parallel Seq Scan on p1_c3
314+ -> Parallel Seq Scan on p1_c4
315+ -> Parallel Seq Scan on p1_c1_c1
316+ -> Parallel Seq Scan on p1_c1_c2
317+ -> Parallel Seq Scan on p1_c3_c1
318+ -> Parallel Seq Scan on p1_c3_c2
319+ -> Hash
320+ -> Append
321+ -> Seq Scan on p2
322+ -> Seq Scan on p2_c1
323+ -> Seq Scan on p2_c2
324+ -> Seq Scan on p2_c3
325+ -> Seq Scan on p2_c4
326+ -> Seq Scan on p2_c1_c1
327+ -> Seq Scan on p2_c1_c2
328+ -> Seq Scan on p2_c3_c1
329+ -> Seq Scan on p2_c3_c2
330+(25 rows)
331+
298332 SET parallel_setup_cost to DEFAULT;
299333 SET parallel_tuple_cost to DEFAULT;
300334 SET min_parallel_relation_size to DEFAULT;
@@ -464,7 +498,7 @@ error hint:
464498 -> Parallel Seq Scan on p2_c3_c2
465499 (27 rows)
466500
467--- parallelism is not available for the case
501+-- parallel overrides index scan
468502 /*+Parallel(p1 10 hard) IndexScan(p1) */
469503 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
470504 LOG: pg_hint_plan:
@@ -506,6 +540,43 @@ error hint:
506540 -> Parallel Seq Scan on p2_c3_c2
507541 (27 rows)
508542
543+/*+Parallel(p1 0 hard) IndexScan(p1) */
544+EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
545+LOG: pg_hint_plan:
546+used hint:
547+IndexScan(p1)
548+Parallel(p1 0 hard)
549+not used hint:
550+duplication hint:
551+error hint:
552+
553+ QUERY PLAN
554+--------------------------------------------------------
555+ Hash Join
556+ Hash Cond: (p1.id = p2.id)
557+ -> Append
558+ -> Index Scan using p1_pkey on p1
559+ -> Index Scan using p1_c1_pkey on p1_c1
560+ -> Index Scan using p1_c2_pkey on p1_c2
561+ -> Index Scan using p1_c3_pkey on p1_c3
562+ -> Index Scan using p1_c4_pkey on p1_c4
563+ -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
564+ -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
565+ -> Index Scan using p1_c3_c1_pkey on p1_c3_c1
566+ -> Index Scan using p1_c3_c2_pkey on p1_c3_c2
567+ -> Hash
568+ -> Append
569+ -> Seq Scan on p2
570+ -> Seq Scan on p2_c1
571+ -> Seq Scan on p2_c2
572+ -> Seq Scan on p2_c3
573+ -> Seq Scan on p2_c4
574+ -> Seq Scan on p2_c1_c1
575+ -> Seq Scan on p2_c1_c2
576+ -> Seq Scan on p2_c3_c1
577+ -> Seq Scan on p2_c3_c2
578+(23 rows)
579+
509580 -- Parallel on UNION
510581 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
511582 QUERY PLAN
@@ -531,7 +602,7 @@ EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
531602 -> Seq Scan on p2_c3_c2
532603 (19 rows)
533604
534--- some of the scans are not parallel, so this cannot be parallel
605+-- parallel hinting on any relation enables parallel
535606 SET parallel_setup_cost to 0;
536607 SET parallel_tuple_cost to 0;
537608 SET min_parallel_relation_size to 0;
@@ -570,13 +641,12 @@ error hint:
570641 -> Parallel Seq Scan on p2_c3_c2
571642 (21 rows)
572643
573--- all children are parallel, so this can be parallel
574-/*+Parallel(p1 10) Parallel(p2 10) */
644+-- set hint also does
645+/*+Set(max_parallel_workers_per_gather 1)*/
575646 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
576647 LOG: pg_hint_plan:
577648 used hint:
578-Parallel(p1 10 soft)
579-Parallel(p2 10 soft)
649+Set(max_parallel_workers_per_gather 1)
580650 not used hint:
581651 duplication hint:
582652 error hint:
@@ -606,16 +676,17 @@ error hint:
606676 -> Parallel Seq Scan on p2_c3_c2
607677 (21 rows)
608678
679+-- applies largest number of workers on merged parallel paths
609680 SET parallel_setup_cost to DEFAULT;
610681 SET parallel_tuple_cost to DEFAULT;
611682 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) */
683+SET max_parallel_workers_per_gather to 10;
684+/*+Parallel(p1 5 hard)Parallel(p2 6 hard) */
614685 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
615686 LOG: pg_hint_plan:
616687 used hint:
617-Parallel(p1 10 hard)
618-Parallel(p2 10 hard)
688+Parallel(p1 5 hard)
689+Parallel(p2 6 hard)
619690 not used hint:
620691 duplication hint:
621692 error hint:
@@ -623,7 +694,7 @@ error hint:
623694 QUERY PLAN
624695 -------------------------------------------
625696 Gather
626- Workers Planned: 10
697+ Workers Planned: 6
627698 -> Append
628699 -> Parallel Seq Scan on p1
629700 -> Parallel Seq Scan on p1_c1
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -2996,26 +2996,43 @@ standard_planner_proc:
29962996
29972997 /*
29982998 * Find scan method hint to be applied to the given relation
2999+ *
29993000 */
30003001 static ScanMethodHint *
3001-find_scan_hint(PlannerInfo *root, Index relid, RelOptInfo *rel)
3002+find_scan_hint(PlannerInfo *root, Index relid)
30023003 {
3004+ RelOptInfo *rel;
30033005 RangeTblEntry *rte;
3006+ ScanMethodHint *real_name_hint = NULL;
3007+ ScanMethodHint *alias_hint = NULL;
30043008 int i;
30053009
3010+ /* This should not be a join rel */
3011+ Assert(relid > 0);
3012+ rel = root->simple_rel_array[relid];
3013+
30063014 /*
3007- * We don't apply scan method hint if the relation is:
3008- * - not a base relation
3009- * - not an ordinary relation (such as join and subquery)
3015+ * This function is called for any RelOptInfo or its inheritance parent if
3016+ * any. If we are called from inheritance planner, the RelOptInfo for the
3017+ * parent of target child relation is not set in the planner info.
3018+ *
3019+ * Otherwise we should check that the reloptinfo is base relation or
3020+ * inheritance children.
30103021 */
3011- if (rel && (rel->reloptkind != RELOPT_BASEREL ||
3012- rel->rtekind != RTE_RELATION))
3022+ if (rel &&
3023+ rel->reloptkind != RELOPT_BASEREL &&
3024+ rel->reloptkind != RELOPT_OTHER_MEMBER_REL)
30133025 return NULL;
30143026
3027+ /*
3028+ * This is baserel or appendrel children. We can refer to RangeTblEntry.
3029+ */
30153030 rte = root->simple_rte_array[relid];
3031+ Assert(rte);
30163032
3017- /* We don't force scan method of foreign tables */
3018- if (rte->relkind == RELKIND_FOREIGN_TABLE)
3033+ /* We don't hint on other than relation and foreign tables */
3034+ if (rte->rtekind != RTE_RELATION ||
3035+ rte->relkind == RELKIND_FOREIGN_TABLE)
30193036 return NULL;
30203037
30213038 /* Find scan method hint, which matches given names, from the list. */
@@ -3027,36 +3044,70 @@ find_scan_hint(PlannerInfo *root, Index relid, RelOptInfo *rel)
30273044 if (!hint_state_enabled(hint))
30283045 continue;
30293046
3030- if (RelnameCmp(&rte->eref->aliasname, &hint->relname) == 0)
3031- return hint;
3047+ if (!alias_hint &&
3048+ RelnameCmp(&rte->eref->aliasname, &hint->relname) == 0)
3049+ alias_hint = hint;
3050+
3051+ /* check the real name for appendrel children */
3052+ if (!real_name_hint &&
3053+ rel && rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
3054+ {
3055+ char *realname = get_rel_name(rte->relid);
3056+
3057+ if (realname && RelnameCmp(&realname, &hint->relname) == 0)
3058+ real_name_hint = hint;
3059+ }
3060+
3061+ /* No more match expected, break */
3062+ if(alias_hint && real_name_hint)
3063+ break;
30323064 }
30333065
3034- return NULL;
3066+ /* real name match precedes alias match */
3067+ if (real_name_hint)
3068+ return real_name_hint;
3069+
3070+ return alias_hint;
30353071 }
30363072
30373073 static ParallelHint *
3038-find_parallel_hint(PlannerInfo *root, Index relid, RelOptInfo *rel)
3074+find_parallel_hint(PlannerInfo *root, Index relid)
30393075 {
3076+ RelOptInfo *rel;
30403077 RangeTblEntry *rte;
3078+ ParallelHint *real_name_hint = NULL;
3079+ ParallelHint *alias_hint = NULL;
30413080 int i;
30423081
3082+ /* This should not be a join rel */
3083+ Assert(relid > 0);
3084+ rel = root->simple_rel_array[relid];
3085+
30433086 /*
3044- * We don't apply scan method hint if the relation is:
3045- * - not a base relation and inheritance children
3046- * - not an ordinary relation (such as join and subquery)
3087+ * This function is called for any RelOptInfo or its inheritance parent if
3088+ * any. If we are called from inheritance planner, the RelOptInfo for the
3089+ * parent of target child relation is not set in the planner info.
3090+ *
3091+ * Otherwise we should check that the reloptinfo is base relation or
3092+ * inheritance children.
30473093 */
3048- if (!rel || rel->rtekind != RTE_RELATION ||
3049- (rel->reloptkind != RELOPT_BASEREL &&
3050- rel->reloptkind != RELOPT_OTHER_MEMBER_REL))
3094+ if (rel &&
3095+ rel->reloptkind != RELOPT_BASEREL &&
3096+ rel->reloptkind != RELOPT_OTHER_MEMBER_REL)
30513097 return NULL;
30523098
3099+ /*
3100+ * This is baserel or appendrel children. We can refer to RangeTblEntry.
3101+ */
30533102 rte = root->simple_rte_array[relid];
3103+ Assert(rte);
30543104
3055- /* We can't force scan method of foreign tables */
3056- if (rte->relkind == RELKIND_FOREIGN_TABLE)
3105+ /* We don't hint on other than relation and foreign tables */
3106+ if (rte->rtekind != RTE_RELATION ||
3107+ rte->relkind == RELKIND_FOREIGN_TABLE)
30573108 return NULL;
30583109
3059- /* Find scan method hint, which matches given names, from the list. */
3110+ /* Find parallel method hint, which matches given names, from the list. */
30603111 for (i = 0; i < current_hint_state->num_hints[HINT_TYPE_PARALLEL]; i++)
30613112 {
30623113 ParallelHint *hint = current_hint_state->parallel_hints[i];
@@ -3065,11 +3116,30 @@ find_parallel_hint(PlannerInfo *root, Index relid, RelOptInfo *rel)
30653116 if (!hint_state_enabled(hint))
30663117 continue;
30673118
3068- if (RelnameCmp(&rte->eref->aliasname, &hint->relname) == 0)
3069- return hint;
3119+ if (!alias_hint &&
3120+ RelnameCmp(&rte->eref->aliasname, &hint->relname) == 0)
3121+ alias_hint = hint;
3122+
3123+ /* check the real name for appendrel children */
3124+ if (!real_name_hint &&
3125+ rel && rel->reloptkind == RELOPT_OTHER_MEMBER_REL)
3126+ {
3127+ char *realname = get_rel_name(rte->relid);
3128+
3129+ if (realname && RelnameCmp(&realname, &hint->relname) == 0)
3130+ real_name_hint = hint;
3131+ }
3132+
3133+ /* No more match expected, break */
3134+ if(alias_hint && real_name_hint)
3135+ break;
30703136 }
30713137
3072- return NULL;
3138+ /* real name match precedes alias match */
3139+ if (real_name_hint)
3140+ return real_name_hint;
3141+
3142+ return alias_hint;
30733143 }
30743144
30753145 /*
@@ -3508,10 +3578,10 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel)
35083578
35093579 /* Find hints for the parent */
35103580 current_hint_state->parent_scan_hint =
3511- find_scan_hint(root, current_hint_state->parent_relid, NULL);
3581+ find_scan_hint(root, current_hint_state->parent_relid);
35123582
35133583 current_hint_state->parent_parallel_hint =
3514- find_parallel_hint(root, current_hint_state->parent_relid, NULL);
3584+ find_parallel_hint(root, current_hint_state->parent_relid);
35153585
35163586 /*
35173587 * If hint is found for the parent, apply it for this child instead
@@ -3560,10 +3630,9 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel)
35603630 }
35613631 }
35623632
3563- if (current_hint_state->parent_scan_hint)
3633+ shint = find_scan_hint(root, rel->relid);
3634+ if (!shint)
35643635 shint = current_hint_state->parent_scan_hint;
3565- else
3566- shint = find_scan_hint(root, rel->relid, rel);
35673636
35683637 if (shint)
35693638 {
@@ -3600,10 +3669,9 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel)
36003669 }
36013670
36023671 /* Do the same for parallel plan enforcement */
3603- if (current_hint_state->parent_parallel_hint)
3672+ phint = find_parallel_hint(root, rel->relid);
3673+ if (!phint)
36043674 phint = current_hint_state->parent_parallel_hint;
3605- else
3606- phint = find_parallel_hint(root, rel->relid, rel);
36073675
36083676 setup_parallel_plan_enforcement(phint, current_hint_state);
36093677
@@ -3613,7 +3681,7 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel)
36133681 if (debug_level > 1)
36143682 ereport(pg_hint_plan_message_level,
36153683 (errhidestmt (true),
3616- errmsg ("pg_hint_plan%s: get_relation_info"
3684+ errmsg ("pg_hint_plan%s: setup_hint_enforcement"
36173685 " no hint applied:"
36183686 " relation=%u(%s), inhparent=%d, current_hint=%p,"
36193687 " hint_inhibit_level=%d, scanmask=0x%x",
@@ -4126,14 +4194,10 @@ add_paths_to_joinrel_wrapper(PlannerInfo *root,
41264194 SpecialJoinInfo *sjinfo,
41274195 List *restrictlist)
41284196 {
4129- ScanMethodHint *scan_hint = NULL;
41304197 Relids joinrelids;
41314198 JoinMethodHint *join_hint;
41324199 int save_nestlevel;
41334200
4134- if ((scan_hint = find_scan_hint(root, innerrel->relid, innerrel)) != NULL)
4135- setup_scan_method_enforcement(scan_hint, current_hint_state);
4136-
41374201 joinrelids = bms_union(outerrel->relids, innerrel->relids);
41384202 join_hint = find_join_hint(joinrelids);
41394203 bms_free(joinrelids);
@@ -4168,10 +4232,6 @@ add_paths_to_joinrel_wrapper(PlannerInfo *root,
41684232 else
41694233 add_paths_to_joinrel(root, joinrel, outerrel, innerrel, jointype,
41704234 sjinfo, restrictlist);
4171-
4172- /* Reset the environment if changed */
4173- if (scan_hint != NULL)
4174- setup_scan_method_enforcement(NULL, current_hint_state);
41754235 }
41764236
41774237 static int
@@ -4316,7 +4376,7 @@ pg_hint_plan_set_rel_pathlist(PlannerInfo * root, RelOptInfo *rel,
43164376
43174377 /* Here, we regenerate paths with the current hint restriction */
43184378
4319- /* Remove prviously paths except dummy rels */
4379+ /* Remove prviously generated paths */
43204380 list_free_deep(rel->pathlist);
43214381 rel->pathlist = NIL;
43224382
@@ -4328,7 +4388,7 @@ pg_hint_plan_set_rel_pathlist(PlannerInfo * root, RelOptInfo *rel,
43284388 * estimated number of workers. Force the requested number of workers if
43294389 * hard mode.
43304390 */
4331- phint = find_parallel_hint(root, rel->relid, rel);
4391+ phint = find_parallel_hint(root, rel->relid);
43324392
43334393 if (phint)
43344394 {
--- a/sql/ut-W.sql
+++ b/sql/ut-W.sql
@@ -8,20 +8,19 @@ SET client_min_messages TO LOG;
88 CREATE TABLE s1.tl (a int);
99 INSERT INTO s1.tl (SELECT a FROM generate_series(0, 100000) a);
1010
11--- Queries on ordinary tables
11+-- Queries on ordinary tables with default setting
1212 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
1313
1414 SET parallel_setup_cost to 0;
1515 SET parallel_tuple_cost to 0;
1616 SET min_parallel_relation_size to 0;
17+SET max_parallel_workers_per_gather to DEFAULT;
18+
1719 /*+Parallel(t1 10)*/
1820 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
1921
2022 /*+Parallel(t1 10 soft)*/
2123 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
22-SET parallel_setup_cost to DEFAULT;
23-SET parallel_tuple_cost to DEFAULT;
24-SET min_parallel_relation_size to DEFAULT;
2524
2625 /*+Parallel(t1 10 hard)*/
2726 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
@@ -39,22 +38,29 @@ SET min_parallel_relation_size to DEFAULT;
3938 /*+Parallel(p1 10 hard)*/
4039 EXPLAIN (COSTS false) SELECT * FROM p1;
4140
42--- hinting on children don't work but enables parallel
41+-- hinting on children makes the whole inheritance parallel
4342 /*+Parallel(p1_c1 10 hard)*/
4443 EXPLAIN (COSTS false) SELECT * FROM p1;
4544
4645
4746 -- Joins
48-EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
47+EXPLAIN (COSTS false) SELECT * FROM p1_c1_c1 join p2_c1_c1 on p1_c1_c1.id = p2_c1_c1.id;
4948
50-/*+Parallel(p1_c1 10 hard)*/
51-EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_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;
51+
52+SET parallel_setup_cost to 0;
53+SET parallel_tuple_cost to 0;
54+SET min_parallel_relation_size to 0;
5255
53-/*+Parallel(p2_c1 10 hard)*/
54-EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
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;
5558
56-/*+Parallel(p1_c1 10 hard) Parallel(p2_c1 10 hard)*/
57-EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_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;
61+
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;
5864
5965
6066 -- Joins on inheritance tables
@@ -63,6 +69,10 @@ SET parallel_tuple_cost to 0;
6369 SET min_parallel_relation_size to 0;
6470 /*+Parallel(p1 10)*/
6571 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
72+
73+/*+Parallel(p1 10)Parallel(p2 0)*/
74+EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
75+
6676 SET parallel_setup_cost to DEFAULT;
6777 SET parallel_tuple_cost to DEFAULT;
6878 SET min_parallel_relation_size to DEFAULT;
@@ -83,31 +93,35 @@ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
8393 /*+Parallel(p1 10 hard) SeqScan(p1) */
8494 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
8595
86--- parallelism is not available for the case
96+-- parallel overrides index scan
8797 /*+Parallel(p1 10 hard) IndexScan(p1) */
8898 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;
89101
90102
91103 -- Parallel on UNION
92104 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
93105
94--- some of the scans are not parallel, so this cannot be parallel
106+-- parallel hinting on any relation enables parallel
95107 SET parallel_setup_cost to 0;
96108 SET parallel_tuple_cost to 0;
97109 SET min_parallel_relation_size to 0;
98110 SET max_parallel_workers_per_gather to 0;
111+
99112 /*+Parallel(p1 10) */
100113 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
101114
102--- all children are parallel, so this can be parallel
103-/*+Parallel(p1 10) Parallel(p2 10) */
115+-- set hint also does
116+/*+Set(max_parallel_workers_per_gather 1)*/
104117 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
118+
119+-- applies largest number of workers on merged parallel paths
105120 SET parallel_setup_cost to DEFAULT;
106121 SET parallel_tuple_cost to DEFAULT;
107122 SET min_parallel_relation_size to DEFAULT;
108-SET max_parallel_workers_per_gather to DEFAULT;
109-
110-/*+Parallel(p1 10 hard)Parallel(p2 10 hard) */
123+SET max_parallel_workers_per_gather to 10;
124+/*+Parallel(p1 5 hard)Parallel(p2 6 hard) */
111125 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
112126
113127
Show on old repository browser