public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tobias Gierke <[email protected]>
To: [email protected]
Subject: Parallel sequential scan not supported for stored procedure with RETURN QUERY EXECUTE ?
Date: Mon, 25 Sep 2017 13:10:56 +0200
Message-ID: <[email protected]> (raw)
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-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;



-- 
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Parallel sequential scan not supported for stored procedure with RETURN QUERY EXECUTE ?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox