主页 / dba / 问题 / 294532
Accepted
LaVache
Asked: 2021-06-20 01:59:01 +0800 CST

SELECT 中的廉价函数如何使整个查询变慢？

  • 772

我正在使用带有内部和外部查询的 Postgres 13.3，它们都只产生一行（只是一些关于行数的统计信息）。

我不明白为什么下面的 Query2 比 Query1 慢得多。它们基本上应该几乎完全相同，最多可能相差几毫秒......

查询1：需要49秒

WITH t1 AS (
        SELECT
            (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,
            (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,
            (SELECT COUNT(*) FROM racing.xday) AS xday_row_count
        OFFSET 0 -- this is to prevent inlining
)

SELECT
            t1.all_count,
            t1.all_count-t1.todo_count AS done_count,
            t1.todo_count,
            t1.xday_row_count
FROM t1;

Query2：耗时 4 分 30 秒

我只添加了一行：

WITH t1 AS (
        SELECT
            (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,
            (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,
            (SELECT COUNT(*) FROM racing.xday) AS xday_row_count
        OFFSET 0 -- this is to prevent inlining
)

SELECT
            t1.all_count,
            t1.all_count-t1.todo_count AS done_count,
            t1.todo_count,
            t1.xday_row_count,
            -- the line below is the only difference to Query1:
            util.divide_ints_and_get_percentage_string(todo_count, all_count) AS todo_percentage
FROM t1;

在此之前，并且在外部查询中有一些额外的列（应该几乎为零差异），整个查询非常慢，比如 25 分钟，我认为这可能是由于内联？因此OFFSET 0被添加到两个查询中（这确实有很大帮助）。

我也一直在使用上述 CTE 与子查询之间进行交换，但OFFSET 0包含它似乎没有任何区别。

Query2 中调用的函数的定义：

CREATE OR REPLACE FUNCTION util.ratio_to_percentage_string(FLOAT, INTEGER) RETURNS TEXT AS $$ BEGIN
    RETURN ROUND($1::NUMERIC * 100, $2)::TEXT || '%';
END; $$ LANGUAGE plpgsql IMMUTABLE;


CREATE OR REPLACE FUNCTION util.divide_ints_and_get_percentage_string(BIGINT, BIGINT) RETURNS TEXT AS $$ BEGIN
    
    RETURN CASE 
        WHEN $2 > 0 THEN util.ratio_to_percentage_string($1::FLOAT / $2::FLOAT, 2)
        ELSE 'divide_by_zero' 
        END
        ;

END; $$ LANGUAGE plpgsql IMMUTABLE;

正如你所看到的，它是一个非常简单的函数，它只被调用一次，从整个事情产生的单行开始。这怎么会导致如此大规模的放缓？为什么它会影响 Postgres 是否内联初始子查询/CTE？（或者这里可能发生什么其他事情？）

此外，函数的作用根本不重要，只需将其替换为只返回一个TEXT字符串的函数即可hello导致初始内部查询的完全相同的减慢速度。所以这与函数“做什么”无关，而更像是某种“薛定谔的猫”效应，外部查询中的内容会影响内部查询的最初执行方式。为什么外部查询中的一个简单微小变化（对性能的影响基本上为零）会影响初始内部查询？

EXPLAIN ANALYZE输出：

postgresql postgresql-performance

2 个回答

  1. Best Answer

    函数内联很重要，在这里也适用。您的 PL/pgSQL 函数不能被内联。（除了为微不足道的表达式调用另一个函数之外，这有点矫枉过正。）但是由于它仍然非常便宜并且只调用一次，所以这里不是问题。

    无论您使用OFFSET 0hack 还是WITH CTE t1 AS MATERIALIZED，都可以防止重复评估。（如果您要使用OFFSET 0hack，您不妨使用稍微便宜一点的子查询，但现代 Postgres 中干净的方式是MATERIALIZEDCTE。）这也不是问题。（或者不再，在您成功阻止重复评估之后。）

    最重要的问题是并行性。用户函数是PARALLEL UNSAFE默认的。手册：

    PARALLEL UNSAFE表示该函数不能以并行模式执行，并且SQL 语句中存在这样的函数会强制执行串行执行计划。这是默认设置。

    大胆强调我的。

    您的第一个（快速）查询计划显示 2xParallel Seq Scan和 1x Parallel Index Only Scan。
    您的第二个（慢）查询计划没有并行查询。造成的伤害。

    解决方案

    标记您的功能PARALLEL SAFE（因为它们符合条件！）并且问题消失了。有关的：

    更好的解决方案

    我用几个变体进行了性能测试。看：

    这个等效函数要快得多，并且可以内联：

    CREATE OR REPLACE FUNCTION util.divide_ints_and_get_percentage_string(bigint, bigint)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT CASE WHEN $2 = 0 THEN 'divide_by_zero' 
            ELSE round($1 * 100 / $2::numeric, 2)::text || '%' END  -- explicit cast!
$func$;

    最重要的是，LANGUAGE sql它允许函数内联，（不像LANGUAGE plpgsql）。看：

    值得注意的是，我们需要那种显式的演员表::text。连接运算符||被解析为几个内部函数之一，具体取决于所涉及的数据类型，并不是所有的都是IMMUTABLE. 如果没有显式强制转换，Postgres 会选择一个变体 only STABLE，它会不同意函数声明并阻止函数内联。偷偷摸摸的细节！有关的：

    修复了一个逻辑问题：$2 = 0正确检查除以零（与 不同$2 > 0）。现在，count(*)永远不会是负数，但是由于您将逻辑放入函数中，因此它与该前提条件隔离。

    或者直接将简单表达式放入查询中。没有函数调用。这不受任何上述问题的影响。

    • 8

  2. 看起来您在 PostgreSQL 中遇到了某种优化围栏，您的函数不是在 CTE之后被评估一次，而是被多次评估！

    在您的情况下，我会做以下事情：

    (as before)

WITH t1 AS (
        SELECT
            (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,
            (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,
            (SELECT COUNT(*) FROM racing.xday) AS xday_row_count
        OFFSET 0 -- this is to prevent inlining
)

SELECT
            t1.all_count,
            t1.all_count-t1.todo_count AS done_count,
            t1.todo_count,
            t1.xday_row_count,
            
-- the CASE statement below is the only difference to your 
-- original query - it does the same thing, but removes the
-- function calls.

-- All your function calls appear to do is call a TEXT 
-- representation of a percentage. The CASE statement
-- calculates the percentage "directly" and returns the
-- desired string.

  CASE
    WHEN t1.allcount = 0 THEN 'divide by zero' 

    -- (or maybe an actual 0 might be suitable? 
    -- It avoids the cast to ::TEXT below)

    ELSE ((t1.todo_count::REAL/t1.allcount::REAL) * 100)::TEXT
  AS todo_percentage
FROM t1;

    使用转换::REAL意味着您将“仅”获得精确到小数点后 6 位的百分比（请参阅此处的 PostgreSQL 文档），但我很少遇到需要超过此值的情况。FLOAT事实上，没有精确度是一个DOUBLE PRECISION（15 位）。

    从文档中：

    PostgreSQL 还支持 SQL 标准表示法 float 和 float(p) 用于指定不精确的数字类型。这里，p 指定二进制数字的最小可接受精度。PostgreSQL 接受 float(1) 到 float(24) 作为选择真实类型，而 float(25) 到 float(53) 选择双精度。超出允许范围的 p 值会产生错误。没有指定精度的浮点数被认为是双精度。

    还有其他方法和方法可以做你需要的......

    如果您不需要准确的计数和准确的百分比，请在此处查看 PostgreSQL 站点的一些建议。然后@Erwin Brandstetter在这里给出了（又一个）权威回答——他给了你一些实现目标的方法，并解释了每种方法的优缺点......

    一些结束点：

    • 您的功能：您似乎很麻烦地在必要之前执行（或至少应该是）最终格式化步骤。许多人会争辩说，您在函数中所做的事情应该在客户端/表示层中完成。至少在最后一个 SQL 步骤之前，我会避免执行这种操作！数据库是用来存储数据的，而不是用来呈现的！

      另一种解决方案（如果您希望绝对坚持使用您的函数）可能是将您的查询包装在另一个中SELECT，并让函数对该查询的结果进行操作 - 这应该删除优化围栏（请参见此处的示例）！也许有点令人费解，但你的功能也是如此！

    • 在您进行编辑之后，您所说"some kind of "Schrödinger's cat" effect的实际上是"optimisation fence"CTE 多年来一直存在的问题。它是由指令修复的WITH cte_name AS [ NOT ] [MATERIALIZED] (...（见这里）。从那个答案来看，您的查询并非没有副作用！

      现在，您会说“但是，它所做的只是计算一个百分比......”，但优化器无法提前知道这一点并且“没有机会”并且似乎多次而不是一次评估您的函数。

    • 最后，我确实指出您显然没有向我们提供所有必要的信息 - 计划中的表名没有出现在您的问题中，这对我来说意味着您正在查询VIEWs，这可能很好成为一个混杂因素。

      我建议您在 dbfiddle.uk 上提供一个测试用例（带有基础基表）、您在这些以及所有查询和函数上构建的视图 - 否则，将无法提供进一步的帮助。

    你的类比"Schrödinger's cat"可能特别贴切——我们没有所有的信息——假设VIEW的 s 是否存在？他们VIEW在 s 上VIEW吗？他们VIEW有什么事吗？如果 a在森林中间VIEW​​被DROPped 并且没有人听到它，它真的被DROPped 了吗？

    如果没有充分披露，我们将无能为力。就我而言，我已经按要求回答了这个问题，并且（根据你自己）提供了可行的解决方案。当然，它可能并不完全令人满意，但是就我们所拥有的而言，这是您将获得的最好的！

    • 2

相关问题