PostgreSQL 索引调优完全指南:从一次"加了一堆索引反而更慢"看懂为什么 B-tree 不是万能的

2023 年我负责一个内部数据平台底层用 PostgreSQL 库里几张核心表都过亿行业务跑了半年一切都还稳直到运营某天上线一个新功能要按"用户 + 时间段 + 状态"组合查订单单次查询时间从 200 毫秒涨到了 12 秒慢查询日志一片红我盯着那几条 SQL 心里很笃定加个索引就完事了于是我对着 where 条件几乎每个字段都建了一个 B-tree 索引以为这下稳了可等真上线一串问题冒了出来第一种最先把我打懵索引建完一部分查询确实快了可另一部分查询反而更慢了我去 EXPLAIN 一看居然走的是 Seq Scan 我建的索引被规划器无视了第二种最难缠某天凌晨写入接口突然慢到 800 毫秒一笔我去查发现是因为索引建多了每次 INSERT 都要同步维护一堆 B-tree write amplification 几乎翻倍第三种最离谱我建了一个 (user_id, created_at, status) 的复合索引以为三个字段查询都能用结果只查 status 时根本没走第四种最莫名其妙我对一张 1 亿行表执行 CREATE INDEX 直接锁表 6 小时整个业务挂了 6 小时我才知道生产建索引必须 CONCURRENTLY我盯着这一连串问题想了很久才彻底想明白第一版错在一个根本的认知上我以为"加索引就能让查询快"可这个认知是错的索引不是免费的它会让写变慢会占空间会被规划器无视会因为字段顺序错而失效会因为统计信息不准而走错路真正的索引调优是一个"读写权衡 + 字段顺序 + 索引类型 + 规划器配合"的系统工程本文从头梳理 EXPLAIN ANALYZE 怎么看 B-tree 复合索引的字段顺序为什么决定一切部分索引和表达式索引怎么用 BRIN GIN GiST 什么时候用统计信息怎么影响规划器选择 CONCURRENTLY 建索引的安全做法以及一些把 PostgreSQL 索引做扎实要避开的工程坑

问题背景

很多团队对索引的认知停留在"慢查询加索引"这一句话上,这套认知在小表上没什么问题,放到上亿行的生产表上就处处是坑。索引不是"加上就好",它本质上是一种用磁盘空间和写入开销换查询速度的数据结构,每一种类型有它最擅长的查询模式,选错了不仅没用,还会拖累整张表。常见的几类"加完索引反而出事"现象:

• 索引没生效:EXPLAIN 一看走的还是 Seq Scan,索引白建,可能是字段顺序错、统计信息过期、或类型不匹配导致隐式转换。
• 写入变慢:每张表建了七八个索引,INSERT/UPDATE 都要同步维护,写吞吐砍半。
• 索引膨胀:大量 UPDATE 导致索引页变得稀疏,体积比表本身还大,缓存效率反而下降。
• 建索引锁表:大表上直接 CREATE INDEX 会持有 ShareLock 阻塞所有写,几小时不动业务全挂。
• 规划器选错:有索引但规划器估错行数,改走全表扫描或 Nested Loop,执行时间几十倍。

一、EXPLAIN ANALYZE:别凭感觉调,看执行计划说话

调索引的第一步永远是 EXPLAIN ANALYZE,而不是"加上一个看看"。EXPLAIN 给你的是规划器估算的执行计划,ANALYZE 会真正跑一遍把每一步的实际行数和时间也带出来。两个数字对比着看,你才能判断规划器估得准不准。

-- 一个典型的查询
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, user_id, amount, created_at
FROM orders
WHERE user_id = 12345
  AND status = 'paid'
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

-- 输出示例(精简版)
-- Limit  (cost=0.56..123.45 rows=20 width=48)
--        (actual time=0.123..2.456 rows=20 loops=1)
--   Buffers: shared hit=42 read=3
--   ->  Index Scan Backward using idx_orders_user_created on orders
--         (cost=0.56..15234.12 rows=2456 width=48)
--         (actual time=0.121..2.430 rows=20 loops=1)
--       Index Cond: (user_id = 12345 AND created_at >= '2024-01-01')
--       Filter: (status = 'paid')
--       Rows Removed by Filter: 8
--       Buffers: shared hit=42 read=3
-- Planning Time: 0.456 ms
-- Execution Time: 2.512 ms

