Re: Parallel Seq Scan

On 8 April 2015 at 14:24, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I think one of the philosophical questions that has to be answered
> here is "what does it mean to talk about the cost of a parallel
> plan?". For a non-parallel plan, the cost of the plan means both "the
> amount of effort we will spend executing the plan" and also "the
> amount of time we think the plan will take to complete", but those two
> things are different for parallel plans. I'm inclined to think it's
> right to view the cost of a parallel plan as a proxy for execution
> time, because the fundamental principle of the planner is that we pick
> the lowest-cost plan. But there also clearly needs to be some way to
> prevent the selection of a plan which runs slightly faster at the cost
> of using vastly more resources.
>
>
I'd agree with that as far as CPU costs, or maybe I'd just disagree with
the alternative, as if we costed in <cost of individual worker's work> *
<number of workers> then we'd never choose a parallel plan, as by the time
we costed in tuple communication costs between the processes a parallel
plan would always cost more than the serial equivalent. I/O costs are
different, I'd imagine these shouldn't be divided by the estimated number
of workers.

> Currently, the planner tracks the best unsorted path for each relation
> as well as the best path for each useful sort order. Suppose we treat
> parallelism as another axis for judging the quality of a plan: we keep
> the best unsorted, non-parallel path; the best non-parallel path for
> each useful sort order; the best unsorted, parallel path; and the best
> parallel path for each sort order. Each time we plan a node, we
> generate non-parallel paths first, and then parallel paths. But, if a
> parallel plan isn't markedly faster than the non-parallel plan for the
> same sort order, then we discard it. I'm not sure exactly what the
> thresholds should be here, and they probably need to be configurable,
> because on a single-user system with excess capacity available it may
> be absolutely desirable to use ten times the resources to get an
> answer 25% faster, but on a heavy-loaded system that will stink.
>
>
But with this, and the parallel costing model above, to know the cost of a
parallel path, you need to know how many workers will be available later at
execution time in order to know what that percentage is, or would we just
always assume we'd get max_parallel_degree each time the plan is executed,
similar to how the latest patch works?

Some ideas for GUCs:
>
> max_parallel_degree = The largest number of processes we'll consider
> using for a single query.
> min_parallel_speedup = The minimum percentage by which a parallel path
> must be cheaper (in terms of execution time) than a non-parallel path
> in order to survive. I'm imagining the default here might be
> something like 15%.
> min_parallel_speedup_per_worker = Like the previous one, but per
> worker. e.g. if this is 5%, which might be a sensible default, then a
> plan with 4 workers must be at least 20% better to survive, but a plan
> using only 2 workers only needs to be 10% better.
>
>
max_parallel_degree feels awfully like it would have to be set
conservatively, similar to how work_mem is today. Like with work_mem,
during quiet periods it sure would be nice if it could magically increase.

> An additional benefit of this line of thinking is that planning would
> always produce a best non-parallel path. And sometimes, there would
> also be a best parallel path that is expected to run faster. We could
> then choose between them dynamically at execution time.
>
>
Actually store 2 plans within the plan? Like with an AlternativePlanNode?

> I think it's pretty hard to imagine a scenario as extreme as the one
> you mention above ever actually occurring in practice. I mean, even
> the most naive implementation of parallel query will presumably have
> something like max_parallel_degree, and you probably won't have that
> set to 128. For starters, it can't possibly make sense unless you
> server has at least 128 CPUs, and even then it only makes sense if you
> don't mind a single query using all of them, and even if the first of
> those things is true, the second one probably isn't. I don't doubt
> that less extreme variants of this scenario are possible, though.
>
>
Yeah maybe, it does seem quite extreme, but maybe less so as the years roll
on a bit... perhaps in 5-10 years it might be quite common to have that
many spare CPU cores to throw at a task.

I think if we have this percentage GUC you mentioned to prefer parallel
plans if they're within a % threshold of the serial plan, then we could end
up with problems with I/O and buffers getting thrown out of caches due to
the extra I/O involved in parallel plans going with seq scans instead of
serial plans choosing index scans.

In summary it sounds like with my idea we get:

Pros
* Optimal plan if no workers are available at execution time.
* Parallelism possible if the chosen optimal plan happens to support
parallelism, e.g not index scan.
* No planning overhead

Cons:
* The plan "Parallelizer" must make changes to the plan just before
execution time, which ruins the 1 to 1 ratio of plan/executor nodes by the
time you inject Funnel nodes.

If we parallelise during planning time:

Pros
* More chance of getting a parallel friendly plan which could end up being
very fast if we get enough workers at executor time.

Cons:
* May produce non optimal plans if no worker processes are available during
execution time.
* Planning overhead for considering parallel paths.
* The parallel plan may blow out buffer caches due to increased I/O of
parallel plan.

Of course please say if I've missed any pro or con.

Regards

David Rowley