進階索引類型

B-Tree 是萬用的索引類型，但某些特殊查詢需要專門的索引。本篇將介紹 PostgreSQL 提供的進階索引類型及其應用場景。

一、索引類型總覽

索引類型	適用場景	常見用途
B-Tree	等值、範圍、排序	預設，通用
GIN	多值、全文搜尋	JSONB、陣列、全文
GiST	幾何、範圍重疊	地理空間、範圍查詢
BRIN	大表、自然排序	時序資料、日誌
Hash	純等值查詢	極少使用

二、 GIN 索引 (Generalized Inverted Index)

GIN 是「反向索引」，適合一個欄位包含多個值的情況。

JSONB 索引

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB
);

-- 建立 GIN 索引
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- 支援的查詢
SELECT * FROM products WHERE attributes @> '{"color": "red"}';           -- 包含
SELECT * FROM products WHERE attributes ? 'warranty';                     -- 有此 key
SELECT * FROM products WHERE attributes ?| ARRAY['color', 'size'];        -- 有任一 key
SELECT * FROM products WHERE attributes ?& ARRAY['color', 'size'];        -- 有全部 key

陣列索引

sql

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]
);

CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- 支援的查詢
SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];       -- 包含此標籤
SELECT * FROM posts WHERE tags && ARRAY['sql', 'database'];  -- 有任一標籤
SELECT * FROM posts WHERE 'postgresql' = ANY(tags);          -- 同上

全文搜尋索引

sql

-- 方法 1：直接對 tsvector 建索引
ALTER TABLE articles ADD COLUMN search_vector tsvector;
UPDATE articles SET search_vector = to_tsvector('chinese', title || ' ' || content);
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- 方法 2：表達式索引
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || content));

-- 查詢
SELECT * FROM articles WHERE search_vector @@ to_tsquery('chinese', 'PostgreSQL & 索引');

GIN 操作符類別

sql

-- jsonb_ops（預設）：支援所有 JSONB 運算子，索引較大
CREATE INDEX idx1 ON products USING GIN (attributes);

-- jsonb_path_ops：只支援 @>，但索引更小更快
CREATE INDEX idx2 ON products USING GIN (attributes jsonb_path_ops);

三、 GiST 索引 (Generalized Search Tree)

GiST 支援「重疊」和「包含」查詢，適合幾何形狀和範圍資料。

範圍類型

sql

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name TEXT,
    duration TSTZRANGE  -- 時間範圍類型
);

CREATE INDEX idx_events_duration ON events USING GIST (duration);

INSERT INTO events (name, duration) VALUES
    ('Meeting', '[2024-01-15 10:00, 2024-01-15 12:00)');

-- 查詢重疊的事件
SELECT * FROM events WHERE duration && '[2024-01-15 11:00, 2024-01-15 13:00)';

-- 查詢包含特定時間的事件
SELECT * FROM events WHERE duration @> '2024-01-15 11:00'::timestamptz;

PostGIS 地理空間

sql

-- 需要先安裝 PostGIS
CREATE EXTENSION postgis;

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY(Point, 4326)
);

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);  -- 1 公里內

排除約束

GiST 支援排除約束，例如「會議室不能雙重預訂」：

sql

CREATE TABLE reservations (
    room_id INTEGER,
    during TSTZRANGE,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- 同一會議室、時間重疊的預約會被拒絕
INSERT INTO reservations VALUES (1, '[2024-01-15 10:00, 2024-01-15 12:00)');
INSERT INTO reservations VALUES (1, '[2024-01-15 11:00, 2024-01-15 13:00)');  -- 錯誤！

四、 BRIN 索引 (Block Range Index)

BRIN 非常輕量，適合自然排序的大表（如時序資料）。

原理

BRIN 不索引每一列，而是記錄每個「區塊範圍」的最小/最大值：

Block 1-10:   min=2024-01-01, max=2024-01-10
Block 11-20:  min=2024-01-11, max=2024-01-20
...

查詢時只需掃描可能包含結果的區塊。

範例

sql

CREATE TABLE logs (
    id SERIAL,
    created_at TIMESTAMPTZ,
    message TEXT
);

-- 假設有 1 億筆日誌，按時間順序插入
-- BRIN 索引只佔 B-Tree 的 1/1000 大小
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- 適合範圍查詢
SELECT * FROM logs WHERE created_at BETWEEN '2024-01-01' AND '2024-01-02';

適用條件

條件	符合 BRIN
資料量	超大表（百萬筆以上）
插入順序	自然排序（時間序）
查詢類型	範圍查詢
更新頻率	很少更新

> **BRIN 的限制**

如果資料插入順序隨機，BRIN 會失效。例如按 user_id 查詢時，user_id 分散在各區塊，無法跳過。

五、 Hash 索引

Hash 索引只支援純等值查詢，且 PostgreSQL 10 前不能持久化。現在很少使用。

sql

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- 只支援
SELECT * FROM users WHERE email = 'alice@example.com';

-- 不支援
SELECT * FROM users WHERE email LIKE 'alice%';
SELECT * FROM users ORDER BY email;

> **何時使用 Hash？**

幾乎不需要。B-Tree 的等值查詢效能也很好，而且更靈活。

六、全文搜尋實戰

基本概念

sql

-- tsvector：將文字轉成可搜尋的向量
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 結果：'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

-- tsquery：搜尋條件
SELECT to_tsquery('english', 'quick & fox');
-- 結果：'quick' & 'fox'

-- 匹配
SELECT to_tsvector('english', 'quick brown fox') @@ to_tsquery('english', 'quick & fox');
-- 結果：true

完整範例

sql

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(content, '')), 'B')
    ) STORED
);

CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);

-- 插入資料
INSERT INTO articles (title, content) VALUES
    ('PostgreSQL Index Guide', 'This article explains how indexes work in PostgreSQL...'),
    ('MySQL vs PostgreSQL', 'Comparing the two popular databases...');

-- 搜尋
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'postgresql & index') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

中文全文搜尋

PostgreSQL 內建分詞器不支援中文，需要擴充：

sql

-- 安裝 pg_jieba（結巴分詞）
CREATE EXTENSION pg_jieba;

SELECT to_tsvector('jiebacfg', '我愛台灣的珍珠奶茶');

七、 pg_trgm 模糊搜尋

pg_trgm 擴充支援相似度搜尋和模糊匹配。

sql

CREATE EXTENSION pg_trgm;

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT
);

-- 建立 GIN 索引支援相似度
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- 相似度搜尋
SELECT name, similarity(name, 'Postgres') AS sim
FROM products
WHERE name % 'Postgres'  -- 相似度 > 0.3
ORDER BY sim DESC;

-- 模糊搜尋（LIKE 也能用索引！）
SELECT * FROM products WHERE name LIKE '%sql%';
SELECT * FROM products WHERE name ILIKE '%SQL%';

> **pg_trgm 的妙用**

普通 B-Tree 不支援 LIKE '%xxx%'，但 GIN + pg_trgm 可以！

八、索引類型選擇指南

九、 CONCURRENTLY 建立索引

大表建索引會鎖表，使用 CONCURRENTLY 避免阻塞：

sql

-- 不鎖表建立索引
CREATE INDEX CONCURRENTLY idx_orders_date ON orders(created_at);

-- 注意事項
-- 1. 速度較慢（約 2-3 倍）
-- 2. 不能在交易內使用
-- 3. 可能失敗，檢查 pg_indexes 的 indisvalid

總結

索引類型	最佳用途	大小	寫入開銷
B-Tree	通用查詢	中	中
GIN	JSONB、陣列、全文	大	高
GiST	地理空間、範圍	中	中
BRIN	時序大表	極小	極低
Hash	純等值	中	中

選擇正確的索引類型，能讓查詢效能提升數十甚至數百倍！

進階挑戰

為一個包含 JSONB 欄位的表建立 GIN 索引，測試 @> 和 ? 運算子的查詢效能
使用 pg_trgm 擴充實作模糊搜尋功能，支援 LIKE '%keyword%'
比較 GIN 和 GiST 索引在全文搜尋場景下的效能差異

延伸閱讀與資源

← 上一章：索引原理與策略 | 返回專題首頁 | 下一章：EXPLAIN 完全解讀 →

進階索引類型 ​

一、 索引類型總覽 ​

二、 GIN 索引 (Generalized Inverted Index) ​

JSONB 索引 ​

陣列索引 ​

全文搜尋索引 ​

GIN 操作符類別 ​

三、 GiST 索引 (Generalized Search Tree) ​

範圍類型 ​

PostGIS 地理空間 ​

排除約束 ​

四、 BRIN 索引 (Block Range Index) ​

原理 ​

範例 ​

適用條件 ​

五、 Hash 索引 ​

六、 全文搜尋實戰 ​

基本概念 ​

完整範例 ​

中文全文搜尋 ​

七、 pg_trgm 模糊搜尋 ​

八、 索引類型選擇指南 ​

九、 CONCURRENTLY 建立索引 ​

總結 ​

進階挑戰 ​

延伸閱讀與資源 ​