看 EXPLAIN 输出有几个关键点。第一,看 cost 和 actual time 的差距,差太大说明规划器估算不准,可能要 ANALYZE 更新统计信息。第二,看 rows 估算和实际行数的比值,差一个数量级以上规划器就会选错路。第三,看 Buffers 里 hit 和 read 的比例,read 多说明索引没在缓存里,会有磁盘 IO。第四,看 Rows Removed by Filter,这是索引扫到但 status 不匹配被丢弃的行,数量大说明 status 也该进索引。

三种最常见的 Scan 类型要分清:Seq Scan 是全表扫描,适合小表或返回大部分数据时;Index Scan 是走索引取数据,适合返回少量行;Index Only Scan 是只读索引不回表,前提是 select 字段都在索引里且行的 visibility map 干净。如果你的查询走了 Seq Scan 但你期待走索引,八成是规划器估算返回的行数太多(超过表的某个比例,默认是 25% 左右),它认为全表顺序读比随机索引读更便宜。

认知翻转:调索引不是"凭印象建几个看看"的事,是"看 EXPLAIN ANALYZE 输出有依据地建"的事。每次建索引前先 EXPLAIN 看现在走的什么计划,建完再 EXPLAIN 看变成了什么计划,两次对比才能确认效果。生产上发现慢查询时,养成"先 EXPLAIN 再说"的习惯,而不是直接"加一个索引上线",后者九成会越加越乱。索引调优本质上是一种"规划器协作",你要让规划器愿意选你建的索引,而不是单方面建好就完事。

二、B-tree 复合索引:字段顺序决定能不能用

PostgreSQL 默认的索引类型是 B-tree,90% 的业务场景都是用它。但 B-tree 复合索引有一个反直觉的规则:字段顺序决定能不能命中。索引 (a, b, c) 能加速的查询条件必须从最左字段开始连续匹配——只查 a 能用,查 a + b 能用,查 a + b + c 能用,但只查 b 或只查 c 或查 b + c 都用不上。这就是所谓的"最左前缀原则",MySQL 用过的人都熟悉,PostgreSQL 一模一样。

复合索引 (user_id, status, created_at) 的命中情况

WHERE user_id = ?                              ✓ 命中 (只用最左 1 列)
WHERE user_id = ? AND status = ?               ✓ 命中 (前 2 列)
WHERE user_id = ? AND status = ? AND created_at > ?  ✓ 命中 (前 3 列)
WHERE user_id = ? AND created_at > ?           △ 部分命中 (user_id 走索引,
                                                  created_at 在索引内 Filter)
WHERE status = ? AND created_at > ?            ✗ 不命中 (跳过了 user_id)
WHERE created_at > ?                           ✗ 不命中 (跳过了前 2 列)
WHERE user_id = ? ORDER BY created_at DESC     ✓ 命中且有序 (避免额外排序)

关键启示:
1. 等值条件字段排前面,范围条件字段排后面(因为范围条件之后的列在索引内会失序)
2. 高选择性字段排前面(基数大的)
3. ORDER BY 字段排索引末尾可以避免 sort 步骤
4. 一张表别建超过 5-7 个 B-tree 索引,写入开销会爆炸

这套规则的物理原因是 B-tree 是按字段值排序存储的,(a, b, c) 索引的物理顺序是先按 a 排,a 相同的按 b 排,a + b 相同的按 c 排。所以查 a 时能直接定位,查 b 时索引里的 b 是散乱的,完全没法用。理解了物理结构,字段顺序怎么排就不需要记规则了——直接想"按这个顺序排序后,我要的数据是不是连续聚集的"。

真实业务里有几个常见的失误:第一,把 status 这种基数极低的字段(只有几个值)放在复合索引最前面,大部分查询命中的索引段都很宽,基本等于没建。第二,把 created_at 这种范围查询字段放在中间,后面的字段彻底失效。第三,为了"省事"建一堆单字段索引,以为"能组合使用",实际上 PostgreSQL 的 Bitmap Index Scan 虽然能组合多个单列索引,但效率远不如一个设计好的复合索引。

