索引類型與設計：B-Tree、GIN、GiST、BRIN 完全指南 | PostgreSQL
2026/06/21
索引（Index） 是 PostgreSQL 中加速查詢的輔助資料結構。不同於只有一種索引的資料庫，PostgreSQL 提供了 B-Tree、Hash、GIN、GiST、SP-GiST、BRIN 六種索引類型，各自針對特定查詢模式優化。搭配 Partial Index、Expression Index、Covering Index 等進階技巧，能在寫入效能與查詢速度之間取得最佳平衡。
索引與 Heap Table 的關係
PostgreSQL 的資料儲存採用 Heap Table 結構。索引是獨立的資料結構，每個索引條目儲存指向實際 Row 的 TID（Tuple Identifier）：
索引 → Heap 的關係：
┌──────────────────────────────────────────┐
│ Index Structure │
│ │
│ key=42 → TID (Block=7, Offset=3) │
│ key=55 → TID (Block=7, Offset=8) │
│ key=91 → TID (Block=12, Offset=1) │
└──────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────┐
│ Heap Table Pages │
│ │
│ Page 7: [Item 3 ← 實際 Row data] │
│ Page 12: [Item 1 ← 實際 Row data] │
└──────────────────────────────────────────┘
Index-Only Scan 與 Visibility Map
當查詢只需要索引中已包含的欄位時，PostgreSQL 可執行 Index-Only Scan，不需回訪 Heap。但由於 MVCC 機制，索引不儲存可見性資訊，必須參考 Visibility Map（VM）：
- VM 標記為
all-visible的 Page → 直接跳過 Heap - VM 未標記的 Page → 仍需回訪 Heap，退化為普通 Index Scan
定期 VACUUM 會更新 VM，對 Index-Only Scan 效能至關重要。
B-Tree 索引
B-Tree 是預設索引類型，採用平衡樹結構：
B-Tree 結構：
┌──────────┐
│ Root │
│ [50 | 75]│
└──────────┘
/ | \
┌────────┘ │ └────────┐
▼ ▼ ▼
┌──────────┐ ┌──────────┐ ┌──────────┐
│ [20 | 35]│ │ [55 | 65]│ │ [80 | 90]│
└──────────┘ └──────────┘ └──────────┘
/ | \ / | \ / | \
▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼
[Leaf] ↔ [Leaf] ↔ [Leaf] ↔ [Leaf] ↔ [Leaf]
Leaf 節點以雙向連結串列相連
核心特性：
- 平衡性：所有 Leaf 距 Root 深度相同，保證 O(log n) 查詢
- 排序性：Leaf 按鍵值有序排列，支援範圍掃描
- 雙向連結：Leaf 層雙向連結使範圍查詢可線性掃描
支援的操作：
| 操作 | 範例 |
|---|---|
| 等值查詢 | WHERE id = 42 |
| 範圍查詢 | WHERE age BETWEEN 20 AND 30 |
| 前綴 LIKE | WHERE name LIKE 'John%' |
| 排序 | ORDER BY created_at |
| NULL 判斷 | WHERE status IS NULL |
-- 基本 B-Tree 索引
CREATE INDEX idx_users_email ON users(email);
-- 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- 排序方向
CREATE INDEX idx_products_price ON products(price DESC);
-- 前綴 LIKE（需使用 text_pattern_ops）
CREATE INDEX idx_users_name_prefix
ON users(name text_pattern_ops);
SELECT * FROM users WHERE name LIKE 'John%';
Hash 索引
Hash 索引對鍵值套用 Hash 函式，只支援等值查詢（=），不支援範圍或排序：
CREATE INDEX idx_sessions_token ON sessions USING hash(token);
-- 適用
SELECT * FROM sessions WHERE token = 'abc123def456';
-- 不適用（無法使用 Hash 索引）
-- SELECT * FROM sessions WHERE token > 'abc';
-- SELECT * FROM sessions ORDER BY token;
PG10 之前 Hash 索引不寫 WAL，無法從崩潰中恢復。PG10 後已修正，但實務上 B-Tree 仍是首選。
GIN 索引（倒排索引）
GIN（Generalized Inverted Index）的結構類似搜尋引擎的倒排索引，適合一個 Row 對應多個鍵值的場景：
GIN 倒排索引（Array 欄位範例）：
Row 1: tags = {python, django, api}
Row 2: tags = {python, fastapi}
Row 3: tags = {django, celery}
GIN 索引：
"api" → [Row 1]
"celery" → [Row 3]
"django" → [Row 1, Row 3]
"fastapi" → [Row 2]
"python" → [Row 1, Row 2]
-- Array 包含查詢
CREATE INDEX idx_articles_tags ON articles USING gin(tags);
SELECT * FROM articles WHERE tags @> ARRAY['python', 'django'];
-- JSONB 查詢
CREATE INDEX idx_products_attrs ON products USING gin(attributes);
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
SELECT * FROM products WHERE attributes ? 'warranty';
-- 全文搜尋
CREATE INDEX idx_articles_fts ON articles USING gin(search_vector);
SELECT title FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'machine learning');
-- pg_trgm 模糊搜尋（支援 %keyword%）
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);
SELECT * FROM users WHERE name ILIKE '%john%';
GIN 寫入時使用 Pending List 暫存新增條目，由 VACUUM 或達到閾值時合併，避免每次寫入都重建倒排結構。
GiST 索引（通用搜尋樹）
GiST（Generalized Search Tree）是可擴展的索引框架，支援空間查詢、範圍重疊等：
-- PostGIS 空間查詢
CREATE INDEX idx_locations_geom ON locations USING gist(geom);
SELECT * FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(121.5, 25.0)::geography, 1000);
-- Range Type 重疊查詢
CREATE INDEX idx_schedules_slot ON schedules USING gist(time_slot);
SELECT * FROM schedules
WHERE time_slot && tstzrange('2024-01-01 09:00', '2024-01-01 10:00');
GiST vs GIN 在全文搜尋上的差異：GiST 更新快但查詢慢，GIN 查詢快但更新有 Pending List 開銷。
SP-GiST 索引
SP-GiST 採用非平衡的空間分割方式，適合具有遞迴分割特性的資料：
-- IP 位址查詢
CREATE INDEX idx_logs_ip ON access_logs USING spgist(client_ip);
SELECT * FROM access_logs
WHERE client_ip << '192.168.0.0/16'::inet; -- 子網路包含
-- 電話號碼前綴查詢
CREATE INDEX idx_contacts_phone ON contacts USING spgist(phone text_ops);
SELECT * FROM contacts WHERE phone ^@ '+886';
BRIN 索引（區塊範圍索引）
BRIN（Block Range INdex）以每個 Block Range（預設 128 個連續 Page）為單位，記錄最小值/最大值。索引極小（通常數 KB），但前提是資料有良好的物理排序性：
BRIN 結構（時序資料範例）：
Pages 0-127: created_at [2024-01-01 ~ 2024-01-15]
Pages 128-255: created_at [2024-01-15 ~ 2024-01-31]
Pages 256-383: created_at [2024-02-01 ~ 2024-02-15]
查詢 WHERE created_at = '2024-02-10':
→ 只掃描 Block Range [256-383]
→ 其餘直接跳過
-- 時序資料表的 BRIN 索引
CREATE INDEX idx_events_created_brin
ON events USING brin(created_at);
-- 自定義 pages_per_range
CREATE INDEX idx_events_brin_64
ON events USING brin(created_at)
WITH (pages_per_range = 64);
-- 比較索引大小
SELECT indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename = 'events'
ORDER BY pg_relation_size(indexrelid) DESC;
適合：時序資料表、日誌表、IoT 感測器資料等按時間插入的大表。
Partial Index（部分索引）
只對滿足特定條件的 Row 建立索引，大幅縮小索引大小：
-- 只索引活躍用戶
CREATE INDEX idx_users_active_email
ON users(email) WHERE status = 'active';
-- 查詢必須包含相同 WHERE 條件才會使用
SELECT * FROM users
WHERE email = 'user@example.com' AND status = 'active';
-- 軟刪除場景
CREATE INDEX idx_posts_published
ON posts(published_at DESC)
WHERE deleted_at IS NULL;
-- 排除 NULL 值
CREATE INDEX idx_products_discount
ON products(discount_rate)
WHERE discount_rate IS NOT NULL;
Expression Index（表達式索引）
對函式或表達式的結果建立索引：
-- 大小寫不敏感查詢
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- 日期截斷索引
CREATE INDEX idx_orders_date_trunc
ON orders(DATE_TRUNC('day', created_at));
-- JSONB 欄位的特定鍵值
CREATE INDEX idx_users_metadata_role
ON users((metadata->>'role'));
SELECT * FROM users WHERE metadata->>'role' = 'admin';
Covering Index（INCLUDE）
把非搜尋欄位加入索引，實現完全的 Index-Only Scan：
-- 不使用 INCLUDE：需回 Heap 取 price 和 stock
CREATE INDEX idx_products_category ON products(category_id);
-- 執行：Index Scan → Heap Fetch（兩次 I/O）
-- 使用 INCLUDE：直接 Index-Only Scan
CREATE INDEX idx_products_category_covering
ON products(category_id)
INCLUDE (price, stock);
-- 執行：Index Only Scan（單次 I/O）
注意：INCLUDE 的欄位不能用於排序或範圍查詢，只是避免回 Heap。
多欄位索引欄位順序策略
-- 原則一：等值條件在前，範圍條件在後
-- 差：範圍在前
CREATE INDEX idx_bad ON orders(created_at, user_id);
-- 好：等值在前
CREATE INDEX idx_good ON orders(user_id, created_at);
-- WHERE user_id = 123 AND created_at > '2024-01-01'
-- → 先精確定位 user_id=123，再做 created_at 範圍掃描
-- 原則二：高選擇性欄位優先
-- status 只有 3 種值，user_id 有 100 萬種
CREATE INDEX idx_good2 ON orders(user_id, status);
-- 原則三：前綴可重用
-- (a, b, c) 可服務：
-- WHERE a = ?
-- WHERE a = ? AND b = ?
-- WHERE a = ? AND b = ? AND c = ?
-- 但無法服務 WHERE b = ?（缺少前綴 a）
索引選擇決策樹
需要加索引？
│
▼
查詢類型分析
│
├─ 純等值（=）→ B-Tree（預設）或 Hash
│
├─ 範圍/排序 → B-Tree（等值欄位在前）
│
├─ 多值欄位（Array/JSONB/全文搜尋）→ GIN
│
├─ 空間/範圍重疊 → GiST
│
├─ 大表且物理排序良好（時序/日誌）→ BRIN
│
└─ 遞迴分割資料（IP/電話號碼）→ SP-GiST
CREATE INDEX CONCURRENTLY
一般 CREATE INDEX 會取得 ShareLock，阻塞寫入。CONCURRENTLY 採用兩階段流程：
階段一：
1. 在系統目錄記錄索引（標記 "not ready"）
2. 等待所有現有事務完成
3. 第一次掃描 Heap，建立初始索引
（不阻塞任何 DML 操作）
階段二：
1. 等待仍在讀取舊快照的事務完成
2. 第二次掃描 Heap，同步期間變更
3. 標記索引為 "valid"
| 操作 | 取得的鎖 | 阻塞 |
|---|---|---|
| CREATE INDEX | ShareLock | 阻塞 INSERT/UPDATE/DELETE |
| CREATE INDEX CONCURRENTLY | ShareUpdateExclusiveLock | 不阻塞 DML |
| REINDEX CONCURRENTLY（PG12+） | ShareUpdateExclusiveLock | 不阻塞 DML |
INVALID 索引處理：CONCURRENTLY 失敗會留下 INVALID 索引，不被查詢使用但仍影響寫入效能，必須手動刪除重建：
-- 查詢 INVALID 索引
SELECT c.relname AS index_name
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
WHERE NOT i.indisvalid;
-- 刪除後重建
DROP INDEX CONCURRENTLY idx_invalid;
CREATE INDEX CONCURRENTLY idx_invalid ON users(email);
索引膨脹偵測與處理
頻繁 UPDATE/DELETE 後，B-Tree 葉節點可能出現大量 Dead 空間：
-- 安裝 pgstattuple 偵測膨脹率
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('idx_users_email');
-- avg_leaf_density < 70% 表示膨脹
-- 修復方式
REINDEX INDEX CONCURRENTLY idx_users_email; -- PG12+
-- 或使用 pg_repack（線上重建，不鎖表）
索引維護與監控
-- 找出未使用的索引（idx_scan = 0）
SELECT indexname, idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- 找出重複索引
SELECT t1.indexname AS index1, t2.indexname AS index2, t1.indexdef
FROM pg_indexes t1
JOIN pg_indexes t2
ON t1.tablename = t2.tablename
AND t1.indexname < t2.indexname
AND t1.indexdef = t2.indexdef;
-- 索引 Buffer 命中率
SELECT indexrelname,
CASE WHEN idx_blks_hit + idx_blks_read = 0 THEN 0
ELSE ROUND(100.0 * idx_blks_hit /
(idx_blks_hit + idx_blks_read), 2)
END AS hit_rate_pct
FROM pg_statio_user_indexes
ORDER BY idx_blks_read DESC;
效能考量
random_page_cost 調整
索引掃描產生隨機 I/O，PostgreSQL 用 random_page_cost（預設 4.0）估算成本。SSD 環境建議降低：
-- SSD 優化
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();
當查詢回傳超過表的 10-15% 時，規劃器可能放棄索引改用 Sequential Scan。
版本演進
| 版本 | 改進 |
|---|---|
| PG10 | Hash Index 寫入 WAL，終於可靠 |
| PG11 | Covering Index（INCLUDE 子句） |
| PG12 | REINDEX CONCURRENTLY |
| PG13 | B-Tree Deduplication 縮小索引大小 |
| PG14 | BRIN multi-range 改進 |
| PG15 | BRIN bloom filter 減少誤判率 |
| PG17 | B-Tree 建立效能提升、WAL 優化 |
常見陷阱
隱式型別轉換導致索引失效
-- 欄位是 INTEGER，查詢傳入字串 → 索引可能失效
SELECT * FROM users WHERE id = '123'; -- 字串，可能不用索引
SELECT * FROM users WHERE id = 123; -- 整數，使用索引
LIKE 查詢限制
-- 只有前綴 LIKE 可用 B-Tree 索引
SELECT * FROM users WHERE name LIKE 'John%'; -- 可用
SELECT * FROM users WHERE name LIKE '%John%'; -- 不可用
-- 中間/後綴 LIKE 需要 pg_trgm + GIN
CREATE INDEX idx_name_trgm ON users USING gin(name gin_trgm_ops);
SELECT * FROM users WHERE name ILIKE '%john%'; -- 可用 GIN
過多索引影響寫入
每個索引都需要在 INSERT/UPDATE/DELETE 時同步維護。超過 10 個索引的表應審查是否有冗餘。
總結
索引設計 是 PostgreSQL 效能調校的核心技能：
- B-Tree 是最通用的預設選擇，支援等值、範圍、排序
- GIN 適合 Array、JSONB、全文搜尋等多值查詢
- GiST 適合空間查詢與範圍重疊
- BRIN 以極小的索引大小處理物理有序的大表
- Partial Index 與 Covering Index 大幅提升特定查詢效能
- 多欄位索引 遵循「等值在前、範圍在後、高選擇性優先」原則
- 線上環境必用 CONCURRENTLY 避免鎖表
- 定期監控未使用索引與膨脹率
下一篇，我們將深入探討 查詢規劃器與執行計畫——PostgreSQL 如何選擇最優查詢路徑，以及如何解讀 EXPLAIN 輸出。