二月 8, 2024
摘要：在本教程中，您将学习如何避免查询中冗余的函数调用。
目录
在 PostgreSQL 中，几乎可以用任何通用语言（如 Perl、Python 或 C）编写函数。一般来说，这提供了很大的灵活性和可接受的性能。但是，在某些情况下，用户可能会说：“我们感觉程序很慢”。事实是：PostgreSQL 可能不是这种现象的问题根源 - 在许多情况下，它只是“表面错误”。本文尝试来详细说明下由函数调用引起性能差的关键问题。
PostgreSQL 如何处理函数过程
如前所述，基本上可以用任何语言编写函数。PostgreSQL 只是将函数的代码传递给外部语言，并取回结果。在某种程度上，函数是一种黑盒子 – PostgreSQL 很少知道函数过程内部发生了什么。
下面是一个函数调用的示例：
CREATE OR REPLACE FUNCTION mymax(int, int)
RETURNS int AS
$$
BEGIN
RETURN CASE WHEN $1 > $2 THEN $1 ELSE $2 END;
END;
$$ LANGUAGE 'plpgsql';
结果不是很复杂：
test=# SELECT mymax(20, 30);
mymax
-------
30
(1 row)
这里重要的是：PL/pgSQL 函数完全是一个黑盒子。在这种情况下，规划器不知道“外部”语言做了什么事情。这有一些重要的含义。
请看以下示例：
test=# CREATE TABLE demo AS
SELECT *
FROM generate_series(1, 1000000) AS id;
SELECT 1000000
test=# CREATE INDEX idx_id ON demo(id);
CREATE INDEX
该表已经够大了，查询时会去使用索引：
test=# explain SELECT * FROM demo WHERE id = 20;
QUERY PLAN
---------------------------------------------------------------
Index Only Scan using idx_id on demo
(cost=0.42..8.44 rows=1 width=4)
Index Cond: (id = 20)
(2 rows)
函数调用通常是黑盒子
问题是：如果我们开始使用上面展示的函数，情况会完全改变：
test=# explain SELECT *
FROM demo
WHERE id = mymax(20, 20);
QUERY PLAN
---------------------------------------------------------
Seq Scan on demo (cost=0.00..266925.00 rows=1 width=4)
Filter: (id = mymax(20, 20))
(2 rows)
PostgreSQL 不知道该函数将返回 20。用户都很清楚，但没有人告诉数据库这个结果是肯定的。对于 PostgreSQL 来说，函数的结果被认为是 “VOLATILE” – 任何结果都可能出现。因此，它不能简单地要求索引提供正确的行。第一次调用函数的返回值可能与第二次调用函数不同，即使参数相同也是如此。优化器必须谨慎行事，并进行顺序扫描，这样肯定会产生正确的结果。
在 PostgreSQL 中，一个函数可以是：
- VOLATILE
- STABLE
- IMMUTABLE
如果一个函数被标记为 VOLATILE，那么如果你使用完全相同的输入参数多次调用它，它可能返回任何内容。在标记为 STABLE 的情况下，在同一事务中给定相同的参数多次调用它，该函数将返回相同的结果。
最典型的 STABLE 函数是now()，它在同一事务中始终会返回相同的结果：
test=# SELECT now();
now
-------------------------------
2018-01-09 11:48:46.385457+01
(1 row)
test=# BEGIN;
BEGIN
test=# SELECT now();
now
-------------------------------
2018-01-09 11:48:51.073123+01
(1 row)
test=# SELECT now();
now
-------------------------------
2018-01-09 11:48:51.073123+01
(1 row)
test=# COMMIT;
COMMIT
test=# SELECT now();
now
-------------------------------
2018-01-09 11:48:59.640697+01
(1 row)
有些函数甚至是 IMMUTABLE 的：在这种情况下，不管是什么样的事务，给定相同输入参数返回的结果将是恒定不变的。求余弦值是这类函数调用中的一个示例：
test=# SELECT cos(10), cos(20);
cos | cos
--------------------+-------------------
-0.839071529076452 | 0.408082061813392
(1 row)
test=# SELECT cos(10), cos(20);
cos | cos
--------------------+-------------------
-0.839071529076452 | 0.408082061813392
(1 row)
即使在第二个事务中，一个数字的余弦值也是相同的。
减少函数调用次数
为了解决我们的问题，我们必须将函数的易变性更改为IMMUTABLE：
ALTER FUNCTION mymax(int, int) IMMUTABLE;
规划器将检测到该函数会在给定相同输入值的情况下返回固定值，并进行索引扫描：
test=# explain SELECT *
FROM demo
WHERE id = mymax(20, 20);
QUERY PLAN
------------------------------------------------------
Index Only Scan using idx_id on demo
(cost=0.42..8.44 rows=1 width=4)
Index Cond: (id = 20)
(2 rows)
当然，索引扫描的速度要快好几个数量级，并且基本上会在很短的时间内返回。
检测瓶颈
幸运的是，PostgreSQL 有一个系统视图，它可以帮助您发现函数可能存在的问题。你要做的第一件事是设置 “track_functions = ‘all’ “ – 这样，它会告诉 PostgreSQL 去收集函数统计信息：
test=# SELECT * FROM demo WHERE id = mymax(20, 20);
id
----
20
(1 row)
在启用此设置后，pg_stat_user_functions 视图将包含有价值的信息：
test=# SELECT * FROM pg_stat_user_functions;
funcid | schemaname | funcname | calls | total_time | self_time
--------+------------+----------+-------+------------+-----------
16429 | public | mymax | 1 | 0.025 | 0.025
(1 row)
如果你碰巧看到一个函数经常被疯狂地调用，而且它碰巧是被标记为 VOLATILE，那么检查一下这个函数就很有必要。更改函数易变性可以显著提升速度。
基于函数创建索引
如果要基于一个函数创建索引，则必须确保函数本身实际上是 IMMUTABLE 的。否则，PostgreSQL 不会为您创建索引。原因很简单：PostgreSQL 必须确保索引的内容是稳定的，并且在底层数据保持不变的情况下，索引数据不需要随着时间的推移而更改。