sql - 查询优化器是否能够从 WHERE 子句中提取有效常量以避免重复计算？

Question

我会通过重写如下所示的函数获得任何性能优势吗:

CREATE FUNCTION some_text_predicate(name text) RETURNS boolean AS $$
    SELECT EXISTS (SELECT 1 FROM sc.foo
        WHERE foo.name = some_transform(name)
    );
$$ LANGUAGE SQL;

像这样:

CREATE FUNCTION some_text_predicate(text) RETURNS boolean AS $$
    DECLARE
        name text := some_transform($1);
    BEGIN
        RETURN EXISTS (SELECT 1 FROM sc.foo
            WHERE foo.name = name
        );
    END;
$$ LANGUAGE plpgsql;

理由是 some_transform 的结果是一个常量，不需要在 WHERE 子句执行的每一行上重新计算。查询优化器是否足够智能以将其提取为常量，或者我必须使用第二个函数定义才能获得我寻求的好处？

我正在寻找有关优化 WHERE 子句求值的详细信息。

Answer 1

这完全取决于 some_transform 和查询。

如果该函数是IMMUTABLE(保证对相同的参数返回相同的结果)或STABLE(在持续时间内对相同的参数返回相同的结果)当前查询并且不修改数据库)，您可以将您的功能标记为相同。

然后优化器将知道，如果参数在查询期间保持不变，则该函数只需计算一次。

IMMUTABLE 函数将在计划查询时计算，STABLE 函数在执行时计算一次。

这将帮助您进行类似的查询

WHERE a.x = some_text_predicate('const')

但不是

WHERE some_text_predicate(a.x) = 'const'

如果您可以从中受益，我更喜欢第一个函数，因为它更简单。

如果你无法避免函数被频繁调用，第二个函数可能更好，因为 PL/pgSQL 函数缓存执行计划。但是您无法避免以这种方式多次调用昂贵的函数。