KernelRanch
返回文章列表

PostgreSQL 索引类型速查：btree / hash / gin / gist / brin 各管什么

2026-11-10 #数据库

CREATE INDEX 默认建 B-tree，多数场景也确实够用。但把 JSONB、数组、时序数据也交给 B-tree，就会遇到"索引建了却用不上"。

1. 六种类型对照

类型支持的运算典型场景写开销
btree< <= = >= >，BETWEEN / IN，IS NULL，前缀 LIKE 'foo%'绝大多数等值与范围查询、排序低
hash仅 =超长文本等值查询、只需判等低
gin取决于 opclass，数组/JSONB 常见 <@ @> = &&数组包含、JSONB 键值、全文检索高（写入慢）
gist取决于 opclass，几何类型 &&、@> 等地理/几何、范围类型、最近邻中
spgist取决于 opclass，点的 <<、>>非平衡结构（四叉树、k-d 树）中
brin< <= = >= >与物理顺序强相关的列（时间戳、自增 id）极低

官方对 B-tree 的描述很明确：能处理可排序数据的等值与范围，LIKE 只有"锚定开头"才可能用上（col LIKE 'foo%' 可以，'%bar' 不行）。Hash 只存 32 位哈希值，因此"只能处理简单等值比较"。

2. 三个按需选型的例子

-- JSONB 里查键：GIN
CREATE INDEX idx_orders_meta ON orders USING gin (meta jsonb_path_ops);

-- 数组包含：GIN
CREATE INDEX idx_tags ON articles USING gin (tags);

-- 时间序列表，按插入顺序增长：BRIN 又小又快
CREATE INDEX idx_events_ts ON events USING brin (created_at);

BRIN 的原理是"按连续物理块区间存摘要"（每段记最小值/最大值），所以索引可以小到几百 KB。代价是它只在数据与物理顺序相关时才有效。

3. 四个常见误用

BRIN 用在乱序数据上。如果 created_at 是随机写入的，BRIN 的块区间摘要几乎覆盖全表范围，查询还是要扫大部分数据。BRIN 的前提是相关性，不是"列名带时间"。

指望 hash 支持排序或范围。Hash 索引不能用于 ORDER BY、>、BETWEEN。只有 = 且值很长（比如 UUID 字符串、URL）时，它比 B-tree 省空间。

GIN 建在高频写入的表上。GIN 的插入代价明显高于 B-tree，而且有 pending list 机制。写多读少的表要慎重，必要时调 gin_pending_list_limit。

index-only scan 没生效。EXPLAIN 里明明写了 Index Only Scan，却仍然有大量 Heap Fetches——说明 visibility map 没跟上，先 VACUUM。索引覆盖不等于不回表。

4. 怎么确认索引真的被用了

-- 看计划与实际读取
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

-- 看索引被扫了多少次
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes ORDER BY idx_scan DESC LIMIT 10;

idx_scan = 0 的索引是纯开销：占空间、拖慢写入。定期清掉长期不用的索引比不断加索引更有价值。

小结

选型顺序可以简化成三步：等值与范围用 B-tree；数组/JSONB/全文用 GIN；与物理顺序强相关的大表用 BRIN。几何与最近邻才需要 GiST/SP-GiST，等值长文本可以考虑 Hash。

索引不是越多越好。每加一个都增加写入成本，而写入成本在事务里是串行的。先看 pg_stat_user_indexes，再决定加还是删。

数据来源与检索时间

检索时间 2026-11-10。

来源URL对应数据
PostgreSQL 18 文档《11.2 索引类型》https://www.postgresql.org/docs/18/indexes-types.html六种类型的适用运算符；B-tree 的 LIKE 前缀限制；Hash 仅支持 =；GIN 为倒排索引；BRIN 按物理块区间存摘要
PostgreSQL 18 文档《11.11 只用索引的扫描》https://www.postgresql.org/docs/18/indexes-index-only-scans.htmlindex-only scan 依赖 visibility map，需要 VACUUM 维护

核验说明

  • 索引类型与运算符清单逐条对照文档；第 2、4 节的 SQL 语法在本机 18.6 上执行通过。
  • 未做：没有在同一数据集上对比六种索引的构建时间与查询耗时；BRIN 的相关性前提是文档结论，本机未做对照实验。这类对比在各自文章里更合适，速查表只负责"选哪个"。
#数据库
返回文章列表