PostgreSQL 索引进阶实测对比：GIN、BRIN 与部分索引到底该用在哪

很多从 MySQL 转过来的开发者对 PG 索引的认知停留在 B-tree 上，于是遇到 JSONB 查询慢、日志表查询慢、状态字段查询慢时，第一反应还是「加个普通索引」，结果要么建不上（类型不支持），要么建上了却完全不生效。PostgreSQL 的索引体系比大多数关系库丰富得多：GIN、BRIN、部分索引、表达式索引各自解决一类特定问题，选对索引类型往往能把查询从秒级拉到毫秒级，还能把索引体积压缩一个数量级。这篇文章用我们数仓与业务库上的真实场景，把三类最容易被忽视的索引讲透。

先给一个总原则：索引选型的本质是匹配数据的访问模式，而不是背类型清单。字段能不能等值匹配、数据是否天然有序、查询是否只集中在一个子集上——这三个问题的答案，基本就决定了该用哪类索引。

五类索引横向对比：一张表建立决策框架

先用一张对比表把 PG 常用索引类型的能力边界画清楚，后文再逐类展开：

索引类型	适用数据	典型场景	空间成本	写入代价
B-tree	可排序标量	等值、范围、排序、唯一约束	中	低
GIN	复合值	JSONB、数组、全文、pg_trgm 模糊	大	中高
BRIN	物理有序大表	时序日志、按时间追加的流水	极小（KB/GB 级）	极低
部分索引	高频子集	状态过滤、软删除过滤	按子集比例缩小	只对子集生效
Hash	等值标量	仅等值、无排序需求	中	低

一个直观的体感数字：我们在 8 亿行的 Nginx 访问日志表上分别测试，B-tree 索引建在 request_time 列上占用约 32GB，同样的列 BRIN 只占约 200MB，差距接近 150 倍；而一个只覆盖「待处理」状态订单的部分索引，体积只有全量状态索引的 6%。空间和写放大换来的收益是否值得，取决于你的查询能不能命中它们的能力区间。

GIN：JSONB、数组与模糊检索的通用利器

GIN（Generalized Inverted Index，倒排索引）是 PG 处理「一个值包含多个元素」的标准答案。只要查询条件是「包含」语义而非「等于」语义——JSONB 的键值包含、数组的成员匹配、全文检索的词项命中——B-tree 都无能为力，GIN 却能高效响应：

sql

