二月 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 必须确保索引的内容是稳定的,并且在底层数据保持不变的情况下,索引数据不需要随着时间的推移而更改。