为什么PostgreSQL调用我的稳定/不可变函数多次

我试图在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

为什么会发生这种情况，我如何才能让它只执行一次函数?

您的测试代码的以下扩展是有用的:

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

在这里我们可以看到，虽然在选择列表中不可变函数被调用了多次，但在where子句中它只被调用了一次，而易变函数被调用了三次。

重要的不是PostgreSQL对相同的数据只调用一次STABLE或IMMUTABLE函数——你的例子清楚地表明情况并非如此——而是它可能只调用它一次。或者，当它需要调用volatile版本50次时，它可能会调用它两次，等等。

有不同的方法可以利用稳定性和不变性，成本和收益也不同。为了提供您建议它应该使用选择列表进行的那种保存，它必须缓存结果，然后在返回缓存结果或在缓存丢失时调用函数之前查找此缓存中的每个参数(或参数列表)。这将比调用你的函数更昂贵，即使在有高比例的缓存命中的情况下(可能有0%的缓存命中意味着这个"优化"做了额外的工作，绝对没有收获)。它可能只存储最后一个参数和结果，但同样，这可能是完全无用的。

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

使用where子句，test_multi_calls1的不变性允许PostgreSQL实际上从给定的SQL的普通含义重构查询:

对于每一行计算test_multi_calls1(30)，如果结果是等于30继续处理有问题的行

到完全不同的查询计划:

计算test_multi_calls1(30)，如果它等于30则继续查询，否则返回零行结果集任何进一步的计算

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

还要注意test_multi_calls1(30)在test_multi_calls2(40)之前被调用，无论它们在where子句中出现的顺序如何。这意味着，如果第一次调用的结果是没有返回行(用= 31替换= 30来测试)，那么volatile函数将根本不会被调用-再次不管哪个是在and的哪一边。

这种特殊的重写依赖于不变性或稳定性。在where test_multi_calls1(30) != num中，不可变函数的查询会被重写，而稳定函数则不会。对于where test_multi_calls1(num) != 30，它根本不会发生(多次调用)，尽管还有其他可能的优化:

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

总而言之，不要从记忆的角度来考虑波动性分类，而是从给PostgreSQL的查询规划器机会的角度来考虑，以逻辑上等效(相同的结果)但更高效的方式重构整个查询。

根据文档IMMUTABLE函数将在给定相同参数的情况下返回相同的值。由于您正在提供动态参数(甚至一次都不相同)，优化器没有理由相信它会得到相同的结果，因此调用该函数。更好的问题是:为什么你的查询调用函数多次，如果它不需要?

相关内容

最新更新

热门标签：