[PERFORM] Parallel sequential scan not supported for stored procedure withRETURN QUERY EXECUTE ?

Поиск
Список
Период
Сортировка
От Tobias Gierke
Тема [PERFORM] Parallel sequential scan not supported for stored procedure withRETURN QUERY EXECUTE ?
Дата
Msg-id 57255ffe-f51b-aba0-95e5-7be3c9b88bb9@code-sourcery.de
обсуждение исходный текст
Список 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_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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: Re: [PERFORM] Query regarding EXPLAIN (ANALYZE,BUFFERS)
Следующее
От: Mike Broers
Дата:
Сообщение: Re: [PERFORM] query of partitioned object doesnt use index in qa