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

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revisionc43304db0df8b36ebf1ad205697789d1ff6c36ff (tree)
Time2017-02-16 17:45:42
AuthorKyotaro Horiguchi <horiguchi.kyotaro@lab....>
CommiterKyotaro Horiguchi

Log Message

Reconsider hinting process

I rethinked the steps of enforcement considering the parallel planning
code. pg_hint_plan_set_rel_pathlist() gets simpler and easier to read
(maybe).

As the result of this patch, some behaviors has been changed.

- False generation of gahter path caused by hinting on the other side

of joins.

- Scan hints work with Parallel hints. (It's useless for the moment,

though).

Change Summary

Incremental Difference

--- a/Makefile
+++ b/Makefile
@@ -7,7 +7,7 @@
77 MODULES = pg_hint_plan
88 HINTPLANVER = 1.2.0
99
10-REGRESS = init base_plan pg_hint_plan ut-init ut-A ut-S ut-J ut-L ut-G ut-R ut-W ut-fdw ut-fini
10+REGRESS = init base_plan pg_hint_plan ut-init ut-A ut-S ut-J ut-L ut-G ut-R ut-fdw ut-W ut-fini
1111
1212 REGRESSION_EXPECTED = expected/init.out expected/base_plan.out expected/pg_hint_plan.out expected/ut-A.out expected/ut-S.out expected/ut-J.out expected/ut-L.out expected/ut-G.out
1313
--- a/doc/pg_hint_plan-ja.html
+++ b/doc/pg_hint_plan-ja.html
@@ -621,11 +621,12 @@ postgres=#
621621 </dl>
622622
623623 <dt><h3>並列実行ヒントとスキャン方式ヒントの関係について</h3></dt>
624-<dd>並列実行ヒントは同じオブジェクトにどのスキャン方式ヒントも指定することができますが、並列実行不能なスキャン方式が選択された場合は並列実行指定は無効になります。</dd>
624+<dd>スキャン方式ヒントは並列実行の部分パスを生成する際にも影響を与えます。今のところ並列実行の部分パスはシーケンシャルスキャンとなるため、シーケンシャルスキャンを禁止すると同じオブジェクトへの並列実行ヒントが無効化されます。</dd>
625625 </dl>
626626
627627 <dt><h3>UNION に対する並列実行ヒント</h3></dt>
628-<dd>並列実行ヒントは同じオブジェクトにどのスキャン方式ヒントも指定することができますが、並列実行不能なスキャン方式が選択された場合は並列実行指定は無効になります。</dd>
628+<dd>UNIONは直下のサブクエリが全て並列実行可能な場合にだけ並列実行を行います。一方ですべてのサブクエリが並列実行可能な場合は、そのうちの一つで並列実行を強制するとコスト比較の結果UNION全体が並列実行されることになります。ただし並列実行ヒントによる並列実行の禁止を行った場合はそのスキャンは並列実行不可となります。
629+</dd>
629630 </dl>
630631
631632 <dt><h3>Set ヒントでの pg_hint_plan 自身の制御変数の設定</h3></dt>
--- a/doc/pg_hint_plan.html
+++ b/doc/pg_hint_plan.html
@@ -437,14 +437,16 @@ IN (SELECT ... {<b>LIMIT | OFFSET</b> ...} ...)
437437 <dd>NoIndexScan hint involes NoIndexOnlyScan.</dd>
438438
439439 <h3>Parallel vs Scan hints</h3>
440-<dd>Although any kind of scan method hint is allowed to be specified with parallel hint on the same object, parallel-unsafe scan methods beat parallel.</dd>
440+<dd>Scan hints also affect building partial paths. Since parallel
441+paths are currently consists of partial sequential scans, inhibiting
442+seqscan beats parallel hint on the same relation.</dd>
441443
442444 <h3>Parallel hint and UNION</h3>
443-<dd>Parallel hint affects on both plain and inheritance tables. While
444-UNION gets parallel only if all underlying subqueries are
445-parallel-safe. So a UNION becomes parallel only when the all
446-underlying subqueries are parallel-safe. There's no means to tell
447-UNION itself to be parallel.</dd>
445+<dd>A UNION can run in parallel only when all underlying subqueries
446+are parallel-safe. Conversely enforcing parallel on any of
447+the subqueries let a parallel-executable UNION run in
448+parallel. Meanwhile, a parallel hint with zero workers makes a scan
449+parallel-inexecutable.</dd>
448450
449451 <h3>Setting pg_hint_plan parameters by Set hints</h3>
450452 <dd><p>pg_hint_plan paramters change the behavior of itself so some parameters doesn't work as expected.</p>
--- a/expected/ut-W.out
+++ b/expected/ut-W.out
@@ -332,6 +332,44 @@ error hint:
332332 SET parallel_setup_cost to DEFAULT;
333333 SET parallel_tuple_cost to DEFAULT;
334334 SET min_parallel_relation_size to DEFAULT;
335+/*+Parallel(p2 8 soft)*/
336+EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
337+LOG: pg_hint_plan:
338+used hint:
339+Parallel(p2 8 soft)
340+not used hint:
341+duplication hint:
342+error hint:
343+
344+ QUERY PLAN
345+-------------------------------------------------
346+ Hash Join
347+ Hash Cond: (p2.id = p1.id)
348+ -> Gather
349+ Workers Planned: 1
350+ -> Append
351+ -> Parallel Seq Scan on p2
352+ -> Parallel Seq Scan on p2_c1
353+ -> Parallel Seq Scan on p2_c2
354+ -> Parallel Seq Scan on p2_c3
355+ -> Parallel Seq Scan on p2_c4
356+ -> Parallel Seq Scan on p2_c1_c1
357+ -> Parallel Seq Scan on p2_c1_c2
358+ -> Parallel Seq Scan on p2_c3_c1
359+ -> Parallel Seq Scan on p2_c3_c2
360+ -> Hash
361+ -> Append
362+ -> Seq Scan on p1
363+ -> Seq Scan on p1_c1
364+ -> Seq Scan on p1_c2
365+ -> Seq Scan on p1_c3
366+ -> Seq Scan on p1_c4
367+ -> Seq Scan on p1_c1_c1
368+ -> Seq Scan on p1_c1_c2
369+ -> Seq Scan on p1_c3_c1
370+ -> Seq Scan on p1_c3_c2
371+(25 rows)
372+
335373 /*+Parallel(p2 8 hard)*/
336374 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
337375 LOG: pg_hint_plan:
@@ -341,8 +379,8 @@ not used hint:
341379 duplication hint:
342380 error hint:
343381
344- QUERY PLAN
345--------------------------------------------------------
382+ QUERY PLAN
383+-------------------------------------------------
346384 Hash Join
347385 Hash Cond: (p2.id = p1.id)
348386 -> Gather
@@ -358,19 +396,17 @@ error hint:
358396 -> Parallel Seq Scan on p2_c3_c1
359397 -> Parallel Seq Scan on p2_c3_c2
360398 -> Hash
361- -> Gather
362- Workers Planned: 1
363- -> Append
364- -> Parallel Seq Scan on p1
365- -> Parallel Seq Scan on p1_c1
366- -> Parallel Seq Scan on p1_c2
367- -> Parallel Seq Scan on p1_c3
368- -> Parallel Seq Scan on p1_c4
369- -> Parallel Seq Scan on p1_c1_c1
370- -> Parallel Seq Scan on p1_c1_c2
371- -> Parallel Seq Scan on p1_c3_c1
372- -> Parallel Seq Scan on p1_c3_c2
373-(27 rows)
399+ -> Append
400+ -> Seq Scan on p1
401+ -> Seq Scan on p1_c1
402+ -> Seq Scan on p1_c2
403+ -> Seq Scan on p1_c3
404+ -> Seq Scan on p1_c4
405+ -> Seq Scan on p1_c1_c1
406+ -> Seq Scan on p1_c1_c2
407+ -> Seq Scan on p1_c3_c1
408+ -> Seq Scan on p1_c3_c2
409+(25 rows)
374410
375411 /*+Parallel(p2 8 hard) Parallel(p1 5 hard) */
376412 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
@@ -425,36 +461,34 @@ not used hint:
425461 duplication hint:
426462 error hint:
427463
428- QUERY PLAN
429--------------------------------------------------------
464+ QUERY PLAN
465+--------------------------------------------------------------
430466 Hash Join
431- Hash Cond: (p1.id = p2.id)
432- -> Gather
433- Workers Planned: 8
434- -> Append
435- -> Parallel Seq Scan on p1
436- -> Parallel Seq Scan on p1_c1
437- -> Parallel Seq Scan on p1_c2
438- -> Parallel Seq Scan on p1_c3
439- -> Parallel Seq Scan on p1_c4
440- -> Parallel Seq Scan on p1_c1_c1
441- -> Parallel Seq Scan on p1_c1_c2
442- -> Parallel Seq Scan on p1_c3_c1
443- -> Parallel Seq Scan on p1_c3_c2
467+ Hash Cond: (p2.id = p1.id)
468+ -> Append
469+ -> Index Scan using p2_id2_val on p2
470+ -> Index Scan using p2_c1_id2_val on p2_c1
471+ -> Index Scan using p2_c2_id2_val on p2_c2
472+ -> Index Scan using p2_c3_id_val_idx on p2_c3
473+ -> Index Scan using p2_c4_id_val_idx on p2_c4
474+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
475+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
476+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
477+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
444478 -> Hash
445479 -> Gather
446- Workers Planned: 1
480+ Workers Planned: 8
447481 -> Append
448- -> Parallel Seq Scan on p2
449- -> Parallel Seq Scan on p2_c1
450- -> Parallel Seq Scan on p2_c2
451- -> Parallel Seq Scan on p2_c3
452- -> Parallel Seq Scan on p2_c4
453- -> Parallel Seq Scan on p2_c1_c1
454- -> Parallel Seq Scan on p2_c1_c2
455- -> Parallel Seq Scan on p2_c3_c1
456- -> Parallel Seq Scan on p2_c3_c2
457-(27 rows)
482+ -> Parallel Seq Scan on p1
483+ -> Parallel Seq Scan on p1_c1
484+ -> Parallel Seq Scan on p1_c2
485+ -> Parallel Seq Scan on p1_c3
486+ -> Parallel Seq Scan on p1_c4
487+ -> Parallel Seq Scan on p1_c1_c1
488+ -> Parallel Seq Scan on p1_c1_c2
489+ -> Parallel Seq Scan on p1_c3_c1
490+ -> Parallel Seq Scan on p1_c3_c2
491+(25 rows)
458492
459493 -- seqscan doesn't harm parallelism
460494 /*+Parallel(p1 8 hard) SeqScan(p1) */
@@ -467,8 +501,8 @@ not used hint:
467501 duplication hint:
468502 error hint:
469503
470- QUERY PLAN
471--------------------------------------------------------
504+ QUERY PLAN
505+-------------------------------------------------
472506 Hash Join
473507 Hash Cond: (p1.id = p2.id)
474508 -> Gather
@@ -484,21 +518,19 @@ error hint:
484518 -> Parallel Seq Scan on p1_c3_c1
485519 -> Parallel Seq Scan on p1_c3_c2
486520 -> Hash
487- -> Gather
488- Workers Planned: 1
489- -> Append
490- -> Parallel Seq Scan on p2
491- -> Parallel Seq Scan on p2_c1
492- -> Parallel Seq Scan on p2_c2
493- -> Parallel Seq Scan on p2_c3
494- -> Parallel Seq Scan on p2_c4
495- -> Parallel Seq Scan on p2_c1_c1
496- -> Parallel Seq Scan on p2_c1_c2
497- -> Parallel Seq Scan on p2_c3_c1
498- -> Parallel Seq Scan on p2_c3_c2
499-(27 rows)
521+ -> Append
522+ -> Seq Scan on p2
523+ -> Seq Scan on p2_c1
524+ -> Seq Scan on p2_c2
525+ -> Seq Scan on p2_c3
526+ -> Seq Scan on p2_c4
527+ -> Seq Scan on p2_c1_c1
528+ -> Seq Scan on p2_c1_c2
529+ -> Seq Scan on p2_c3_c1
530+ -> Seq Scan on p2_c3_c2
531+(25 rows)
500532
501--- parallel overrides index scan
533+-- we don't have parallel over index scans so far
502534 /*+Parallel(p1 8 hard) IndexScan(p1) */
503535 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
504536 LOG: pg_hint_plan:
@@ -509,36 +541,32 @@ not used hint:
509541 duplication hint:
510542 error hint:
511543
512- QUERY PLAN
513--------------------------------------------------------
544+ QUERY PLAN
545+--------------------------------------------------------
514546 Hash Join
515547 Hash Cond: (p1.id = p2.id)
516- -> Gather
517- Workers Planned: 8
518- -> Append
519- -> Parallel Seq Scan on p1
520- -> Parallel Seq Scan on p1_c1
521- -> Parallel Seq Scan on p1_c2
522- -> Parallel Seq Scan on p1_c3
523- -> Parallel Seq Scan on p1_c4
524- -> Parallel Seq Scan on p1_c1_c1
525- -> Parallel Seq Scan on p1_c1_c2
526- -> Parallel Seq Scan on p1_c3_c1
527- -> Parallel Seq Scan on p1_c3_c2
548+ -> Append
549+ -> Index Scan using p1_pkey on p1
550+ -> Index Scan using p1_c1_pkey on p1_c1
551+ -> Index Scan using p1_c2_pkey on p1_c2
552+ -> Index Scan using p1_c3_pkey on p1_c3
553+ -> Index Scan using p1_c4_pkey on p1_c4
554+ -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
555+ -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
556+ -> Index Scan using p1_c3_c1_pkey on p1_c3_c1
557+ -> Index Scan using p1_c3_c2_pkey on p1_c3_c2
528558 -> Hash
529- -> Gather
530- Workers Planned: 1
531- -> Append
532- -> Parallel Seq Scan on p2
533- -> Parallel Seq Scan on p2_c1
534- -> Parallel Seq Scan on p2_c2
535- -> Parallel Seq Scan on p2_c3
536- -> Parallel Seq Scan on p2_c4
537- -> Parallel Seq Scan on p2_c1_c1
538- -> Parallel Seq Scan on p2_c1_c2
539- -> Parallel Seq Scan on p2_c3_c1
540- -> Parallel Seq Scan on p2_c3_c2
541-(27 rows)
559+ -> Append
560+ -> Seq Scan on p2
561+ -> Seq Scan on p2_c1
562+ -> Seq Scan on p2_c2
563+ -> Seq Scan on p2_c3
564+ -> Seq Scan on p2_c4
565+ -> Seq Scan on p2_c1_c1
566+ -> Seq Scan on p2_c1_c2
567+ -> Seq Scan on p2_c3_c1
568+ -> Seq Scan on p2_c3_c2
569+(23 rows)
542570
543571 /*+Parallel(p1 0 hard) IndexScan(p1) */
544572 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
@@ -641,7 +669,7 @@ error hint:
641669 -> Parallel Seq Scan on p2_c3_c2
642670 (21 rows)
643671
644--- set hint also does
672+-- set hint does the same thing
645673 /*+Set(max_parallel_workers_per_gather 1)*/
646674 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
647675 LOG: pg_hint_plan:
@@ -721,6 +749,7 @@ SET parallel_setup_cost to 0;
721749 SET parallel_tuple_cost to 0;
722750 SET min_parallel_relation_size to 0;
723751 SET max_parallel_workers_per_gather to 3;
752+SET enable_indexscan to false;
724753 /*+Parallel(p1 8 hard) */
725754 EXPLAIN (COSTS false) SELECT * FROM p1 join t1 on p1.id = t1.id;
726755 LOG: pg_hint_plan:
@@ -753,6 +782,7 @@ error hint:
753782 (18 rows)
754783
755784 -- Negative hint
785+SET enable_indexscan to DEFAULT;
756786 SET parallel_setup_cost to 0;
757787 SET parallel_tuple_cost to 0;
758788 SET min_parallel_relation_size to 0;
@@ -850,6 +880,68 @@ Parallel()
850880 -> Parallel Seq Scan on p2_c3_c2
851881 (21 rows)
852882
883+-- Hints on unhintable relations are just ignored
884+/*+Parallel(p1 5 hard) Parallel(s1 3 hard) IndexScan(ft1) SeqScan(cte1)
885+ TidScan(fs1) IndexScan(t) IndexScan(*VALUES*) */
886+EXPLAIN (COSTS false) SELECT id FROM p1_c1_c1 as s1 TABLESAMPLE SYSTEM(10)
887+ UNION ALL
888+SELECT id FROM ft1
889+ UNION ALL
890+(WITH cte1 AS (SELECT id FROM p1 WHERE id % 2 = 0) SELECT id FROM cte1)
891+ UNION ALL
892+SELECT userid FROM pg_stat_statements fs1
893+ UNION ALL
894+SELECT x FROM (VALUES (1), (2), (3)) t(x);
895+LOG: pg_hint_plan:
896+used hint:
897+Parallel(p1 5 hard)
898+not used hint:
899+IndexScan(*VALUES*)
900+SeqScan(cte1)
901+TidScan(fs1)
902+IndexScan(ft1)
903+IndexScan(t)
904+Parallel(s1 3 hard)
905+duplication hint:
906+error hint:
907+
908+ QUERY PLAN
909+-----------------------------------------------------------------------------------------------
910+ Append
911+ -> Result
912+ -> Append
913+ -> Sample Scan on p1_c1_c1 s1
914+ Sampling: system ('10'::real)
915+ -> Foreign Scan on ft1
916+ Foreign File: /home/horiguti/work/pg_hint_plan/pg_hint_plan/data/data.csv
917+ -> CTE Scan on cte1
918+ CTE cte1
919+ -> Gather
920+ Workers Planned: 5
921+ -> Append
922+ -> Parallel Seq Scan on p1
923+ Filter: ((id % 2) = 0)
924+ -> Parallel Seq Scan on p1_c1
925+ Filter: ((id % 2) = 0)
926+ -> Parallel Seq Scan on p1_c2
927+ Filter: ((id % 2) = 0)
928+ -> Parallel Seq Scan on p1_c3
929+ Filter: ((id % 2) = 0)
930+ -> Parallel Seq Scan on p1_c4
931+ Filter: ((id % 2) = 0)
932+ -> Parallel Seq Scan on p1_c1_c1
933+ Filter: ((id % 2) = 0)
934+ -> Parallel Seq Scan on p1_c1_c2
935+ Filter: ((id % 2) = 0)
936+ -> Parallel Seq Scan on p1_c3_c1
937+ Filter: ((id % 2) = 0)
938+ -> Parallel Seq Scan on p1_c3_c2
939+ Filter: ((id % 2) = 0)
940+ -> Function Scan on pg_stat_statements
941+ -> Subquery Scan on "*SELECT* 5"
942+ -> Values Scan on "*VALUES*"
943+(33 rows)
944+
853945 ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
854946 SELECT pg_reload_conf();
855947 pg_reload_conf
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -184,6 +184,12 @@ typedef enum HintType
184184 HINT_TYPE_PARALLEL
185185 } HintType;
186186
187+typedef enum HintTypeBitmap
188+{
189+ HINT_BM_SCAN_METHOD = 1,
190+ HINT_BM_PARALLEL = 2
191+} HintTypeBitmap;
192+
187193 static const char *HintTypeName[] = {
188194 "scan method",
189195 "join method",
@@ -3512,10 +3518,13 @@ reset_hint_enforcement()
35123518 }
35133519
35143520 /*
3515- * Set planner guc parameters according to corresponding scan hints.
3521+ * Set planner guc parameters according to corresponding scan hints. Returns
3522+ * bitmap of HintTypeBitmap. If shint or phint is not NULL, set used hint
3523+ * there respectively.
35163524 */
35173525 static bool
3518-setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel)
3526+setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel,
3527+ ScanMethodHint **rshint, ParallelHint **rphint)
35193528 {
35203529 Index new_parent_relid = 0;
35213530 ListCell *l;
@@ -3523,6 +3532,11 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel)
35233532 ParallelHint *phint = NULL;
35243533 bool inhparent = root->simple_rte_array[rel->relid]->inh;
35253534 Oid relationObjectId = root->simple_rte_array[rel->relid]->relid;
3535+ int ret = 0;
3536+
3537+ /* reset returns if requested */
3538+ if (rshint != NULL) *rshint = NULL;
3539+ if (rphint != NULL) *rphint = NULL;
35263540
35273541 /*
35283542 * We could register the parent relation of the following children here
@@ -3542,7 +3556,7 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel)
35423556 qnostr, relationObjectId,
35433557 get_rel_name(relationObjectId),
35443558 inhparent, current_hint_state, hint_inhibit_level)));
3545- return false;
3559+ return 0;
35463560 }
35473561
35483562 /* Find the parent for this relation other than the registered parent */
@@ -3638,6 +3652,8 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel)
36383652 bool using_parent_hint =
36393653 (shint == current_hint_state->parent_scan_hint);
36403654
3655+ ret |= HINT_BM_SCAN_METHOD;
3656+
36413657 /* Setup scan enforcement environment */
36423658 setup_scan_method_enforcement(shint, current_hint_state);
36433659
@@ -3674,6 +3690,9 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel)
36743690
36753691 setup_parallel_plan_enforcement(phint, current_hint_state);
36763692
3693+ if (phint)
3694+ ret |= HINT_BM_PARALLEL;
3695+
36773696 /* Nothing to apply. Reset the scan mask to intial state */
36783697 if (!shint && ! phint)
36793698 {
@@ -3691,10 +3710,13 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel)
36913710
36923711 setup_scan_method_enforcement(NULL, current_hint_state);
36933712
3694- return false;
3713+ return ret;
36953714 }
36963715
3697- return true;
3716+ if (rshint != NULL) *rshint = shint;
3717+ if (rphint != NULL) *rphint = phint;
3718+
3719+ return ret;
36983720 }
36993721
37003722 /*
@@ -4328,95 +4350,118 @@ pg_hint_plan_set_rel_pathlist(PlannerInfo * root, RelOptInfo *rel,
43284350 {
43294351 ParallelHint *phint;
43304352 ListCell *l;
4353+ int found_hints;
43314354
43324355 /* call the previous hook */
43334356 if (prev_set_rel_pathlist)
43344357 prev_set_rel_pathlist(root, rel, rti, rte);
43354358
4336- /* Nothing to do when hint has not been parsed yet */
4359+ /* Nothing to do if no hint available */
43374360 if (current_hint_state == NULL)
43384361 return;
43394362
4340- /* Don't touch dummy rel */
4363+ /* Don't touch dummy rels. */
43414364 if (IS_DUMMY_REL(rel))
43424365 return;
43434366
4367+ /*
4368+ * We can accept only plain relations, foreign tables and table saples are
4369+ * also unacceptable. See set_rel_pathlist.
4370+ */
4371+ if (rel->rtekind != RTE_RELATION ||
4372+ rte->relkind == RELKIND_FOREIGN_TABLE ||
4373+ rte->tablesample != NULL)
4374+ return;
4375+
43444376 /* We cannot handle if this requires an outer */
43454377 if (rel->lateral_relids)
43464378 return;
43474379
4348- if (!setup_hint_enforcement(root, rel))
4380+ /* Return if this relation gets no enfocement */
4381+ if ((found_hints = setup_hint_enforcement(root, rel, NULL, &phint)) == 0)
4382+ return;
4383+
4384+ /* Here, we regenerate paths with the current hint restriction */
4385+
4386+ if (found_hints & HINT_BM_SCAN_METHOD)
43494387 {
43504388 /*
4351- * No enforcement requested, but we might have to generate gather path
4352- * on this relation. We could regenerate gather for relations not
4353- * getting enforcement or even relations other than ordinary ones.
4389+ * With scan hints, we regenerate paths for this relation from the
4390+ * first under the restricion.
43544391 */
4392+ list_free_deep(rel->pathlist);
4393+ rel->pathlist = NIL;
43554394
4356- /* If no need of a gather path, just return */
4357- if (rel->reloptkind != RELOPT_BASEREL || max_hint_nworkers < 1 ||
4358- rel->partial_pathlist == NIL)
4359- return;
4395+ set_plain_rel_pathlist(root, rel, rte);
4396+ }
4397+
4398+ if (found_hints & HINT_BM_PARALLEL)
4399+ {
4400+ Assert (phint);
43604401
4361- /* Lower the priorities of existing paths, then add a new path */
4362- foreach (l, rel->pathlist)
4402+ /* the partial_pathlist may be for different parameters, discard it */
4403+ if (rel->partial_pathlist)
43634404 {
4364- Path *path = (Path *) lfirst(l);
4405+ list_free_deep(rel->partial_pathlist);
4406+ rel->partial_pathlist = NIL;
4407+ }
4408+
4409+ /* also remove gather path */
4410+ if (rel->pathlist)
4411+ {
4412+ ListCell *cell, *prev = NULL;
43654413
4366- if (path->startup_cost < disable_cost)
4414+ foreach (cell, rel->pathlist)
43674415 {
4368- path->startup_cost += disable_cost;
4369- path->total_cost += disable_cost;
4416+ Path *path = (Path *) lfirst(cell);
4417+
4418+ if (IsA(path, GatherPath))
4419+ rel->pathlist = list_delete_cell(rel->pathlist,
4420+ cell, prev);
4421+ else
4422+ prev = cell;
43704423 }
43714424 }
43724425
4373- generate_gather_paths(root, rel);
4374- return;
4375- }
4376-
4377- /* Don't touch other than ordinary relation hereafter */
4378- if (rte->rtekind != RTE_RELATION)
4379- return;
4380-
4381- /* Here, we regenerate paths with the current hint restriction */
4382-
4383- /* Remove prviously generated paths */
4384- list_free_deep(rel->pathlist);
4385- rel->pathlist = NIL;
4386-
4387- /* Rebuild access paths */
4388- set_plain_rel_pathlist(root, rel, rte);
4426+ /* then generate new paths if needed */
4427+ if (phint->nworkers > 0)
4428+ {
4429+ /* Lower the priorities of non-parallel paths */
4430+ foreach (l, rel->pathlist)
4431+ {
4432+ Path *path = (Path *) lfirst(l);
43894433
4390- /*
4391- * create_plain_partial_paths creates partial paths with reasonably
4392- * estimated number of workers. Force the requested number of workers if
4393- * hard mode.
4394- */
4395- phint = find_parallel_hint(root, rel->relid);
4434+ if (path->startup_cost < disable_cost)
4435+ {
4436+ path->startup_cost += disable_cost;
4437+ path->total_cost += disable_cost;
4438+ }
4439+ }
43964440
4397- if (phint)
4398- {
4399- /* if inhibiting parallel, remove existing partial paths */
4400- if (phint->nworkers == 0 && rel->partial_pathlist)
4401- {
4402- list_free_deep(rel->partial_pathlist);
4403- rel->partial_pathlist = NIL;
4404- }
4441+ /*
4442+ * generate partial paths with enforcement, this is affected by
4443+ * scan method enforcement. Specifically, the cost of this partial
4444+ * seqscan path will be disabled_cost if seqscan is inhibited by
4445+ * hint or GUC parameters.
4446+ */
4447+ Assert (rel->partial_pathlist == NIL);
4448+ create_plain_partial_paths(root, rel);
44054449
4406- /* enforce number of workers if requested */
4407- if (rel->partial_pathlist && phint->force_parallel)
4408- {
4409- foreach (l, rel->partial_pathlist)
4450+ /* enforce number of workers if requested */
4451+ if (phint->force_parallel)
44104452 {
4411- Path *ppath = (Path *) lfirst(l);
4453+ foreach (l, rel->partial_pathlist)
4454+ {
4455+ Path *ppath = (Path *) lfirst(l);
44124456
4413- ppath->parallel_workers = phint->nworkers;
4457+ ppath->parallel_workers = phint->nworkers;
4458+ }
44144459 }
4415- }
44164460
4417- /* Generate gather paths for base rels */
4418- if (rel->reloptkind == RELOPT_BASEREL)
4419- generate_gather_paths(root, rel);
4461+ /* Generate gather paths for base rels */
4462+ if (rel->reloptkind == RELOPT_BASEREL)
4463+ generate_gather_paths(root, rel);
4464+ }
44204465 }
44214466
44224467 reset_hint_enforcement();
--- a/sql/ut-W.sql
+++ b/sql/ut-W.sql
@@ -77,6 +77,9 @@ SET parallel_setup_cost to DEFAULT;
7777 SET parallel_tuple_cost to DEFAULT;
7878 SET min_parallel_relation_size to DEFAULT;
7979
80+/*+Parallel(p2 8 soft)*/
81+EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
82+
8083 /*+Parallel(p2 8 hard)*/
8184 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
8285
@@ -93,7 +96,7 @@ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
9396 /*+Parallel(p1 8 hard) SeqScan(p1) */
9497 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
9598
96--- parallel overrides index scan
99+-- we don't have parallel over index scans so far
97100 /*+Parallel(p1 8 hard) IndexScan(p1) */
98101 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
99102 /*+Parallel(p1 0 hard) IndexScan(p1) */
@@ -112,7 +115,7 @@ SET max_parallel_workers_per_gather to 0;
112115 /*+Parallel(p1 8) */
113116 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
114117
115--- set hint also does
118+-- set hint does the same thing
116119 /*+Set(max_parallel_workers_per_gather 1)*/
117120 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
118121
@@ -130,11 +133,13 @@ SET parallel_setup_cost to 0;
130133 SET parallel_tuple_cost to 0;
131134 SET min_parallel_relation_size to 0;
132135 SET max_parallel_workers_per_gather to 3;
136+SET enable_indexscan to false;
133137
134138 /*+Parallel(p1 8 hard) */
135139 EXPLAIN (COSTS false) SELECT * FROM p1 join t1 on p1.id = t1.id;
136140
137141 -- Negative hint
142+SET enable_indexscan to DEFAULT;
138143 SET parallel_setup_cost to 0;
139144 SET parallel_tuple_cost to 0;
140145 SET min_parallel_relation_size to 0;
@@ -149,5 +154,19 @@ EXPLAIN (COSTS false) SELECT * FROM p1;
149154 Parallel(p1 8 hoge)Parallel(p1)Parallel(p1 100 soft x)*/
150155 EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
151156
157+-- Hints on unhintable relations are just ignored
158+/*+Parallel(p1 5 hard) Parallel(s1 3 hard) IndexScan(ft1) SeqScan(cte1)
159+ TidScan(fs1) IndexScan(t) IndexScan(*VALUES*) */
160+EXPLAIN (COSTS false) SELECT id FROM p1_c1_c1 as s1 TABLESAMPLE SYSTEM(10)
161+ UNION ALL
162+SELECT id FROM ft1
163+ UNION ALL
164+(WITH cte1 AS (SELECT id FROM p1 WHERE id % 2 = 0) SELECT id FROM cte1)
165+ UNION ALL
166+SELECT userid FROM pg_stat_statements fs1
167+ UNION ALL
168+SELECT x FROM (VALUES (1), (2), (3)) t(x);
169+
170+
152171 ALTER SYSTEM SET session_preload_libraries TO DEFAULT;
153172 SELECT pg_reload_conf();
Show on old repository browser