透過使用 EXPLAIN 分析 PostgreSQL 中的資料庫查詢來優化效能緩慢的應用程式。緩慢的查詢是大多數 Web 應用程式中最大的瓶頸。本指南將引導您了解基本的索引優化指南、如何閱讀執行計劃以及自託管或託管資料庫的關鍵配置參數調整。

1. 設計有效的指標

現代資料庫的主要瓶頸是磁碟 I/O（從儲存中讀取區塊檔案）。新增索引可以讓PostgreSQL直接查詢資料記錄，而不是掃描整個表。

1）B樹索引預設值

標準 B 樹索引支援多種運算符類別：

• 直接匹配和範圍（=、<、>、BETWEEN）
• 連接條件 (JOIN ON ...)
• 排序操作 (ORDER BY)

2）複合索引的列順序規則

建立複合索引（組合多個欄位）時，PostgreSQL 從左到右解析列。訂單事宜：

-- Create a composite index
CREATE INDEX idx_users_status_created ON users (status, created_at);

-- ◯ Index will be used (leftmost column "status" is present in the query)
SELECT * FROM users WHERE status = 'active' AND created_at > '2026-01-01';
SELECT * FROM users WHERE status = 'active';

-- ✕ Index will NOT be used efficiently (leftmost column "status" is missing)
SELECT * FROM users WHERE created_at > '2026-01-01';

2. 使用 EXPLAIN ANALYZE 分析執行計劃

若要診斷慢速查詢，請將 EXPLAIN ANALYZE 新增至您的 SQL 語句中並在控制台中執行它：

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 45291;

輸出中的關鍵術語：

• Seq Scan（順序掃描）：資料庫正在掃描表中的每一行。如果這種情況發生在大型表上，則表示您缺少索引。
• 索引掃描/僅索引掃描：資料庫正在查詢索引。 Index Only Scan 是最快的查找策略，因為所有請求的資料列都存在於索引樹中，這表示 Postgres 不需要在主表堆中尋找資料區塊。
• 實際時間：執行持續時間（以毫秒為單位）。使用它來找出哪個子操作（如排序或雜湊連接）消耗最多時間。

3. 優化配置參數

如果您是自託管 Postgres 或使用專用伺服器，則預設記憶體值通常比較保守。在 postgresql.conf 檔案中調整這些值：

• shared_buffers：專用於快取資料庫表的記憶體。分配系統總 RAM 的大約 25%。
• work_mem：寫入臨時磁碟檔案之前內部排序作業 (ORDER BY) 和雜湊聯接的記憶體限制。將其從 4MB 增加到 16MB 可以大大加快複雜查詢的速度。
• maintenance_work_mem：用於維護過程的最大記憶體限制，例如VACUUM和CREATE INDEX。將其設定在 128MB 到 512MB 之間可以加快遷移速度。

4.結論

資料庫效能調優應該始終由資料驅動。永遠不要猜測要添加什麼索引。在資料庫修改之前和之後執行 EXPLAIN ANALYZE 以驗證執行成本和查詢時間是否有所改善。