sql - 如何优化 Postgres 中存储过程的性能？

Question

我在 Ubuntu 18.04.1 上使用 PostgreSQL 10.9。以下存储过程确实将数组作为输入和变量 beta。两个输入变量都用于计算聚合索引。数组可以有不同的长度。由于我在另一个查询中多次使用此函数，因此此函数的性能非常重要。我删除了一个 CTE 并将其更改为子查询，这有点帮助。但是我找不到任何其他方法来进一步提高性能。

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$

函数可以这样调用

SELECT part_accessibility_index(ARRAY[100.2,323.9,1323.313,432.232,1100.22,144.1], -0.001)

我得到以下查询计划。

ProjectSet  (cost=0.00..5.27 rows=1000 width=32) (actual time=0.180..0.184 rows=1 loops=1)
  ->  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

如前所述，查询并不是真的很慢，但可能仍有改进的方法。如果有任何帮助，我将非常高兴!

Answer 1

您可以尝试一些可能有助于提高性能的方法:

1. 用 C 语言重写函数。这显然是最费力的，但如果您真的需要性能，这可能是最大的返回。
2. 您可以通过将合并向下移动到 coalesce(sum(part), 0) 来删除一个子查询。事实上，我认为您不需要任何子查询:

  SELECT coalesce(sum(exp(part*beta)), 0) sum_index
        FROM UNNEST(traveltime_array) AS u(part)

1. 您的函数不返回集合。将其更改为仅返回数字。
2. 尝试将其更改为 plpgsql 函数。如果查询计划没有比通用计划差很多，则可以在每个 session 第五次执行后缓存查询计划。
3. 由于该函数不修改数据库或使用 session 信息，因此可以安全地将其标记为不可变的，这可能有助于优化器选择更好的计划。
4. 同样，该函数可以标记为并行安全的，这将允许并行 worker 调用该函数。如果可以并行化调用查询，这可能会有所帮助。