使用函数波动性优化 PostgreSQL 查询:Volatile, Stable, and Immutable

1.overview

在 PostgreSQL 中，函数的不同波动性选择——即 Volatile（易变）、Stable（稳定）和Immutable（不可变）——在查询优化器规划和执行查询过程中起着重要作用。理解这些类别并正确应用它们可以带来显著的性能改进。让我们深入探讨每个类别，包括详细的示例，以说明它们的用例以及它们如何影响 PostgreSQL 的优化器。

2. Volatile函数

何时使用：当函数的输出在同一会话中具有相同输入参数的调用之间可能发生变化时，应使用volatile函数。 这包括进行外部调用、查询或修改数据库或依赖于非确定性源（例如curren time）的函数。
优化器行为：优化器假设volatile函数的结果在每次调用时都会发生变化。 因此，每次函数出现在查询中时，它都会重新执行该函数，从而避免任何依赖于函数结果的cache(缓存)或precomputing(预计算)的优化。
Example:

CREATE OR REPLACE FUNCTION fetch_latest_price(stock_symbol text)
RETURNS numeric AS $$
BEGIN
    -- Imagine this function queries a constantly updating table of stock prices
    SELECT price INTO STRICT FROM stock_prices WHERE symbol = stock_symbol ORDER BY updated_at DESC LIMIT 1;
    RETURN price;
END;
$$ LANGUAGE plpgsql VOLATILE;

-- Example Query
SELECT stock_symbol, fetch_latest_price(stock_symbol)
FROM stocks
WHERE stock_symbol = 'AAPL';

1
2
3
4
5
6
7
8
9
10
11
12
13
14

在此示例中， fetch_latest_price 是不稳定的(volatile)，因为股票的价格可能会在调用之间发生变化。 PostgreSQL 将在每次调用时执行该函数，以确保检索到最新的价格。

3. Stable函数

何时使用：Stable 函数在以下情况下适用,当函数在单个事务或查询执行中对于相同的输入参数产生一致的输出，但在不同的事务之间可能会有所不同时。这些函数不会修改数据库，并且不受查询运行过程中可能发生的数据库状态变化的影响。
优化器行为：优化器可以对stable函数做出某些假设，比如在单个查询执行中重复使用结果。这可以减少函数调用次数，提高查询性能。
Example:

CREATE OR REPLACE FUNCTION user_has_permission(user_id int, permission text)
RETURNS boolean AS $$
BEGIN
    -- Checks if a user has a specific permission, which is unlikely to change during the execution of a query
    SELECT EXISTS (
        SELECT 1 FROM user_permissions WHERE user_id = user_id AND permission = permission
    ) INTO result;
    RETURN result;
END;
$$ LANGUAGE plpgsql STABLE;

-- Example Query
SELECT username
FROM users
WHERE user_has_permission(id, 'view_dashboard');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在这种情况下， user_has_permission 是稳定的，因为虽然用户权限可能会随着时间的推移而改变，但它们在查询执行期间不太可能改变。 优化器可以为每个用户调用一次该函数并重用结果，从而避免不必要的数据库查找。

4. Immutable 函数

何时使用：当输出结果完全由输入参数决定，并且无论会话状态或事务如何改变都不会变化，应使用immutable函数。
优化器行为：优化器可以对immutable函数应用最激进的优化。它可以在查询规划时预先计算结果，在多次执行中缓存结果，并在索引表达式中使用这些结果。
Example:

CREATE OR REPLACE FUNCTION add_numbers(a int, b int)
RETURNS int AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Example Query
SELECT add_numbers(5, 10) AS result;
1
2
3
4
5
6
7
8
9

add_numbers 是不可变的，因为两个数字的和不会改变。在可能的情况下，PostgreSQL 可以预先计算对此函数的调用，甚至可以优化使用 add_numbers 的条件或存储过程的查询。

结论

将函数正确分类为volatile、stable或immutable可以让 PostgreSQL 的优化器做出明智的决策，从而制定更高效的执行计划。 易失性函数提供新鲜度，稳定函数平衡可预测性与灵活性，不可变函数通过结果缓存和预计算实现最高程度的优化。 了解和利用这些类别可以极大地提高基于 PostgreSQL 的应用程序的性能和可扩展性。