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=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=502.607..665.039 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=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=833333 loops=12)
Buffers: shared hit=63695
Planning time: 0.250 ms
Execution time: 724.293 ms
(16 rows)

ipdr=> explain (analyze,buffers) select parallel_test_plpgsql();
QUERY PLAN
------------------------------------------------------------------------------------------------
Gather (cost=1000.00..1000.26 rows=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.999 rows=1 loops=1)
Buffers: shared hit=64149
Planning time: 0.025 ms
Execution time: 4100.026 ms
(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: 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)

ipdr=> explain (analyze,buffers) select parallel_test_sql();
QUERY PLAN
------------------------------------------------------------------------------------------------
Gather (cost=1000.00..1000.26 rows=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.403 rows=1 loops=1)
Buffers: shared hit=64095
Planning time: 0.151 ms
Execution time: 4267.959 ms
(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=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)

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(dot)ignatov(at)postgrespro(dot)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.
>