Re: Parallel sec scan in plpgsql

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

ipdr=> create table test as select (random ()*1000)::int % 3 as a,
ipdr->                             (random ()*1000)::int % 5 as b,
ipdr->                             (random ()*1000)::int % 7 as c,
ipdr->                             (random ()*1000)::int % 11 as d,
ipdr->                             (random ()*1000)::int % 13 as e,
ipdr->                             (random ()*1000)::int % 17 as bytes
ipdr->                             from generate_series(1,10*1000*1000);
SELECT 10000000


ipdr=> create or replace function parallel_test_plpgsql() returns bigint as
ipdr-> $$
ipdr$> declare
ipdr$>    cnt int:=0;
ipdr$> begin
ipdr$>    select count(*) into cnt from (select a,b,c,d,e,sum(bytes) from test group by a,b,c,d,e)t;
ipdr$>    return cnt;
ipdr$> end;
ipdr$> $$ language plpgsql PARALLEL SAFE  STRICT;
CREATE FUNCTION

ipdr=>
ipdr=> create or replace function parallel_test_sql() returns bigint as
ipdr-> $$
ipdr$>    select count(*) from (select a,b,c,d,e,sum(bytes) from test group by a,b,c,d,e)t;
ipdr$> $$ language sql PARALLEL SAFE STRICT;
CREATE FUNCTION

ipdr=> analyze test;
ANALYZE
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=723.792..723.792 rows=1 loops=1)   Buffers: shared
hit=65015  ->  Finalize HashAggregate  (cost=87364.49..87514.64 rows=15015 width=28) (actual time=720.496..722.589
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=502.607..665.039 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=497.106..501.170 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.018..166.556
rows=833333loops=12)                           Buffers: shared hit=63695 Planning time: 0.250 ms Execution time:
724.293ms
 
(16 rows)

ipdr=> explain (analyze,buffers) select  parallel_test_plpgsql();                                           QUERY PLAN
------------------------------------------------------------------------------------------------ Gather
(cost=1000.00..1000.26rows=1 width=8) (actual time=4088.952..4088.956 rows=1 loops=1)   Workers Planned: 1   Workers
Launched:1   Single Copy: true   Buffers: shared hit=64186   ->  Result  (cost=0.00..0.26 rows=1 width=8) (actual
time=4084.997..4084.999rows=1 loops=1)         Buffers: shared hit=64149 Planning time: 0.025 ms Execution time:
4100.026ms
 
(9 rows)

Log from auto_explain:
2016-09-16 16:05:11 MSK [28209]: [1-1] user=,db=,app=,client= LOG:  duration: 4082.517 ms  plan:        Query Text:
selectcount(*)          from (select a,b,c,d,e,sum(bytes) from test group by a,b,c,d,e)t        Aggregate
(cost=289035.43..289035.44rows=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)
 


ipdr=> explain (analyze,buffers) select  parallel_test_sql();                                           QUERY PLAN
------------------------------------------------------------------------------------------------ Gather
(cost=1000.00..1000.26rows=1 width=8) (actual time=4256.830..4256.837 rows=1 loops=1)   Workers Planned: 1   Workers
Launched:1   Single Copy: true   Buffers: shared hit=64132   ->  Result  (cost=0.00..0.26 rows=1 width=8) (actual
time=4252.401..4252.403rows=1 loops=1)         Buffers: shared hit=64095 Planning time: 0.151 ms Execution time:
4267.959ms
 
(9 rows)

Log from auto_explain:
2016-09-16 16:22:03 MSK [731]: [1-1] user=,db=,app=,client= LOG:  duration: 4249.851 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=15015width=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)
 



So as we can see parallel secscan doesn't works in plpgsql and sql functions.
Can somebody explains me where I was wrong?


Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


On 16.09.2016 07:27, Ashutosh Bapat wrote:
> On Thu, Sep 15, 2016 at 9:15 PM, Alex Ignatov <a.ignatov@postgrespro.ru> wrote:
>> Hello!
>> Does parallel secscan works in plpgsql?
>>
>
> Parallel seq scan is a query optimization that will work independent
> of the source of the query - i.e whether it comes directly from a
> client or a procedural language like plpgsql. So, I guess, answer to
> your question is yes. If you are expecting something else, more
> context will help.
>