下面是一个常见订单表的索引设计示例,体现"等值优先 + 范围在后 + ORDER BY 末尾"原则:

-- 业务最高频的查询是:
--   按用户查最近订单, 按状态过滤, 按时间倒序
-- 推荐索引:
CREATE INDEX CONCURRENTLY idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

-- 业务次高频:运营按状态查最近订单
-- 推荐索引(status 基数虽低,但叠加 created_at 后选择性可接受):
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at DESC)
WHERE status IN ('paid', 'refunding');   -- 进一步窄化,部分索引

-- 反例:每个字段一个索引(看似全覆盖,实际全是浪费)
-- CREATE INDEX idx_orders_user ON orders (user_id);
-- CREATE INDEX idx_orders_status ON orders (status);
-- CREATE INDEX idx_orders_created ON orders (created_at);
-- 这种建法:占空间、写慢、规划器还得猜怎么 Bitmap And,不如一个复合好

认知翻转:复合索引的设计核心不是"加上所有字段",而是"按查询模式排好字段顺序"。一张表最常被查的几种 where 模式抽出来,按"等值在前、范围在后、ORDER BY 在末"的原则设计两三个复合索引,远胜于"每个字段一个单列索引"。一个良好的索引设计能让一张大表的 99% 查询都走 Index Scan 而不是 Seq Scan,而错误的索引设计会让你建了 10 个索引却没解决任何问题,只是把写入拖垮了。

三、部分索引和表达式索引:你可能没用过但应该用

PostgreSQL 比 MySQL 强的一个地方是支持部分索引(partial index)和表达式索引(expression index),这两种索引在某些场景下比普通 B-tree 高效得多。可惜大多数团队没用过,因为教程里很少讲。

部分索引就是只对表里满足某个条件的子集建索引。最经典的例子是订单状态:一张订单表里 99% 的订单是 status='completed' 的历史数据,运营和客服日常关心的只有 status IN ('paid', 'refunding', 'shipping') 那 1% 的活跃订单。如果你对 status 字段建全表索引,索引里 99% 的条目是"completed",占空间又没用。改成部分索引只索引活跃状态:

-- 部分索引:只索引活跃订单
CREATE INDEX CONCURRENTLY idx_orders_active
ON orders (created_at DESC, user_id)
WHERE status IN ('paid', 'refunding', 'shipping');

-- 全量索引(对比)
-- CREATE INDEX idx_orders_full ON orders (status, created_at DESC, user_id);

-- 实际效果(在 1 亿行 / 100 万活跃订单的表上)
-- idx_orders_active 体积约 50 MB    (只索引 100 万行)
-- idx_orders_full   体积约 4 GB     (索引全部 1 亿行)
-- 查活跃订单时两者性能相当,但部分索引省了 80 倍空间和写入开销

部分索引的额外好处是查询规划器会自动识别——当你的 WHERE 包含 status IN ('paid', 'refunding', 'shipping') 时,规划器知道这个查询完全包含在部分索引的覆盖范围内,会直接用它。如果你写的 WHERE 是 status = 'completed',规划器知道这个查询不在索引覆盖范围,会走全表扫描——这正是你想要的(对历史数据用其他方式归档查询)。

表达式索引是对计算结果建索引,而不是对原始字段。常见场景是大小写不敏感查询、日期截断、JSON 字段:

-- 场景 1:邮箱大小写不敏感登录
CREATE INDEX CONCURRENTLY idx_users_email_lower
ON users (LOWER(email));
-- 配套查询(必须用同样的表达式才能命中):
SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Example.com');

-- 场景 2:按天聚合统计
CREATE INDEX CONCURRENTLY idx_orders_day
ON orders (DATE(created_at));
SELECT DATE(created_at), COUNT(*) FROM orders
WHERE DATE(created_at) BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY DATE(created_at);

-- 场景 3:JSONB 字段里的某个 key
CREATE INDEX CONCURRENTLY idx_events_user_id
ON events ((payload->>'user_id'));
SELECT * FROM events WHERE payload->>'user_id' = '12345';

