Re: Parallel sec scan in plpgsql

On 16.09.2016 16:50, Amit Kapila wrote:
> On Fri, Sep 16, 2016 at 6:57 PM, Alex Ignatov <a.ignatov@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=15015loops=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               ->
PartialHashAggregate  (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=833333loops=12)                           Buffers: shared hit=63695 Planning time: 0.318 ms Execution time:
710.600ms
 
(16 rows)


ipdr=> explain (analyze,buffers) select  parallel_test_plpgsql();                                        QUERY PLAN
------------------------------------------------------------------------------------------ Result  (cost=0.00..0.26
rows=1width=8) (actual time=4003.719..4003.720 rows=1 loops=1)   Buffers: shared hit=63869 Planning time: 0.021 ms
Executiontime: 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.15rows=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=1width=8) (actual time=4497.820..4497.822 rows=1 loops=1)   Buffers: shared hit=63695 Planning time: 0.023 ms
Executiontime: 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.15rows=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