From 0073a061f7632e270cebefd5c92b58954b4aea8a Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Thu, 16 Feb 2017 17:41:30 +0900 Subject: [PATCH] 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). --- Makefile | 2 +- doc/pg_hint_plan-ja.html | 5 +- doc/pg_hint_plan.html | 14 +-- expected/ut-W.out | 262 ++++++++++++++++++++++++++++++++--------------- pg_hint_plan.c | 167 +++++++++++++++++++----------- sql/ut-W.sql | 23 ++++- 6 files changed, 316 insertions(+), 157 deletions(-) diff --git a/Makefile b/Makefile index 7d79966..38058b2 100644 --- a/Makefile +++ b/Makefile @@ -7,7 +7,7 @@ MODULES = pg_hint_plan HINTPLANVER = 1.2.0 -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 +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 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 diff --git a/doc/pg_hint_plan-ja.html b/doc/pg_hint_plan-ja.html index 9c74b26..665d8c4 100755 --- a/doc/pg_hint_plan-ja.html +++ b/doc/pg_hint_plan-ja.html @@ -621,11 +621,12 @@ postgres=#

並列実行ヒントとスキャン方式ヒントの関係について

-
並列実行ヒントは同じオブジェクトにどのスキャン方式ヒントも指定することができますが、並列実行不能なスキャン方式が選択された場合は並列実行指定は無効になります。
+
スキャン方式ヒントは並列実行の部分パスを生成する際にも影響を与えます。今のところ並列実行の部分パスはシーケンシャルスキャンとなるため、シーケンシャルスキャンを禁止すると同じオブジェクトへの並列実行ヒントが無効化されます。

UNION に対する並列実行ヒント

-
並列実行ヒントは同じオブジェクトにどのスキャン方式ヒントも指定することができますが、並列実行不能なスキャン方式が選択された場合は並列実行指定は無効になります。
+
UNIONは直下のサブクエリが全て並列実行可能な場合にだけ並列実行を行います。一方ですべてのサブクエリが並列実行可能な場合は、そのうちの一つで並列実行を強制するとコスト比較の結果UNION全体が並列実行されることになります。ただし並列実行ヒントによる並列実行の禁止を行った場合はそのスキャンは並列実行不可となります。 +

Set ヒントでの pg_hint_plan 自身の制御変数の設定

diff --git a/doc/pg_hint_plan.html b/doc/pg_hint_plan.html index 8e6932f..ff4ac9c 100755 --- a/doc/pg_hint_plan.html +++ b/doc/pg_hint_plan.html @@ -437,14 +437,16 @@ IN (SELECT ... {LIMIT | OFFSET ...} ...)
NoIndexScan hint involes NoIndexOnlyScan.

Parallel vs Scan hints

-
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.
+
Scan hints also affect building partial paths. Since parallel +paths are currently consists of partial sequential scans, inhibiting +seqscan beats parallel hint on the same relation.

Parallel hint and UNION

-
Parallel hint affects on both plain and inheritance tables. While -UNION gets parallel only if all underlying subqueries are -parallel-safe. So a UNION becomes parallel only when the all -underlying subqueries are parallel-safe. There's no means to tell -UNION itself to be parallel.
+
A UNION can run in parallel only when all underlying subqueries +are parallel-safe. Conversely enforcing parallel on any of +the subqueries let a parallel-executable UNION run in +parallel. Meanwhile, a parallel hint with zero workers makes a scan +parallel-inexecutable.

Setting pg_hint_plan parameters by Set hints

pg_hint_plan paramters change the behavior of itself so some parameters doesn't work as expected.

