performance - 为什么 PostgreSQL 多次调用我的 STABLE/IMMUTABLE 函数？

Question

我正在尝试优化 PostgreSQL 9.1.2 中的复杂查询，它会调用一些函数。这些函数被标记为 STABLE 或 IMMUTABLE，并在查询中使用相同的参数多次调用。我假设 PostgreSQL 足够聪明，只为每组输入调用一次它们——毕竟，这就是 STABLE 和 IMMUTABLE 的意义所在，不是吗？但似乎这些函数被多次调用。我写了一个简单的函数来测试这一点，这证实了这一点:

CREATE OR REPLACE FUNCTION test_multi_calls1(one integer)
RETURNS integer
AS $BODY$
BEGIN
    RAISE NOTICE 'Called with %', one;
    RETURN one;
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;


WITH data AS
(
    SELECT 10 AS num
    UNION ALL SELECT 10
    UNION ALL SELECT 20
)
SELECT test_multi_calls1(num)
FROM data;

输出:

NOTICE:  Called with 10
NOTICE:  Called with 10
NOTICE:  Called with 20

为什么会这样，我怎样才能让它只执行一次函数？

Answer 1

您的测试代码的以下扩展提供了信息:

CREATE OR REPLACE FUNCTION test_multi_calls1(one integer)
RETURNS integer
AS $BODY$
BEGIN
    RAISE NOTICE 'Immutable called with %', one;
    RETURN one;
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION test_multi_calls2(one integer)
RETURNS integer
AS $BODY$
BEGIN
    RAISE NOTICE 'Volatile called with %', one;
    RETURN one;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;

WITH data AS
(
    SELECT 10 AS num
    UNION ALL SELECT 10
    UNION ALL SELECT 20
)
SELECT test_multi_calls1(num)
FROM data
where test_multi_calls2(40) = 40
and test_multi_calls1(30) = 30

输出:

NOTICE:  Immutable called with 30
NOTICE:  Volatile called with 40
NOTICE:  Immutable called with 10
NOTICE:  Volatile called with 40
NOTICE:  Immutable called with 10
NOTICE:  Volatile called with 40
NOTICE:  Immutable called with 20

在这里我们可以看到，在 select-list 中，不可变函数被调用了多次，在 where 子句中它被调用了一次，而 volatile 被调用了三次。

重要的不是 PostgreSQL 只会使用相同的数据调用一次 STABLE 或 IMMUTABLE 函数 - 您的示例清楚地表明情况并非如此 -它可能只调用一次。或者它可能会在必须调用 volatile 版本 50 次时调用它两次，依此类推。

利用稳定性和不变性的方式有多种，成本和 yield 也各不相同。为了提供您建议它应该使用选择列表进行的保存，它必须缓存结果，然后在返回缓存结果或调用缓存函数之前在此缓存中查找每个参数(或参数列表) -错过。这将比调用您的函数更昂贵，即使在缓存命中率很高的情况下也是如此(缓存命中率可能为 0%，这意味着这种“优化”做了额外的工作而完全没有 yield )。它可能只存储最后一个参数和结果，但同样可能完全没有用。

考虑到稳定和不可变函数通常是最轻量级的函数，这一点尤其重要。

然而，对于 where 子句，test_multi_calls1 的不变性允许 PostgreSQL 根据给定的 SQL 的简单含义实际重构查询:

For every row calculate test_multi_calls1(30) and if the result is equal to 30 continue processing the row in question

完全不同的查询计划:

Calculate test_multi_calls1(30) and if it is equal to 30 then continue with the query otherwise return a zero row result-set without any further calculation

这是 PostgreSQL 对 STABLE 和 IMMUTABLE 的一种使用——不是缓存结果，而是将查询重写为不同的查询，这些查询效率更高但给出相同的结果。

另请注意，test_multi_calls1(30) 在 test_multi_calls2(40) 之前被调用，无论它们在 where 子句中出现的顺序如何。这意味着，如果第一次调用没有返回任何行(将 = 30 替换为 = 31 进行测试)，则 volatile 函数根本不会被调用 - 再次无论哪个在 和 的哪一边。

这种特殊类型的重写取决于不变性或稳定性。使用 where test_multi_calls1(30) != num 查询重写将发生在不可变的情况下，而不仅仅是稳定的功能。使用 where test_multi_calls1(num) != 30 它根本不会发生(多次调用)，尽管还有其他可能的优化:

仅包含 STABLE 和 IMMUTABLE 函数的表达式可用于索引扫描。包含 VOLATILE 函数的表达式不能。调用次数可能会减少，也可能不会减少，但更重要的是，调用的结果将在查询的其余部分以更有效的方式使用(仅在大型表上才真正重要，但它可以产生大量区别)。

总而言之，不要从内存的角度来考虑波动率类别，而是要让 PostgreSQL 的查询规划器有机会以逻辑上等效(相同的结果)但效率更高的方式重组整个查询。