1. 为什么索引这件事值得单独拎出来聊

但凡跟数据库打过交道，大概率都听过一句话：查询慢就加索引。这话对，但只对了三分之一。另外三分之二是什么？是加什么类型的索引、加在哪些列上、加了之后为什么有时候反而更慢。我在实际项目里见过太多“索引一加，性能翻车”的案例，也见过该加索引的地方死活不加、全靠硬件硬扛的配置。所以这篇内容不打算泛泛而谈，而是把 PostgreSQL 里几种主流索引类型掰开揉碎，讲清楚它们各自适合什么场景、背后的数据结构长什么样、以及实操中怎么选、怎么建、怎么验证。

PostgreSQL 数据库的索引类型这个话题，表面上看是 DBA 的活儿，但实际上只要你在写 SQL、做后端开发、甚至做数据分析，都绕不开。它解决的问题很具体：当表里的数据从几千行涨到几千万行，一条原本 10 毫秒的查询变成 10 秒，你怎么办？索引就是那个把 10 秒拉回 10 毫秒的东西。但前提是你得知道 B-tree、Hash、GIN、GiST、BRIN、SP-GiST 这些名字背后分别代表什么能力边界。

这篇内容适合三类人看：一是刚接触 PostgreSQL、想系统了解索引体系的后端开发者；二是已经在用 PostgreSQL 但查询优化全靠“感觉”的工程师；三是需要为特定业务场景（比如 JSON 字段查询、全文检索、地理数据）选型索引的技术负责人。我会尽量用生活化的类比把数据结构讲明白，同时给出可以直接抄的建索引语句和验证方法。

2. PostgreSQL 索引类型全景拆解

2.1 索引到底在解决什么问题

先打个比方。你有一本 800 页的书，想找“事务隔离级别”这个词出现在哪几页。如果没有目录，你只能从第一页翻到最后一页，这叫全表扫描。如果书后面有一个按拼音排序的索引表，你翻到对应字母就能定位页码，这叫索引扫描。数据库索引干的就是这个事，只不过它面对的是磁盘上的数据页，而不是纸质书页。

PostgreSQL 的索引本质上是一种独立的数据结构，它存储了“列值”到“数据行物理位置”的映射关系。当你执行SELECT * FROM orders WHERE user_id = 12345时，如果 user_id 上有索引，优化器就可能选择先查索引找到目标行的位置，再回表取完整数据，而不是逐行扫描整张 orders 表。

但这里有个关键点：索引不是免费的。每建一个索引，插入、更新、删除操作都要额外维护这个索引结构。你建了 5 个索引，一次 INSERT 就要写 6 个地方（表本身加 5 个索引）。所以索引的本质是一个权衡：用写入性能和存储空间，换读取性能。

2.2 六种索引类型的能力边界

PostgreSQL 原生支持多种索引类型，每种背后是不同的数据结构和适用场景。下面这张表是我自己整理的核心对比，建议先有个全局印象：

这张表不是让你背的，而是让你在遇到具体问题时能快速定位到候选类型。比如你要查 JSONB 字段里是否包含某个 key，B-tree 基本帮不上忙，得用 GIN。你要查“距离我 5 公里内的餐厅”，GiST 或 SP-GiST 才是正解。

2.3 为什么默认是 B-tree

如果你建索引时不指定类型，PostgreSQL 默认给你建 B-tree。这不是偷懒，而是因为 B-tree 的通用性最强。它支持等值查询（=）、范围查询（<、>、BETWEEN）、前缀匹配（LIKE 'abc%'）、排序（ORDER BY），甚至可以用来加速MIN/MAX聚合。

B-tree 的底层是一棵平衡多路搜索树，每个节点存储多个键值和指向子节点的指针。它的“平衡”意味着从根到叶的路径长度基本一致，所以查询复杂度稳定在 O(log n)。你可以把它想象成一个组织得非常好的图书馆书架：每一层都按字母顺序分区，你找任何一本书，走的层数都差不多。

B-tree 也有短板。它对多值类型的支持很弱，比如数组[1,2,3]或 JSONB{"tags": ["a","b"]}，B-tree 只能对整个值做比较，没法高效回答“包含元素 2 的行有哪些”。这就是 GIN 存在的理由。

3. 核心索引类型深度解析与实操要点

3.1 B-tree：最常用但也最容易被滥用

B-tree 的建索引语法最简单：

