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_gatherTO 4 set min_parallel_relation_size TO 1 set enable_indexscan TO false set
enable_bitmapscanTO false;
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance