PostgreSQL B-Tree、GIN、GiST、BRIN 索引区别详解-php教程-PHP中文网

b-tree适用于通用有序查询，gin专用于多值/全文倒排索引，gist支持复杂谓词如空间/全文匹配，brin适合超大有序表的块级摘要索引。

postgresql b-tree、gin、gist、brin 索引区别详解 - php中文网

如果您需要为 PostgreSQL 表选择最合适的索引类型，但对 B-Tree、GIN、GiST 和 BRIN 四种核心索引在原理、适用场景与行为差异上缺乏清晰认知，则可能造成查询性能低下或存储资源浪费。以下是区分这四类索引的关键维度及对应操作说明：

一、B-Tree 索引：通用有序结构

B-Tree 是 PostgreSQL 默认索引类型，采用自平衡 B+ 树结构，所有键值按严格升序物理存储，天然支持等值、范围、排序及唯一性约束。其设计目标是兼顾通用性与稳定性，适用于绝大多数 OLTP 场景。

1、执行 CREATE INDEX idx_name ON table_name (column_name); 时未指定 USING 子句，系统自动创建 B-Tree 索引。

2、对多列建立复合索引时，前导列的等值条件是高效利用索引的前提，例如 WHERE a = 1 AND b > 10 可使用 (a, b) 复合索引，而 WHERE b > 10 则无法有效使用该索引。

3、如需支持大小写不敏感的前缀匹配（如 ILIKE 'abc%'），可配合 text_pattern_ops 操作符类 创建：CREATE INDEX idx_text ON t USING btree (col text_pattern_ops);

GIN（Generalized Inverted Index）将每个原子值（如数组元素、JSONB 键路径、全文词干）映射到包含该值的所有行 ID 列表，适合字段内含多个逻辑项的场景。其优势在于精确命中任意子项，但写入开销大、不支持排序。

1、对 JSONB 字段启用路径查询加速：CREATE INDEX idx_jsonb_gin ON orders USING GIN (data);

2、对数组字段实现包含查询（@>）：CREATE INDEX idx_tags_gin ON posts USING GIN (tags);，随后可高效执行 WHERE tags @> ARRAY['pg', 'sql'];

3、启用 pg_trgm 扩展后支持模糊 LIKE 查询：CREATE EXTENSION IF NOT EXISTS pg_trgm;，再创建 CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops);

GiST（Generalized Search Tree）是一种接口框架，允许开发者定义自定义数据类型与操作符类，底层支持 R-Tree 或类似空间划分结构。它专为几何重叠（&&）、包含（@>）、全文匹配（@@）等非标谓词设计，但不保证全局有序。

1、为地理坐标字段添加索引以加速 ST_Within 或 && 查询：CREATE INDEX idx_geom_gist ON locations USING GIST (geom);

2、对 tsvector 类型实现全文检索：CREATE INDEX idx_doc_fts ON documents USING GIST (content_tsvector);，配合 @@ 操作符使用。

3、对 CIDR 类型网络地址进行路由匹配：CREATE INDEX idx_net_gist ON networks USING GIST (network_addr inet_ops);

BRIN（Block Range INdex）不为每行建索引项，而是为每个页（通常 8KB）存储该块内列值的摘要（如 min/max），仅当数据在物理存储上天然有序（如时间戳、序列号）时才高效。其优势是极低的存储与维护成本，适用于 TB 级只读或追加型时序表。

1、在按时间递增插入的日志表上创建：CREATE INDEX idx_log_time_brin ON logs USING BRIN (created_at);

2、调整块范围粒度以匹配访问模式：CREATE INDEX idx_log_brin_64 ON logs USING BRIN (created_at) WITH (pages_per_range = 64);

3、验证 BRIN 有效性需检查 EXPLAIN ANALYZE 输出中是否出现 "Rows Removed by Filter" 显著下降，且索引扫描行数远小于全表扫描行数。