Re: A reloption for partitioned tables - parallel_workers

On Tue, Mar 2, 2021 at 5:47 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> On Tue, 2021-03-02 at 11:23 +0900, Amit Langote wrote:
> > +ALTER TABLE pagg_tab_ml SET (parallel_workers = 0);
> > +EXPLAIN (COSTS OFF)
> > +SELECT a FROM pagg_tab_ml WHERE b = 42;
> > + QUERY PLAN
> > +---------------------------------------------------
> > + Append
> > + -> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml_1
> > + Filter: (b = 42)
> > + -> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2
> > + Filter: (b = 42)
> > + -> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3
> > + Filter: (b = 42)
> > +(7 rows)
> >
> > I got the same result with my implementation, but I am wondering if
> > setting parallel_workers=0 on the parent table shouldn't really
> > disable a regular (non-parallel-aware) Append running under Gather
> > even if it does Parallel Append (parallel-aware)? So in this test
> > case, there should have been a Gather atop Append, with individual
> > partitions scanned using Parallel Seq Scan where applicable.
>
> I am not sure, but I tend to think that if you specify no
> parallel workers, you want no parallel workers.

I am thinking that one would set parallel_workers on a parent
partitioned table to control only how many workers a Parallel Append
can spread across partitions or use parallel_workers=0 to disable this
form of partition parallelism. However, one may still want the
individual partitions to be scanned in parallel, where workers only
spread across the partition's blocks. IMO, we should try to keep
those two forms of parallelism separately configurable.

> But I noticed the following:
>
> SET enable_partitionwise_aggregate = on;
>
> EXPLAIN (COSTS OFF)
> SELECT count(*) FROM pagg_tab_ml;
> QUERY PLAN
> ------------------------------------------------------------------------------
> Finalize Aggregate
> -> Gather
> Workers Planned: 4
> -> Parallel Append
> -> Partial Aggregate
> -> Parallel Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
> -> Partial Aggregate
> -> Parallel Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_3
> -> Partial Aggregate
> -> Parallel Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_1
> -> Partial Aggregate
> -> Parallel Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_4
> -> Partial Aggregate
> -> Parallel Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_2
> (14 rows)
>
> The default number of parallel workers is taken, because the append is
> on an upper relation, not the partitioned table itself.
>
> One would wish that "parallel_workers" somehow percolated up,

I would have liked that too.

> but I
> have no idea how that should work.

It appears that we don't set the fields of an upper relation such that
IS_PARTITIONED_REL() would return true for it, like we do for base and
join relations. In compute_append_parallel_workers(), we're requiring
it to be true to even look at the relation's rel_parallel_workers. We
can set those properties in *some* grouping rels, for example, when
the aggregation is grouped on the input relation's partition key.
That would make it possible for the Append on such grouping relations
to refer to their input partitioned relation's rel_parallel_workers.
For example, with the attached PoC patch:

SET parallel_setup_cost TO 0;
SET max_parallel_workers_per_gather TO 8;
SET enable_partitionwise_aggregate = on;

alter table pagg_tab_ml set (parallel_workers=5);

EXPLAIN (COSTS OFF) SELECT a, count(*) FROM pagg_tab_ml GROUP BY 1;
QUERY PLAN
---------------------------------------------------------------------
Gather
Workers Planned: 5
-> Parallel Append
-> HashAggregate
Group Key: pagg_tab_ml_5.a
-> Append
-> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5
-> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6
-> HashAggregate
Group Key: pagg_tab_ml.a
-> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
-> HashAggregate
Group Key: pagg_tab_ml_2.a
-> Append
-> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2
-> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3
(16 rows)

alter table pagg_tab_ml set (parallel_workers=0);

EXPLAIN (COSTS OFF) SELECT a, count(*) FROM pagg_tab_ml GROUP BY 1;
QUERY PLAN
------------------------------------------------------------------------------------------
Append
-> Finalize GroupAggregate
Group Key: pagg_tab_ml.a
-> Gather Merge
Workers Planned: 1
-> Sort
Sort Key: pagg_tab_ml.a
-> Partial HashAggregate
Group Key: pagg_tab_ml.a
-> Parallel Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
-> Finalize GroupAggregate
Group Key: pagg_tab_ml_2.a
-> Gather Merge
Workers Planned: 2
-> Sort
Sort Key: pagg_tab_ml_2.a
-> Parallel Append
-> Partial HashAggregate
Group Key: pagg_tab_ml_2.a
-> Parallel Seq Scan on
pagg_tab_ml_p2_s1 pagg_tab_ml_2
-> Partial HashAggregate
Group Key: pagg_tab_ml_3.a
-> Parallel Seq Scan on
pagg_tab_ml_p2_s2 pagg_tab_ml_3
-> Finalize GroupAggregate
Group Key: pagg_tab_ml_5.a
-> Gather Merge
Workers Planned: 2
-> Sort
Sort Key: pagg_tab_ml_5.a
-> Parallel Append
-> Partial HashAggregate
Group Key: pagg_tab_ml_5.a
-> Parallel Seq Scan on
pagg_tab_ml_p3_s1 pagg_tab_ml_5
-> Partial HashAggregate
Group Key: pagg_tab_ml_6.a
-> Parallel Seq Scan on
pagg_tab_ml_p3_s2 pagg_tab_ml_6
(36 rows)

alter table pagg_tab_ml set (parallel_workers=9);

EXPLAIN (COSTS OFF) SELECT a, count(*) FROM pagg_tab_ml GROUP BY 1;
QUERY PLAN
---------------------------------------------------------------------
Gather
Workers Planned: 8
-> Parallel Append
-> HashAggregate
Group Key: pagg_tab_ml_5.a
-> Append
-> Seq Scan on pagg_tab_ml_p3_s1 pagg_tab_ml_5
-> Seq Scan on pagg_tab_ml_p3_s2 pagg_tab_ml_6
-> HashAggregate
Group Key: pagg_tab_ml.a
-> Seq Scan on pagg_tab_ml_p1 pagg_tab_ml
-> HashAggregate
Group Key: pagg_tab_ml_2.a
-> Append
-> Seq Scan on pagg_tab_ml_p2_s1 pagg_tab_ml_2
-> Seq Scan on pagg_tab_ml_p2_s2 pagg_tab_ml_3
(16 rows)

--
Amit Langote
EDB: http://www.enterprisedb.com