索引類型與選擇策略
PostgreSQL 豐富的索引類型——B-Tree、Hash、GIN、GiST、BRIN，各自的適用場景與選擇策略
PostgreSQL 的索引遠比 MySQL 豐富
MySQL（InnoDB）幾乎只有一種索引結構——B+ Tree（加上 Adaptive Hash Index 自動生成）。PostgreSQL 提供六種索引類型，針對不同的資料型態和查詢模式。這是 PostgreSQL 在複雜查詢場景下比 MySQL 更強的主要原因之一。
| 索引類型 | 適用查詢 | 資料型態 | 常見場景 |
|---|---|---|---|
| B-Tree | = < > BETWEEN ORDER BY | 所有可排序型態 | 絕大多數場景（預設） |
| Hash | = | 所有可 hash 型態 | 純等值查詢 |
| GIN | 包含、交集 | Array, JSONB, tsvector, hstore | 全文搜尋、JSON 欄位查詢 |
| GiST | 範圍重疊、最近鄰 | Geometry, range, ltree | 地理空間、範圍查詢 |
| SP-GiST | 分層資料 | IP, phone, geometry | 前綴搜尋、quadtree |
| BRIN | 範圍查詢（有序資料） | 所有可排序型態 | 時序資料、大表 |
B-Tree
預設索引類型，和 MySQL 一樣基於 B+ Tree 結構（葉節點用鏈表串接）。
CREATE INDEX idx_orders_created_at ON orders (created_at);
-- 等同於
CREATE INDEX idx_orders_created_at ON orders USING btree (created_at);
與 MySQL B-Tree 的差異
| 面向 | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| 葉節點存什麼 | 索引值 + 指向 heap tuple 的 TID | Clustered: 整行；Secondary: 索引值 + PK |
| Clustered Index | 沒有（heap table，無物理排序） | 有（PK = 資料的物理組織） |
| 回表 | 所有索引都要回 heap | 只有 Secondary Index 回 Clustered |
| 覆蓋索引 | INCLUDE 子句 或 Visibility Map | 直接在 Secondary Index 讀 |
PostgreSQL 沒有 Clustered Index——表的資料以 heap 形式存放，插入順序隨機。所有索引（包括 PK）都是 secondary index，葉節點存的是 TID（tuple identifier = page + offset），查到後都要回 heap 取完整行。
Covering Index（INCLUDE）
PostgreSQL 12+ 支援 INCLUDE 子句，把非索引欄位附加到葉節點：
CREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (amount, status);
-- 查詢 customer_id + amount + status 時不需要回 heap
INCLUDE 的欄位不參與索引排序，只是搭便車存在葉節點裡。搭配 Visibility Map 的 all-visible 標記，可以完全避免回 heap（Index-Only Scan）。
Hash Index
只支援等值查詢（=），不支援範圍、排序。
CREATE INDEX idx_users_email ON users USING hash (email);
PostgreSQL 10 之前 Hash Index 不寫 WAL（crash 後損壞），10 之後修復了。理論上等值查詢比 B-Tree 快（O(1) vs O(log n)），但實務中差異不大，大部分場景用 B-Tree 就夠了。
GIN（Generalized Inverted Index）
倒排索引，適合「元素包含」查詢。把複合值拆成元素，建立「元素 → 哪些行包含它」的映射。
最佳場景：
全文搜尋
-- tsvector 欄位的 GIN 索引
CREATE INDEX idx_articles_fts ON articles USING gin (to_tsvector('english', content));
SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & mvcc');
JSONB 查詢
CREATE INDEX idx_events_data ON events USING gin (data);
-- 包含查詢
SELECT * FROM events WHERE data @> '{"type": "click"}';
-- 路徑存在
SELECT * FROM events WHERE data ? 'user_id';
GIN 支援兩種 operator class：
jsonb_ops（預設）：支援@>??|?&，索引較大jsonb_path_ops：只支援@>，索引較小、查詢更快
Array 包含
CREATE INDEX idx_products_tags ON products USING gin (tags);
SELECT * FROM products WHERE tags @> ARRAY['electronics', 'sale'];
GIN 的代價
寫入慢——每次 INSERT 要更新大量倒排列表。GIN 用 pending list 緩解：新的 entry 先寫到 pending list，等累積到 gin_pending_list_limit（預設 4MB）再批次合併到索引。代價是查詢時要額外掃描 pending list。
GiST（Generalized Search Tree）
平衡搜尋樹，支援「重疊」「包含」「最近鄰」等空間概念的查詢。
地理空間（PostGIS）
CREATE INDEX idx_places_location ON places USING gist (location);
-- 最近鄰查詢
SELECT * FROM places ORDER BY location <-> ST_MakePoint(121.5, 25.0) LIMIT 10;
Range 型態
CREATE INDEX idx_events_period ON events USING gist (period);
-- 重疊查詢
SELECT * FROM events WHERE period && '[2026-01-01, 2026-03-01)'::daterange;
GiST vs GIN
| 面向 | GIN | GiST |
|---|---|---|
| 查詢速度 | 快（精確命中） | 中等（可能 false positive） |
| 寫入速度 | 慢 | 快 |
| 索引大小 | 大 | 小 |
| 支援排序 | 不支援 | 支援（KNN） |
| 最佳場景 | 全文搜尋、JSONB、array | 地理空間、range |
BRIN（Block Range Index）
輕量級索引，只記錄每一段 page range（預設 128 pages）中的最小值和最大值。
CREATE INDEX idx_logs_created_at ON logs USING brin (created_at);
適用條件
資料的物理順序和欄位值的順序高度相關。例如時間戳欄位按時間順序寫入的 log 表——前面的 page 存的都是舊資料，後面存新資料。BRIN 只需要知道「page 100-227 的 created_at 在 2026-01-01 到 2026-01-15 之間」，查詢時可以直接跳過不相關的 range。
BRIN 的優勢
- 索引極小（一張 100GB 的表，BRIN 索引可能只有幾 MB）
- 維護成本極低（不像 B-Tree 每筆 INSERT 都要更新）
- 適合 append-only 的大表（log、事件、時序資料）
不適合
- 資料物理順序和邏輯順序不相關（隨機 INSERT 的表）
- 需要高選擇性的等值查詢（BRIN 只能做粗過濾）
進階索引功能
Partial Index（部分索引）
只對符合條件的行建索引：
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
如果 99% 的訂單是 completed，只有 1% 是 pending，這個索引只有 B-Tree 的 1% 大小，查 pending 訂單時卻一樣快。
Expression Index（表達式索引）
對表達式建索引：
CREATE INDEX idx_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = '[email protected]';
MySQL 8.0 也支援 functional index，但 PostgreSQL 從很早就有了。
Multicolumn Index 與順序
和 MySQL 一樣遵守最左前綴原則，但 PostgreSQL 額外支援對每個欄位指定排序方向：
CREATE INDEX idx_orders_multi ON orders (customer_id ASC, created_at DESC);
-- 適合 ORDER BY customer_id ASC, created_at DESC
MySQL 8.0 才支援 descending index，PostgreSQL 一直都有。
索引維護
REINDEX
索引和表一樣會膨脹（dead index entry）。VACUUM 會清理 heap 的 dead tuple，但索引的回收效果有限。嚴重膨脹時需要 REINDEX：
REINDEX INDEX idx_orders_created_at;
-- 或重建所有索引
REINDEX TABLE orders;
-- PG 12+ 支援 CONCURRENTLY（不阻塞）
REINDEX INDEX CONCURRENTLY idx_orders_created_at;
監控
-- 索引使用率
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;
-- 未使用的索引（idx_scan = 0）
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- 索引大小
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;