On 16/03/2016 17:55, Robert Haas wrote: > On Wed, Mar 16, 2016 at 12:47 PM, Julien Rouhaud > <julien.rouh...@dalibo.com> wrote: >> Something like a "min_parallel_degree" then ? > > Why not just parallel_degree without any prefix? As in, when scanning > this table in parallel, the reloption suggests using N workers. >
Agreed. PFA v2 that implements that. -- Julien Rouhaud http://dalibo.com - http://dalibo.org
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 6c73fb4..472f3d5 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3912,6 +3912,22 @@ SELECT * FROM parent WHERE key = 2400;
</listitem>
</varlistentry>
+ <varlistentry id="guc-parallel-treshold" xreflabel="parallel-treshold">
+ <term><varname>parallel_threshold</varname> (<type>integer</type>)
+ <indexterm>
+ <primary><varname>parallel_threshold</> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ The planner will decide to use parallel plans for relation that are at
+ least larger than this parameter. If you lower this setting, the planner
+ will use more parallel workers.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
<varlistentry id="guc-force-parallel-mode" xreflabel="force_parallel_mode">
<term><varname>force_parallel_mode</varname> (<type>enum</type>)
<indexterm>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index cd234db..80e1f09 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -909,6 +909,17 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
</varlistentry>
<varlistentry>
+ <term><literal>parallel_degree</> (<type>integer</>)</term>
+ <listitem>
+ <para>
+ Number of workers wanted for this table. The number of worker will be
+ limited according to the <xref linkend="gux-max-parallel-degree">
+ parameter.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>autovacuum_enabled</>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</>)</term>
<listitem>
<para>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index ea0755a..68bb133 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -267,6 +267,15 @@ static relopt_int intRelOpts[] =
0, 0, 0
#endif
},
+ {
+ {
+ "parallel_degree",
+ "Number of parallel processes per executor node wanted for this relation.",
+ RELOPT_KIND_HEAP,
+ AccessExclusiveLock
+ },
+ -1, 1, INT_MAX
+ },
/* list terminator */
{{NULL}}
@@ -1291,7 +1300,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
{"autovacuum_analyze_scale_factor", RELOPT_TYPE_REAL,
offsetof(StdRdOptions, autovacuum) +offsetof(AutoVacOpts, analyze_scale_factor)},
{"user_catalog_table", RELOPT_TYPE_BOOL,
- offsetof(StdRdOptions, user_catalog_table)}
+ offsetof(StdRdOptions, user_catalog_table)},
+ {"parallel_degree", RELOPT_TYPE_INT,
+ offsetof(StdRdOptions, parallel_degree)}
};
options = parseRelOptions(reloptions, validate, kind, &numoptions);
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 4f60b85..afb1896 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -659,7 +659,7 @@ set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
static void
create_parallel_paths(PlannerInfo *root, RelOptInfo *rel)
{
- int parallel_threshold = 1000;
+ int p_threshold = parallel_threshold;
int parallel_degree = 1;
/*
@@ -669,7 +669,7 @@ create_parallel_paths(PlannerInfo *root, RelOptInfo *rel)
* just for this relation, but when combined with all of its inheritance siblings
* it may well pay off.
*/
- if (rel->pages < parallel_threshold && rel->reloptkind == RELOPT_BASEREL)
+ if (rel->pages < p_threshold && rel->reloptkind == RELOPT_BASEREL)
return;
/*
@@ -677,15 +677,22 @@ create_parallel_paths(PlannerInfo *root, RelOptInfo *rel)
* relation. This probably needs to be a good deal more sophisticated, but we
* need something here for now.
*/
- while (rel->pages > parallel_threshold * 3 &&
+ while (rel->pages > p_threshold * 3 &&
parallel_degree < max_parallel_degree)
{
parallel_degree++;
- parallel_threshold *= 3;
- if (parallel_threshold >= PG_INT32_MAX / 3)
+ p_threshold *= 3;
+ if (p_threshold >= PG_INT32_MAX / 3)
break;
}
+ /*
+ * Raise the parallel degree if it was specified for the relation, but don't
+ * go further than max_parallel_degree.
+ */
+ if (rel->parallel_degree > parallel_degree)
+ parallel_degree = Min(rel->parallel_degree, max_parallel_degree);
+
/* Add an unordered partial path based on a parallel sequential scan. */
add_partial_path(rel, create_seqscan_path(root, rel, NULL, parallel_degree));
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 943fcde..d53e794 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -113,6 +113,7 @@ int effective_cache_size = DEFAULT_EFFECTIVE_CACHE_SIZE;
Cost disable_cost = 1.0e10;
int max_parallel_degree = 0;
+int parallel_threshold = DEFAULT_PARALLEL_THRESHOLD;
bool enable_seqscan = true;
bool enable_indexscan = true;
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index ad715bb..cdeb3e4 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -128,6 +128,8 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
estimate_rel_size(relation, rel->attr_widths - rel->min_attr,
&rel->pages, &rel->tuples, &rel->allvisfrac);
+ /* Setup the per-relation parallel_degree */
+ rel->parallel_degree = RelationGetParallelDegree(relation, -1);
/*
* Make list of indexes. Ignore indexes on system catalogs if told to.
* Don't bother with indexes for an inheritance parent, either.
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 20e4bf7..1a8f7df 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -107,6 +107,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind)
rel->consider_startup = (root->tuple_fraction > 0);
rel->consider_param_startup = false; /* might get changed later */
rel->consider_parallel = false; /* might get changed later */
+ rel->parallel_degree = -1; /* set up in GetRelationInfo */
rel->reltarget = create_empty_pathtarget();
rel->pathlist = NIL;
rel->ppilist = NIL;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index edcafce..cfab130 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -1697,6 +1697,17 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
{
+ {"parallel_threshold", PGC_USERSET, QUERY_TUNING_OTHER,
+ gettext_noop("Sets the minimum size of a relation to consider a"
+ "parallel plan."),
+ NULL,
+ GUC_UNIT_BLOCKS
+ },
+ ¶llel_threshold,
+ 1000, 1, INT_MAX,
+ NULL, NULL, NULL
+ },
+ {
{"geqo_threshold", PGC_USERSET, QUERY_TUNING_GEQO,
gettext_noop("Sets the threshold of FROM items beyond which GEQO is used."),
NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index ee3d378..5d4a499 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -315,6 +315,7 @@
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
#force_parallel_mode = off
+#parallel_threshold = 8MB # minimum relation size to consider a parallel plan
#------------------------------------------------------------------------------
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6a81416..e8b00ca 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1782,6 +1782,7 @@ psql_completion(const char *text, int start, int end)
"autovacuum_vacuum_scale_factor",
"autovacuum_vacuum_threshold",
"fillfactor",
+ "parallel_degree",
"log_autovacuum_min_duration",
"toast.autovacuum_enabled",
"toast.autovacuum_freeze_max_age",
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 5032696..bb39e64 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -487,6 +487,7 @@ typedef struct RelOptInfo
bool consider_startup; /* keep cheap-startup-cost paths? */
bool consider_param_startup; /* ditto, for parameterized paths? */
bool consider_parallel; /* consider parallel paths? */
+ int parallel_degree; /* wanted number of parallel workers */
/* default result targetlist for Paths scanning this relation */
struct PathTarget *reltarget; /* list of Vars/Exprs, cost, width */
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index fea2bb7..280f0e5 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -28,6 +28,7 @@
#define DEFAULT_CPU_OPERATOR_COST 0.0025
#define DEFAULT_PARALLEL_TUPLE_COST 0.1
#define DEFAULT_PARALLEL_SETUP_COST 1000.0
+#define DEFAULT_PARALLEL_THRESHOLD 1000
#define DEFAULT_EFFECTIVE_CACHE_SIZE 524288 /* measured in pages */
@@ -55,6 +56,7 @@ extern PGDLLIMPORT double parallel_setup_cost;
extern PGDLLIMPORT int effective_cache_size;
extern Cost disable_cost;
extern int max_parallel_degree;
+extern int parallel_threshold;
extern bool enable_seqscan;
extern bool enable_indexscan;
extern bool enable_indexonlyscan;
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index f2bebf2..8bf5dbc 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -203,6 +203,7 @@ typedef struct StdRdOptions
AutoVacOpts autovacuum; /* autovacuum-related options */
bool user_catalog_table; /* use as an additional catalog
* relation */
+ int parallel_degree; /* max number of parallel worker */
} StdRdOptions;
#define HEAP_MIN_FILLFACTOR 10
@@ -217,6 +218,14 @@ typedef struct StdRdOptions
((StdRdOptions *) (relation)->rd_options)->fillfactor : (defaultff))
/*
+ * RelationGetMaxParallelDegree
+ * Returns the relation's parallel_degree. Note multiple eval of argument!
+ */
+#define RelationGetParallelDegree(relation, defaultmpd) \
+ ((relation)->rd_options ? \
+ ((StdRdOptions *) (relation)->rd_options)->parallel_degree : (defaultmpd))
+
+/*
* RelationGetTargetPageUsage
* Returns the relation's desired space usage per page in bytes.
*/
signature.asc
Description: OpenPGP digital signature
