Re: [BUGS] BUG #14732: partitioned table cann't alter setparallel_workers?

At 2017-07-04 12:45:15, "Amit Langote" <Langote_Amit_f8@lab.ntt.co.jp> wrote:
>Hi,
>
>On 2017/07/04 11:09, digoal@126.com wrote:
>> The following bug has been logged on the website:
>> 
>> Bug reference:      14732
>> Logged by:          Zhou Digoal
>> Email address:      digoal@126.com
>> PostgreSQL version: 10beta1
>> Operating system:   CentOS 6.x x64
>> Description:        
>> 
>> HI,
>> 
>> there is an normal table and a partitiond table.
>> normal table can set parallel_works parameter, but partitioned table
>> cann't.
>> 
>> ```
>> postgres=# \d orders
>>                                        Unlogged table "public.orders"
>>      Column      |         Type          | Collation | Nullable |           
>>       Default                   
>> -----------------+-----------------------+-----------+----------+--------------------------------------------
>>  o_orderkey      | bigint                |           | not null |
>> nextval('orders_o_orderkey_seq'::regclass)
>>  o_custkey       | bigint                |           | not null | 
>>  o_orderstatus   | character(1)          |           |          | 
>>  o_totalprice    | double precision      |           |          | 
>>  o_orderdate     | date                  |           |          | 
>>  o_orderpriority | character(15)         |           |          | 
>>  o_clerk         | character(15)         |           |          | 
>>  o_shippriority  | integer               |           |          | 
>>  o_comment       | character varying(79) |           |          | 
>> 
>> postgres=# \d orders1
>>                                        Unlogged table "public.orders1"
>>      Column      |         Type          | Collation | Nullable |           
>>        Default                   
>> -----------------+-----------------------+-----------+----------+---------------------------------------------
>>  o_orderkey      | bigint                |           | not null |
>> nextval('orders1_o_orderkey_seq'::regclass)
>>  o_custkey       | bigint                |           | not null | 
>>  o_orderstatus   | character(1)          |           |          | 
>>  o_totalprice    | double precision      |           |          | 
>>  o_orderdate     | date                  |           |          | 
>>  o_orderpriority | character(15)         |           |          | 
>>  o_clerk         | character(15)         |           |          | 
>>  o_shippriority  | integer               |           |          | 
>>  o_comment       | character varying(79) |           |          | 
>> Partition key: RANGE (o_orderdate)
>> Number of partitions: 84 (Use \d+ to list them.)
>> 
>> postgres=# alter table orders set (parallel_workers =32);
>> ALTER TABLE
>> postgres=# alter table orders1 set (parallel_workers =32);
>> ERROR:  22023: unrecognized parameter "parallel_workers"
>> LOCATION:  parseRelOptions, reloptions.c:1094
>> ```
>
>This is not really a bug.  We do not support setting reloptions
>(parallel_workers is a reloption) for partitioned tables, because setting
>them will currently have no effect.  Maybe the error message doesn't make
>that clear.  It's actually trying to say: "parallel workers" is
>unrecognized parameter for partitioned tables.
>
>You can set them on the individual partitions.
>
>> but i can update pg_class to modify partitioned table's parallel_works.
>> 
>> ```
>> postgres=# update pg_class set reloptions =array['parallel_workers=13']
>> where relname ~ 'lineitem' and relkind='r';
>> UPDATE 85
>
>Yes, you can to do that, but the system still won't use it.  When
>appropriate system support for partitioned tables to use parallel query
>will be added, then we will also make the above alter table command
>succeed, but not until then.
>
>By the way, the update statement above doesn't actually affect partitioned
>tables, because its relkind is 'p'.
>
>> postgres=# explain select count(*) from lineitem1;
>>                                                 QUERY PLAN                  
>>                               
>> -----------------------------------------------------------------------------------------------------------
>>  Finalize Aggregate  (cost=130579654.20..130579654.21 rows=1 width=8)
>>    ->  Gather  (cost=130579654.16..130579654.17 rows=13 width=8)
>>          Workers Planned: 13
>>          ->  Partial Aggregate  (cost=130579654.16..130579654.17 rows=1
>> width=8)
>>                ->  Append  (cost=0.00..130557628.94 rows=8810089 width=0)
>>                      ->  Parallel Seq Scan on lineitem_ptr_0 
>> (cost=0.00..209424.27 rows=106127 width=0)
>>                      ->  Parallel Seq Scan on lineitem_ptr_1 
>> (cost=0.00..585852.15 rows=106115 width=0)
>>                      ->  Parallel Seq Scan on lineitem_ptr_2 
>> (cost=0.00..1043031.30 rows=106130 width=0)
>>                      ->  Parallel Seq Scan on lineitem_ptr_3 
>> (cost=0.00..1419621.31 rows=106131 width=0)
>> ...
>> ```
>
>Use of parallelism in this case may be the result of setting parallel
>workers on the individual partitions, that is, lineitem_ptr_* relations.
>
>Thanks,
>Amit
>