CREATE INDEX idx_orders_user_id ON orders (user_id);

但“最简单”往往意味着“最容易不加思考”。我见过一个典型反例：某业务表有一个status字段，取值只有 0、1、2 三种，有人给它建了 B-tree 索引，然后发现查询反而变慢了。原因很简单：当某个值占总行数的比例很高时（比如 90% 的行 status=1），优化器会判断走索引再回表的成本比直接全表扫描还高，于是放弃索引。这种情况下索引不仅没用，还白白增加了写入开销。

B-tree 真正发挥威力的场景是高选择性的列。所谓选择性，就是不同值的数量除以总行数。主键的选择性是 1，唯一性最高；性别字段的选择性接近 0，索引价值极低。经验法则是：选择性低于 0.01 的列，建 B-tree 索引要慎重。

另一个实操要点是复合索引的列顺序。假设你经常执行这样的查询：

SELECT * FROM orders WHERE user_id = 123 AND created_at > '2024-01-01';

那么复合索引应该这样建：

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

顺序不能反。因为 B-tree 复合索引遵循“最左前缀”原则：它先按第一列排序，第一列相同再按第二列排序。所以(user_id, created_at)能加速“user_id 等值 + created_at 范围”的查询，但(created_at, user_id)对上述查询的帮助就小很多。

注意：复合索引的列顺序应该按照“等值条件列在前，范围条件列在后”的原则排列。如果查询里全是等值条件，那么选择性最高的列放最前面。

3.2 Hash 索引：专一但局限

Hash 索引在 PostgreSQL 里存在感不高，因为它只支持等值查询，不支持范围、排序、前缀匹配。它的底层就是一个哈希表，通过哈希函数把键值映射到桶里。

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

什么时候用 Hash？当你的列值非常长（比如 URL 或长字符串），B-tree 索引因为要存储完整键值会变得很大，而 Hash 索引只存储哈希值，体积小很多。但代价是你只能做=查询，LIKE、>、<统统用不上。

我的个人建议是：除非你明确知道只需要等值查询且列值很长，否则优先选 B-tree。Hash 索引在 PostgreSQL 10 之后才支持 WAL 日志（意味着崩溃后能恢复），在此之前它甚至不被推荐用于生产环境。现在虽然可用了，但适用面依然很窄。

3.3 GIN：JSONB 和全文检索的幕后功臣

GIN 的全称是 Generalized Inverted Index，翻译过来叫“通用倒排索引”。倒排索引这个概念在搜索引擎里很常见：正排是“文档 → 关键词”，倒排是“关键词 → 文档列表”。GIN 就是把这个思路搬到了数据库里。

假设你有一个products表，里面有个tags数组字段：

CREATE TABLE products ( id serial PRIMARY KEY, name text, tags text[] );

你要查“包含 tag 'sale' 的所有商品”，B-tree 无能为力，但 GIN 可以：

CREATE INDEX idx_products_tags ON products USING GIN (tags); SELECT * FROM products WHERE tags @> ARRAY['sale'];

GIN 对 JSONB 的支持更是刚需。现在很多业务把半结构化数据直接存成 JSONB，比如：

CREATE TABLE events ( id serial PRIMARY KEY, payload jsonb );

你要查payload里type字段等于'click'的记录：

CREATE INDEX idx_events_payload ON events USING GIN (payload); SELECT * FROM events WHERE payload @> '{"type": "click"}';

GIN 的代价是写入慢。因为每次插入或更新，都要为每个元素更新倒排列表。如果一张表写入非常频繁，GIN 索引可能成为瓶颈。PostgreSQL 提供了fastupdate参数来缓解这个问题，它先把新条目放进一个待处理列表，批量合并，但查询时可能需要扫描这个列表，带来额外的查询开销。

实操心得：对于写入频繁的 JSONB 表，可以先用fastupdate = on建 GIN 索引，然后根据查询性能表现决定是否关闭。关闭后写入变慢但查询更稳定。

3.4 GiST 与 SP-GiST：空间数据的左右手

GiST 的全称是 Generalized Search Tree，它不是一个具体的数据结构，而是一个框架，允许你实现自定义的平衡树。PostgreSQL 内置的几何类型、范围类型、全文检索的tsvector都可以用 GiST 索引。

最典型的场景是地理位置查询。假设你有一个places表，存储了经纬度：

