PostgreSQL 索引与执行计划工程化完全指南:从一次"5 亿行订单表查询走全表扫 8 秒不出"看懂为什么加 B-tree 远远不够

2022 年我们公司有一套核心的订单库跑在 PostgreSQL 上数据量大概 5 亿行单库 30 多张表上线两年一直挺稳定。直到某天产品突然要做一个 卖家后台 的查询页面 要支持按订单状态时间范围金额范围客户名称等多个条件组合查询。我心想 PostgreSQL 嘛 加几个索引就完事 顺手在 status time amount 上各加了个 B-tree 索引部署上线。第二天卖家中心的接口接连超时业务那边把电话打到我桌上 我打开数据库一看 一个查询跑了 8 秒 explain analyze 显示走了全表扫描 我加的索引完全没用上。第二种最难缠 我以为是统计信息没更新 ANALYZE 跑了一遍 还是没用 后来才意识到是查询条件里有一个 LIKE %xxx% 的模糊匹配 B-tree 索引天生帮不上忙。第三种最离谱 我加了一个 GIN 索引终于走对了 但维护成本很高 INSERT 速度掉了 40% 业务那边的写性能告警立刻响了。第四种最莫名其妙 同一个 SQL 在测试环境秒级返回 上了生产 30 秒不出 我看 explain 发现选了完全不同的执行计划 后来才发现是测试环境的数据分布跟生产差太多。第五种最致命 某个夜间报表跑了 6 小时把库压到了 IO 饱和 影响白天业务 排查发现是一个 6 表联接的 SQL 选了错误的 hash join 顺序 我才意识到 PostgreSQL 的执行计划在数据量大时是真的会选错。我盯着这一连串问题想了很久才彻底想明白第一版错在一个根本的认知上我以为 PostgreSQL 的索引就是 给字段建个 B-tree 然后查询就会用到 可这个认知是错的真正能扛业务的 PostgreSQL 索引体系 是一个索引选型 加 统计信息 加 执行计划诊断 加 部分索引 加 联合索引顺序 加 查询改写 的整套工程方法论 任何一环不到位都会被某个查询慢死本文从头梳理 PostgreSQL 的几种索引类型 B-tree GIN GiST BRIN Hash 各自的适用场景 explain analyze 怎么看出执行计划的真正问题 联合索引顺序怎么决定 部分索引和表达式索引能解决什么 以及一些把 PostgreSQL 性能调优做扎实要避开的工程坑

问题背景:为什么 PostgreSQL 索引比想象中难

很多人对索引的认识停留在 给字段加 B-tree 查询就快了 但生产里你会发现 同一个表 同样的查询 加和不加索引可能差几千倍 加错索引可能比不加还慢 加了对的索引但 PostgreSQL 不用它。问题的根源在于:

• PostgreSQL 索引类型多样 选错就翻车:B-tree 适合范围查询 GIN 适合多值字段全文搜索 GiST 适合几何空间 BRIN 适合时间序列 选错索引等于没建。
• 优化器靠统计信息决定计划:统计信息陈旧或数据倾斜 优化器就会选错计划 比如该走索引却选了全表扫描。
• 联合索引的列顺序影响巨大:同样三列 a b c 索引 (a,b,c) 和 (b,a,c) 在不同查询上效果可能差几百倍。
• 索引不是越多越好:每加一个索引 写操作都要多维护一份 高写入表索引太多会拖垮性能。
• 覆盖索引 (covering index) 能避免回表:加 INCLUDE 字段或者让索引包含所有查询字段 可以直接 index only scan 性能提升一个数量级。
• 部分索引 (partial index) 和表达式索引常被忽视:加 WHERE 条件的索引比全表索引小得多 表达式索引能加速函数转换后的查询 这些都是 PostgreSQL 独有的利器。

一 索引类型选型:B-tree GIN GiST BRIN Hash 各自的边界

PostgreSQL 提供了多种索引类型 每种都有自己的强项和弱项。理解它们的边界是选型的起点。

-- B-tree 索引最常用的一种 适合等值范围排序查询
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 联合索引列顺序非常重要 高基数列在前 等值在前范围在后
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- 覆盖索引 INCLUDE 子句把查询字段加进索引 避免回表
CREATE INDEX idx_orders_user_status_inc ON orders(user_id, status)
    INCLUDE (amount, created_at);

-- 部分索引只为子集建索引 占用更小
CREATE INDEX idx_orders_pending ON orders(created_at)
    WHERE status = 'pending';

-- 表达式索引为函数表达式建索引 支持函数化查询
CREATE INDEX idx_orders_lower_email ON orders(lower(email));

B-tree 是 99% 场景的默认选择 但它有个致命弱点 不能加速 LIKE '%xxx%' 这类前置通配符查询 不能加速 JSON 字段的多值匹配 不能加速数组的 contains 这些场景必须用 GIN 索引。

