From: | Tobias Gierke <tobias(dot)gierke(at)code-sourcery(dot)de> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Parallel sequential scan not supported for stored procedure with RETURN QUERY EXECUTE ? |
Date: | 2017-09-25 11:10:56 |
Message-ID: | 57255ffe-f51b-aba0-95e5-7be3c9b88bb9@code-sourcery.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
We're using PostgreSQL 9.6.3 on Linux.
I have a pl/pgsql stored procedure that is not utilizing the new
parallel sequential scan feature although manually running the same
query does (assuming the same settings/optimizer hints are used ofc).
A rough outline of the stored procedure (omitting all the boring parts)
is given below ; basically I'm dynamically creating a SQL statement and
then using RETURN QUERY EXECUTE to run it.
EXPLAIN'ing the query that's printed by the "RAISE NOTICE" (with the
same options as the stored procedure) produces a plan that uses parallel
execution but invoking the stored procedure obviously does not as the
execution time is orders of magnitudes slower.
Any ideas ?
Thanks,
Tobias
---------------------------------
CREATE OR REPLACE FUNCTION do_stuff(....lots of parameters...)
RETURNS SETOF importer.statistic_type AS
$BODY$
DECLARE
_sql text;
BEGIN
_sql := 'SELECT ''' || _hostname || '''::text AS hostname,'
'interval_start, '
'total_filesize, '
'total_filecount, '
'EXTRACT(EPOCH FROM combined_import_time_seconds) AS
combined_import_time_seconds, '
'min_throughput, '
'max_throughput, '
''''|| _filetype ||'''::text AS filetype, '
'busy_seconds '
'FROM ( SELECT '
'vf_cut_func(starttime' ||
_cut_func_parameter || ') AS interval_start, '
'sum(filesize) AS total_filesize, '
'count(*) AS total_filecount, '
'sum( endtime-starttime ) AS
combined_import_time_seconds, '
'min(filesize/EXTRACT(EPOCH FROM
endtime-starttime)) AS min_throughput, '
'max(filesize/EXTRACT(EPOCH FROM
endtime-starttime)) AS max_throughput, '
'busy_time_seconds(
tstzrange(starttime,MIN(endtime, vf_cut_func(starttime' ||
_cut_func_parameter || ') + ' || _interval || ') ) ) AS busy_seconds '
'FROM importer.log '
'WHERE filetype = '''|| _filetype ||''' '
'AND starttime >= ''' || _starttime || ''' '
'AND starttime < ''' || _endtime || ''' '
'AND hostname=''' || _hostname || ''' '
'GROUP BY vf_cut_func(starttime' ||
_cut_func_parameter || '), hostname) AS foo;';
RAISE NOTICE '_sql:%', _sql;
RETURN QUERY EXECUTE _sql;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
SET "TimeZone" TO 'utc'
set parallel_setup_cost TO 1
set max_parallel_workers_per_gather TO 4
set min_parallel_relation_size TO 1
set enable_indexscan TO false
set enable_bitmapscan TO false;
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Broers | 2017-09-25 16:21:41 | Re: query of partitioned object doesnt use index in qa |
Previous Message | Pavel Stehule | 2017-09-21 18:37:34 | Re: Query regarding EXPLAIN (ANALYZE,BUFFERS) |