商家后台的订单列表页，每页 20 条，可以按状态筛选，默认按创建时间倒序。这条查询上线三个月后开始变慢，EXPLAIN 一看索引走了，但每次要读几百个数据页。问题出在联合索引里列的顺序上——顺序换一下，同样的数据、同样的 SQL，读取的数据页从 987 降到 23。
下面所有结果都可以在本地复现，PostgreSQL 12 以上都适用。
-- 关掉并行，让计划稳定一点，方便对比
SET max_parallel_workers_per_gather = 0;
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id bigserial PRIMARY KEY,
merchant_id int NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL,
amount numeric(10,2) NOT NULL
);
-- 200 万行：2000 个商户，5 种状态，时间打散在过去一年
INSERT INTO orders (merchant_id, status, created_at, amount)
SELECT
(random() * 1999)::int + 1,
(ARRAY['pending','paid','shipped','done','cancelled'])[1 + (random() * 4)::int],
now() - (random() * interval '365 days'),
round((random() * 1000)::numeric, 2)
FROM generate_series(1, 2000000);
-- 统计信息不刷新，规划器会按默认选择率估算，后面的 EXPLAIN 全是错的
ANALYZE orders;
建完大概是：每个商户 1000 条订单，每个「商户 + 状态」组合 200 条，时间均匀铺在一年里。现实中分布不会这么均匀，但结论不受影响，不均匀只会让下面的差距更大。
同一条查询，两种索引，差 40 倍
先看一个不带状态筛选的查询——就是列表页默认打开的那一屏：
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, amount, created_at
FROM orders
WHERE merchant_id = 1234
ORDER BY created_at DESC
LIMIT 20;
索引 A：(merchant_id, status, created_at)
Limit (actual time=1.612..1.619 rows=20 loops=1)
-> Sort (actual time=1.601..1.603 rows=20 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 28kB
-> Bitmap Heap Scan on orders (actual time=0.121..1.213 rows=1000 loops=1)
Recheck Cond: (merchant_id = 1234)
Heap Blocks: exact=976
Buffers: shared hit=976
-> Bitmap Index Scan on idx_mst (actual time=0.072..0.072 rows=1000 loops=1)
Index Cond: (merchant_id = 1234)
Buffers: shared hit=11
Execution Time: 1.650 ms
索引 B：(merchant_id, created_at, status)
Limit (actual time=0.026..0.168 rows=20 loops=1)
-> Index Scan Backward using idx_mts on orders (actual time=0.024..0.161 rows=20 loops=1)
Index Cond: (merchant_id = 1234)
Buffers: shared hit=23
Execution Time: 0.190 ms
一个是 987 个页命中，一个是 23 个，差了 40 多倍。
原因在 B-tree 的物理排序上。索引 A 在 merchant_id 相等的那一段里，是按 status 排的，created_at 只是第三排序键。而 status 有 5 个值，created_at 在 5 个状态内部各自有序，跨状态就乱了。所以 PG 拿到 WHERE merchant_id = 1234 之后，没有任何一列能直接给它想要的顺序，只能把该商户的 1000 行全捞出来在内存里排一遍。1000 行散落在 200 万个随机位置，就是 976 个数据页。
索引 B 在 merchant_id 相等的那一段里直接按 created_at 排，LIMIT 20 拿到 20 行就停，后面的 980 行根本没碰过。
再加回状态筛选，情况就反过来了：
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, amount, created_at
FROM orders
WHERE merchant_id = 1234
AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
索引 A：
Limit (actual time=0.031..0.204 rows=20 loops=1)
-> Index Scan Backward using idx_mst on orders (actual time=0.029..0.196 rows=20 loops=1)
Index Cond: ((merchant_id = 1234) AND (status = 'paid'::text))
Buffers: shared hit=24
Execution Time: 0.240 ms
索引 B：
Limit (actual time=0.033..1.087 rows=20 loops=1)
-> Index Scan Backward using idx_mts on orders (actual time=0.031..1.078 rows=20 loops=1)
Index Cond: (merchant_id = 1234)
Buffers: shared hit=104
Execution Time: 1.130 ms
索引 B 扫了 104 个页。paid 只占五分之一，要凑够 20 条就得在索引里往回翻大约 100 个条目，这 100 个条目对应的数据页基本互不相同，于是就变成了 100 次随机读。
注意这里有个很容易被误读的地方：EXPLAIN 输出的 Index Cond 里出现了某一列，不代表这一列帮你缩小了扫描范围。索引 B 完全有能力在索引条目上直接判断 status = 'paid'，不用回表，省掉了一部分 I/O；但它没法跳过不匹配的条目。定位和过滤是两件事，Index Cond 把它们混在一起显示了。
所以真实结论是：索引 A 和索引 B 没有谁绝对更好，它们各自服务一半的查询。你的列表页如果默认带状态筛选，索引 A 是对的；如果默认不带，索引 B 是对的。两个都建当然可以，但写放大和缓存占用要自己算账。
范围条件会把后面的列“焊死”
这是最容易踩、也最难自己想明白的一条。
假设你把索引建成 (merchant_id, created_at, status)——因为「反正都要按时间排序」，然后写了一条带时间范围的统计查询：
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*)
FROM orders
WHERE merchant_id = 1234
AND created_at >= now() - interval '180 days'
AND created_at < now()
AND status = 'paid';
索引扫描的定位部分只会用到 merchant_id = 1234 和 created_at 的那段区间，半年的数据大约是 500 条。status 虽然写在索引里，却完全没法参与定位——因为 B-tree 只能确定一个连续的键区间，created_at 一旦是范围条件，扫描区间就已经被它封死了，status 只是区间内散落的值，跳不过去。
把它换成 (merchant_id, status, created_at)，扫描区间变成「这个商户 + paid 这个状态」下的一段时间，同样半年只有大约 100 条。扫描的条目数从 500 掉到 100。
选择性越差，这个差距越夸张。如果 status = 'cancelled' 只占全表 1%，而你用 (merchant_id, created_at, status) 去查这 1%，那就得把这个商户对应时间范围内的所有订单全部过一遍。
列顺序的经验规则，按优先级排：
| 位置 | 放什么 | 原因 |
|---|---|---|
| 最左 | 等值条件列（=、IN） |
每个等值列都能把扫描区间切掉一大块 |
| 中间 | 还需要继续等值的列 | 同上 |
| 靠后 | ORDER BY 的列 |
前面全是等值时，排序可以直接由索引提供，连 Sort 节点都省了 |
| 最后 | 范围条件列（>、<、BETWEEN、LIKE 'x%'） |
它后面的列全部失去定位能力 |
还有一个常被搞混的点：WHERE 里条件的书写顺序无所谓。WHERE status = 'paid' AND merchant_id = 1234 和反过来写，优化器会重排成同一个等值列集合，计划完全一样。真正决定一切的是 CREATE INDEX 里的列顺序。
另外，ORDER BY 后面如果跟了范围条件列，排序就没法免了。比如索引 (merchant_id, status, created_at) 配上 WHERE merchant_id = 1 AND created_at > '2024-01-01' ORDER BY created_at DESC，created_at 上的范围条件让它没法同时充当排序来源，PG 会老老实实加一个 Sort。等值和范围，二选一。
索引建了却不用，通常是这几种写法
一、把索引列包进函数或表达式里
-- ❌ 索引 (merchant_id, created_at) 直接作废，走全表扫
SELECT id FROM orders
WHERE merchant_id = 1234
AND created_at::date = '2024-05-01';
-- ✅ 改写成等价的半开区间，索引用得上
SELECT id FROM orders
WHERE merchant_id = 1234
AND created_at >= '2024-05-01 00:00:00+08'
AND created_at < '2024-05-02 00:00:00+08';
created_at::date 还有个隐藏问题：它的结果依赖当前会话的 TimeZone 参数。同一个 timestamptz 值，在不同时区的连接里算出来的日期可能差一天。用范围写法顺便把这个问题也解决了。
如果确实需要按「本地日期」聚合，可以建表达式索引，但查询里的表达式必须一模一样，一个字都不能差：
CREATE INDEX idx_orders_local_day
ON orders (merchant_id, ((created_at AT TIME ZONE 'Asia/Shanghai')::date));
-- 用得上
SELECT count(*) FROM orders
WHERE merchant_id = 1234
AND (created_at AT TIME ZONE 'Asia/Shanghai')::date = '2024-05-01';
-- 用不上，少了 AT TIME ZONE
SELECT count(*) FROM orders
WHERE merchant_id = 1234
AND created_at::date = '2024-05-01';
二、部分索引的谓词对不上
-- 只给待处理订单建一个小索引
CREATE INDEX idx_pending ON orders (merchant_id, created_at)
WHERE status = 'pending';
-- ✅ 谓词完全匹配，这个索引才有资格被考虑
SELECT id FROM orders
WHERE merchant_id = 1234 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;
-- ❌ status <> 'done' 推不出 status = 'pending'，索引连候选列表都进不去
SELECT id FROM orders
WHERE merchant_id = 1234 AND status <> 'done'
ORDER BY created_at DESC LIMIT 20;
这里有个更阴的坑：如果用的是预编译语句或者 ORM 的参数占位符，规划阶段看不到 status 的实际值，优化器就没法证明 status = 'pending' 成立，部分索引同样不会被选中，会退化成通用计划。同一句 SQL，在 psql 里跑得飞快，在应用里慢十倍，经常是这个原因。
三、排序键里偷偷塞了一列
-- 索引是 (merchant_id, status, created_at)，但排序多带了一个 id
SELECT id, amount, created_at FROM orders
WHERE merchant_id = 1234 AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20;
created_at 可能有并列值（同一秒插入的订单），业务上为了保证分页稳定，习惯再加一个 id DESC。这一加，索引提供的顺序就不完整了，PG 会插入一个 Sort 节点，LIMIT 的提前终止也就没了。要么把索引改成 (merchant_id, status, created_at DESC, id DESC)，要么接受这个排序成本。做游标分页的时候这一条特别关键。
四、以为在索引列上做 LIKE 前缀匹配一定生效
LIKE 'abc%' 在数据库使用 C 排序规则时能走索引；如果数据库用的是 en_US.UTF-8、zh_CN.UTF-8 这类排序规则，普通 B-tree 索引用不上，得建 <column>_pattern_ops 的操作符类：
CREATE INDEX idx_name_pattern ON users (name varchar_pattern_ops);
而 LIKE '%abc' 这种前导通配符，任何 B-tree 都救不了，得上 pg_trgm + GIN 索引，那是另一个话题了。
那到底该按什么顺序建索引
别从表结构出发，从查询出发。顺序是这样的：
- 用
pg_stat_statements按total_exec_time排出最贵的几条 SQL。 - 对每条 SQL，把
WHERE条件分成三堆：等值、范围、排序。 - 按「等值 → 排序 → 范围」拼出候选列顺序。同一张表上如果有多条高频 SQL，优先让它们共用同一个索引的最左前缀，而不是一人一个索引。
- 建完必须用
EXPLAIN (ANALYZE, BUFFERS)验证。看Buffers和rows，不要只看「有没有走索引」——走了索引也可能比全表扫还慢。特别关注Rows Removed by Filter和Heap Blocks这两个数字。
还有几件事容易被忽略：
别留冗余索引。 已经有 (merchant_id, status, created_at) 的时候，单独一个 (merchant_id) 基本没有存在价值，它只会拖慢写入。定期查一遍：
SELECT indexrelid::regclass AS index_name,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan;
idx_scan 是 0、而且已经统计了足够长时间的（pg_stat_reset() 之后算起），可以考虑删。但注意主键和唯一约束索引的 idx_scan 经常偏低，因为约束检查不走计数器，别误删。
线上建索引用 CONCURRENTLY。 普通 CREATE INDEX 会持有 SHARE 锁，阻塞所有写入。代价是它更慢、要扫两遍表，而且不能放在事务块里——很多迁移工具默认包了事务，会直接报错。
-- 不要在 BEGIN ... COMMIT 里执行
CREATE INDEX CONCURRENTLY idx_mst ON orders (merchant_id, status, created_at);
-- 如果中途失败，会留下一个 INVALID 索引：不报错、不生效、还占空间
SELECT indexrelid::regclass AS invalid_index
FROM pg_index WHERE NOT indisvalid;
-- 确认后手动 DROP 掉再重来
写放大的账要算清楚。 每多一个索引，INSERT 就多一棵 B-tree 要维护，UPDATE 只要改到索引列，还会失去 HOT 更新的机会——本来可以在同一个数据页里原地完成的更新，被迫写新版本并更新所有索引。热更新频繁的列（状态、计数、最后登录时间）上加索引，代价比读性能收益往往更明显。
索引不是「加了总比不加好」的东西，它是一笔用写性能换读性能的交易。判断标准始终是那一句：这个索引的最左前缀，能不能同时被多条真实查询用上。能用上，建；只能被一条边缘 SQL 用上，先想想改写 SQL 或者换个列顺序能不能解决。