PostgreSQL 16 索引选择与性能验证：B-Tree、GIN、BRIN 的 EXPLAIN ANALYZE 实战

## 测试环境

• 操作系统: Linux x86_64，内核 ≥ 5.10
• 数据库: PostgreSQL 16.0，`shared_buffers=4GB`，`work_mem=64MB`，`effective_cache_size=12GB`
• 扩展: `pg_stat_statements` 已启用，用于采集语句级指标

## 数据集准备

CREATE TABLE articles (
  id BIGSERIAL PRIMARY KEY,
  category TEXT NOT NULL,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  tags TEXT[],
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 1000 万行，均匀与偏斜混合分布（示意）
INSERT INTO articles(category, title, body, tags, created_at)
SELECT
  CASE WHEN random() < 0.8 THEN 'tech' ELSE 'life' END,
  md5(gen_random_uuid()::text),
  repeat('x', (50 + (random()*500))::int),
  ARRAY['pgsql','index','perf'],
  now() - (random()* interval '365 days')
FROM generate_series(1, 10000000);

VACUUM (ANALYZE) articles;

## 索引创建

-- B-Tree：范围与等值查询（选择性高）
CREATE INDEX idx_articles_created_at ON articles USING btree(created_at);
CREATE INDEX idx_articles_category ON articles USING btree(category);

-- GIN：全文检索或数组包含
CREATE INDEX idx_articles_body_fts ON articles USING gin(to_tsvector('simple', body));
CREATE INDEX idx_articles_tags_gin ON articles USING gin(tags);

-- BRIN：按时间追加的大表，低选择性但扫描代价低
CREATE INDEX idx_articles_created_at_brin ON articles USING brin(created_at) WITH (pages_per_range=128);

## 查询与采集方法

使用 `EXPLAIN (ANALYZE, BUFFERS, TIMING)` 评估执行路径与耗时；结合 `pg_stat_statements` 统计。

-- 1) 时间范围（高选择性）：B-Tree 优势明显
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT id, title FROM articles
WHERE created_at BETWEEN now()- interval '1 day' AND now();

-- 2) 类别等值匹配（中选择性）：B-Tree，可能走 Bitmap Heap Scan
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT count(*) FROM articles WHERE category='tech';

-- 3) 全文检索：GIN 优势（`to_tsvector`/`plainto_tsquery`）
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT id FROM articles
WHERE to_tsvector('simple', body) @@ plainto_tsquery('simple', 'index perf');

-- 4) 追加型时间范围（低选择性大跨度）：BRIN 以顺序扫描+过滤更省 IO
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT id FROM articles
WHERE created_at BETWEEN now()- interval '180 days' AND now();

-- pg_stat_statements 采集
SELECT query, calls, mean_exec_time, rows FROM pg_stat_statements
WHERE query LIKE 'SELECT id FROM articles%'
ORDER BY mean_exec_time DESC LIMIT 10;

## 结果对比与结论

• 小范围时间窗口与高选择性等值查询：B-Tree 以 Index Scan/Bitmap Heap Scan 获胜，`buffers` 命中率高，随机读少。
• 全文检索/数组包含：GIN 显著降低过滤代价，`recheck` 开销可接受，适合高并发读。
• 大跨度时间窗口：BRIN 倾向于顺序读，减少随机 IO；在 TB 级 append-only 表上总体耗时更可控。
• 调参要点：
• `random_page_cost` 在 NVMe/SSD 上建议降至 1.1~1.3，更贴近真实随机读成本。
• `effective_cache_size` 需反映 OS 页缓存，避免计划器低估缓存命中收益。

## 选型建议

• 高选择性等值/范围：首选 B-Tree；
• 全文/JSONB 包含：首选 GIN（必要时 `jsonb_path_ops`）；
• 时间序列/追加型超大表：优先 BRIN 配合 `pages_per_range` 调整；
• 结合 `EXPLAIN ANALYZE + BUFFERS` 与 `pg_stat_statements` 做持续验证。

## 注意事项

• GIN 建索引时间与体积较大，更新开销高；
• BRIN 依赖数据按列值相关性存储（例如时间递增插入），否则收益有限；
• 生产变更需评估写负载与维护窗口，避免高峰期重建索引。