Обсуждение: [BUGS] BUG #14732: partitioned table cann't alter set parallel_workers?
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 ``` 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 Time: 11.803 ms 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 Scanon 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) ... ``` best regards. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
HI,
Thank you for your reply, but i mean it's good for user if PG support ```alter partitioned table set (parallel_workers=?)``` syntax.
because there some partition tables sometimes, so direct set partition table is not comfortable.
best regards.
--
公益是一辈子的事,I'm Digoal,Just Do It.
公益是一辈子的事,I'm Digoal,Just Do It.
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 >