如何在 PostgreSQL 中有效地儲存和存取常用的子查詢？

最佳化 PostgreSQL 中的頻繁子查詢

PostgreSQL 使用者經常遇到依賴重複子查詢的查詢，通常會聚合相關表中的資料。 本文探討了管理這些常見子查詢模式的有效方法，從而提高效能和程式碼可讀性。 一個典型的場景是根據外鍵關係從輔助表中檢索聚合資料。

視圖方法：一個強大的解決方案

雖然直接將子查詢嵌入為偽列是不可能的，但 PostgreSQL 的 VIEW 功能提供了一個很好的替代方案。 VIEW 定義了一個基於現有資料表和查詢的虛擬表，有效地建立了資料的自訂視圖。這樣可以避免修改基表結構，同時達到所需的結果。

產生欄位（PostgreSQL 11）：增強效能

PostgreSQL 11 及更高版本提供產生欄位（也稱為虛擬欄位）。這些允許定義一個列，其值是使用表達式計算的，反映了嵌入子查詢的目標。 這種方法通常會產生比視圖更好的效能。

模擬舊版本中產生的欄位

對於 PostgreSQL 11 之前的版本，或是首選虛擬列方法時，使用者定義的函數可以模擬產生的列。 此函數將表格的行類型輸入並計算所需的值。 然後函數連結到偽列，實現與生成列類似的功能。

實際實作

我們舉個例子來說明：

-- Create tables
CREATE TABLE tbl_a (a_id int, col1 int, col2 int);
CREATE TABLE tbl_b (b_id int, a_id int, colx int);

-- Sample data
INSERT INTO tbl_a VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);
INSERT INTO tbl_b VALUES
(1,1,5),  (2,1,5),  (3,1,1)
, (4,2,8),  (5,2,8),  (6,2,6)
, (7,3,11), (8,3,11), (9,3,11);

-- Function to emulate the generated column
CREATE FUNCTION col3(tbl_a)
RETURNS int8
LANGUAGE sql STABLE AS
$func$
SELECT sum(colx)
FROM   tbl_b b
WHERE  b.a_id = .a_id
$func$;

現在，存取col3偽列：

SELECT a_id, col1, col2, tbl_a.col3
FROM   tbl_a;

SELECT *, a.col3 FROM tbl_a a;

產生欄位的優點

與視圖相比，產生的欄位提供：

• 無縫整合：包含在SELECT *查詢中，但沒有明確提及。
• 動態更新：自動反映基底表中的變更。
• 最佳化效能：利用 PostgreSQL 的查詢最佳化器提高效率。

結論

雖然不支援直接嵌入子查詢，但 PostgreSQL 提供了有效的替代方案——檢視和產生欄位——來管理常用的子查詢。 這些方法提高了查詢效能和程式碼清晰度，從而實現更有效率的資料庫管理。 選擇最適合您的 PostgreSQL 版本和效能要求的方法。

以上是如何在 PostgreSQL 中有效地儲存和存取常用的子查詢？的詳細內容。更多資訊請關注PHP中文網其他相關文章！