PostgreSQL的索引并不是一成不变的默认B-tree。很多项目在数据量增长后出现慢查询，第一反应往往是继续加索引，但真正的原因是索引类型与查询模式不匹配。过去几个版本中，PostgreSQL在索引结构、创建方式、覆盖扫描和在线维护方面都有明显进步。理解这些能力，可以让索引从降低写入速度的负担变为精准加速查询的工具。
以下从三个角度展开：专用索引类型如何替代B-tree、索引创建与维护如何减少阻塞、以及查询优化时如何根据执行计划选择索引。
一、索引类型演进：B-tree之外还有更优选择
PostgreSQL最常用的B-tree索引适合等值查询和有序扫描，但当列上存储的是数组、JSON文档或全文文本时，B-tree往往无法直接命中。GIN索引通过倒排结构将数组元素、JSON键值或词位映射到行号，使@>、?、@@等操作能够高效执行。例如一张商品表需要按标签数组进行包含查询，普通B-tree基本派不上用场，而GIN索引可以直接定位到包含指定标签的行。
BRIN索引专门面向超大规模顺序存储的表。它把相邻数据块的最小值与最大值汇总成一行元数据，因此体积远小于B-tree。对于日志、传感器读数或按时间追加的数据，BRIN能显著降低索引维护成本。缺点是精度受限，不适合随机分布的数据，因为一个数据块范围内的最小值和最大值可能覆盖大量无关行。
CREATE INDEX idx_events_brin ON events USING brin (created_at); CREATE INDEX idx_products_gin ON products USING gin (tags jsonb_path_ops);
GiST与SP-GiST提供更灵活的树形结构，适用于几何类型、范围类型以及自定义数据类型。例如，timetz范围查询、IP地址前缀匹配等，都可以借助GiST索引。选择索引类型的第一步，不是看列是否经常出现在WHERE中，而是看查询操作符与数据物理分布。B-tree仍然是最通用的选择，但面对数组、JSON、全文、几何数据和超大规模顺序数据时，专用索引通常能带来数量级的性能提升。
二、索引创建与维护的关键改进
早期PostgreSQL在创建索引时会长时间阻塞写入，且大表上重建索引通常需要停机窗口。如今CREATE INDEX CONCURRENTLY可以让索引在后台构建，不阻塞正常读写，但这会消耗更多系统资源，并可能因为唯一约束冲突而失败。在线创建索引适合生产环境，但需要监控内存和磁盘IO是否出现瓶颈。
CREATE INDEX CONCURRENTLY idx_users_email ON users (lower(email));
INCLUDE语法允许把非键列带到索引叶子节点中，实现覆盖查询，又不会增加索引树排序负担。比如经常按user_id查询且同时返回nickname和avatar，可以把这两个列放入INCLUDE，避免回表。与普通多列索引相比，INCLUDE列不参与排序和唯一性判断，但能在索引扫描时直接输出结果。
CREATE INDEX idx_orders_cover ON orders (user_id, created_at) INCLUDE (total_amount, status);
对于索引膨胀或损坏，REINDEX CONCURRENTLY允许在线重建索引。维护索引时还应关注pg_stat_user_indexes中的扫描次数，长期为零的索引可能只是浪费存储和写入性能。删除不用的索引能降低每次INSERT和UPDATE的维护成本，这对高频写入表尤其重要。
三、索引优化实践：从查询计划到选择性
了解索引类型之后，更重要的是读懂执行计划。使用EXPLAIN (ANALYZE, BUFFERS)可以发现索引扫描与位图扫描的差异。仅索引扫描（Index Only Scan）只有在查询所需列全部被索引覆盖且可见性映射允许时才会出现。如果查询结果需要回表获取其他列，执行计划通常会退化为普通索引扫描或位图扫描。
EXPLAIN (ANALYZE, BUFFERS) SELECT user_id, created_at FROM orders WHERE user_id = 10086 AND created_at > '2025-01-01';
如果查询条件总是过滤掉绝大多数行，部分索引可以显著减少索引体积。例如只为未完成订单建立索引，比全表订单索引更小、更快。表达式索引则适用于经常对列进行函数运算的场景，比如对邮箱地址做小写归一化后再查询。它能让WHERE lower(email) = 'abc@ippipp.com'直接命中索引，而不是全表计算。
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending'; CREATE INDEX idx_users_email_lower ON users (lower(email));
最后，不要忽略多列索引的列顺序。PostgreSQL遵循最左前缀原则，把等值条件列放在前面、范围条件列放在后面，能让索引过滤能力最大化。结合pg_stats中的直方图和相关性，可以判断某列是否适合建立BRIN或B-tree。对于高度相关的顺序列，BRIN的压缩收益非常明显；而对于高基数且分布均匀的列，B-tree仍然是最稳妥的选择。
索引优化不是一次性动作，而是随着数据分布和查询负载变化持续调整的过程。定期检查未使用索引、分析慢查询的执行计划、尝试专用索引类型，才能真正发挥PostgreSQL索引进步带来的价值。
PostgreSQL索引BRIN索引索引优化修改时间：2026-08-29 01:43:43