CREATE TABLE places ( id serial PRIMARY KEY, name text, location point ); CREATE INDEX idx_places_location ON places USING GIST (location);

查“距离某点 1 度以内的地点”：

SELECT * FROM places WHERE location <@> point(116.4, 39.9) < 1;

GiST 还支持“最近邻”查询，比如找离我最近的 10 个地点：

SELECT * FROM places ORDER BY location <-> point(116.4, 39.9) LIMIT 10;

SP-GiST 则是另一种空间分区树，它适合非平衡的数据分布，比如电话号码区号、IP 地址段。它的优势在于对“空间分区”场景的查询效率更高，但通用性不如 GiST。

注意：GiST 索引的构建时间通常比 B-tree 长，因为它的结构更复杂。在大表上建 GiST 索引时，建议先用CREATE INDEX CONCURRENTLY避免锁表。

3.5 BRIN：大表的轻量级选择

BRIN 的全称是 Block Range Index，它的思路和前面几种完全不同。它不存储每一行的位置，而是把表按物理块分组，每组记录一个“摘要信息”，比如最小值、最大值。

CREATE INDEX idx_logs_created_brin ON logs USING BRIN (created_at);

BRIN 索引非常小，可能只有 B-tree 的千分之一。但它有一个前提：数据在物理上必须大致有序。比如日志表按时间顺序插入，那么 created_at 在物理块上就是递增的。查询“最近一小时”的日志时，BRIN 可以快速排除掉不相关的块。

如果数据物理无序，BRIN 的摘要信息会变得很宽泛，查询时几乎要扫描所有块，效果还不如不建索引。所以 BRIN 适合的是“只追加、按时间递增”的场景，比如监控数据、日志、事件流。

4. 索引选型与实操全流程

4.1 从查询模式反推索引类型

选索引类型的第一步不是看表结构，而是看查询。我通常会让团队先把慢查询日志打开，收集一周的慢 SQL，然后按查询模式分类：

• 等值查询为主，列选择性高 → B-tree
• 等值查询，列值超长 → Hash（谨慎）
• 数组/JSONB 包含查询 → GIN
• 地理位置/范围重叠 → GiST
• 物理有序大表的时间范围查询 → BRIN

这个映射关系不是绝对的，但能覆盖 80% 的场景。剩下的 20% 需要结合数据分布和写入频率来权衡。

4.2 建索引的完整操作流程

假设我们有一张orders表，数据量 5000 万行，经常执行以下查询：

SELECT * FROM orders WHERE user_id = 12345 AND status = 'paid' AND created_at > '2024-01-01';

第一步，分析选择性：

SELECT count(DISTINCT user_id)::float / count(*) AS user_selectivity, count(DISTINCT status)::float / count(*) AS status_selectivity FROM orders;

假设 user_id 选择性是 0.8，status 选择性是 0.001。那么 user_id 应该放在复合索引的第一位。

第二步，建索引：

CREATE INDEX CONCURRENTLY idx_orders_user_status_created ON orders (user_id, status, created_at);

用CONCURRENTLY是为了避免锁表，代价是建索引时间更长，且如果中途失败会留下无效索引需要清理。

第三步，验证索引是否被使用：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 12345 AND status = 'paid' AND created_at > '2024-01-01';

看输出里有没有Index Scan或Bitmap Index Scan，以及actual time是否明显下降。

第四步，检查索引大小和维护成本：

SELECT indexrelname AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes WHERE relname = 'orders';

如果索引大小接近甚至超过表本身，就要重新审视是否值得。

4.3 参数计算与选择依据

复合索引的列顺序不是拍脑袋决定的。除了选择性，还要考虑查询里条件的类型。等值条件（=）应该放在范围条件（>、<、BETWEEN）前面。因为 B-tree 在遇到范围条件后，后续列的有序性就无法用于快速定位了。

假设查询是：

WHERE user_id = 123 AND created_at > '2024-01-01' AND status = 'paid'

那么索引(user_id, status, created_at)比(user_id, created_at, status)更好。因为 user_id 和 status 都是等值，可以精确定位到索引的一个点，然后在这个点之后按 created_at 顺序扫描范围。

如果查询里没有等值条件，全是范围条件，比如：

WHERE created_at > '2024-01-01' AND amount > 100