-- 场景 4:全文搜索辅助
CREATE INDEX CONCURRENTLY idx_articles_title_trgm
ON articles USING gin (title gin_trgm_ops);
SELECT * FROM articles WHERE title ILIKE '%PostgreSQL%';

表达式索引的坑是"表达式必须一字不差"才能命中。LOWER(email) 索引,你查 WHERE email = LOWER('x') 是不会命中的,必须 WHERE LOWER(email) = 'x'。生产里这种"以为加了索引就行,查询里没用表达式"的失误很常见,EXPLAIN 一看走的 Seq Scan 才发现。

认知翻转:部分索引和表达式索引是 PostgreSQL 给你的"精细工具",它们能让你的索引贴着实际查询模式建,体积小、写入快、命中率高。它们的代价是配套查询必须严格匹配条件或表达式,所以使用前要先盘清楚业务的查询模式,把它和索引定义"绑死"。不肯用这两个工具的团队,会用 B-tree 全量索引硬扛所有场景,结果索引膨胀到几个 GB,缓存命中率掉到 30%,数据库 IO 焦头烂额——而真正需要的可能只是几十 MB 的部分索引。

四、索引类型大全:B-tree 之外的选择

PostgreSQL 内置了多种索引类型,各擅其长。大多数业务场景 B-tree 够用,但有些场景换索引类型能让性能提升一个数量级:

[mermaid]
flowchart TD
A[选索引类型] --> B{查询类型}
B -->|等值与范围查询| C[B-tree 默认]
B -->|全文搜索 数组 JSONB 包含| D[GIN 倒排索引]
B -->|几何 全文搜索 模糊匹配| E[GiST 通用]
B -->|高基数列 顺序写入| F[BRIN 块范围]
B -->|去重判断 UNIQUE 约束| C
B -->|哈希等值唯一| G[Hash 仅等值]
C --> H[一般业务首选]
D --> I[文章标签 JSONB 查询]
E --> J[geo 范围 trigram 相似]
F --> K[日志表 时序表 体积极小]
G --> L[很少用 大多用 B-tree 等值]

BRIN(Block Range INdex)是被低估最严重的索引类型。它的工作方式是把表分成连续的"块范围",每个范围只存最小值和最大值。优点是体积极小(几 MB 索引能覆盖几 GB 表),缺点是只有当数据在物理上按索引列顺序排列时才有效——典型场景就是按时间顺序追加的日志表、订单表、事件流。

-- 1 亿行的日志表,按 created_at 顺序追加
CREATE INDEX idx_logs_created_brin ON logs
USING brin (created_at) WITH (pages_per_range = 32);

-- 同样字段的 B-tree
CREATE INDEX idx_logs_created_btree ON logs (created_at);

-- 体积对比(实测):
-- brin   :  ~ 4 MB     (惊人地小)
-- btree  :  ~ 3.2 GB   (按行计算,接近表 1/3 大)

-- 查询性能(WHERE created_at BETWEEN ... 查 1 天范围):
-- brin   :  ~ 150 ms   (扫到候选块再 Filter)
-- btree  :  ~ 40 ms    (直接定位)

-- 结论:写多读少 + 按时间顺序的大表强烈推荐 BRIN
-- 4 MB vs 3.2 GB 的空间节约换 100 ms 的查询差,几乎所有日志/审计场景都值

GIN(Generalized Inverted Index)是处理"多值字段"的神器,典型场景是数组、JSONB、全文搜索:

-- 数组字段:用户的兴趣标签
CREATE INDEX idx_users_tags ON users USING gin (tags);
SELECT * FROM users WHERE tags @> ARRAY['photography', 'travel'];

-- JSONB 字段:整个 JSONB 内部的 key/value 查询
CREATE INDEX idx_events_payload ON events USING gin (payload);
SELECT * FROM events WHERE payload @> '{"action": "purchase"}';

-- JSONB 路径索引:只对特定 key 建,体积更小
CREATE INDEX idx_events_payload_action ON events
USING gin ((payload->'action'));

