从 1200ms 到 8ms：PG 复合索引列顺序推导实战

问题背景：为什么复合索引建了还是走 Seq Scan

很多业务表都会遇到类似条件：

WHERE tenant_id = $1
  AND status = $2
  AND created_at >= $3
  ORDER BY created_at DESC
  LIMIT 20;

开发者通常知道要给 tenant_id、status、created_at 建复合索引，但真正影响性能的关键是：哪一列放前面，哪一列放后面。在 PostgreSQL 的 B-tree 中，索引项按列从左到右排序。前导列用于定位，后续列只在前导列值相同的区间内有序。

因此，常见经验是：

• 等值条件列尽量靠前；
• 范围条件列放在等值列之后；
• 排序列如果能与范围列复用，通常可以同时支持过滤和排序；
• 多个范围列一般只有第一个范围列能充分利用 B-tree 的有序性。

但这不是死记硬背的口诀，而是由 B-tree 的扫描方式推导出来的。

方案对比：三种列顺序的访问路径差异

下面用一张订单明细表模拟典型的「租户等值 + 状态等值 + 时间范围 + 按时间排序」查询。

-- 可直接在 PostgreSQL 中运行
DROP TABLE IF EXISTS orders_demo;

CREATE TABLE orders_demo (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id   int NOT NULL,
    status      text NOT NULL,
    amount      numeric(12,2) NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- 构造约 100 万行测试数据
INSERT INTO orders_demo(tenant_id, status, amount, created_at)
SELECT
    (random() * 20 + 1)::int AS tenant_id,
    (ARRAY['paid','pending','canceled','refunded'])[floor(random() * 4 + 1)] AS status,
    round((random() * 1000)::numeric, 2) AS amount,
    now() - (random() * interval '365 days') AS created_at
FROM generate_series(1, 1000000);

-- 候选索引 A：等值列在前，范围列在后
CREATE INDEX idx_orders_tenant_status_time
    ON orders_demo (tenant_id, status, created_at DESC);

-- 候选索引 B：范围列在前
CREATE INDEX idx_orders_time_tenant_status
    ON orders_demo (created_at DESC, tenant_id, status);

ANALYZE orders_demo;

对比查询如下：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, created_at
FROM orders_demo
WHERE tenant_id = 7
  AND status = 'paid'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 20;

顺序一：tenant_id、status、created_at

这是推荐顺序。B-tree 先通过 tenant_id = 7 缩小到一个租户，再通过 status = 'paid' 在该租户内继续精确定位，最后在 created_at 上做连续范围扫描。因为索引中 created_at 已经在固定的等值前缀内有序，ORDER BY created_at DESC LIMIT 20 往往不需要额外 Sort。

理想执行计划特征类似：

Limit
  -> Index Scan using idx_orders_tenant_status_time
         Index Cond: ((tenant_id = 7)
                  AND (status = 'paid')
                  AND (created_at >= ...))

顺序二：created_at、tenant_id、status

这种顺序看似把高区分度的时间放在前面，但查询同时指定了时间范围。B-tree 可以沿时间倒序扫描，却必须在扫描过程中不断过滤 tenant_id 与 status。如果某个时间范围内满足租户和状态的数据很稀疏，数据库可能扫描大量索引项后才能凑够 LIMIT 20。

更糟的是，如果优化器认为选择性不足，可能直接选择 Seq Scan + Sort。

顺序三：tenant_id、created_at、status

该顺序比时间列在前更常见，也更容易迷惑人。tenant_id 是等值，created_at 是范围；一旦在第二列进入范围扫描，第三列 status 在整个扫描区间内并不全局有序。PostgreSQL 仍可能把 status = 'paid' 作为 Index Cond 或 Filter，但它无法像等值前缀那样先精确定位到一个连续子树。

可以通过下面语句观察不同索引的真实效果：

-- 强制测试推荐索引
BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, created_at
FROM orders_demo
WHERE tenant_id = 7
  AND status = 'paid'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 20;

ROLLBACK;

注意：enable_seqscan = off 只用于实验对比，不建议在生产参数中永久关闭。

B-tree 扫描原理：从排序键推导列顺序

复合索引可以理解为先按第一列排序，第一列相同时按第二列排序，第二列也相同时再按第三列排序。对于 (tenant_id, status, created_at)，索引逻辑上类似：

(1, canceled, 2026-01-03)
(1, paid,     2026-01-01)
(1, paid,     2025-12-20)
(7, paid,     2026-02-09)
(7, paid,     2026-02-08)
(7, pending,  2026-02-10)

当条件是：

tenant_id = 7 AND status = 'paid' AND created_at >= ...

数据库能直接定位到 (7, paid, 某个时间)，再沿叶子节点单向扫描。等值列像楼层和房间号，范围列像房间内按时间排好的文件。先锁定楼层和房间，再按时间翻文件，代价最低。

实操步骤：用统计信息和执行计划验证顺序

不要只凭字段区分度判断顺序，而应结合谓词类型、排序方式和数据分布。

第一步：查看列的离散程度

SELECT
    count(DISTINCT tenant_id) AS tenant_cnt,
    count(DISTINCT status) AS status_cnt,
    count(DISTINCT created_at) AS created_at_cnt,
    count(*) AS total_cnt
FROM orders_demo;

created_at 的 distinct 值通常很多，但高基数不等于它应该放最前。范围查询不是在一个时间点上精确定位，而是在一个区间内扫描。若区间内大部分行不满足其他等值条件，扫描量仍然很大。

第二步：确认排序是否被索引消除

执行 EXPLAIN (ANALYZE, BUFFERS) 后重点看：

• 是否出现 Sort；
• 是否出现 Bitmap Heap Scan 后再排序；
• actual time 与 rows 是否接近 LIMIT；
• Buffers: shared read/hit 是否异常偏大。

如果计划中出现：

Sort Key: created_at DESC
Sort Method: top-N heapsort

说明数据库取到一批候选行后又排序。虽然 LIMIT 20 的 top-N heapsort 不一定很慢，但高并发下仍会消耗 CPU。若索引顺序与查询排序方向匹配，通常更稳定。

第三步：为固定热点条件使用部分索引

如果业务绝大多数请求只查询有效订单，可以把「总是出现的状态」从索引键中移到部分索引条件里：

-- 适合 status 永远为 paid 的查询
CREATE INDEX idx_orders_tenant_time_paid
    ON orders_demo (tenant_id, created_at DESC)
    WHERE status = 'paid';

ANALYZE orders_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount, created_at
FROM orders_demo
WHERE tenant_id = 7
  AND status = 'paid'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 20;

部分索引更小、缓存命中更高。但前提是 SQL 必须包含相同的 status = 'paid' 条件，规划器才会选择它。

多范围条件时如何取舍

如果查询是：

WHERE tenant_id = 7
  AND created_at >= now() - interval '30 days'
  AND amount > 500;

通常应建：

CREATE INDEX idx_orders_tenant_time_amount
    ON orders_demo (tenant_id, created_at DESC);

而不是盲目把 amount 追加到后面期待两个范围都被高效利用。因为进入 created_at 范围后，amount 只在每个相同时间点内有序，对整个时间区间并不连续有序，多数情况下只是过滤条件。是否加入 amount，需要用真实数据比较索引大小、过滤收益和写入成本。

避坑总结：复合索引最常见的 4 个错误

1. 对索引列包函数，导致前导列失效

错误写法：

WHERE date(created_at) >= current_date - 30;

即使有 (tenant_id, status, created_at) 索引，date(created_at) 是函数表达式，普通 B-tree 无法直接使用原始的 created_at 有序性。应改成范围：

WHERE created_at >= date_trunc('day', now()) - interval '30 days'
  AND created_at <  date_trunc('day', now()) + interval '1 day';

或者建立与表达式完全一致的表达式索引。

2. 参数类型不匹配，引发隐式转换

如果 tenant_id 是 integer，却写成字符串：

WHERE tenant_id = '7';

PostgreSQL 可能发生 CAST('7' AS integer)。简单常量场景通常仍可执行，但 Prepared Statement、ORM 参数类型推断错误时，可能出现 (tenant_id)::bigint = $1 这类谓词，造成索引利用异常。代码中应让参数类型与表结构一致。

3. 只按区分度排顺序，忽略范围会截断后续有序性

很多人把 created_at 放第一列，理由是它 distinct 最多。实际查询是时间范围而不是时间等值，范围列之后的等值列无法继续形成全局连续定位。对典型「多等值 + 单范围 + 排序」查询，应优先采用「等值列在前、范围列在后」。

4. 索引越多越好，忽视写入和维护成本

给每种排列都建一个索引，表面上覆盖了所有 SQL，实际会拖慢 INSERT、UPDATE、DELETE，并增加 autovacuum 与缓存压力。建议用 pg_stat_user_indexes 观察使用率，删除长期不用或被覆盖的冗余索引：

SELECT schemaname, relname, indexrelname,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders_demo'
ORDER BY idx_scan DESC;

最终结论

PostgreSQL 复合索引列顺序不是简单的「谁区分度高谁在前」。对于等值与范围组合查询，应从 B-tree 的左前缀有序性推导：先用稳定的等值列锁定连续区间，再让范围列承担扫描和排序；多个范围列不要期待全部走连续索引路径。最后用 EXPLAIN (ANALYZE, BUFFERS) 验证扫描行数、缓冲命中和 Sort 是否消除，再决定普通复合索引还是更小的部分索引，才能把查询从千毫秒级稳定压到个位数毫秒。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。