Re: Parallel Query - Can it be used within functions? - Mailing list pgsql-general

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,

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