-- 全文搜索
CREATE INDEX idx_articles_search ON articles
USING gin (to_tsvector('chinese', title || ' ' || body));
SELECT * FROM articles
WHERE to_tsvector('chinese', title || ' ' || body)
      @@ to_tsquery('chinese', 'PostgreSQL & 索引');

Hash 索引在 PostgreSQL 10 之后才被列入正式支持(之前是不被推荐的),它只能做等值查询,不能 ORDER BY 不能范围。理论上等值查询比 B-tree 快一点,但实际优势很小,几乎所有场景都被 B-tree 替代了。真正用 Hash 的场景非常少,知道有这东西就行。

认知翻转:索引类型不是"B-tree 解决一切",选对类型能让性能提升一个数量级且空间省一两个数量级。看到大表第一反应别只想 B-tree——日志/审计/时序表先想 BRIN,数组/JSONB/全文搜索先想 GIN,几何/模糊匹配先想 GiST。每种类型的物理结构决定了它的最佳场景,了解清楚再选,远胜于"反正不知道就 B-tree"。

五、统计信息与规划器:为什么"明明有索引却走全表"

调索引的"看似无解"问题中,最常见的就是"我明明建了索引,EXPLAIN 一看走的还是 Seq Scan"。这种现象八成不是索引建错了,而是统计信息让规划器估错了行数。

规划器决定走索引还是全表扫描时,核心依据是"这个 WHERE 条件预计返回多少行"。如果它估算返回的行数超过表总行数的某个比例(默认 25% 左右),它认为"反正要读这么多,顺序扫盘比随机索引读还便宜",就走 Seq Scan。这个估算靠的是 pg_statistic 系统表里维护的列直方图和最常见值(MCV)。统计信息不准,估算就不准,规划器就会选错。

统计信息的几个常见失准来源:第一,大量 INSERT/UPDATE 后没有 autovacuum 跑过 ANALYZE,统计还是几天前的。第二,默认采样数(default_statistics_target=100)对大表来说太小,某些值的分布完全没采到。第三,字段之间的相关性没有统计(PostgreSQL 默认假设字段间独立),实际相关的字段组合估算会大幅偏差。

-- 看某张表当前的统计采样情况
SELECT
  schemaname, tablename, attname,
  n_distinct,       -- 估算的不同值数量
  most_common_vals, -- 最常见的值
  null_frac         -- NULL 比例
FROM pg_stats
WHERE tablename = 'orders'
ORDER BY attname;

-- 手动触发 ANALYZE(平时 autovacuum 会自动跑,但慢)
ANALYZE orders;

-- 大表提高采样精度
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- 字段间相关性统计(PG 10+):
-- 比如 city 和 country 强相关,知道 city 就知道 country
CREATE STATISTICS s_geo (dependencies, ndistinct)
ON city, country FROM addresses;
ANALYZE addresses;

-- 强制规划器走索引(调试用,生产不要长期用)
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT ... ;
-- 看看强制走索引后真实的执行时间
-- 如果比 Seq Scan 还慢,说明规划器选对了
-- 如果明显更快,说明统计信息有问题,要去 ANALYZE 或调 SET STATISTICS
SET enable_seqscan = ON;

规划器的另一个常见误判来自 LIMIT 优化。规划器看到 LIMIT 20 时会假设"找到 20 行就停,所以走索引一行行扫到 20 就完事了"。但如果你的 WHERE 选择性很低(比如 status = 'shipping' 实际命中 99% 的行),它估算"找 20 行很快",但实际可能要扫完整张表才发现 20 行不够。这种 case 的特征是 EXPLAIN ANALYZE 里 actual rows 远大于 estimate rows。解决办法通常是建一个更准的部分索引或调整 STATISTICS 采样。

认知翻转:索引能否生效不是"索引建了就行",而是"规划器认不认你这个索引"。规划器认不认取决于统计信息准不准,统计信息准不准取决于 ANALYZE 跑没跑、采样数大不大、有没有字段相关性统计。生产里大表索引调优的最大杠杆往往不是"再加一个索引",而是"调高统计采样 + 强制 ANALYZE + 建多列相关性统计"。这些操作不写一行代码,纯靠 SQL 命令,但能让你已有的索引重新被规划器选中,效果立竿见影。

