這篇涵蓋 PostgreSQL 的 index 選型：何時用哪種 index，跟 query-optimization 的「為什麼這個 plan 慢」互補。

Index Method 與各自適用的 Workload

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

Index method適用 query pattern典型 column type儲存成本
B-tree= / < / > / BETWEEN / IS NULL / LIKE 'prefix%'任何 scalar、最常用中
Hash純 = 比對scalar、不常用低
GIN@> / ? / ?| / FTS / array 包含JSONB / tsvector / array高（write 慢）
GiST範圍 / 空間 / 自訂 operatorgeometry / tsvector / range中
SP-GiSTNon-balanced 樹結構IP / phone prefix / quad-tree中
BRIN大表的 range scan、physical order 跟 logical order 相關timestamp / id（append-only）極低

選錯 index 的代價：

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

B-tree：預設選擇、多數 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 的 column 順序決定哪些 query 用得到它。PostgreSQL 手冊〈Multicolumn Indexes〉寫的規則是：leading column 上的等值條件，加上第一個沒有等值條件的 column 上的範圍條件，會用來限縮 index 掃描的範圍；其餘條件只能在掃到的 entry 上逐筆過濾。

1-- 兩欄都是等值條件：WHERE user_id = 42 AND status = 'pending'
2-- 兩種順序都把掃描限縮到同一組 (user_id, status)
3-- 200 萬列的 orders、PostgreSQL 16 實測兩者都是 Buffers: shared hit=1 read=3
4CREATE INDEX idx_orders_user_status ON orders (user_id, status);
5CREATE INDEX idx_orders_status_user ON orders (status, user_id);
6
7-- 只查 user_id：WHERE user_id = 42
8-- (user_id, status)：Index Scan，Buffers: shared hit=2 read=3
9-- (status, user_id)：leading column 沒有條件，planner 改走 Parallel Seq Scan

順序原則：

  1. 等值條件的 column 在前、範圍條件的 column 在後：範圍條件後面的 column 不再限縮掃描範圍
  2. 會被單獨查詢的 column 放 leading 位置：leading column 沒有條件的 query 用不到這個 index
  3. 兩欄都只用等值條件查時，這個 query 不受順序影響：順序要看的是其他 query 會不會只帶其中一欄

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 選擇影響大：

Op class適用索引大小支援 operator
jsonb_ops（預設）通用大@> / ? / ?| / ?&
jsonb_path_ops只 @> containment1/3-1/2只 @>

只用 @> 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 的選擇：

維度GINGiST
Lookup 速度快 3x慢
Update 速度慢 3x快
索引大小大小
適合場景Read-heavy FTSWrite-heavy / 即時更新

多數 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 與刪除後的 insert 破壞 physical order：UPDATE 把新版本的 row 寫進有空位的 block（不一定是原本那個），DELETE 經 vacuum 回收的空間會讓之後 insert 的新 row 落回舊 block → 那些 block range 的 min/max 被拉寬、BRIN 能跳過的 range 變少
  • 隨機 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 條件推得出 index 的 WHERE 條件時才用得到；query 可以多帶其他條件：

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-- 對日期截斷（created_at 是 timestamptz 時，date_trunc 的結果隨 session 的 TimeZone 變，
10-- 函式是 STABLE、不能進 index，先轉成固定時區的 timestamp）
11CREATE INDEX idx_orders_day ON orders (date_trunc('day', created_at AT TIME ZONE 'UTC'));

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（多數場景）
 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（罕見）

Production 踩雷

過度 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) 多欄）通常可拆掉單欄

Partial index 條件跟 query 不匹配

情境：建 WHERE status = 'active' partial index、application 用 prepared statement 把 status 當參數傳（WHERE status = $1），走 generic plan 時不用 index。

Planner 在規劃時要能從 query 的 WHERE 推出 index 的 WHERE 條件。單一值的 IN ('active') 與 = ANY(ARRAY['active']) 推得出來；多一個值、或值在規劃時還不知道，就推不出來：

 1CREATE INDEX idx_users_active ON users (email) WHERE status = 'active';
 2
 3-- 推得出 status = 'active'：Index Scan using idx_users_active
 4EXPLAIN SELECT * FROM users WHERE status IN ('active') AND email = 'u10@x.com';
 5EXPLAIN SELECT * FROM users WHERE status = ANY(ARRAY['active']) AND email = 'u10@x.com';
 6
 7-- 推不出：('active', 'pending') 裡有 index 條件以外的值，改走 Seq Scan
 8EXPLAIN SELECT * FROM users WHERE status IN ('active', 'pending') AND email = 'u10@x.com';
 9
10-- 推不出：generic plan 規劃時不知道 $1 的值，改走 Seq Scan
11SET plan_cache_mode = force_generic_plan;
12PREPARE q(text, text) AS SELECT * FROM users WHERE status = $1 AND email = $2;
13EXPLAIN EXECUTE q('active', 'u10@x.com');

修法：

  • Partial 條件只用在 query 以常數寫出的條件上；status 以參數傳入時，改建以 status 為 key 的 index（(status, email)）
  • 寫完用 EXPLAIN 驗證 query 真的用到 partial index；prepared statement 用 EXPLAIN EXECUTE 看實際執行的 plan

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 選哪個

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（依時間倒序寫入的 column 是 -1，同樣適合）
  • 真要對 random column 加 index、回 B-tree

Multi-column index 順序錯：範圍條件放在 leading column

情境：常見 query WHERE user_id = 42 AND created_at > now() - interval '10 days'、建 index (created_at, user_id)、效能差。

created_at 上是範圍條件，它在 leading 位置時，index 掃描要走過這段時間內所有 user 的 entry，user_id = 42 只能在掃到的 entry 上逐筆比對。

修法：等值條件的 column 放前面：

 1-- 範圍條件在 leading column：掃過 10 天內所有 user 的 entry
 2CREATE INDEX idx_orders_created_user ON orders (created_at, user_id);
 3
 4-- 等值條件在前：先定位 user_id = 42、再在它底下掃 created_at 範圍
 5CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);
 6
 7EXPLAIN (ANALYZE, BUFFERS)
 8SELECT * FROM orders WHERE user_id = 42 AND created_at > now() - interval '10 days';
 9-- 200 萬列的 orders、PostgreSQL 16 實測：
10--   (created_at, user_id)：Buffers: shared read=3313
11--   (user_id, created_at)：Buffers: shared read=3

跟 MySQL Index 差異

維度PostgreSQLMySQL
Index method6 種（B-tree / Hash / GIN / GiST / SP-GiST / BRIN）主要 B-tree、空間另算 R-tree
預設B-treeB-tree（InnoDB clustered）
Clustered index沒有原生（CLUSTER 一次性）InnoDB primary key 永遠 clustered
CoveringINCLUDE（PG 11+）自然支援（secondary index 帶 PK）
JSON indexGIN on JSONB（強）functional index on JSON（弱）
Partial index原生支援8.0+ 支援（受限）
Expression index原生支援5.7+ functional index
BRIN-like原生沒有
SpatialGiST / PostGISR-tree（基本）

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

相關連結

下一步