PostgreSQL UDF（使用者定義函式）成本

免責聲明

這項任務可能看起來很深奧，但我還是想創建某種形式的 POC。

目標

我的目標是讓 PostgreSQL 數據庫（版本 10）向使用它的應用程序公開一個 API。

API 需要採用一組 UDF 的形式：所有函式都屬於一個公共方案，這是應用程序唯一可以訪問的方案。桌子和其他東西隱藏在私人計劃中。幾乎就像，你知道的，一個物件導向的數據庫。

這就是我試圖讓它發揮作用的原因：

• 它將數據庫與應用程序分離，因此您可以重組/優化/非規範化前者，而破壞後者的風險較小。您甚至可以將其維護委託給另一個團隊或部門（哦，我的）
• API 形式化了服務的需求。數據庫當然是一種服務，但稱為遷移的傳統機制並不能很好地弄清楚其中發生了什麼。想想多年來收集的成百上千的遷移，其中一些已經損壞，再也無法工作了，並且

好吧，沒關係。

問題

因此，當我嘗試創建一些非常簡單的函式（例如從表中獲取所有記錄）時，我提到它們總是比它包裝的查詢慢。雖然這本身是完全可以接受和理解的，但時間差異可能很大。因此，無法接受。

這個例子

我有一張這樣的桌子。

CREATE TABLE notifications (
   id SERIAL PRIMARY KEY,
   source_type INTEGER NOT NULL,
   content JSONB,
   created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
)

其中有超過 12 萬條記錄。

想像一下，我們想要得到所有這些。

在這裡，我們通過一個簡單的查詢來做到這一點。沒有索引，每條記錄的 JSONB 數據幾乎是 1kb。

EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * FROM private.notifications;
                                                        QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on private.notifications  (cost=0.00..16216.13 rows=120113 width=877) (actual time=0.015..496.473 rows=120113 loops=1)
 Output: id, source_type, content, created
 Buffers: shared hit=15015
Planning time: 0.063 ms
Execution time: 973.935 ms

496 毫秒。

現在讓我們嘗試使用這樣的 pl/pgsql 函式：

CREATE OR REPLACE FUNCTION notifications_get()
RETURNS SETOF private.notifications AS
$$
BEGIN
   RETURN QUERY SELECT * from private.notifications;
END
$$
LANGUAGE 'plpgsql' 
SECURITY DEFINER;

EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * FROM notifications_get();

                                                           QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
Function Scan on notifications_get  (cost=0.25..10.25 rows=1000 width=48) (actual time=99.561..589.129 rows=120113 loops=1)
 Output: id, source_type, content, created
 Function Call: notifications_get()
 Buffers: shared hit=15015
Planning time: 0.045 ms
Execution time: 1091.698 ms

589 毫秒。

顯然，函式和查詢之間的區別在於這 99.5 毫秒用於獲取第一條記錄。

我已經嘗試了進一步的優化（也許天真）：

1. 調整行以使查詢計劃更加現實。比如說120k。它產生相同的結果（102.373..593.628）
2. 使用 SQL 語言（很公平，查詢很簡單）。令人驚訝的是，相同的結果 (95.760..595.746)
3. 使函式穩定。現在應該好轉了吧？沒有。相同的結果 (93.132..594.331)

問題

1. 還有什麼可以做的來使功能更高效（與簡單的查詢相比）？
2. 為什麼這些技巧都沒有產生影響？
3. 這些前 100 毫秒到底是什麼？這些不是恆定的：當表中有 20k 行時，該函式會花費神秘的 18-20 毫秒來嘗試先做某事。所以很明顯，它試圖對錶格中的**每一行都做這件事。**如何減少這種浪費或完全擺脫它？這甚至可能嗎？

附言

我面臨的另一個問題是通過 id 獲取記錄的函式。0.25 毫秒與 0.025 毫秒。十倍的差異，但我或多或少地知道它來自哪裡。同樣，上面列出的優化技巧沒有任何作用（似乎不應該）。

這（幾乎）等同於您在問題中的功能，但執行起來很簡單SELECT：

CREATE OR REPLACE FUNCTION notifications_get_faster()
 RETURNS SETOF private.notifications AS
$func$
SELECT * FROM private.notifications
$func$  LANGUAGE sql STABLE;

幾乎，因為它不是SECURITY DEFINER，這會阻止預期的效果。

最值得注意的是，您將在查詢計劃中看到 aSeq Scan而不是 the 。Function Scan這就是最大的不同。

為什麼？

您的各種嘗試都沒有滿足表函式內聯的所有條件。這個功能可以。尤其：

• 功能是LANGUAGE SQL
• 功能不是SECURITY DEFINER
• 該函式已聲明STABLE或IMMUTABLE

因此 Postgres 可以獲取函式體並在沒有函式成本的情況下執行它（“函式內聯”）。與普通的SELECT.

另外：不要引用語言名稱。這是一個標識符。

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