Thread: [PERFORM] Parallel sequential scan not supported for stored procedure withRETURN QUERY EXECUTE ?
[PERFORM] Parallel sequential scan not supported for stored procedure withRETURN QUERY EXECUTE ?
From
Tobias Gierke
Date:
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