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
</dl>
<dt><h3>並列実行ヒントとスキャン方式ヒントの関係について</h3></dt>
-<dd>並列実行ヒントは同じオブジェクトにどのスキャン方式ヒントも指定することができますが、並列実行不能なスキャン方式が選択された場合は並列実行指定は無効になります。</dd>
+<dd>スキャン方式ヒントは並列実行の部分パスを生成する際にも影響を与えます。今のところ並列実行の部分パスはシーケンシャルスキャンとなるため、シーケンシャルスキャンを禁止すると同じオブジェクトへの並列実行ヒントが無効化されます。</dd>
</dl>
<dt><h3>UNION に対する並列実行ヒント</h3></dt>
-<dd>並列実行ヒントは同じオブジェクトにどのスキャン方式ヒントも指定することができますが、並列実行不能なスキャン方式が選択された場合は並列実行指定は無効になります。</dd>
+<dd>UNIONは直下のサブクエリが全て並列実行可能な場合にだけ並列実行を行います。一方ですべてのサブクエリが並列実行可能な場合は、そのうちの一つで並列実行を強制するとコスト比較の結果UNION全体が並列実行されることになります。ただし並列実行ヒントによる並列実行の禁止を行った場合はそのスキャンは並列実行不可となります。
+</dd>
</dl>
<dt><h3>Set ヒントでの pg_hint_plan 自身の制御変数の設定</h3></dt>
<dd>NoIndexScan hint involes NoIndexOnlyScan.</dd>
<h3>Parallel vs Scan hints</h3>
-<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>
+<dd>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.</dd>
<h3>Parallel hint and UNION</h3>
-<dd>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.</dd>
+<dd>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.</dd>
<h3>Setting pg_hint_plan parameters by Set hints</h3>
<dd><p>pg_hint_plan paramters change the behavior of itself so some parameters doesn't work as expected.</p>
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:
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Hash Join
Hash Cond: (p2.id = p1.id)
-> Gather
-> 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;
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) */
duplication hint:
error hint:
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------
Hash Join
Hash Cond: (p1.id = p2.id)
-> Gather
-> 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:
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;
-> 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:
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:
(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;
-> 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
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",
}
/*
- * 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;
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
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 */
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);
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)
{
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;
}
/*
{
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();
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;
/*+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) */
/*+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;
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;
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();
OSDN Git Service