六、CONCURRENTLY 建索引与生产维护安全

生产环境对大表 CREATE INDEX 是一个高危操作。默认的 CREATE INDEX 会持有 ShareLock,阻塞所有 INSERT/UPDATE/DELETE 直到索引建完——对 1 亿行的表来说可能要几小时,业务期间这等于停服。PostgreSQL 提供了 CREATE INDEX CONCURRENTLY 来解决这个问题,但它有自己的一套规则要遵守。

-- 生产强烈推荐:并发建索引,不阻塞读写
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders (user_id, status);

-- 注意事项:
-- 1. CONCURRENTLY 不能放在事务块内 (BEGIN/COMMIT 不可用)
-- 2. 建索引时间是普通 CREATE 的 2-3 倍 (要扫表两次保证一致性)
-- 3. 如果中途失败,会留下一个 INVALID 索引,要手动 DROP
-- 4. 整个过程会消耗较多 IO,建议在业务低峰

-- 检查是否有失败留下的 INVALID 索引
SELECT schemaname, indexrelname, indisvalid
FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid
WHERE NOT i.indisvalid;

-- 重命名索引(无锁)
ALTER INDEX idx_old RENAME TO idx_new;

-- 删除索引也要 CONCURRENTLY,否则会持有 AccessExclusiveLock
DROP INDEX CONCURRENTLY idx_orders_unused;

-- REINDEX 在 PG 12+ 支持 CONCURRENTLY,修复索引膨胀
REINDEX INDEX CONCURRENTLY idx_orders_user_status;

生产中除了 CONCURRENTLY,还有几个常用的"安全索引运维"动作。第一,定期检查未使用的索引——pg_stat_user_indexes.idx_scan = 0 说明这个索引半年没被用过,要么是查询写错了没命中,要么这个索引根本没用,后者应该删除以节省空间和写入开销。第二,定期检查索引膨胀率,膨胀超过 30% 的索引建议 REINDEX 重建。第三,大批量 INSERT 之前可以临时 DROP 非必要索引,导入完再 CREATE CONCURRENTLY,这样总耗时通常比"带着索引边写边维护"短很多。

下面是一段常用的索引健康巡检 SQL,生产里推荐每周跑一次,把结果发到运维群:

-- 1. 找出从未被使用过的索引(可考虑删除)
SELECT
  schemaname, relname AS table, indexrelname AS index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan AS times_used
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisunique         -- 排除唯一索引(可能用于约束)
  AND NOT indisprimary        -- 排除主键
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- 2. 找出体积最大的索引(关注是否需要拆分或换类型)
SELECT
  schemaname, tablename, indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(indexname::regclass) DESC
LIMIT 20;

-- 3. 找出写入热表(写多的表上索引数量要严格控制)
SELECT
  schemaname, relname,
  n_tup_ins + n_tup_upd + n_tup_del AS total_writes,
  (SELECT COUNT(*) FROM pg_indexes
   WHERE tablename = relname) AS index_count
FROM pg_stat_user_tables
ORDER BY total_writes DESC
LIMIT 20;

-- 4. 找出 Seq Scan 次数多但有索引的表(可能索引设计有问题)
SELECT
  schemaname, relname,
  seq_scan, seq_tup_read,
  idx_scan, idx_tup_fetch,
  ROUND(100.0 * seq_scan / NULLIF(seq_scan + idx_scan, 0), 2) AS seq_pct
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 1000
ORDER BY seq_scan DESC
LIMIT 20;

认知翻转:索引不是"建完就一劳永逸"的对象,它需要持续运维。生产里要把"索引健康巡检"当成跟备份、监控、慢查询日志同等重要的固定任务。每周跑一次上面那种巡检 SQL,删掉不用的、重建膨胀的、警惕写入热表上索引过多的,几个月下来数据库性能会有质的差别。最常见的现象是:运行多年的库里往往堆着十几个"以前某个开发加的没人删"的死索引,清理一遍空间能省好几个 GB,写入性能能涨 30%——而这些只需要执行几条 DROP INDEX CONCURRENTLY。

关键概念速查

避坑清单

