Add a 'parallel_degree' reloption.

The code that estimates what parallel degree should be uesd for the
scan of a relation is currently rather stupid, so add a parallel_degree
reloption that can be used to override the planner's rather limited
judgement.

Julien Rouhaud, reviewed by David Rowley, James Sewell, Amit Kapila,
and me.  Some further hacking by me.

7 files changed, 73 insertions, 23 deletions

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index ea0755a8785..797be63c44b 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -26,6 +26,7 @@
 #include "commands/tablespace.h"
 #include "commands/view.h"
 #include "nodes/makefuncs.h"
+#include "postmaster/postmaster.h"
 #include "utils/array.h"
 #include "utils/attoptcache.h"
 #include "utils/builtins.h"
@@ -267,6 +268,15 @@ static relopt_int intRelOpts[] =
 		0, 0, 0
 #endif
 	},
+	{
+		{
+			"parallel_degree",
+			"Number of parallel processes that can be used per executor node for this relation.",
+			RELOPT_KIND_HEAP,
+			AccessExclusiveLock
+		},
+		-1, 0, MAX_BACKENDS
+	},
 
 	/* list terminator */
 	{{NULL}}
@@ -1251,8 +1261,7 @@ fillRelOptions(void *rdopts, Size basesize,
 
 
 /*
- * Option parser for anything that uses StdRdOptions (i.e. fillfactor and
- * autovacuum)
+ * Option parser for anything that uses StdRdOptions.
  */
 bytea *
 default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
@@ -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 cc77ff9e1f0..17c1edd9703 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -659,31 +659,55 @@ set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
 static void
 create_parallel_paths(PlannerInfo *root, RelOptInfo *rel)
 {
-	int		parallel_threshold = 1000;
-	int		parallel_degree = 1;
+	int			parallel_degree = 1;
 
 	/*
-	 * If this relation is too small to be worth a parallel scan, just return
-	 * without doing anything ... unless it's an inheritance child.  In that case,
-	 * we want to generate a parallel path here anyway.  It might not be worthwhile
-	 * just for this relation, but when combined with all of its inheritance siblings
-	 * it may well pay off.
+	 * If the user has set the parallel_degree reloption, we decide what to do
+	 * based on the value of that option.  Otherwise, we estimate a value.
 	 */
-	if (rel->pages < parallel_threshold && rel->reloptkind == RELOPT_BASEREL)
-		return;
+	if (rel->rel_parallel_degree != -1)
+	{
+		/*
+		 * If parallel_degree = 0 is set for this relation, bail out.  The
+		 * user does not want a parallel path for this relation.
+		 */
+		if (rel->rel_parallel_degree == 0)
+			return;
 
-	/*
-	 * Limit the degree of parallelism logarithmically based on the size of the
-	 * relation.  This probably needs to be a good deal more sophisticated, but we
-	 * need something here for now.
-	 */
-	while (rel->pages > parallel_threshold * 3 &&
-		   parallel_degree < max_parallel_degree)
+		/*
+		 * Use the table parallel_degree, but don't go further than
+		 * max_parallel_degree.
+		 */
+		parallel_degree = Min(rel->rel_parallel_degree, max_parallel_degree);
+	}
+	else
 	{
-		parallel_degree++;
-		parallel_threshold *= 3;
-		if (parallel_threshold >= PG_INT32_MAX / 3)
-			break;
+		int			parallel_threshold = 1000;
+
+		/*
+		 * If this relation is too small to be worth a parallel scan, just
+		 * return without doing anything ... unless it's an inheritance child.
+		 * In that case, we want to generate a parallel path here anyway.  It
+		 * might not be worthwhile 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)
+			return;
+
+		/*
+		 * Limit the degree of parallelism logarithmically based on the size
+		 * of the relation.  This probably needs to be a good deal more
+		 * sophisticated, but we need something here for now.
+		 */
+		while (rel->pages > parallel_threshold * 3 &&
+			   parallel_degree < max_parallel_degree)
+		{
+			parallel_degree++;
+			parallel_threshold *= 3;
+			if (parallel_threshold >= PG_INT32_MAX / 3)
+				break;
+		}
 	}
 
 	/* Add an unordered partial path based on a parallel sequential scan. */
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 0f291275dcc..86cc640cf26 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -133,6 +133,9 @@ 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);
 
+	/* Retrive the parallel_degree reloption, if set. */
+	rel->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 802eab3718a..187437b6941 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->rel_parallel_degree = -1; /* set up in GetRelationInfo */
 	rel->reltarget = create_empty_pathtarget();
 	rel->pathlist = NIL;
 	rel->ppilist = NIL;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index cb8a06d15e4..6d092711e83 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1783,6 +1783,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 d430f6e9fd4..e9dfb663c20 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -522,6 +522,7 @@ typedef struct RelOptInfo
 	double		allvisfrac;
 	PlannerInfo *subroot;		/* if subquery */
 	List	   *subplan_params; /* if subquery */
+	int			rel_parallel_degree;	/* wanted number of parallel workers */
 
 	/* Information about foreign tables and foreign joins */
 	Oid			serverid;		/* identifies server for the table or join */
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 51eb27a381b..c7582c2a11c 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -206,6 +206,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 workers */
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR			10
@@ -242,6 +243,14 @@ typedef struct StdRdOptions
 	((relation)->rd_options ?				\
 	 ((StdRdOptions *) (relation)->rd_options)->user_catalog_table : false)
 
+/*
+ * RelationGetParallelDegree
+ *		Returns the relation's parallel_degree.  Note multiple eval of argument!
+ */
+#define RelationGetParallelDegree(relation, defaultpd) \
+	((relation)->rd_options ? \
+	 ((StdRdOptions *) (relation)->rd_options)->parallel_degree : (defaultpd))
+
 
 /*
  * ViewOptions