disable only nonparallel seq scan.

Started by Jeff Janesabout 6 years ago3 messages

jeff.janes@gmail.com

about 6 years ago

Is there a way to force a meaningful parallel seq scan, or at least the
planning of one, when the planner wants a non-parallel one?

Usually I can do things like with with enable_* setting, but if I `set
enable_seqscan to off`, it penalizes the parallel seq scan 8 times harder
than it penalizes the non-parallel one, so the plan does not switch.

If I set `force_parallel_mode TO on` then I do get a parallel plan, but it
is a degenerate one which tells me nothing I want to know.

If I `set parallel_tuple_cost = 0` (or in some cases to a negative number),
I can force it switch, but that destroys the purpose, which is to see what
the "would have been" plan estimates are for the parallel seq scan under
the default setting of the cost parameters.

I can creep parallel_tuple_cost downward until it switches, and then try to
extrapolate back up, but this tedious and not very reliable.

Cheers,

Jeff

Robert Haas

robertmhaas@gmail.com

about 6 years ago

In reply to: Jeff Janes (#1)

Re: disable only nonparallel seq scan.

On Sun, Dec 8, 2019 at 1:24 PM Jeff Janes <jeff.janes@gmail.com> wrote:

Is there a way to force a meaningful parallel seq scan, or at least the planning of one, when the planner wants a non-parallel one?

Usually I can do things like with with enable_* setting, but if I `set enable_seqscan to off`, it penalizes the parallel seq scan 8 times harder than it penalizes the non-parallel one, so the plan does not switch.

If I set `force_parallel_mode TO on` then I do get a parallel plan, but it is a degenerate one which tells me nothing I want to know.

If I `set parallel_tuple_cost = 0` (or in some cases to a negative number), I can force it switch, but that destroys the purpose, which is to see what the "would have been" plan estimates are for the parallel seq scan under the default setting of the cost parameters.

I can creep parallel_tuple_cost downward until it switches, and then try to extrapolate back up, but this tedious and not very reliable.

I don't think there's a way to force this, but setting both
parallel_setup_cost and parallel_tuple_cost to 0 seems to often be
enough.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Jeff Janes

jeff.janes@gmail.com

about 6 years ago

In reply to: Robert Haas (#2)

1 attachment(s)

Re: disable only nonparallel seq scan.

On Tue, Dec 10, 2019 at 1:32 PM Robert Haas <robertmhaas@gmail.com> wrote:

On Sun, Dec 8, 2019 at 1:24 PM Jeff Janes <jeff.janes@gmail.com> wrote:

Is there a way to force a meaningful parallel seq scan, or at least the

planning of one, when the planner wants a non-parallel one?

Usually I can do things like with with enable_* setting, but if I `set

enable_seqscan to off`, it penalizes the parallel seq scan 8 times harder
than it penalizes the non-parallel one, so the plan does not switch.

If I set `force_parallel_mode TO on` then I do get a parallel plan, but

it is a degenerate one which tells me nothing I want to know.

If I `set parallel_tuple_cost = 0` (or in some cases to a negative

number), I can force it switch, but that destroys the purpose, which is to
see what the "would have been" plan estimates are for the parallel seq scan
under the default setting of the cost parameters.

I can creep parallel_tuple_cost downward until it switches, and then try

to extrapolate back up, but this tedious and not very reliable.

I don't think there's a way to force this, but setting both
parallel_setup_cost and parallel_tuple_cost to 0 seems to often be
enough.

Yes, that is fine if I want the actual execution results. And I patch
guc.c to allow negative settings, for when some extra persuasion is needed.

But here I want to see what the planner is thinking, and changing the *cost
settings changes that thinking. So I want to force the planner to choose
the "next-best" plan under the original cost settings so I can see how far
away they are from each other. I made a crude patch to add
enable_singleseqscan, which has been letting me get at this information now.

I'm not proposing to apply this particular patch to the code base, but I do
wonder if we can do something about this "dark spot" which no combination
of current enable_* setting seems to be able to get at.

Cheers,

Jeff

Attachments:

enable_singleseqscan.patchapplication/octet-stream; name=enable_singleseqscan.patchDownload

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index c5f6593..daaefdc 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -122,6 +122,7 @@ Cost		disable_cost = 1.0e10;
 int			max_parallel_workers_per_gather = 2;
 
 bool		enable_seqscan = true;
+bool		enable_singleseqscan = true;
 bool		enable_indexscan = true;
 bool		enable_indexonlyscan = true;
 bool		enable_bitmapscan = true;
@@ -231,6 +232,9 @@ cost_seqscan(Path *path, PlannerInfo *root,
 	if (!enable_seqscan)
 		startup_cost += disable_cost;
 
+	if (!enable_singleseqscan && path->parallel_workers==0)
+		startup_cost += disable_cost;
+
 	/* fetch estimated page cost for tablespace containing table */
 	get_tablespace_page_costs(baserel->reltablespace,
 							  NULL,
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index ba74bf9..df22e08 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -898,6 +898,16 @@ static struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 	{
+		{"enable_singleseqscan", PGC_USERSET, QUERY_TUNING_METHOD,
+			gettext_noop("Enables the planner's use of non-parallel sequential-scan plans."),
+			NULL,
+			GUC_EXPLAIN
+		},
+		&enable_singleseqscan,
+		true,
+		NULL, NULL, NULL
+	},
+	{
 		{"enable_indexscan", PGC_USERSET, QUERY_TUNING_METHOD,
 			gettext_noop("Enables the planner's use of index-scan plans."),
 			NULL,
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index b3d0b4f..79ce31d 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -48,6 +48,7 @@ typedef enum
 extern PGDLLIMPORT Cost disable_cost;
 extern PGDLLIMPORT int max_parallel_workers_per_gather;
 extern PGDLLIMPORT bool enable_seqscan;
+extern PGDLLIMPORT bool enable_singleseqscan;
 extern PGDLLIMPORT bool enable_indexscan;
 extern PGDLLIMPORT bool enable_indexonlyscan;
 extern PGDLLIMPORT bool enable_bitmapscan;
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index a1c90eb..8389cf9 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -87,9 +87,10 @@ select name, setting from pg_settings where name like 'enable%';
  enable_partitionwise_aggregate | off
  enable_partitionwise_join      | off
  enable_seqscan                 | on
+ enable_singleseqscan           | on
  enable_sort                    | on
  enable_tidscan                 | on
-(17 rows)
+(18 rows)
 
 -- Test that the pg_timezone_names and pg_timezone_abbrevs views are
 -- more-or-less working.  We can't test their contents in any great detail