PostgreSQL 订单列表慢查询优化：用组合索引把 1.8s 压到 40ms

我最近处理了一个订单列表接口的慢查询问题。接口本身很普通：按租户、订单状态、创建时间筛选，再按创建时间倒序取前 20 条。开发环境里数据量小，接口一直没暴露问题；到了预发库，订单表有 300 多万行，查询时间开始稳定在 1.5s 到 2s 之间。

数据库环境是 PostgreSQL 15.6，表结构做过脱敏，但索引和查询条件基本保留了原来的样子。

CREATE TABLE order_event (
    id          BIGSERIAL PRIMARY KEY,
    tenant_id   BIGINT      NOT NULL,
    user_id     BIGINT      NOT NULL,
    status      SMALLINT    NOT NULL,
    amount      NUMERIC(12, 2) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL,
    paid_at     TIMESTAMPTZ,
    deleted_at  TIMESTAMPTZ,
    remark      TEXT
);

接口里的 SQL 大概是这样：

SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = 42
  AND status = 2
  AND deleted_at IS NULL
  AND created_at >= '2025-12-01 00:00:00+08'
ORDER BY created_at DESC
LIMIT 20;

当时表上已经有两个索引：

CREATE INDEX idx_order_event_tenant_id
ON order_event (tenant_id);

CREATE INDEX idx_order_event_created_at
ON order_event (created_at DESC);

从名字看，好像条件里涉及的字段都已经建过索引。实际执行计划拿出来后，问题就比较清楚了。

执行计划里卡住的地方

我排查慢查询时习惯先开 BUFFERS，不只看总耗时，也看读了多少数据页。

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = 42
  AND status = 2
  AND deleted_at IS NULL
  AND created_at >= '2025-12-01 00:00:00+08'
ORDER BY created_at DESC
LIMIT 20;

脱敏后的执行计划如下：