那么复合索引的帮助有限，因为两个范围条件无法同时利用 B-tree 的有序性。这种情况下，可以考虑分别建两个单列索引，让优化器做 Bitmap AND。

5. 常见问题与排查技巧实录

5.1 为什么建了索引还是慢

这是最高频的问题。可能的原因有：

我遇到过一个案例：某表建了索引，但查询始终走全表扫描。后来发现是统计信息太旧，优化器以为表里只有几百行，觉得全表扫描更快。执行ANALYZE后问题消失。

5.2 索引膨胀怎么处理

PostgreSQL 的 MVCC 机制会导致更新和删除操作留下“死元组”，索引里也会留下对应的死条目。时间长了索引会膨胀，查询效率下降。

排查方法：

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC;

如果索引大小远大于预期，可以考虑重建：

REINDEX INDEX CONCURRENTLY idx_orders_user_status_created;

CONCURRENTLY同样是为了避免锁表。重建后索引会紧凑很多。

5.3 GIN 索引的写入瓶颈

GIN 索引在写入频繁的表上可能成为瓶颈。我实测过一个场景：每秒 5000 次插入的 JSONB 表，建了 GIN 索引后插入延迟从 2ms 涨到 15ms。解决方案有两个：一是开启fastupdate，二是把 GIN 索引建在从库上，主库只负责写入。

开启fastupdate：

ALTER INDEX idx_events_payload SET (fastupdate = on);

但要注意，fastupdate会让查询时多扫描一个待处理列表，查询延迟可能增加。所以这是一个“写入换查询”的权衡。

5.4 BRIN 索引不生效的排查

BRIN 索引不生效通常是因为数据物理无序。排查方法：

SELECT correlation FROM pg_stats WHERE tablename = 'logs' AND attname = 'created_at';

correlation接近 1 表示物理有序，接近 0 表示无序。如果 correlation 很低，BRIN 基本没用，应该考虑 B-tree 或者调整数据写入方式。

6. 几个容易被忽略的索引细节

6.1 部分索引：只索引你需要的行

如果你的查询总是带一个固定条件，比如WHERE status = 'active'，那么可以建部分索引：

CREATE INDEX idx_orders_active_user ON orders (user_id) WHERE status = 'active';

这个索引只包含 active 的行，体积更小，维护成本更低。但前提是查询必须带上status = 'active'这个条件，否则优化器不会用这个索引。

6.2 表达式索引：让函数查询也能走索引

如果你经常执行WHERE lower(email) = 'abc@example.com'，普通索引没用，因为索引存的是原始 email 值。这时候需要表达式索引：

CREATE INDEX idx_users_lower_email ON users (lower(email));

这样查询WHERE lower(email) = 'abc@example.com'就能走索引了。但注意，查询里的表达式必须和索引定义完全一致，否则不生效。

6.3 覆盖索引：避免回表

PostgreSQL 支持INCLUDE子句，可以把非键列也放进索引里：

CREATE INDEX idx_orders_user_include ON orders (user_id) INCLUDE (status, amount);

这样查询SELECT status, amount FROM orders WHERE user_id = 123时，如果索引里已经包含了 status 和 amount，就不需要回表了。代价是索引变大，写入变慢。适合“读多写少”且查询列固定的场景。

7. 我在实际项目中的几点体会

索引这件事，说到底是一个“用空间和写入换读取”的交易。没有银弹，只有权衡。我自己的习惯是：新表上线前先不建多余索引，等慢查询日志出来再针对性加。每加一个索引，都要问三个问题：这个查询的频率有多高？不加索引的代价有多大？加了之后写入会慢多少？

另外，EXPLAIN ANALYZE是我用得最多的工具，没有之一。它不会骗人，执行时间、扫描行数、缓冲区命中率都摆在那里。比起凭感觉猜，看执行计划要靠谱得多。

还有一个容易被忽略的点：索引的维护和监控。建了索引不代表一劳永逸，随着数据增长和分布变化，原本有效的索引可能变得无效。定期跑pg_stat_user_indexes看看哪些索引从未被使用过，该删就删。我见过一张表上建了 12 个索引，其中 7 个的idx_scan是 0，纯属浪费。

最后分享一个小技巧：如果你不确定某个索引是否有效，可以先在测试环境用hypopg扩展模拟索引，看优化器是否会选择它，再决定是否在生产环境真正创建。这个扩展在 PostgreSQL 里很好用，能省下不少反复建删索引的时间。