title: How to optimize the performance of a stored procedure in Postgres?
body: <p>I am using PostgreSQL 10.9 on Ubuntu 18.04.1. The follwing stored procedure does take an array as input and a variable beta. Both input variables are used to compute an aggregated index. The array can have a varying length. 
  As I am using this function several thousands times inside another query, the performance of this function is very important. I removed a CTE and changed it to a subquery, this helped a bit. However I couldn't find any other way to improve performance further. </p>
  
  <pre><code>CREATE OR REPLACE FUNCTION public.part_accessibility_index(traveltime_array NUMERIC[], beta numeric)
  RETURNS SETOF NUMERIC 
  
  LANGUAGE sql
  AS $function$
  
      SELECT COALESCE(sum_index,0) 
      FROM 
      (
          SELECT sum(part) sum_index
          FROM ( 
              SELECT EXP(UNNEST(traveltime_array)*(beta)) AS part
          )
          index_part 
      )
      sum_index;
  
  $function$
  </code></pre>
  
  <p>The function can be called likes this</p>
  
  <pre><code>SELECT part_accessibility_index(ARRAY[100.2,323.9,1323.313,432.232,1100.22,144.1], -0.001)
  </code></pre>
  
  <p>I get the following query plan. </p>
  
  <pre><code>ProjectSet  (cost=0.00..5.27 rows=1000 width=32) (actual time=0.180..0.184 rows=1 loops=1)
    -&gt;  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
  Planning time: 0.022 ms
  Execution time: 0.197 ms
  </code></pre>
  
  <p>As mentioned the query is not really slow but probably there are still ways in improving it. I would be very happy for any help!</p>