二月 17, 2024
摘要：在本教程中，您将学习如何调整函数的估计执行成本。
目录
介绍
当您在 PostgreSQL 中引用/调用函数时，优化器实际上不太了解执行的成本和函数返回的行数。这并不奇怪，因为很难预测函数在做什么，以及给定的一组输入参数将返回多少行。您可能不知道的是，其实您可以告诉优化器更多有关您的函数的成本信息。
PostgreSQL 为函数提供了 ROWS 和 COST 声明。它们可用于用任何语言编写的任何 PostgreSQL 函数。这些声明允许函数设计者向规划器指示预期的行数，并提供有关函数调用的开销的提示。COST 以 CPU 周期为单位。COST 数值越高，成本越高。例如，如果在 WHERE 子句中的 AND 表达式中，有任何成本较低的函数计算返回逻辑否的结果，则不会再调用成本高的函数。ROWS 和 COST 的数值可以让规划器更好地选择使用哪种策略。
在 WHERE 子句中使用函数
让我们开始设置一个小的测试用例：
CREATE TABLE t1 (id integer, str text);
INSERT INTO t1 (id, str)
SELECT a.i, repeat(chr(65 + mod(a.r, 26)), 10 + mod(a.r, 100)) as str
FROM
(SELECT i, CAST(random() * 1000 AS int) + 1 AS r
FROM generate_series(1, 1000) AS s(i)) AS a;
创建了一个包含 1000 行的简单表。此外，让我们再创建 2 个简单的函数：
CREATE FUNCTION fn_cheapfunction(id in int)
RETURNS integer AS $$
BEGIN
RETURN mod(id, 10);
END $$ LANGUAGE plpgsql;
CREATE FUNCTION fn_costlyfunction(str in text)
RETURNS integer AS $$
BEGIN
PERFORM pg_sleep(0.01); -- make this function slower
RETURN length(str);
END $$ LANGUAGE plpgsql;
当您在 WHERE 子句中调用这些函数时，优化器会做什么？
EXPLAIN (analyze) SELECT count(*) FROM t1
WHERE (fn_costlyfunction(str) > 80 AND fn_cheapfunction(id) > 8);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Aggregate (cost=526.28..526.29 rows=1 width=8) (actual time=15710.681..15710.681 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..526.00 rows=111 width=0) (actual time=293.123..15710.539 rows=27 loops=1)
Filter: ((fn_costlyfunction(str) > 80) AND (fn_cheapfunction(id) > 8))
Rows Removed by Filter: 973
Planning time: 2.181 ms
Execution time: 15714.909 ms
(6 rows)
指定函数的估计成本
PostgreSQL 为函数定义提供了一个 COST 参数。这是一个正数，用于指定函数的估计执行成本，以 cpu_operator_cost 为单位。如果函数返回一个集合，那它是返回每行的成本。如果未指定成本，则 C 语言函数和内部函数默认为 1 个单位，所有其他语言的函数默认为 100 个单位。更大的值会导致规划器尝试避免对该函数的不必要的过多计算。
要更改上面的函数的估计执行成本，请执行以下操作：
ALTER FUNCTION fn_cheapfunction(id in int) COST 10; -- a lower cost
ALTER FUNCTION fn_costlyfunction(str in text) COST 1000; -- a higher cost
规划器会检测到fn_costlyfunction()执行开销大，并首先运行fn_cheapfunction()，即使它在顺序上是第二个出现的函数。再来看一下执行计划：
EXPLAIN (analyze) SELECT count(*) FROM t1
WHERE (fn_costlyfunction(str) > 80 AND fn_cheapfunction(id) > 8);
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Aggregate (cost=2776.28..2776.29 rows=1 width=8) (actual time=1575.873..1575.873 rows=1 loops=1)
-> Seq Scan on t1 (cost=0.00..2776.00 rows=111 width=0) (actual time=36.927..1575.726 rows=27 loops=1)
Filter: ((fn_cheapfunction(id) > 8) AND (fn_costlyfunction(str) > 80))
Rows Removed by Filter: 973
Planning time: 0.142 ms
Execution time: 1575.958 ms
(6 rows)
现在，查询的运行速度比之前快多了。