-- GIN 索引适合多值字段 数组 JSON 全文搜索
CREATE INDEX idx_orders_tags ON orders USING GIN(tags);
CREATE INDEX idx_orders_extra ON orders USING GIN(extra jsonb_path_ops);

-- 全文搜索 GIN 比 B-tree 快几个数量级
CREATE INDEX idx_orders_desc_fts ON orders USING GIN(to_tsvector('simple', description));

-- BRIN 索引适合超大且有序的表 比如时间序列
CREATE INDEX idx_logs_ts_brin ON logs USING BRIN(ts) WITH (pages_per_range = 32);

-- GiST 索引适合几何 范围类型 邻近搜索
CREATE INDEX idx_events_period ON events USING GIST(period);

-- Hash 索引只支持等值查询 PG 10 后才完整 WAL 化
CREATE INDEX idx_orders_token_hash ON orders USING HASH(token);

GIN 索引的代价是写入慢 因为它要为每个值的每个元素建立倒排。一个 jsonb 字段的 GIN 索引可能让 INSERT 速度降低 30-50% 必须权衡。BRIN 索引非常轻量 适合 logs metrics 这类按时间追加的超大表 一个 10 亿行表的 BRIN 索引可能只有几十 MB 但只对范围查询有效 等值查询不如 B-tree。

二 执行计划 EXPLAIN ANALYZE:看懂优化器在想什么

不会看执行计划就别谈 PostgreSQL 调优。EXPLAIN ANALYZE 是优化的眼睛 它告诉你优化器选了什么计划 实际跑下来是什么样 计划估算的行数和实际行数差多少。

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
  AND o.created_at >= '2024-01-01'
  AND o.created_at < '2024-02-01'
ORDER BY o.created_at DESC
LIMIT 100;

-- 关注几个关键指标
-- 1. Seq Scan vs Index Scan vs Bitmap Index Scan
-- 2. rows= 实际 vs estimated 差几倍说明统计信息不准
-- 3. Buffers shared hit/read 命中和读盘数
-- 4. Sort Method 是 quicksort 还是 external merge 后者意味着内存不够
-- 5. Hash Join Merge Join Nested Loop 选哪种

看 EXPLAIN 最重要的不是看节点 而是看 rows 的估算误差 如果优化器估算 1000 行实际 100 万行 那它选的计划必然是错的 接下来要做的是 ANALYZE 该表更新统计信息 或者调高该列的 statistics target。

-- 手动更新统计信息
ANALYZE orders;

-- 针对单列调高统计精度 默认 100 调到 1000
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- 查看自动 vacuum/analyze 的活动
SELECT relname,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze,
       n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- 查看索引使用情况找出从未被使用的索引
SELECT schemaname, relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

这第二个语句是日常运维必备 那些 idx_scan=0 又占了几 GB 的索引就是纯粹拖累 应该删掉。我们曾经在一个老业务的库里发现 200 多个索引里有 47 个从未被使用 删掉后写入速度提升了 30% 表大小减小了 20%。

三 联合索引列顺序:决定查询能不能走对

联合索引的列顺序经常被忽视 但它直接决定一个索引能否被某个查询用上。原则是 等值条件在前 范围条件在后 高基数在前 低基数在后。但更精确的原则是 看实际查询模式 不要拍脑袋设计。

-- 假设查询模式如下
-- Q1: WHERE status = 'paid' AND created_at >= ?
-- Q2: WHERE user_id = ? AND created_at >= ?
-- Q3: WHERE user_id = ? AND status = 'paid'

-- 错误设计 一个 (created_at, status, user_id) 三个查询都用不上前缀
CREATE INDEX idx_bad ON orders(created_at, status, user_id);

-- 正确设计 按查询模式分别建索引
CREATE INDEX idx_status_created ON orders(status, created_at);   -- Q1
CREATE INDEX idx_user_created ON orders(user_id, created_at);    -- Q2
CREATE INDEX idx_user_status ON orders(user_id, status);         -- Q3

-- 如果 user_id 是高基数 status 低基数 Q3 可以省 只用 idx_user_status 前缀 user_id
-- 因为 PostgreSQL 可以做 index condition skip

这里有个关键原则 PostgreSQL 在多列索引上只能使用 最左前缀 然后第一个范围列后面的列基本无效。也就是说 索引 (a,b,c) 对 WHERE a=? AND b=? AND c=? 完美匹配 对 WHERE a=? AND b>? AND c=? 只有 a 和 b 走索引 c 在 B-tree 里要扫所有匹配的行。设计联合索引时要把等值条件全部放在范围前面。