Limit  (cost=18342.19..18342.24 rows=20 width=34)
       (actual time=1817.381..1817.392 rows=20 loops=1)
  Buffers: shared hit=4218 read=18763
  ->  Sort  (cost=18342.19..18389.64 rows=18982 width=34)
            (actual time=1817.379..1817.384 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 28kB
        Buffers: shared hit=4218 read=18763
        ->  Bitmap Heap Scan on order_event
              (cost=612.44..17836.99 rows=18982 width=34)
              (actual time=142.615..1809.228 rows=16342 loops=1)
              Recheck Cond: (tenant_id = 42)
              Filter: ((deleted_at IS NULL)
                    AND (status = 2)
                    AND (created_at >= '2025-12-01 00:00:00+08'::timestamp with time zone))
              Rows Removed by Filter: 246311
              Heap Blocks: exact=22741
              Buffers: shared hit=4215 read=18763
              ->  Bitmap Index Scan on idx_order_event_tenant_id
                    (cost=0.00..607.69 rows=265928 width=0)
                    (actual time=135.842..135.843 rows=262653 loops=1)
                    Index Cond: (tenant_id = 42)
Planning Time: 0.313 ms
Execution Time: 1817.451 ms

这里有两个地方比较刺眼。

PG 只用了 tenant_id 单列索引，先把这个租户下的 26 万多条记录找出来，再回表过滤 status、deleted_at、created_at。过滤完剩 1.6 万多条，再做一次 created_at DESC 排序，最后取前 20 条。

慢的原因不在 LIMIT 20，而在前面为了拿到这 20 条，扫描和过滤了太多无关数据。

单列索引在这种查询里帮得有限。tenant_id 能缩小一部分范围，但状态、软删除、时间排序都没有被索引顺序接住。

索引顺序要跟查询习惯对上

这个接口的查询模式很固定：

WHERE tenant_id = ?
  AND status = ?
  AND deleted_at IS NULL
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT ?

这里最适合放进组合索引的是：

tenant_id, status, created_at DESC

原因也不复杂。

tenant_id 和 status 都是等值条件，放在索引前面可以快速定位到某个租户下某种状态的订单。created_at 同时承担范围过滤和排序，放在后面之后，PG 可以顺着索引倒序读取，遇到满足条件的前 20 条就停下来。

deleted_at IS NULL 是软删除条件，业务里绝大部分查询都只查未删除记录。这个字段我没有直接放进普通组合索引，而是用了部分索引，把已删除数据排除在索引之外。

CREATE INDEX CONCURRENTLY idx_order_event_list_tenant_status_ctime
ON order_event (tenant_id, status, created_at DESC)
INCLUDE (id, user_id, amount)
WHERE deleted_at IS NULL;

这里用了三个处理：

tenant_id, status, created_at DESC 用来匹配筛选和排序。

WHERE deleted_at IS NULL 缩小索引体积，也让索引更贴近订单列表接口的真实查询习惯。

INCLUDE (id, user_id, amount) 是为了让查询列尽量被索引覆盖。能不能走 Index Only Scan 还要看可见性映射和表的更新频率，不能把它当成稳定承诺，但这几个字段放进去没有改变索引排序规则，对列表查询有帮助。

生产环境加索引时我用了 CONCURRENTLY。这个语句不会长时间阻塞普通读写，但执行更慢，也不能放在事务块里跑。

CREATE INDEX CONCURRENTLY idx_order_event_list_tenant_status_ctime
ON order_event (tenant_id, status, created_at DESC)
INCLUDE (id, user_id, amount)
WHERE deleted_at IS NULL;

如果迁移工具默认把 DDL 包在事务里，这条语句会直接报错：

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

我在上线脚本里把它单独拆出来执行，并通过下面这个视图观察进度：

SELECT
    pid,
    phase,
    blocks_total,
    blocks_done,
    tuples_total,
    tuples_done
FROM pg_stat_progress_create_index;

加索引后的执行计划

索引创建完成后，重新执行同一条 SQL。

Limit  (cost=0.43..36.18 rows=20 width=34)
       (actual time=3.746..39.812 rows=20 loops=1)
  Buffers: shared hit=41 read=12
  ->  Index Scan using idx_order_event_list_tenant_status_ctime on order_event
        (cost=0.43..33842.91 rows=18930 width=34)
        (actual time=3.744..39.804 rows=20 loops=1)
        Index Cond: ((tenant_id = 42)
                 AND (status = 2)
                 AND (created_at >= '2025-12-01 00:00:00+08'::timestamp with time zone))
        Buffers: shared hit=41 read=12
Planning Time: 0.421 ms
Execution Time: 39.846 ms

这次执行计划没有再出现 Bitmap Heap Scan，也没有额外 Sort。PG 直接沿着新索引读取，条件命中后拿到 20 行就结束。

对比一下前后变化：

指标	调整前	调整后
执行时间	1817ms 左右	40ms 左右
主要扫描方式	Bitmap Heap Scan	Index Scan
是否额外排序	需要 Sort	不需要 Sort
读取数据页	2 万多个 buffer	50 个左右 buffer
过滤掉的行数	24 万多行	基本没有无效扫描

这个结果也解释了为什么只看 LIMIT 20 容易误判。LIMIT 只限制最终返回数量，前面的扫描、过滤、排序如果没有被索引接住，数据库照样要干很多活。

状态条件变成多个值时要重新看计划

上线后还有一个相近接口，查询条件从单个状态变成了多个状态：

SELECT
    id,
    user_id,
    status,
    amount,
    created_at
FROM order_event
WHERE tenant_id = 42
  AND status IN (1, 2, 3)
  AND deleted_at IS NULL
  AND created_at >= '2025-12-01 00:00:00+08'
ORDER BY created_at DESC
LIMIT 20;

这个查询看起来和前面的 SQL 很像，但执行计划不一定完全一样。

因为索引顺序是：

tenant_id -> status -> created_at

当 status = 2 时，某个状态内部的 created_at DESC 是有序的。

当 status IN (1, 2, 3) 时，PG 可能需要从多个状态范围里取数据，再合并排序。状态值少、命中数据不多时，性能通常还能接受；状态值变多或者数据分布不均匀时，排序可能又会回来。

如果多状态查询是高频接口，我会考虑另建一个偏向时间线的索引：

CREATE INDEX CONCURRENTLY idx_order_event_list_tenant_ctime
ON order_event (tenant_id, created_at DESC)
INCLUDE (id, user_id, status, amount)
WHERE deleted_at IS NULL;

这条索引适合“某个租户下按时间线翻订单，再顺手过滤若干状态”的场景。代价也很明显：多维护一个索引，写入订单时会多一次索引更新，磁盘占用也会上去。

所以我没有在第一次优化里直接加两条索引。先解决最慢、最稳定复现的单状态接口，再观察 pg_stat_statements 里的调用次数和平均耗时，避免把索引加成另一种负担。

旧索引要等观察期结束后再删

新索引上线后，原来的 idx_order_event_tenant_id 和 idx_order_event_created_at 并没有马上删除。

单列索引可能还被其他 SQL 使用。比如后台统计只按 tenant_id 聚合，或者运营页面按创建时间拉全局订单。如果直接删掉，订单列表变快了，其他接口可能又慢下来。

我当时用了 pg_stat_user_indexes 看索引扫描次数：

SELECT
    relname  AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'order_event'
ORDER BY idx_scan DESC;

观察一段时间后，如果确认旧索引没有使用价值，再用并发方式删除：

DROP INDEX CONCURRENTLY idx_order_event_created_at;

这里同样要注意，DROP INDEX CONCURRENTLY 也不能放在事务块里执行。

总结

这次慢查询的根源是索引没有贴合查询路径。订单列表接口需要同时处理租户隔离、状态筛选、软删除过滤和时间倒序，两个单列索引只能解决其中一小段，剩下的过滤和排序都会落到堆表扫描上。

对这类 PostgreSQL 列表查询，我现在会按这个顺序检查：

查询条件里哪些是等值过滤，哪些是范围过滤，排序字段是哪一个。

等值字段尽量放在组合索引前面，排序和范围字段放在后面。

软删除、固定类型、固定业务状态这类长期存在的条件，可以考虑部分索引。

EXPLAIN (ANALYZE, BUFFERS) 要看执行时间，也要看扫描方式、Sort、Rows Removed by Filter 和 buffer 读写。

索引上线后别急着删旧索引，先用真实流量观察一段时间。