diff --git a/expected/ut-W.out b/expected/ut-W.out index 20c1650..0828d14 100644 --- a/expected/ut-W.out +++ b/expected/ut-W.out @@ -332,6 +332,44 @@ error hint: SET parallel_setup_cost to DEFAULT; SET parallel_tuple_cost to DEFAULT; SET min_parallel_relation_size to DEFAULT; +/*+Parallel(p2 8 soft)*/ +EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; +LOG: pg_hint_plan: +used hint: +Parallel(p2 8 soft) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------- + Hash Join + Hash Cond: (p2.id = p1.id) + -> Gather + Workers Planned: 1 + -> Append + -> Parallel Seq Scan on p2 + -> Parallel Seq Scan on p2_c1 + -> Parallel Seq Scan on p2_c2 + -> Parallel Seq Scan on p2_c3 + -> Parallel Seq Scan on p2_c4 + -> Parallel Seq Scan on p2_c1_c1 + -> Parallel Seq Scan on p2_c1_c2 + -> Parallel Seq Scan on p2_c3_c1 + -> Parallel Seq Scan on p2_c3_c2 + -> Hash + -> Append + -> Seq Scan on p1 + -> Seq Scan on p1_c1 + -> Seq Scan on p1_c2 + -> Seq Scan on p1_c3 + -> Seq Scan on p1_c4 + -> Seq Scan on p1_c1_c1 + -> Seq Scan on p1_c1_c2 + -> Seq Scan on p1_c3_c1 + -> Seq Scan on p1_c3_c2 +(25 rows) + /*+Parallel(p2 8 hard)*/ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; LOG: pg_hint_plan: @@ -341,8 +379,8 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +------------------------------------------------- Hash Join Hash Cond: (p2.id = p1.id) -> Gather @@ -358,19 +396,17 @@ error hint: -> Parallel Seq Scan on p2_c3_c1 -> Parallel Seq Scan on p2_c3_c2 -> Hash - -> Gather - Workers Planned: 1 - -> Append - -> Parallel Seq Scan on p1 - -> Parallel Seq Scan on p1_c1 - -> Parallel Seq Scan on p1_c2 - -> Parallel Seq Scan on p1_c3 - -> Parallel Seq Scan on p1_c4 - -> Parallel Seq Scan on p1_c1_c1 - -> Parallel Seq Scan on p1_c1_c2 - -> Parallel Seq Scan on p1_c3_c1 - -> Parallel Seq Scan on p1_c3_c2 -(27 rows) + -> Append + -> Seq Scan on p1 + -> Seq Scan on p1_c1 + -> Seq Scan on p1_c2 + -> Seq Scan on p1_c3 + -> Seq Scan on p1_c4 + -> Seq Scan on p1_c1_c1 + -> Seq Scan on p1_c1_c2 + -> Seq Scan on p1_c3_c1 + -> Seq Scan on p1_c3_c2 +(25 rows) /*+Parallel(p2 8 hard) Parallel(p1 5 hard) */ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; @@ -425,36 +461,34 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------------- Hash Join - Hash Cond: (p1.id = p2.id) - -> Gather - Workers Planned: 8 - -> Append - -> Parallel Seq Scan on p1 - -> Parallel Seq Scan on p1_c1 - -> Parallel Seq Scan on p1_c2 - -> Parallel Seq Scan on p1_c3 - -> Parallel Seq Scan on p1_c4 - -> Parallel Seq Scan on p1_c1_c1 - -> Parallel Seq Scan on p1_c1_c2 - -> Parallel Seq Scan on p1_c3_c1 - -> Parallel Seq Scan on p1_c3_c2 + Hash Cond: (p2.id = p1.id) + -> Append + -> Index Scan using p2_id2_val on p2 + -> Index Scan using p2_c1_id2_val on p2_c1 + -> Index Scan using p2_c2_id2_val on p2_c2 + -> Index Scan using p2_c3_id_val_idx on p2_c3 + -> Index Scan using p2_c4_id_val_idx on p2_c4 + -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 + -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 + -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1 + -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2 -> Hash -> Gather - Workers Planned: 1 + Workers Planned: 8 -> Append - -> Parallel Seq Scan on p2 - -> Parallel Seq Scan on p2_c1 - -> Parallel Seq Scan on p2_c2 - -> Parallel Seq Scan on p2_c3 - -> Parallel Seq Scan on p2_c4 - -> Parallel Seq Scan on p2_c1_c1 - -> Parallel Seq Scan on p2_c1_c2 - -> Parallel Seq Scan on p2_c3_c1 - -> Parallel Seq Scan on p2_c3_c2 -(27 rows) + -> Parallel Seq Scan on p1 + -> Parallel Seq Scan on p1_c1 + -> Parallel Seq Scan on p1_c2 + -> Parallel Seq Scan on p1_c3 + -> Parallel Seq Scan on p1_c4 + -> Parallel Seq Scan on p1_c1_c1 + -> Parallel Seq Scan on p1_c1_c2 + -> Parallel Seq Scan on p1_c3_c1 + -> Parallel Seq Scan on p1_c3_c2 +(25 rows) -- seqscan doesn't harm parallelism /*+Parallel(p1 8 hard) SeqScan(p1) */ @@ -467,8 +501,8 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +------------------------------------------------- Hash Join Hash Cond: (p1.id = p2.id) -> Gather @@ -484,21 +518,19 @@ error hint: -> Parallel Seq Scan on p1_c3_c1 -> Parallel Seq Scan on p1_c3_c2 -> Hash - -> Gather - Workers Planned: 1 - -> Append - -> Parallel Seq Scan on p2 - -> Parallel Seq Scan on p2_c1 - -> Parallel Seq Scan on p2_c2 - -> Parallel Seq Scan on p2_c3 - -> Parallel Seq Scan on p2_c4 - -> Parallel Seq Scan on p2_c1_c1 - -> Parallel Seq Scan on p2_c1_c2 - -> Parallel Seq Scan on p2_c3_c1 - -> Parallel Seq Scan on p2_c3_c2 -(27 rows) + -> Append + -> Seq Scan on p2 + -> Seq Scan on p2_c1 + -> Seq Scan on p2_c2 + -> Seq Scan on p2_c3 + -> Seq Scan on p2_c4 + -> Seq Scan on p2_c1_c1 + -> Seq Scan on p2_c1_c2 + -> Seq Scan on p2_c3_c1 + -> Seq Scan on p2_c3_c2 +(25 rows) --- parallel overrides index scan +-- we don't have parallel over index scans so far /*+Parallel(p1 8 hard) IndexScan(p1) */ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; LOG: pg_hint_plan: @@ -509,36 +541,32 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------- + QUERY PLAN +-------------------------------------------------------- Hash Join Hash Cond: (p1.id = p2.id) - -> Gather - Workers Planned: 8 - -> Append - -> Parallel Seq Scan on p1 - -> Parallel Seq Scan on p1_c1 - -> Parallel Seq Scan on p1_c2 - -> Parallel Seq Scan on p1_c3 - -> Parallel Seq Scan on p1_c4 - -> Parallel Seq Scan on p1_c1_c1 - -> Parallel Seq Scan on p1_c1_c2 - -> Parallel Seq Scan on p1_c3_c1 - -> Parallel Seq Scan on p1_c3_c2 + -> Append + -> Index Scan using p1_pkey on p1 + -> Index Scan using p1_c1_pkey on p1_c1 + -> Index Scan using p1_c2_pkey on p1_c2 + -> Index Scan using p1_c3_pkey on p1_c3 + -> Index Scan using p1_c4_pkey on p1_c4 + -> Index Scan using p1_c1_c1_pkey on p1_c1_c1 + -> Index Scan using p1_c1_c2_pkey on p1_c1_c2 + -> Index Scan using p1_c3_c1_pkey on p1_c3_c1 + -> Index Scan using p1_c3_c2_pkey on p1_c3_c2 -> Hash - -> Gather - Workers Planned: 1 - -> Append - -> Parallel Seq Scan on p2 - -> Parallel Seq Scan on p2_c1 - -> Parallel Seq Scan on p2_c2 - -> Parallel Seq Scan on p2_c3 - -> Parallel Seq Scan on p2_c4 - -> Parallel Seq Scan on p2_c1_c1 - -> Parallel Seq Scan on p2_c1_c2 - -> Parallel Seq Scan on p2_c3_c1 - -> Parallel Seq Scan on p2_c3_c2 -(27 rows) + -> Append + -> Seq Scan on p2 + -> Seq Scan on p2_c1 + -> Seq Scan on p2_c2 + -> Seq Scan on p2_c3 + -> Seq Scan on p2_c4 + -> Seq Scan on p2_c1_c1 + -> Seq Scan on p2_c1_c2 + -> Seq Scan on p2_c3_c1 + -> Seq Scan on p2_c3_c2 +(23 rows) /*+Parallel(p1 0 hard) IndexScan(p1) */ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; @@ -641,7 +669,7 @@ error hint: -> Parallel Seq Scan on p2_c3_c2 (21 rows) --- set hint also does +-- set hint does the same thing /*+Set(max_parallel_workers_per_gather 1)*/ EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2; LOG: pg_hint_plan: @@ -721,6 +749,7 @@ SET parallel_setup_cost to 0; SET parallel_tuple_cost to 0; SET min_parallel_relation_size to 0; SET max_parallel_workers_per_gather to 3; +SET enable_indexscan to false; /*+Parallel(p1 8 hard) */ EXPLAIN (COSTS false) SELECT * FROM p1 join t1 on p1.id = t1.id; LOG: pg_hint_plan: @@ -753,6 +782,7 @@ error hint: (18 rows) -- Negative hint +SET enable_indexscan to DEFAULT; SET parallel_setup_cost to 0; SET parallel_tuple_cost to 0; SET min_parallel_relation_size to 0; @@ -850,6 +880,68 @@ Parallel() -> Parallel Seq Scan on p2_c3_c2 (21 rows) +-- Hints on unhintable relations are just ignored +/*+Parallel(p1 5 hard) Parallel(s1 3 hard) IndexScan(ft1) SeqScan(cte1) + TidScan(fs1) IndexScan(t) IndexScan(*VALUES*) */ +EXPLAIN (COSTS false) SELECT id FROM p1_c1_c1 as s1 TABLESAMPLE SYSTEM(10) + UNION ALL +SELECT id FROM ft1 + UNION ALL +(WITH cte1 AS (SELECT id FROM p1 WHERE id % 2 = 0) SELECT id FROM cte1) + UNION ALL +SELECT userid FROM pg_stat_statements fs1 + UNION ALL +SELECT x FROM (VALUES (1), (2), (3)) t(x); +LOG: pg_hint_plan: +used hint: +Parallel(p1 5 hard) +not used hint: +IndexScan(*VALUES*) +SeqScan(cte1) +TidScan(fs1) +IndexScan(ft1) +IndexScan(t) +Parallel(s1 3 hard) +duplication hint: +error hint: + + QUERY PLAN +----------------------------------------------------------------------------------------------- + Append + -> Result + -> Append + -> Sample Scan on p1_c1_c1 s1 + Sampling: system ('10'::real) + -> Foreign Scan on ft1 + Foreign File: /home/horiguti/work/pg_hint_plan/pg_hint_plan/data/data.csv + -> CTE Scan on cte1 + CTE cte1 + -> Gather + Workers Planned: 5 + -> Append + -> Parallel Seq Scan on p1 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c1 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c2 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c3 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c4 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c1_c1 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c1_c2 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c3_c1 + Filter: ((id % 2) = 0) + -> Parallel Seq Scan on p1_c3_c2 + Filter: ((id % 2) = 0) + -> Function Scan on pg_stat_statements + -> Subquery Scan on "*SELECT* 5" + -> Values Scan on "*VALUES*" +(33 rows) + ALTER SYSTEM SET session_preload_libraries TO DEFAULT; SELECT pg_reload_conf(); pg_reload_conf diff --git a/pg_hint_plan.c b/pg_hint_plan.c index 41baf67..6b747c8 100644 --- a/pg_hint_plan.c +++ b/pg_hint_plan.c @@ -184,6 +184,12 @@ typedef enum HintType HINT_TYPE_PARALLEL } HintType; +typedef enum HintTypeBitmap +{ + HINT_BM_SCAN_METHOD = 1, + HINT_BM_PARALLEL = 2 +} HintTypeBitmap; + static const char *HintTypeName[] = { "scan method", "join method", @@ -3512,10 +3518,13 @@ reset_hint_enforcement() } /* - * Set planner guc parameters according to corresponding scan hints. + * Set planner guc parameters according to corresponding scan hints. Returns + * bitmap of HintTypeBitmap. If shint or phint is not NULL, set used hint + * there respectively. */ static bool -setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel) +setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel, + ScanMethodHint **rshint, ParallelHint **rphint) { Index new_parent_relid = 0; ListCell *l; @@ -3523,6 +3532,11 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel) ParallelHint *phint = NULL; bool inhparent = root->simple_rte_array[rel->relid]->inh; Oid relationObjectId = root->simple_rte_array[rel->relid]->relid; + int ret = 0; + + /* reset returns if requested */ + if (rshint != NULL) *rshint = NULL; + if (rphint != NULL) *rphint = NULL; /* * We could register the parent relation of the following children here @@ -3542,7 +3556,7 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel) qnostr, relationObjectId, get_rel_name(relationObjectId), inhparent, current_hint_state, hint_inhibit_level))); - return false; + return 0; } /* Find the parent for this relation other than the registered parent */ @@ -3638,6 +3652,8 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel) bool using_parent_hint = (shint == current_hint_state->parent_scan_hint); + ret |= HINT_BM_SCAN_METHOD; + /* Setup scan enforcement environment */ setup_scan_method_enforcement(shint, current_hint_state); @@ -3674,6 +3690,9 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel) setup_parallel_plan_enforcement(phint, current_hint_state); + if (phint) + ret |= HINT_BM_PARALLEL; + /* Nothing to apply. Reset the scan mask to intial state */ if (!shint && ! phint) { @@ -3691,10 +3710,13 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel) setup_scan_method_enforcement(NULL, current_hint_state); - return false; + return ret; } - return true; + if (rshint != NULL) *rshint = shint; + if (rphint != NULL) *rphint = phint; + + return ret; } /* @@ -4328,95 +4350,118 @@ pg_hint_plan_set_rel_pathlist(PlannerInfo * root, RelOptInfo *rel, { ParallelHint *phint; ListCell *l; + int found_hints; /* call the previous hook */ if (prev_set_rel_pathlist) prev_set_rel_pathlist(root, rel, rti, rte); - /* Nothing to do when hint has not been parsed yet */ + /* Nothing to do if no hint available */ if (current_hint_state == NULL) return; - /* Don't touch dummy rel */ + /* Don't touch dummy rels. */ if (IS_DUMMY_REL(rel)) return; + /* + * We can accept only plain relations, foreign tables and table saples are + * also unacceptable. See set_rel_pathlist. + */ + if (rel->rtekind != RTE_RELATION || + rte->relkind == RELKIND_FOREIGN_TABLE || + rte->tablesample != NULL) + return; + /* We cannot handle if this requires an outer */ if (rel->lateral_relids) return; - if (!setup_hint_enforcement(root, rel)) + /* Return if this relation gets no enfocement */ + if ((found_hints = setup_hint_enforcement(root, rel, NULL, &phint)) == 0) + return; + + /* Here, we regenerate paths with the current hint restriction */ + + if (found_hints & HINT_BM_SCAN_METHOD) { /* - * No enforcement requested, but we might have to generate gather path - * on this relation. We could regenerate gather for relations not - * getting enforcement or even relations other than ordinary ones. + * With scan hints, we regenerate paths for this relation from the + * first under the restricion. */ + list_free_deep(rel->pathlist); + rel->pathlist = NIL; - /* If no need of a gather path, just return */ - if (rel->reloptkind != RELOPT_BASEREL || max_hint_nworkers < 1 || - rel->partial_pathlist == NIL) - return; + set_plain_rel_pathlist(root, rel, rte); + } + + if (found_hints & HINT_BM_PARALLEL) + { + Assert (phint); - /* Lower the priorities of existing paths, then add a new path */ - foreach (l, rel->pathlist) + /* the partial_pathlist may be for different parameters, discard it */ + if (rel->partial_pathlist) { - Path *path = (Path *) lfirst(l); + list_free_deep(rel->partial_pathlist); + rel->partial_pathlist = NIL; + } + + /* also remove gather path */ + if (rel->pathlist) + { + ListCell *cell, *prev = NULL; - if (path->startup_cost < disable_cost) + foreach (cell, rel->pathlist) { - path->startup_cost += disable_cost; - path->total_cost += disable_cost; + Path *path = (Path *) lfirst(cell); + + if (IsA(path, GatherPath)) + rel->pathlist = list_delete_cell(rel->pathlist, + cell, prev); + else + prev = cell; } } - generate_gather_paths(root, rel); - return; - } - - /* Don't touch other than ordinary relation hereafter */ - if (rte->rtekind != RTE_RELATION) - return; - - /* Here, we regenerate paths with the current hint restriction */ - - /* Remove prviously generated paths */ - list_free_deep(rel->pathlist); - rel->pathlist = NIL; - - /* Rebuild access paths */ - set_plain_rel_pathlist(root, rel, rte); + /* then generate new paths if needed */ + if (phint->nworkers > 0) + { + /* Lower the priorities of non-parallel paths */ + foreach (l, rel->pathlist) + { + Path *path = (Path *) lfirst(l); - /* - * create_plain_partial_paths creates partial paths with reasonably - * estimated number of workers. Force the requested number of workers if - * hard mode. - */ - phint = find_parallel_hint(root, rel->relid); + if (path->startup_cost < disable_cost) + { + path->startup_cost += disable_cost; + path->total_cost += disable_cost; + } + } - if (phint) - { - /* if inhibiting parallel, remove existing partial paths */ - if (phint->nworkers == 0 && rel->partial_pathlist) - { - list_free_deep(rel->partial_pathlist); - rel->partial_pathlist = NIL; - } + /* + * generate partial paths with enforcement, this is affected by + * scan method enforcement. Specifically, the cost of this partial + * seqscan path will be disabled_cost if seqscan is inhibited by + * hint or GUC parameters. + */ + Assert (rel->partial_pathlist == NIL); + create_plain_partial_paths(root, rel); - /* enforce number of workers if requested */ - if (rel->partial_pathlist && phint->force_parallel) - { - foreach (l, rel->partial_pathlist) + /* enforce number of workers if requested */ + if (phint->force_parallel) { - Path *ppath = (Path *) lfirst(l); + foreach (l, rel->partial_pathlist) + { + Path *ppath = (Path *) lfirst(l); - ppath->parallel_workers = phint->nworkers; + ppath->parallel_workers = phint->nworkers; + } } - } - /* Generate gather paths for base rels */ - if (rel->reloptkind == RELOPT_BASEREL) - generate_gather_paths(root, rel); + /* Generate gather paths for base rels */ + if (rel->reloptkind == RELOPT_BASEREL) + generate_gather_paths(root, rel); + } } reset_hint_enforcement(); diff --git a/sql/ut-W.sql b/sql/ut-W.sql index 1ff2704..28cc247 100644 --- a/sql/ut-W.sql +++ b/sql/ut-W.sql @@ -77,6 +77,9 @@ SET parallel_setup_cost to DEFAULT; SET parallel_tuple_cost to DEFAULT; SET min_parallel_relation_size to DEFAULT; +/*+Parallel(p2 8 soft)*/ +EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; + /*+Parallel(p2 8 hard)*/ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; @@ -93,7 +96,7 @@ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; /*+Parallel(p1 8 hard) SeqScan(p1) */ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; --- parallel overrides index scan +-- we don't have parallel over index scans so far /*+Parallel(p1 8 hard) IndexScan(p1) */ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id; /*+Parallel(p1 0 hard) IndexScan(p1) */ @@ -112,7 +115,7 @@ SET max_parallel_workers_per_gather to 0; /*+Parallel(p1 8) */ EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2; --- set hint also does +-- set hint does the same thing /*+Set(max_parallel_workers_per_gather 1)*/ EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2; @@ -130,11 +133,13 @@ SET parallel_setup_cost to 0; SET parallel_tuple_cost to 0; SET min_parallel_relation_size to 0; SET max_parallel_workers_per_gather to 3; +SET enable_indexscan to false; /*+Parallel(p1 8 hard) */ EXPLAIN (COSTS false) SELECT * FROM p1 join t1 on p1.id = t1.id; -- Negative hint +SET enable_indexscan to DEFAULT; SET parallel_setup_cost to 0; SET parallel_tuple_cost to 0; SET min_parallel_relation_size to 0; @@ -149,5 +154,19 @@ EXPLAIN (COSTS false) SELECT * FROM p1; Parallel(p1 8 hoge)Parallel(p1)Parallel(p1 100 soft x)*/ EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2; +-- Hints on unhintable relations are just ignored +/*+Parallel(p1 5 hard) Parallel(s1 3 hard) IndexScan(ft1) SeqScan(cte1) + TidScan(fs1) IndexScan(t) IndexScan(*VALUES*) */ +EXPLAIN (COSTS false) SELECT id FROM p1_c1_c1 as s1 TABLESAMPLE SYSTEM(10) + UNION ALL +SELECT id FROM ft1 + UNION ALL +(WITH cte1 AS (SELECT id FROM p1 WHERE id % 2 = 0) SELECT id FROM cte1) + UNION ALL +SELECT userid FROM pg_stat_statements fs1 + UNION ALL +SELECT x FROM (VALUES (1), (2), (3)) t(x); + + ALTER SYSTEM SET session_preload_libraries TO DEFAULT; SELECT pg_reload_conf(); -- 2.11.0