1. 不要凭感觉加索引,每次改之前先 EXPLAIN ANALYZE 看现在走的什么计划,改完再 EXPLAIN 对比效果。
2. 不要建"每个字段一个单列索引",改用 2-3 个精心设计的复合索引,字段顺序按"等值在前、范围在后、ORDER BY 末尾"。
3. 不要忽视部分索引,状态类、活跃数据类字段强烈推荐,体积小一两个数量级、写入开销几乎为零。
4. 不要在不使用表达式的查询上建表达式索引,表达式必须一字不差才能命中。
5. 不要在所有大表上无脑用 B-tree,时序日志类首选 BRIN,数组 JSONB 全文搜索首选 GIN,选错类型空间和性能都浪费。
6. 不要忘了 ANALYZE,规划器估错行数就会选错路,大表 default_statistics_target 一定要调高。
7. 不要在生产用 CREATE INDEX 而不是 CREATE INDEX CONCURRENTLY,前者会锁表几小时,业务直接停摆。
8. 不要让索引"自生自灭",每周跑一次巡检 SQL,删掉从未使用的索引,REINDEX 膨胀超过 30% 的索引。
9. 不要在写入热表上建超过 5-7 个索引,每个索引都会让 INSERT/UPDATE 同步维护,写入吞吐被砍。
10. 不要相信 LIMIT 优化"一定快",规划器对 LIMIT 的估算容易错,慢查询里很多就是 LIMIT 估错的锅。

总结

PostgreSQL 索引调优是数据库工程里"看似入门简单、实际深不见底"的那一类技能。简单是因为 CREATE INDEX 一行命令谁都会写,深是因为同一个 CREATE INDEX 命令背后是一连串"是否命中、是否被规划器选中、是否拖垮写入、是否锁表、是否随时间膨胀"的隐藏问题。一份生产数据库的索引方案是否"扎实",不取决于建了多少个索引,取决于"每一个索引都有人能解释为什么建,每个没建的索引都有人想过为什么不建"。

另一层被低估的是,索引调优是一个"系统视角"问题而不是"单点优化"问题。新手会盯着一条慢 SQL 加索引把它调快,老手会看整张表的全部查询模式综合设计两三个索引让所有查询都能受益,专家会同时考虑写入吞吐、缓存命中、磁盘占用、规划器统计、运维成本等多个维度做整体取舍。这种"系统视角"是写好索引方案的核心技能,它比记住每种索引类型的语法重要得多。

打个不太严谨的比方,做索引调优有点像给一座图书馆设计分类系统。你可以按书名首字母排(一种索引)、按学科分(另一种)、按出版年代分(再一种)。没有任何一种分类能让所有读者所有需求都最快——按书名查最快的分类对"找 2020 年的物理学书"反而最慢。一个好图书馆会同时维护两三种分类目录,让每种最常见的查找方式都有对应的目录可用,但绝不会"每本书的每种属性都建一个目录",因为目录本身要占空间也要维护。索引也是一样:为最常见的查询模式服务,不为想象中的所有查询都准备,这才是工程平衡。

所以做 PostgreSQL 索引,本地几千行的测试数据永远暴露不了真正的问题。它暴露不了亿行表上字段顺序错带来的全表扫描,暴露不了几十个无用索引拖垮 INSERT 的写入瓶颈,暴露不了 ANALYZE 没跑导致规划器选错路的疑似 bug,暴露不了大表 CREATE INDEX 锁表 6 小时的运维事故,更暴露不了索引膨胀几个 GB 后缓存命中率断崖式下降。真正的检验在生产环境,在一次大促前夜的慢查询风暴里,在一次清库后突然 30% 写入变慢的事故里,在一次新业务上线时规划器突然集体走 Seq Scan 的早晨。把上面六节里的功夫提前做扎实,等那些时刻到来时,你会感谢自己当初没图省事。如果你正在维护一套 PostgreSQL 库,不妨找一段空闲时间把你的索引按这套标准盘一遍——尤其是那张"几年没人动过"的核心大表,你大概率会找到至少五处可以变得更扎实的地方,而这是收益极高、风险极低的投资。

—— 别看了 · 2026