SELECT 中的廉價函式如何使整個查詢變慢？

我正在使用帶有內部和外部查詢的 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輸出：

• 查詢1：https ://explain.depesz.com/s/bq7u
• 查詢2：https ://explain.depesz.com/s/9w3rY

函式內聯很重要，在這裡也適用。您的 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**（因為它們符合條件！）並且問題消失了。有關的：

• 何時將函式標記為 PARALLEL RESTRICTED 與 PARALLEL SAFE？

更好的解決方案

我用幾個變體進行了性能測試。看：

• 獲取PostgreSQL查詢的執行時間

這個等效函式要快得多，並且可以內聯：

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）。看：

• 帶有 SELECT 的 SQL 函式與帶有 RETURN QUERY SELECT 的 PLPGSQL 函式？

值得注意的是，我們需要那種顯式的演員表::text。連接運算符||被解析為幾個內部函式之一，具體取決於所涉及的數據類型，並不是所有的都是IMMUTABLE. 如果沒有顯式強制轉換，Postgres 會選擇一個變體 only STABLE，它會不同意函式聲明並阻止函式內聯。偷偷摸摸的細節！有關的：

• 如何連接 Postgres SELECT 中的列？
• 聲明函式可變性 IMMUTABLE 會損害性能嗎？

修復了一個邏輯問題：$2 = 0正確檢查除以零（與 不同$2 > 0）。現在，count(*)永遠不會是負數，但是由於您將邏輯放入函式中，因此它與該前提條件隔離。

或者直接將簡單表達式放入查詢中。沒有函式呼叫。這不受任何上述問題的影響。

引用自：https://dba.stackexchange.com/questions/294532