[mermaid]flowchart TD
A[查询到达] --> B{优化器估算
各计划成本}
B --> C[全表扫描成本]
B --> D[索引扫描成本]
B --> E[位图索引成本]
C --> F{比较选最小}
D --> F
E --> F
F --> G{选中计划}
G -->|全表| H[Seq Scan]
G -->|索引| I[Index Scan 走索引B-tree]
G -->|位图| J[Bitmap Index 加 Bitmap Heap]
I --> K{是否需要回表}
K -->|需要| L[Index Scan 加 heap fetch]
K -->|不需要 INCLUDE| M[Index Only Scan]

四 部分索引与表达式索引:PostgreSQL 独有的利器

很多 DBA 习惯 MySQL 那种思路 给字段建索引 完事。但 PostgreSQL 有两个 MySQL 没有的强大特性 部分索引 partial index 和表达式索引 expression index 这两个用好了能让索引体积小几十倍 查询快几倍。

-- 场景 99% 的订单是 completed 状态 只有 1% 是 pending
-- 但业务总是查 pending 的订单
-- 全字段索引 30GB 部分索引可能只有 300MB
CREATE INDEX idx_orders_pending_partial ON orders(created_at)
    WHERE status IN ('pending', 'processing');

-- 场景 用户登录用 lower(email) 比较 加普通索引匹配不上
CREATE INDEX idx_users_lower_email ON users(lower(email));

-- 场景 高频查询是按月统计
CREATE INDEX idx_orders_month ON orders(date_trunc('month', created_at));

-- 场景 软删除场景只索引活跃记录
CREATE INDEX idx_active_records ON records(updated_at)
    WHERE deleted_at IS NULL;

-- 场景 多租户表只为单租户建大索引
CREATE INDEX idx_tenant_orders_2024 ON orders(tenant_id, created_at)
    WHERE tenant_id = 1001 AND created_at >= '2024-01-01';

部分索引最大的好处是 索引体积小 缓存命中率高 维护成本低。一个 5 亿行的订单表 如果 99% 是 completed 状态 给 pending 状态建部分索引可能只有 5MB 整个索引能完全装进内存 查询近乎是常数时间。表达式索引则解决了函数转换的问题 用户写 lower(email)='x' 普通的 email 索引根本用不上 必须建 lower(email) 的表达式索引才能匹配。

五 慢查询治理:从 pg_stat_statements 到查询改写

线上慢查询的治理流程是 找出来 看明白 改掉它。找出来靠 pg_stat_statements 看明白靠 EXPLAIN ANALYZE 改掉它有几种典型手法。

-- 启用 pg_stat_statements 扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查询 P99 慢且高频的 SQL
SELECT
    substring(query, 1, 100) AS query_sample,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
    rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

-- 查询当前正在跑的长查询
SELECT pid, age(clock_timestamp(), query_start) AS duration,
       state, query
FROM pg_stat_activity
WHERE state = 'active'
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start
LIMIT 10;

-- kill 掉指定的慢查询
SELECT pg_cancel_backend(pid);   -- 温柔取消
SELECT pg_terminate_backend(pid); -- 强制终止

查询改写常见的手法有几个。第一是把 NOT IN 改成 NOT EXISTS 因为 NULL 处理上 NOT IN 容易让优化器选错计划。第二是把 OR 拆成 UNION ALL 因为 OR 经常让索引失效 但 UNION ALL 可以分别用各自的索引。第三是 LIMIT N 加 ORDER BY 时建议显式有 LIMIT 路径的索引 否则可能选错。第四是分页深翻 OFFSET 1000000 几乎一定是慢的 必须改成 keyset pagination 用 WHERE id > last_id LIMIT 20。

-- 反例 OR 让索引失效
SELECT * FROM orders WHERE status = 'paid' OR user_id = 1001;

-- 正例 UNION ALL 各自走索引
SELECT * FROM orders WHERE status = 'paid'
UNION ALL
SELECT * FROM orders WHERE user_id = 1001 AND status != 'paid';

-- 反例 深翻分页 OFFSET 越大越慢
SELECT * FROM orders ORDER BY created_at DESC OFFSET 1000000 LIMIT 20;

-- 正例 keyset pagination 用上一页最后一条的 created_at 做游标
SELECT * FROM orders
WHERE created_at < :last_seen_created_at
ORDER BY created_at DESC
LIMIT 20;

-- 反例 子查询每行执行一次
SELECT o.*,
       (SELECT name FROM users WHERE id = o.user_id) AS user_name
FROM orders o;

-- 正例 JOIN 一次完成
SELECT o.*, u.name AS user_name
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

六 PostgreSQL 索引的工程坑:那些文档里学不到的

