本文是 PostgreSQL overview 的 implementation-layer deep article。Overview 已說明 PG 在 OLTP 譜系的定位、本文聚焦 index 選型 — 何時用哪種 index、跟 query-optimization 的「為什麼這個 plan 慢」互補。

6 種 Index Method 對應 Workload

PG 有 6 種 index access method、各有自己擅長的 query pattern：

選錯 index 的代價：

• Write workload：每 write 都更新所有相關 index、5 個 unused index = 5x write 放大
• Storage：JSONB 加 GIN 可能比表本身還大
• Plan misjudge：planner 看到 index 不一定用、EXPLAIN 才確認

B-tree：預設選擇、95% workload 適用

B-tree 是 PG 預設 index、CREATE INDEX 不指定 method 就是 B-tree：

1CREATE INDEX idx_orders_user_id ON orders (user_id);
2CREATE INDEX idx_orders_created_at ON orders (created_at);

B-tree 擅長的 query：

 1-- 等值
 2SELECT * FROM orders WHERE user_id = 42;
 3
 4-- 範圍
 5SELECT * FROM orders WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';
 6
 7-- IS NULL
 8SELECT * FROM orders WHERE shipped_at IS NULL;
 9
10-- Prefix LIKE
11SELECT * FROM products WHERE sku LIKE 'ABC%';

B-tree 不擅長：

• LIKE '%suffix'（前綴 wildcard）→ 改 trigram + GIN
• column @> array（包含）→ 改 GIN
• JSON 內部 path query → 改 GIN on JSONB

Multi-column B-tree 的順序很重要：

1-- 假設常 query: WHERE user_id = ? AND status = ?
2CREATE INDEX idx_orders_user_status ON orders (user_id, status);  -- 對
3CREATE INDEX idx_orders_status_user ON orders (status, user_id);  -- 錯（status 選擇性低）

順序原則：

1. 等值 column 在前（高選擇性）
2. 範圍 column 在後（B-tree leftmost 規則）
3. selectivity 高的在前（filter 更多 row）

GIN：JSONB / FTS / Array 的標配

GIN（Generalized Inverted Index）對「一個 value 內含多個 sub-element」的 column 高效：

 1-- JSONB
 2CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 3
 4-- Array
 5CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
 6
 7-- Full-text search
 8CREATE INDEX idx_articles_content ON articles USING GIN (to_tsvector('english', content));
 9
10-- Trigram（fuzzy match）
11CREATE EXTENSION pg_trgm;
12CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

GIN 代價：

• Write 慢 2-10x：每個 sub-element 都要更新 inverted index
• Storage 大：可能比表還大
• Vacuum 沉重：bloat 累積快

Operator class 選擇影響大：

只用 @> query 時、jsonb_path_ops 救大量 storage。

GiST：範圍 / 空間 / 自訂

GiST（Generalized Search Tree）擅長範圍跟空間：

1-- 範圍 type（PostgreSQL 內建 int4range / tsrange 等）
2CREATE INDEX idx_bookings_period ON bookings USING GiST (period);
3
4-- 空間（PostGIS）
5CREATE INDEX idx_locations_geom ON locations USING GiST (geom);
6
7-- Exclusion constraint（範圍不重疊）
8ALTER TABLE bookings ADD CONSTRAINT no_overlap
9EXCLUDE USING GiST (room_id WITH =, period WITH &&);

GiST vs GIN 對 FTS 的選擇：

多數 FTS workload 選 GIN — read 占多、index size 換 query latency 划算。

BRIN：大表 + Physical Order Correlated

BRIN（Block Range Index）對 physical 儲存順序跟 logical 順序強相關 的 column 高效：

1-- timestamp column（append-only insert、physical 順序 = 時間順序）
2CREATE INDEX idx_events_created_at ON events USING BRIN (created_at);

BRIN 機制：每個 block range（預設 128 page）記 min/max、query 時跳過 range 外的 block。

適用場景：

• append-only 表：log、metrics、events
• 大表（10GB+）：B-tree 太貴、BRIN 1/1000 大小
• column physical order 跟 query 一致：時間欄、自增 id

BRIN 失效情境：

• UPDATE 破壞 physical order（row 被 vacuum 移到別 block）→ BRIN 失效
• 隨機 insert（uuid / hash id）→ BRIN range 完全沒選擇性

何時不該用 BRIN：表 < 1GB（沒省 storage 收益）、column 沒 physical order correlation（CLUSTER 後可能改善）。

Partial Index：條件式 index 救 storage

對 只 query 部分 row 的 column、partial index 救大量 storage：

 1-- 只 index unshipped order
 2CREATE INDEX idx_orders_unshipped ON orders (created_at)
 3WHERE shipped_at IS NULL;
 4
 5-- 只 index active user
 6CREATE INDEX idx_users_active ON users (email)
 7WHERE status = 'active';
 8
 9-- 只 index 高金額 transaction
10CREATE INDEX idx_orders_high_value ON orders (user_id)
11WHERE total > 1000;

Partial index 的 query 要 完全匹配 WHERE 條件 才用得到：

1-- 用得到 partial index
2SELECT * FROM orders WHERE shipped_at IS NULL AND created_at > '2025-01-01';
3
4-- 用不到（planner 不 prove WHERE 包含 partial 條件）
5SELECT * FROM orders WHERE created_at > '2025-01-01';

實務 size 救法：unshipped order 只 1% 總量、partial index 1/100 大小。

