PostgreSQL 的 LATERAL JOIN 如何最佳化數組參數上的多個函數呼叫？

陣列參數上多個函數呼叫的查詢最佳化

在Postgres 中，在陣列參數上多次呼叫函數可能效率很低，特別是當函數傳回多列時。讓我們探索一種查詢優化策略來應對這項挑戰。

考慮一個函數foo，它處理具有給定參數的行數組並傳回一組行和一個新列：

CREATE OR REPLACE FUNCTION foo(data data[], parameter int) RETURNS SETOF enhanceddata AS
...

最初，該函數使用以下方法處理一組數據：

SELECT * FROM foo( (SELECT ARRAY_AGG(data) FROM datatable GROUP BY dataid WHERE dataid = something), 1) 

但是，目標是使其適用於多個群組不指定dataid 參數的資料。

一種方法涉及使用子查詢將資料聚合到數組中，然後將其傳遞給foo 函數：

SELECT dataid, (foo(ARRAY_AGG(data)),1).*
FROM dataset
WHERE dataid = something -- only testing on 1
GROUP BY dataid

雖然這看起來合乎邏輯，它存在多次調用foo的問題，每個資料行調用一次。

使用橫向連接最佳化

要最佳化此查詢，可以使用 PostgreSQl LATERAL JOIN。這種強大的技術在子查詢的結果和另一個表的行之間創建基於行的笛卡爾積。在這種情況下，子查詢將資料聚合到一個陣列中，橫向連接會對聚合數組中的每一行執行 foo 一次。

使用PostgreSQL 9.3 或更高版本，以下查詢最佳化了多個函數呼叫：

SELECT sub.dataid, f.*
FROM  (
   SELECT dataid, array_agg(data) AS arr
   FROM   dataset
   WHERE  dataid = something
   GROUP  BY 1
   ) sub
LEFT   JOIN LATERAL foo(sub.arr) f ON true;

LEFT JOIN LATERAL 確保保留左表（子查詢）中的所有行，即使沒有返回任何行富。如果函數不能傳回任何行，則這是首選語法。

對於始終傳回結果的函數，可以使用簡化語法：

CROSS JOIN LATERAL foo(sub.arr)

或其等效簡寫：

, foo(sub.arr)

正如Postgres 手冊中強調的那樣，使用LATERAL JOIN 可以透過foo 等傳回集合的函數來最佳化查詢。該技術可以有效地處理多組數據，而不會產生不必要的函數評估。

以上是PostgreSQL 的 LATERAL JOIN 如何最佳化數組參數上的多個函數呼叫？的詳細內容。更多資訊請關注PHP中文網其他相關文章！