讲完原理来说几个真实生产里的坑。第一个坑是 索引膨胀 频繁 UPDATE 会让 B-tree 索引产生大量死元组 索引体积可能膨胀到原来的 2-5 倍 查询变慢 必须定期 REINDEX CONCURRENTLY 不会锁表。第二个坑是 自动 vacuum 跟不上 高写入表的 vacuum 默认配置经常不够 dead tuples 堆积导致 bloat 必须给热点表单独调 autovacuum_vacuum_scale_factor。

-- 检查索引膨胀
SELECT schemaname, tablename, indexname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       pg_size_pretty(pg_relation_size(indrelid)) AS table_size,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- 在线重建索引不锁表
REINDEX INDEX CONCURRENTLY idx_orders_user_status_inc;

-- 给热点表调高 autovacuum 频率
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_vacuum_cost_limit = 2000
);

-- 创建索引必须用 CONCURRENTLY 不锁写
CREATE INDEX CONCURRENTLY idx_orders_user_amount ON orders(user_id, amount);

-- 监控长事务长事务会阻止 vacuum 必须告警
SELECT pid, now() - xact_start AS xact_duration, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

第三个坑是 创建索引不加 CONCURRENTLY 直接 CREATE INDEX 会锁写 几亿行的表创建索引可能阻塞业务半小时 必须用 CONCURRENTLY 它会用多次扫描换不锁表。第四个坑是 PostgreSQL 默认 work_mem 是 4MB 复杂排序或 hash 操作会溢出到磁盘 必须按业务调高 但要小心连接数乘起来不要爆内存。第五个坑是 长事务会阻塞 vacuum 一个跑了几小时的 SELECT 事务会让所有表的 dead tuples 无法回收 数据库膨胀剧烈 必须做长事务监控告警 并对所有事务设 statement_timeout 兜底。

关键概念速查

避坑清单

1. 建索引一定用 CONCURRENTLY 否则锁写阻塞业务可能持续半小时以上。
2. 多列索引按 等值在前范围在后 高基数在前低基数在后 设计否则前缀匹配失效。
3. LIKE '%xxx%' 类前置通配符 B-tree 索引无能为力必须 GIN trigram 索引。
4. 统计信息不准时优化器会选错计划 高基数列要调高 STATISTICS。
5. 定期检查 pg_stat_user_indexes 删掉 idx_scan=0 的死索引。
6. 高读低更新的场景大胆用 INCLUDE 做覆盖索引 避免回表。
7. 软删除字段要用部分索引 不要全表索引否则浪费 90% 空间。
8. JSON 字段加索引前先看是查整个 JSON 还是某个 path GIN 和 expression 索引选不同。
9. autovacuum 跟不上时给热点表单独调 vacuum_scale_factor 别用全局默认值。
10. 必须设 statement_timeout 兜底 否则一个失控的 SQL 能把库压垮。

总结

PostgreSQL 索引这事 很多人的直觉是 给字段建个索引就好了 这其实是把 我会写 CREATE INDEX 和 我能让查询快 混为一谈。前者是会写 SQL 后者是懂 PG 优化器。中间隔着的是 索引类型选型 联合列顺序 统计信息 部分索引 表达式索引 vacuum 调度 长事务监控 索引膨胀治理 整整一套工程方法论。

从上线到稳定 你需要做的事远不止 建几个索引。你要懂 B-tree GIN GiST BRIN 各自的边界 要会看 EXPLAIN ANALYZE 要分析 pg_stat_statements 要监控 pg_stat_user_indexes 要定期 REINDEX 要给热点表调 autovacuum 要应对索引膨胀长事务统计信息倾斜。每一项单独看都不复杂 但它们组合在一起 才是一个能扛业务的 PostgreSQL 体系。少任何一项 都会在某个查询上某个客户上某个时刻 给你一次慢查询故障 而那种故障通常一炸就是几十分钟。

我经常用一个比喻来理解 PostgreSQL 索引 它有点像图书馆的目录系统。B-tree 是按拼音排的总目录 GIN 是关键词反查目录 BRIN 是分区目录 部分索引是只为热门书做的精选目录。如果你只有一本总目录 找冷门书很方便 但人多了大家都来翻同一本就堵了 找复杂条件的书也跑不动。真正的图书馆是有多套目录配合使用 而不是用一本厚字典走遍天下。

这套架构最难的地方在于 它的复杂度在数据量小时几乎完全暴露不了。你在开发环境 10 万行数据 怎么查都很快 觉得 PG 真好用 但真上了 5 亿行的生产 你才发现 99% 的复杂度都在 那 1% 的查询模式 数据分布 长事务 vacuum 跟不上 索引膨胀里。建议任何想做 PostgreSQL 调优的团队 上线前一定要在生产规模的数据上压测 用真实分布 真实并发 真实查询模式去跑一跑 千万别只测了开发环境就上 那种系统一定会在数据量上来后给你看一场慢查询雪崩。

—— 别看了 · 2026