On 16.09.2016 16:50, Amit Kapila wrote:
On Fri, Sep 16, 2016 at 6:57 PM, Alex Ignatov <a.igna...@postgrespro.ru> wrote:
No it doesn't.
Paralleling neither sql function nor plpgsql:
Here is example :
ipdr=> show max_worker_processes ;
max_worker_processes
----------------------
128
(1 row)
ipdr=> set max_parallel_workers_per_gather to 128;
SET
ipdr=> set force_parallel_mode=on;
SET
ipdr=> set min_parallel_relation_size =0;
SET
ipdr=> set parallel_tuple_cost=0;
SET
Can you try by setting force_parallel_mode = off;? I think it is
sending the whole function execution to worker due to
force_parallel_mode.
No changes:
ipdr=> set max_parallel_workers_per_gather to 128;
SET
ipdr=> set min_parallel_relation_size =0;
SET
ipdr=> set parallel_tuple_cost=0;
SET
ipdr=> set force_parallel_mode = off;
SET
ipdr=> select name,setting from pg_settings where name
in('max_parallel_workers_per_gather',
ipdr(>
'min_parallel_relation_size',
ipdr(> 'parallel_tuple_cost',
ipdr(>
'force_parallel_mode');
name | setting
---------------------------------+---------
force_parallel_mode | off
max_parallel_workers_per_gather | 128
min_parallel_relation_size | 0
parallel_tuple_cost | 0
(4 rows)
ipdr=> explain (analyze,buffers) select count(*) from (select
a,b,c,d,e,sum(bytes) from test group by a,b,c,d,e)t;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=87702.33..87702.34 rows=1 width=8) (actual
time=709.643..709.643 rows=1 loops=1)
Buffers: shared hit=65015
-> Finalize HashAggregate (cost=87364.49..87514.64 rows=15015 width=28)
(actual time=706.382..708.456 rows=15015 loops=1)
Group Key: test.a, test.b, test.c, test.d, test.e
Buffers: shared hit=65015
-> Gather (cost=85149.78..85299.93 rows=165165 width=20) (actual
time=478.626..645.209 rows=180180 loops=1)
Workers Planned: 11
Workers Launched: 11
Buffers: shared hit=65015
-> Partial HashAggregate (cost=84149.78..84299.93 rows=15015
width=20) (actual time=473.890..478.309 rows=15015 loops=12)
Group Key: test.a, test.b, test.c, test.d, test.e
Buffers: shared hit=63695
-> Parallel Seq Scan on test (cost=0.00..72786.01
rows=909101 width=20) (actual time=0.021..163.120 rows=833333 loops=12)
Buffers: shared hit=63695
Planning time: 0.318 ms
Execution time: 710.600 ms
(16 rows)
ipdr=> explain (analyze,buffers) select parallel_test_plpgsql();
QUERY PLAN
------------------------------------------------------------------------------------------
Result (cost=0.00..0.26 rows=1 width=8) (actual time=4003.719..4003.720
rows=1 loops=1)
Buffers: shared hit=63869
Planning time: 0.021 ms
Execution time: 4003.769 ms
(4 rows)
auto_explain:
2016-09-16 18:02:29 MSK [29353]: [53-1]
user=ipdr,db=ipdr,app=psql,client=[local] LOG: duration: 4001.275 ms plan:
Query Text: select count(*) from (select a,b,c,d,e,sum(bytes)
from test group by a,b,c,d,e)t
Aggregate (cost=289035.43..289035.44 rows=1 width=8)
-> HashAggregate (cost=288697.59..288847.74 rows=15015 width=28)
Group Key: test.a, test.b, test.c, test.d, test.e
-> Seq Scan on test (cost=0.00..163696.15 rows=10000115
width=20)
2016-09-16 18:02:29 MSK [29353]: [54-1] user=ipdr,db=ipdr,app=psql,client=[local]
CONTEXT: SQL statement "select count(*) from (select a,b,c,d,e,sum(bytes)
from test group by a,b,c,d,e)t"
PL/pgSQL function parallel_test_plpgsql() line 5 at SQL statement
ipdr=> explain (analyze,buffers) select parallel_test_plpgsql();
QUERY PLAN
------------------------------------------------------------------------------------------
Result (cost=0.00..0.26 rows=1 width=8) (actual time=4497.820..4497.822
rows=1 loops=1)
Buffers: shared hit=63695
Planning time: 0.023 ms
Execution time: 4497.872 ms
(4 rows)
auto_explain:
2016-09-16 18:03:23 MSK [29353]: [57-1]
user=ipdr,db=ipdr,app=psql,client=[local] LOG: duration: 4497.050 ms plan:
Query Text: select count(*) from (select a,b,c,d,e,sum(bytes)
from test group by a,b,c,d,e)t
Aggregate (cost=289035.43..289035.44 rows=1 width=8)
-> HashAggregate (cost=288697.59..288847.74 rows=15015 width=28)
Group Key: test.a, test.b, test.c, test.d, test.e
-> Seq Scan on test (cost=0.00..163696.15 rows=10000115
width=20)
2016-09-16 18:03:23 MSK [29353]: [58-1] user=ipdr,db=ipdr,app=psql,client=[local]
CONTEXT: SQL statement "select count(*) from (select a,b,c,d,e,sum(bytes)
from test group by a,b,c,d,e)t"
PL/pgSQL function parallel_test_plpgsql() line 5 at SQL statement
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
