PostgreSQL 教程: 避免查询中冗余的函数调用

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