-- 商品表：JSONB 属性 + 标签数组
CREATE TABLE product (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    attrs       JSONB NOT NULL DEFAULT '{}',   -- 颜色、尺码等动态属性
    tags        TEXT[] NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- JSONB 默认 GIN 索引：支持 @>、?、?? 等包含类操作符
CREATE INDEX idx_product_attrs ON product USING GIN (attrs);

-- 数组列 GIN 索引：支持 &&（交集）、@>（包含）查询
CREATE INDEX idx_product_tags ON product USING GIN (tags);

-- 查询一：属性包含红色且尺码为 XL 的商品，走 idx_product_attrs
SELECT id, name FROM product WHERE attrs @> '{"color": "红色", "size": "XL"}';

-- 查询二：打了「新品」或「爆款」标签的商品，走 idx_product_tags
SELECT id, name FROM product WHERE tags && ARRAY['新品', '爆款'];

另一个高价值用法是配合 pg_trgm 扩展做模糊匹配。PG 的 B-tree 只支持左前缀 LIKE（LIKE 'abc%'），而 Gin+trigram 组合可以让任意位置的模糊查询走索引：

sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 对名称列建 trigram GIN 索引，LIKE '% Keyword%'、ILIKE 均可命中
CREATE INDEX idx_product_name_trgm ON product USING GIN (name gin_trgm_ops);

EXPLAIN ANALYZE SELECT id FROM product WHERE name LIKE '%蓝牙耳机%';
-- 8 万行表上实测：走 GIN 位图扫描约 1ms，顺序扫描约 25ms

代价也要说清楚：GIN 索引体积大（我们商品表 attrs 列的 GIN 索引是表本体的 1.4 倍）、写入代价高。PG 内部用 pending list 缓冲写放大（fastupdate 默认开启），但高频写入的表要留意 gin_pending_list_limit 堆积导致的查询毛刺，必要时降低该值或在低峰期定期 VACUUM 清理。

BRIN：时序大表上以小博大的王者

BRIN（Block Range Index）的思路完全不同于其他索引：它不为每一行记录键值，只为每个物理块区间记录 min/max 摘要。因此体积极小、维护成本近乎为零，但生效前提非常苛刻——列值的物理存储顺序必须与逻辑顺序高度一致。这恰好是时序数据的天然特征：日志表、流水表、监控采样表按时间追加写入，created_at 的物理顺序就是时间顺序。

sql

-- 8 亿行访问日志表，按时间顺序持续追加
CREATE TABLE access_log (
    id            BIGINT GENERATED ALWAYS AS IDENTITY,
    host          TEXT NOT NULL,
    path          TEXT NOT NULL,
    status        INT NOT NULL,
    request_time  NUMERIC(8,3) NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- BRIN 索引：整表仅约 200MB（对比 B-tree 约 32GB）
CREATE INDEX idx_log_created_brin
    ON access_log USING BRIN (created_at) WITH (pages_per_range = 64);

-- 查昨晚 20:00 到 22:00 的慢请求，BRIN 快速排除无关块区间
SELECT host, path, request_time
FROM access_log
WHERE created_at >= '2026-10-11 20:00' AND created_at < '2026-10-11 22:00'
  AND request_time > 1.5;

pages_per_range 是 BRIN 最重要的参数：值越小、摘要越精细、过滤效果越好，但索引也越大。默认 128 对大多数追加型表已经够用；如果时间列存在大量乱序回填（比如补录历史数据），过滤效果会急剧劣化，此时要么调小 range，要么放弃 BRIN 改回 B-tree。判断方法很直接：EXPLAIN (ANALYZE, BUFFERS) 里看 BRIN 索引扫描后被过滤掉的块数占比，占比低于 80% 就说明物理相关性已经不行了。

部分索引：用十分之一的体积覆盖九成的查询

部分索引（Partial Index）是 PG 最「工程化」的一个特性：只对满足 WHERE 谓词的行建索引。绝大多数业务查询只关心数据的一个小子集——待处理的订单、未读的消息、未删除的记录——给这些热点子集建小索引，比给全表建大索引更快也更省：

sql

-- 订单表 2 亿行，其中「待处理」状态只占约 4%
CREATE TABLE orders (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    status     TEXT NOT NULL,          -- pending / paid / done / cancelled
    created_at TIMESTAMPTZ NOT NULL
);

-- 全量状态索引要覆盖 2 亿行；部分索引只覆盖 800 万行 pending 订单
CREATE INDEX idx_orders_pending
    ON orders (created_at) WHERE status = 'pending';

-- 查询谓词与索引谓词一致（或为其子集）时才会命中
EXPLAIN ANALYZE
SELECT id FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 50;
-- 实测：部分索引扫描约 0.3ms，规划器还能根据谓词推算出更准的行数

-- 进阶：软删除场景的组合部分索引，把 is_deleted 过滤内建进索引
CREATE INDEX idx_orders_alive_user
    ON orders (user_id, created_at) WHERE status <> 'cancelled';

部分索引有个容易踩死的规则：查询条件必须能让规划器在逻辑上推出「命中的行都落在索引谓词范围内」。写 WHERE status IN ('pending') 能命中，写 WHERE lower(status) = 'pending' 就命中不了，因为谓词形式变了；写 WHERE status = 'pending' OR status = 'paid' 时也只会对 pending 分支使用该索引。保持查询写法与索引谓词字面一致，是最稳妥的用法。

避坑总结

先看访问模式再选索引，别用 B-tree 解决一切。「包含」语义找 GIN，物理有序大范围扫描找 BRIN，热点子集找部分索引；选错类型等于白花空间还拖慢写入。
GIN 的写放大要有预案。高频写入表上 GIN 索引可能拖垮批量导入，导入期可先删索引后重建，线上则关注 pending list 积压与 gin_pending_list_limit 配置。
BRIN 只信物理顺序，不信表结构想象。乱序回填、UPDATE 搬迁行都会破坏相关性，用 pageinspect 或执行计划的块过滤占比定期验证，失效就换方案。
部分索引的谓词要与查询字面保持一致。函数包裹、OR 分支、隐式类型转换都可能让规划器无法证明谓词覆盖，索引悄悄失效是这类问题最典型的表现。
索引数量是负债不是资产。每加一个索引都在为所有写操作加税，定期用 pg_stat_user_indexes 查 idx_scan 为零的索引，敢于下线。

写在最后

PostgreSQL 的索引体系给了开发者远超「加索引」三个字的优化空间：GIN 处理复合值、BRIN 撬动时序大表、部分索引精准覆盖热点。真正的工作量不在建索引那几秒 SQL，而在理解自己数据的访问模式、验证索引真实命中、以及持续清理不再被使用的索引。把决策框架建立起来，索引才会从运维负担变成性能杠杆。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

PostgreSQL 索引进阶实测对比：GIN、BRIN 与部分索引到底该用在哪 ​

五类索引横向对比：一张表建立决策框架 ​

GIN：JSONB、数组与模糊检索的通用利器 ​

BRIN：时序大表上以小博大的王者 ​

部分索引：用十分之一的体积覆盖九成的查询 ​

避坑总结 ​

写在最后 ​