主題: PostgreSQL

Index 不是欄位清單：先看 query shape，再決定要不要多維護一份資料

PostgreSQL index 的價值取決於真實 query 的篩選、排序、回傳量與寫入負擔。從 query shape 出發，才知道 B-tree、partial、GIN 或 BRIN 是否真的值得。

慢 query 出現時，最容易做的事是盯著 table 的欄位，然後替每個看起來重要的欄位加上 index。這很像把所有書都塞進索引，最後才發現找書的人只會用其中兩種分類方式。

index 不是免費的加速鈕。PostgreSQL 要在 INSERT、UPDATE、DELETE 時同步維護它；它也會佔用儲存空間與 cache。它值得存在，是因為某個常見 query 能少讀很多 row，或直接以需要的順序拿到少量結果。

先寫出 query，再談 index

假設作者後台最常開的是「這位作者最近發布的 20 篇文章」：

SELECT id, title, created_at
FROM articles
WHERE author_id = $1
  AND status = 'published'
ORDER BY created_at DESC
LIMIT 20;

這個 query shape 有三個訊號：author_id 和 status 是 equality filter、created_at 是排序、LIMIT 20 表示只需要很少結果。(author_id, status, created_at DESC) 這種複合 B-tree 才是在回答這個使用情境，不是在替三個欄位各自貼標籤。

PostgreSQL 的 B-tree 可處理 equality、範圍與排序。對複合 B-tree 而言，前導欄位通常最重要：等值條件放在前面，接著才是第一個範圍或排序需要的欄位。只有 created_at 的查詢不會因此自動獲益；它是另一個 workload，是否需要獨立 index 要看頻率與實際計畫。

不要因為某個 index 存在，就假設 planner 一定使用它。當 query 要回傳 table 的大部分 row，循序掃描可能更便宜。index 的目標不是消滅 sequential scan，而是讓特定讀取少做不必要的工作。

複合 index 不是把兩個單欄 index 黏起來

若系統有 (team_id) 和 (priority)，WHERE team_id = $1 AND priority = 'high' 有機會使用 bitmap AND，把兩個獨立 index 的結果交集起來。這有時很合理，特別是兩個條件也各自服務其他 query。

但 bitmap 取回 table row 時通常按實體位置處理，不保留任一 index 的排序。所以它不等於 (team_id, priority, created_at DESC)；若熱門畫面同時需要兩個 filter、最新排序與小 LIMIT，專門的複合 B-tree 可能更貼近需求。

反過來說，少用的交叉組合不值得為每一種欄位排列都建立 index。先列出常見 query，再比較讀取收益和寫入成本，通常比「欄位越多、index 越全」更省事。

特殊 index 是窄工具，不是更酷的預設值

有些 query shape 不適合硬塞進一般 B-tree，但它們各有前提。

• partial index：例如失敗付款只佔訂單的 1%，可用 WHERE payment_status = 'failed' 建較小的 index。query 必須明確帶出或可推導同一個條件；IN ('failed', 'paid') 不能當成相同前提。
• expression index：若查詢固定寫 WHERE lower(email) = $1，index 應對準 lower(email)，不是原始 email。索引表示式與 predicate 也受 immutable 函式限制。
• GIN：適合 jsonb 或 array 的元素與 containment 查詢。它不是一般排序的替代品。
• BRIN：適合大型 append table，且時間或序號和實體 block 順序高度相關。它很小，但只能排除不可能的 block，不能像 B-tree 一樣逐列精準導航。

INCLUDE 也常被誤會。它能把只供 SELECT 回傳的欄位放進 B-tree payload，讓符合條件的 query 有機會少回 table 拿資料；它不是 WHERE 或 ORDER BY 的 search key。把寬欄位一股腦塞進去，仍會放大 index 和寫入成本。

建 index 也是一次寫入決策

在 production 為大型 live table 建 index，標準 CREATE INDEX 會阻擋寫入。CREATE INDEX CONCURRENTLY 可以避免這個寫入封鎖，但執行時間較長，也有自己的使用限制。這不是語法偏好，而是部署選擇。

新增前，先確認 query 的 filter、排序、回傳比例、頻率與 table 的寫入負擔；新增後，再由實際計畫和延遲驗證。下一個 EXPLAIN lesson 才會把「資料庫為什麼選這條路」拆開來看。現在先守住一個原則：index 要服務被觀察到的 query，不是服務欄位清單。

我學到什麼

• 我會先寫出常見 query 的 filter、排序和 LIMIT，再決定 index 的 key order，而不是先替每個欄位建一份索引。
• B-tree、partial、expression、GIN 與 BRIN 各自服務不同資料形狀；選擇之前要先辨認 query 真正在找什麼。
• index 的讀取收益必須和儲存、cache 及寫入維護成本一起比較。循序掃描不是失敗，而是 planner 在某些回傳量下更合理的選擇。
• INCLUDE 只補回傳欄位，不能取代 search key；複合 index 和 bitmap 組合也不能視為同一種排序能力。

外部參考連結

• PostgreSQL：Indexes Introduction
• PostgreSQL：Index Types
• PostgreSQL：Multicolumn Indexes
• PostgreSQL：Indexes and ORDER BY
• PostgreSQL：Partial Indexes
• PostgreSQL：Combining Multiple Indexes
• Reddit：PostgreSQL 使用者談最有感的效能改善

延伸學習

• PostgreSQL：Index Types：對照 B-tree、GIN、BRIN 與可處理的 operator。
• PostgreSQL：Multicolumn Indexes：理解 leading columns、範圍條件與複合 key 的限制。
• PostgreSQL：CREATE INDEX：查 expression、partial、INCLUDE 與 CONCURRENTLY 的精確限制。
• CS50 SQL Week 5：Optimizing：可跟著操作 B-tree、partial index、query plan 與空間／寫入取捨的公開課程。