Expression Index：對函式結果 index

 1-- 對 lowercased email index（case-insensitive search）
 2CREATE INDEX idx_users_email_lower ON users (lower(email));
 3SELECT * FROM users WHERE lower(email) = lower('USER@example.com');
 4
 5-- 對 JSONB 內部欄位
 6CREATE INDEX idx_products_category ON products ((metadata->>'category'));
 7SELECT * FROM products WHERE metadata->>'category' = 'shoes';
 8
 9-- 對日期截斷
10CREATE INDEX idx_orders_day ON orders (date_trunc('day', created_at));

Expression 必須 IMMUTABLE — now() / random() 不能用、timezone('UTC', ts) 可以。

Covering Index（INCLUDE）：避免回表

PG 11+ 支援 INCLUDE column：

1-- 只 index user_id、但 query 常要 email
2CREATE INDEX idx_users_user_id_covering ON users (user_id) INCLUDE (email);
3
4-- Index-only scan：不用回表
5SELECT email FROM users WHERE user_id = 42;

INCLUDE column 不參與 sorting / equality、只放 leaf node、救 IO。

Index 選擇決策樹

 1Query pattern 是什麼？
 2
 3├─ 等值 / 範圍 / prefix LIKE / IS NULL
 4│  └─ B-tree（90% 場景）
 5│     ├─ 只 query 部分 row？→ Partial B-tree
 6│     ├─ 對函式結果？→ Expression B-tree
 7│     └─ 需要回表更多 column？→ Covering（INCLUDE）
 8│
 9├─ JSONB 內部 query / array 包含 / FTS
10│  └─ GIN
11│     ├─ 只用 @>？→ jsonb_path_ops 救 storage
12│     └─ FTS write-heavy？→ 改 GiST
13│
14├─ 範圍 type（int4range / tsrange）/ 空間
15│  └─ GiST
16│
17├─ 大表 + append-only + physical order correlated
18│  └─ BRIN
19│
20├─ 純 equality + 簡單 column
21│  └─ Hash（很少用、B-tree 通常更好）
22│
23└─ Non-balanced 樹（IP prefix / quad-tree）
24   └─ SP-GiST（罕見）

5 個 Production 踩雷

Case 1：過度 index（write 放大）

情境：team「為了 query 快」對 20 個 column 各建 index、寫入量大時 INSERT 慢 10x。

每個 INSERT 要更新 20 個 index、WAL volume 也跟著放大、replication lag 拉長。

修法：

• 用 pg_stat_user_indexes 找 idx_scan = 0 的 index、可能根本沒用
• 用 pg_stat_statements 找實際被執行的 query、反推真正需要的 index
• 同 column 多 index（user_id 單欄 + (user_id, status) 多欄）通常可拆掉單欄

Case 2：Partial index 條件跟 query 不匹配

情境：建 WHERE status = 'active' partial index、application query 寫 WHERE status IN ('active')、planner 不 prove 等價、不用 index。

修法：

• Partial 條件用最 generic form（避免 IN / OR 跟 = 的差異）
• 寫完用 EXPLAIN 驗證 query 真的用到 partial index
• Application 統一 query 寫法、不要混 = 跟 IN 跟 ANY

Case 3：B-tree 對 JSONB 內部欄位無效

情境：對 metadata JSONB column 建 B-tree、query metadata->>'category' = 'shoes' 不用 index。

B-tree 對 整個 JSONB 排序、但 path query 不是整個 JSONB 的比對。

修法：

• 對固定 path 建 expression index：CREATE INDEX ... ON products ((metadata->>'category'))
• 對動態 path 建 GIN index：CREATE INDEX ... USING GIN (metadata)
• 兩者並存可、EXPLAIN 看 planner 選哪個

Case 4：BRIN 對非 correlated 資料無效

情境：對 user_id 建 BRIN index（user_id 是隨機 UUID）、query 完全跑 seq scan。

UUID 沒 physical order correlation、每個 block range 的 min/max 涵蓋整個 ID space、BRIN 完全沒 prune 效果。

修法：

• BRIN 只用 timestamp / 自增 id / 其他自然 correlate 的 column
• 用 pg_stats 看 correlation value、< 0.1 就不適合 BRIN
• 真要對 random column 加 index、回 B-tree

Case 5：Multi-column index 順序錯

情境：常見 query WHERE status = 'pending' AND user_id = 42、建 index (status, user_id)、效能差。

status 只 5 個 distinct value、選擇性 1/5；user_id 1M distinct、選擇性 1/1M。Index leftmost 是 status、scan range 太大。

修法：

1-- 拆兩個或調順序
2CREATE INDEX idx_user_status ON orders (user_id, status);
3
4-- 或加 partial 限定低選擇性 column
5CREATE INDEX idx_orders_pending ON orders (user_id) WHERE status = 'pending';

跟 MySQL Index 差異

PG index 系統比 MySQL 表達力高、但代價是 選對 index method 是 application 責任、MySQL 預設 B-tree 多數場景夠用。

相關連結

• query-optimization：EXPLAIN 看 index 用沒用
• jsonb-deep-dive：JSONB + GIN 細節
• full-text-search：FTS + GIN
• autovacuum-tuning：index bloat
• online-schema-change：CREATE INDEX CONCURRENTLY

下一步

• 看 query-optimization 驗證 index 有沒有被 plan 用到
• 回 PostgreSQL overview 看全圖