Thread: Parallel Query - Can it be used within functions?
Dear community,
I need a bit of advice on parallel query execution.
I have a table with roughly 2 million rows in it. These rows are distributed over a set of IDs. Therefore I can group my rows based on the ID set.
If I query the table directly like this:
select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1;
mediatrunkid | count
--------------+-------
409612 | 11862
409613 | 11862
2101260 | 11860
2101261 | 11882
2101263 | 11809
2109452 | 11751
2117644 | 11833
2125836 | 11832
2125839 | 11852
2125841 | 11882
2129932 | 11882
2129933 | 11854
2134028 | 11718
2138124 | 11850
2142220 | 11861
2154508 | 11825
2187276 | 11826
(17 rows)
Time: 44,056 ms
I see from the query plan that it executed nicely in parallel using two workers:
2018-02-06 08:18:47.381 CET [18898] LOG: duration: 43.072 ms plan:
Query Text: select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1;
Finalize GroupAggregate (cost=57784.41..57792.66 rows=300 width=40) (actual time=40.583..40.596 rows=17 loops=1)
Group Key: mediatrunkid
Buffers: shared hit=3076
-> Sort (cost=57784.41..57785.91 rows=600 width=16) (actual time=40.578..40.582 rows=51 loops=1)
Sort Key: mediatrunkid
Sort Method: quicksort Memory: 27kB
Buffers: shared hit=3076
-> Gather (cost=57693.72..57756.72 rows=600 width=16) (actual time=40.534..40.561 rows=51 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=3076
-> Partial HashAggregate (cost=56693.72..56696.72 rows=300 width=16) (actual time=33.901..33.909 rows=17 loops=3)
Group Key: mediatrunkid
Buffers: shared hit=6006
-> Parallel Bitmap Heap Scan on mediatrunkkpi (cost=4525.01..56279.28 rows=82889 width=8) (actual time=4.575..23.145 rows=67080 loops=3)
Recheck Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
Heap Blocks: exact=2253
Buffers: shared hit=6006
-> Bitmap Index Scan on idx_mediatrunkkpi_trunk (cost=0.00..4475.27 rows=198933 width=0) (actual time=10.057..10.057 rows=201241 loops=1)
Index Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
Buffers: shared hit=823
But when I do the very same within a function:
create or replace function reports.generic_query(_sql text)
RETURNS SETOF record
LANGUAGE 'plpgsql'
PARALLEL SAFE
COST 100
SET "TimeZone"='utc'
STABLE
ROWS 10000
AS $BODY$
DECLARE
--
BEGIN
RETURN QUERY EXECUTE _sql;
END
$BODY$;
select * from reports.generic_query($$ select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1 $$) as foo (trunkid bigint, count numeric);
trunkid | count
---------+-------
2109452 | 11751
409613 | 11862
2125839 | 11852
2125841 | 11882
2142220 | 11861
2117644 | 11833
2154508 | 11825
2134028 | 11718
2101263 | 11809
2101261 | 11882
2129933 | 11854
2129932 | 11882
2125836 | 11832
2138124 | 11850
409612 | 11862
2187276 | 11826
2101260 | 11860
(17 rows)
Time: 86,275 ms
the execution time almost doubles, and the query no longer is considered for parallel execution:
2018-02-06 08:20:30.553 CET [18898] LOG: duration: 85.358 ms plan:
Query Text: select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1
HashAggregate (cost=60900.33..60904.08 rows=300 width=40) (actual time=85.341..85.348 rows=17 loops=1)
Group Key: mediatrunkid
Buffers: shared hit=6006
-> Bitmap Heap Scan on mediatrunkkpi (cost=4525.01..59905.66 rows=198933 width=8) (actual time=9.084..39.655 rows=201241 loops=1)
Recheck Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
Heap Blocks: exact=5183
Buffers: shared hit=6006
-> Bitmap Index Scan on idx_mediatrunkkpi_trunk (cost=0.00..4475.27 rows=198933 width=0) (actual time=8.386..8.386 rows=201241 loops=1)
Index Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
Buffers: shared hit=823
2018-02-06 08:20:30.553 CET [18898] CONTEXT: PL/pgSQL function reports.generic_query(text) line 5 at RETURN QUERY
2018-02-06 08:20:30.553 CET [18898] LOG: duration: 85.986 ms plan:
Query Text: select * from reports.generic_query($$ select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1 $$) as foo (trunkid bigint, count numeric);
Function Scan on generic_query foo (cost=0.25..100.25 rows=10000 width=40) (actual time=85.968..85.970 rows=17 loops=1)
Buffers: shared hit=6006
This whole topic around parallel execution is fairly new, but from the documentation I would assume that functions declared as parallel safe, will really be used in parallel. And even if the function itself is not invoked in parallel (maybe does not even make sense here), the function body for sure should run in parallel if I'm not mistaken.
So what do I obviously do wrong here?
Regards,
Michael
Hi, Am 06.02.2018 um 08:24 schrieb Michael Krüger: > create or replace function reports.generic_query(_sql text) > RETURNS SETOF record > LANGUAGE 'plpgsql' > PARALLEL SAFE > COST 100 there is an other parameter, parallel_setup_cost, with default = 1000. I think, you should set this parameter too. Please keep me informed, it is interessting me. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Dear all,
still same behavior with Postgres 10.2 ...
Just as a reminder that the issue still exists.
Regards,
Michael
Andreas Kretschmer <andreas@a-kretschmer.de> schrieb am Di., 6. Feb. 2018 um 08:35 Uhr:
Hi,
Am 06.02.2018 um 08:24 schrieb Michael Krüger:
> create or replace function reports.generic_query(_sql text)
> RETURNS SETOF record
> LANGUAGE 'plpgsql'
> PARALLEL SAFE
> COST 100
there is an other parameter, parallel_setup_cost, with default = 1000. I
think, you should set this parameter too.
Please keep me informed, it is interessting me.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
Ok, to close this thread. The problem is, that plpgsql function do seem to return data using a cursor. That fact is disabling parallel execution. So if we instead hand over the SQL to a function with e.g. a python body, then parallel execution is happening, because the data is first assembled in memory before it is returned, without using a cursor:
mkrueger=# explain analyze select * from reports.generic_query_python($$ select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1 $$) as foo (mediatrunkid bigint, count numeric);
LOG: 00000: duration: 35.158 ms plan:
Query Text: select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1
Partial HashAggregate (cost=56693.72..56696.72 rows=300 width=16) (actual time=35.144..35.149 rows=17 loops=1)
Group Key: mediatrunkid
Buffers: shared hit=1641
-> Parallel Bitmap Heap Scan on mediatrunkkpi (cost=4525.01..56279.28 rows=82889 width=8) (actual time=2.350..24.584 rows=63794 loops=1)
Recheck Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
Heap Blocks: exact=1641
Buffers: shared hit=1641
-> Bitmap Index Scan on idx_mediatrunkkpi_trunk (cost=0.00..4475.27 rows=198933 width=0) (never executed)
Index Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
LOCATION: explain_ExecutorEnd, auto_explain.c:359
LOG: 00000: duration: 35.165 ms plan:
Query Text: select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1
Partial HashAggregate (cost=56693.72..56696.72 rows=300 width=16) (actual time=35.152..35.157 rows=17 loops=1)
Group Key: mediatrunkid
Buffers: shared hit=1630
-> Parallel Bitmap Heap Scan on mediatrunkkpi (cost=4525.01..56279.28 rows=82889 width=8) (actual time=2.364..24.702 rows=63272 loops=1)
Recheck Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
Heap Blocks: exact=1630
Buffers: shared hit=1630
-> Bitmap Index Scan on idx_mediatrunkkpi_trunk (cost=0.00..4475.27 rows=198933 width=0) (never executed)
Index Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
LOCATION: explain_ExecutorEnd, auto_explain.c:359
LOG: 00000: duration: 47.855 ms plan:
Query Text: select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1
Finalize GroupAggregate (cost=57784.41..57792.66 rows=300 width=40) (actual time=45.331..45.344 rows=17 loops=1)
Group Key: mediatrunkid
Buffers: shared hit=2735
-> Sort (cost=57784.41..57785.91 rows=600 width=16) (actual time=45.322..45.325 rows=51 loops=1)
Sort Key: mediatrunkid
Sort Method: quicksort Memory: 27kB
Buffers: shared hit=2735
-> Gather (cost=57693.72..57756.72 rows=600 width=16) (actual time=45.270..45.295 rows=51 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2735
-> Partial HashAggregate (cost=56693.72..56696.72 rows=300 width=16) (actual time=38.387..38.391 rows=17 loops=3)
Group Key: mediatrunkid
Buffers: shared hit=6006
-> Parallel Bitmap Heap Scan on mediatrunkkpi (cost=4525.01..56279.28 rows=82889 width=8) (actual time=5.564..27.399 rows=67080 loops=3)
Recheck Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
Heap Blocks: exact=1912
Buffers: shared hit=6006
-> Bitmap Index Scan on idx_mediatrunkkpi_trunk (cost=0.00..4475.27 rows=198933 width=0) (actual time=11.229..11.229 rows=201241 loops=1)
Index Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))
Buffers: shared hit=823
LOCATION: explain_ExecutorEnd, auto_explain.c:359
LOG: 00000: duration: 49.924 ms plan:
Query Text: explain analyze select * from reports.generic_query_python($$ select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1 $$) as foo (mediatrunkid bigint, count numeric);
Function Scan on generic_query_python foo (cost=0.25..50.25 rows=5000 width=40) (actual time=49.920..49.922 rows=17 loops=1)
Buffers: shared hit=6388
LOCATION: explain_ExecutorEnd, auto_explain.c:359
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Function Scan on generic_query_python foo (cost=0.25..50.25 rows=5000 width=40) (actual time=49.920..49.922 rows=17 loops=1)
Planning time: 0.029 ms
Execution time: 49.977 ms
(3 rows)
Michael Krüger <michael@kruegers.email> schrieb am Fr., 16. Feb. 2018 um 11:42 Uhr:
Dear all,still same behavior with Postgres 10.2 ...Just as a reminder that the issue still exists.Regards,MichaelAndreas Kretschmer <andreas@a-kretschmer.de> schrieb am Di., 6. Feb. 2018 um 08:35 Uhr:Hi,
Am 06.02.2018 um 08:24 schrieb Michael Krüger:
> create or replace function reports.generic_query(_sql text)
> RETURNS SETOF record
> LANGUAGE 'plpgsql'
> PARALLEL SAFE
> COST 100
there is an other parameter, parallel_setup_cost, with default = 1000. I
think, you should set this parameter too.
Please keep me informed, it is interessting me.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
ah, and for completeness the simple python function I use for the test:
create or replace function reports.generic_query_python(_sql text)
RETURNS SETOF record
LANGUAGE 'plpythonu'
PARALLEL SAFE
COST 100
VOLATILE
ROWS 5000
AS $BODY$
return plpy.execute( _sql )
$BODY$;
Michael Krüger <michael@kruegers.email> schrieb am Mi., 28. Feb. 2018 um 09:05 Uhr:
Ok, to close this thread. The problem is, that plpgsql function do seem to return data using a cursor. That fact is disabling parallel execution. So if we instead hand over the SQL to a function with e.g. a python body, then parallel execution is happening, because the data is first assembled in memory before it is returned, without using a cursor:mkrueger=# explain analyze select * from reports.generic_query_python($$ select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1 $$) as foo (mediatrunkid bigint, count numeric);LOG: 00000: duration: 35.158 ms plan:Query Text: select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1Partial HashAggregate (cost=56693.72..56696.72 rows=300 width=16) (actual time=35.144..35.149 rows=17 loops=1)Group Key: mediatrunkidBuffers: shared hit=1641-> Parallel Bitmap Heap Scan on mediatrunkkpi (cost=4525.01..56279.28 rows=82889 width=8) (actual time=2.350..24.584 rows=63794 loops=1)Recheck Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))Heap Blocks: exact=1641Buffers: shared hit=1641-> Bitmap Index Scan on idx_mediatrunkkpi_trunk (cost=0.00..4475.27 rows=198933 width=0) (never executed)Index Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))LOCATION: explain_ExecutorEnd, auto_explain.c:359LOG: 00000: duration: 35.165 ms plan:Query Text: select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1Partial HashAggregate (cost=56693.72..56696.72 rows=300 width=16) (actual time=35.152..35.157 rows=17 loops=1)Group Key: mediatrunkidBuffers: shared hit=1630-> Parallel Bitmap Heap Scan on mediatrunkkpi (cost=4525.01..56279.28 rows=82889 width=8) (actual time=2.364..24.702 rows=63272 loops=1)Recheck Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))Heap Blocks: exact=1630Buffers: shared hit=1630-> Bitmap Index Scan on idx_mediatrunkkpi_trunk (cost=0.00..4475.27 rows=198933 width=0) (never executed)Index Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))LOCATION: explain_ExecutorEnd, auto_explain.c:359LOG: 00000: duration: 47.855 ms plan:Query Text: select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1Finalize GroupAggregate (cost=57784.41..57792.66 rows=300 width=40) (actual time=45.331..45.344 rows=17 loops=1)Group Key: mediatrunkidBuffers: shared hit=2735-> Sort (cost=57784.41..57785.91 rows=600 width=16) (actual time=45.322..45.325 rows=51 loops=1)Sort Key: mediatrunkidSort Method: quicksort Memory: 27kBBuffers: shared hit=2735-> Gather (cost=57693.72..57756.72 rows=600 width=16) (actual time=45.270..45.295 rows=51 loops=1)Workers Planned: 2Workers Launched: 2Buffers: shared hit=2735-> Partial HashAggregate (cost=56693.72..56696.72 rows=300 width=16) (actual time=38.387..38.391 rows=17 loops=3)Group Key: mediatrunkidBuffers: shared hit=6006-> Parallel Bitmap Heap Scan on mediatrunkkpi (cost=4525.01..56279.28 rows=82889 width=8) (actual time=5.564..27.399 rows=67080 loops=3)Recheck Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))Heap Blocks: exact=1912Buffers: shared hit=6006-> Bitmap Index Scan on idx_mediatrunkkpi_trunk (cost=0.00..4475.27 rows=198933 width=0) (actual time=11.229..11.229 rows=201241 loops=1)Index Cond: (mediatrunkid = ANY ('{2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028}'::bigint[]))Buffers: shared hit=823LOCATION: explain_ExecutorEnd, auto_explain.c:359LOG: 00000: duration: 49.924 ms plan:Query Text: explain analyze select * from reports.generic_query_python($$ select mediatrunkid,count(*)::numeric from reports.mediatrunkkpi where mediatrunkid in (2129932,2101261,2125841,409613,409612,2142220,2101260,2129933,2125839,2138124,2117644,2125836,2187276,2154508,2101263,2109452,2134028) group by 1 $$) as foo (mediatrunkid bigint, count numeric);Function Scan on generic_query_python foo (cost=0.25..50.25 rows=5000 width=40) (actual time=49.920..49.922 rows=17 loops=1)Buffers: shared hit=6388LOCATION: explain_ExecutorEnd, auto_explain.c:359QUERY PLAN-------------------------------------------------------------------------------------------------------------------------------Function Scan on generic_query_python foo (cost=0.25..50.25 rows=5000 width=40) (actual time=49.920..49.922 rows=17 loops=1)Planning time: 0.029 msExecution time: 49.977 ms(3 rows)
Am 06.02.2018 um 08:24 schrieb Michael Krüger:
> create or replace function reports.generic_query(_sql text)
> RETURNS SETOF record
> LANGUAGE 'plpgsql'
> PARALLEL SAFE
> COST 100
there is an other parameter, parallel_setup_cost, with default = 1000. I
think, you should set this parameter too.
Please keep me informed, it is interessting me.
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com