PostgreSQL 索引类型选择与场景实践：B-Tree、GIN、GiST、BRIN 的性能与存储权衡可复现实验

本文以可执行的 SQL 与度量方法说明不同索引的适用场景、性能与存储开销。所有结论可用 `EXPLAIN ANALYZE`、`pg_relation_size` 与 `pg_stat_statements` 验证。

## 适用版本与前提

• PostgreSQL 13–16（`tsvector`/GIN、GiST 与 BRIN 在上述版本表现稳定）。
• 安装扩展示例：`CREATE EXTENSION IF NOT EXISTS pg_stat_statements;`

## 测试表与数据

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

-- 生成样例数据（约 100k 行）可使用应用侧批量插入或 generate_series；此处略。

## 场景一：精确/范围查询（B-Tree）

适用：主键查找、唯一约束、时间范围查询。

-- 时间范围：
CREATE INDEX idx_docs_created ON documents(created_at);

EXPLAIN ANALYZE
SELECT id FROM documents
WHERE created_at BETWEEN now() - interval '7 days' AND now();

验证要点：`Index Only Scan` 或 `Index Scan` 的出现，`Planning/Execution Time` 显著下降；

使用：

SELECT pg_relation_size('idx_docs_created'); -- 索引尺寸（字节）

## 场景二：全文检索（GIN on tsvector）

适用：中文/英文混合文本搜索，支持布尔与短语匹配。

ALTER TABLE documents ADD COLUMN body_tsv tsvector;
UPDATE documents SET body_tsv = to_tsvector('simple', coalesce(title,'') || ' ' || coalesce(body,''));
CREATE INDEX idx_docs_body_tsv ON documents USING GIN(body_tsv);

EXPLAIN ANALYZE
SELECT id FROM documents WHERE body_tsv @@ plainto_tsquery('simple', '延迟 优化');

验证要点：与 `LIKE '%词%'` 相比，`Bitmap Index Scan` + `Recheck` 更高效；

用 `pg_stat_statements` 观察平均耗时与调用频次：

SELECT query, mean_time, calls FROM pg_stat_statements
WHERE query LIKE '%body_tsv @@%'
ORDER BY mean_time DESC LIMIT 5;

## 场景三：数组包含/集合查询（GIN on array)

适用：标签包含、集合交集。

CREATE INDEX idx_docs_tags ON documents USING GIN(tags);
EXPLAIN ANALYZE
SELECT id FROM documents WHERE tags @> ARRAY['postgres','index'];

验证要点：`Bitmap Index Scan` 出现，行过滤成本显著降低；与无索引相比扫描行数减少到集合相对规模。

## 场景四：几何/相似度（GiST）

适用：范围、近邻、几何/文本相似度（需具体数据类型支持）。

示例以 `cube` 或 PostGIS（若已安装）为例：

-- 以二维点近邻为例（PostGIS）：
-- CREATE EXTENSION postgis;
-- ALTER TABLE documents ADD COLUMN pt geometry(Point, 4326);
-- CREATE INDEX idx_docs_pt_gist ON documents USING GiST(pt);
-- EXPLAIN ANALYZE SELECT id FROM documents ORDER BY pt <-> ST_MakePoint(116.4, 39.9) LIMIT 10;

验证要点：`Index Scan using ... GiST` 或 KNN (`ORDER BY ... <->`) 出现，近邻检索在千万级点数据可达毫秒级。

## 场景五：时序/追加写（BRIN）

适用：按时间/自增主键单调写入的大表，低选择性范围过滤。

CREATE INDEX idx_docs_created_brin ON documents USING BRIN(created_at);
VACUUM (INDEX_CLEANUP ON, ANALYZE ON) documents;

EXPLAIN ANALYZE
SELECT count(*) FROM documents WHERE created_at >= now() - interval '30 days';

验证要点：BRIN 索引尺寸与表大小近似线性极小（典型仅几十 KB～数 MB），在最近窗口过滤显著减少块扫描；

对比尺寸：

SELECT pg_size_pretty(pg_relation_size('idx_docs_created_brin'));
SELECT pg_size_pretty(pg_relation_size('idx_docs_created'));

## 尺寸与维护开销对比

• 统计尺寸：`pg_relation_size`/`pg_total_relation_size`。
• 维护建议：高写入频率下 GIN 适度 `REINDEX`；BRIN 周期性 `VACUUM` 保持摘要新鲜；B-Tree 在热点更新下关注膨胀。

SELECT relname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index JOIN pg_class ON pg_index.indexrelid = pg_class.oid
WHERE indrelid = 'documents'::regclass ORDER BY 2 DESC;

## 选择建议（可验证）

• 精确/范围：优先 B-Tree；窗口过滤大表可辅以 BRIN。
• 文本/数组：GIN；对相似度/几何近邻用 GiST。
• 通过 `EXPLAIN ANALYZE` 与 `pg_stat_statements` 实测你的数据与查询；索引选择以实际耗时与尺寸为准而非教科书。

## 注意事项

• GIN 构建耗时与内存占用较高，生产建议并行与维护窗口构建。
• BRIN 适合“追加写”与“低选择性范围”，对离散随机写入收益有限。
• 更新频繁字段建立多索引需评估写入放大与膨胀。

## 相关文章（同分类热门）

• [PostgreSQL 查询计划与索引优化实战：EXPLAIN ANALYZE 与 pg_stat_statements](./PostgreSQL 查询计划与索引优化实战：EXPLAIN ANALYZE 与 pg_stat_statements.md)
• [PostgreSQL 分区表与数据生命周期管理：RANGE、LIST、HASH 分区与性能验证](./PostgreSQL 分区表与数据生命周期管理：RANGE、LIST、HASH 分区与性能验证.md)

## 结语

通过场景化的可复现实验与尺寸/耗时度量，能够在真实业务中为 PostgreSQL 选择正确的索引类型，实现性能与成本的平衡。