Postgres 15慢查询优化：获取赛车手指定日期前最近赛事记录

有一张存储赛事车手时间序列化日志的race_racer表，共约6400万条记录。需求是查询指定赛车手在指定日期前最近x场赛事的最终统计记录，但使用的框架不支持窗口函数，也无法使用数据库函数。由于车手可能未参与某些赛事，不能通过race_ids列表筛选。使用Postgres 15，表结构如下：

CREATE TABLE race_racer (
    race_racer_id SERIAL PRIMARY KEY,
    log_id integer NOT NULL,
    racer_id integer NOT NULL,
    race_id integer NOT NULL,
    stats jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT now()
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX race_racer_pkey ON race_racer(race_racer_id int4_ops);
CREATE INDEX race_racer_log_id_idx ON race_racer(log_id int4_ops);
CREATE INDEX race_racer_racer_id_idx ON race_racer(racer_id int4_ops);
CREATE INDEX race_racer_race_id_idx ON race_racer(race_id int4_ops);
CREATE INDEX race_racer_created_at_idx ON race_racer(created_at timestamp_ops);

其中race_racer_id为自增主键，log_id是供应商提供的记录ID，因批量导入可能存在重复。当前使用的查询语句：

SELECT MAX(p2.race_racer_id) race_racer_id
FROM race_racer p2
WHERE p2.racer_id = 10002093
AND p2.created_at < '2024-08-01'
GROUP BY p2.log_id, p2.race_id
ORDER BY p2.log_id DESC
LIMIT 25

执行计划显示查询耗时约45秒，核心问题是执行时通过race_racer_log_id_idx倒序扫描，过滤掉了450多万条无关记录，IO开销极大。但通过race_id查询指定车手单场最新数据耗时不到20毫秒。

当前查询的瓶颈在于：

• 选择log_id索引倒序扫描，但该索引不包含racer_id和created_at过滤条件，导致扫描大量无关数据后再过滤，资源浪费严重。
• GROUP BY和排序逻辑依赖log_id，现有索引无法支撑快速筛选目标车手的有效记录，需额外进行大量数据处理。

1. 创建覆盖型复合索引 #

针对查询的过滤条件、排序/分组字段，创建复合覆盖索引，让数据库直接从索引获取所需数据，避免回表和无效过滤：

CREATE INDEX idx_racer_created_log_race ON race_racer (racer_id, created_at DESC, log_id DESC, race_id, race_racer_id);

索引逻辑：

• 先按racer_id快速定位目标车手的所有记录
• 再按created_at倒序，优先获取指定日期前的最新记录
• 包含log_id、race_id用于分组和排序，race_racer_id用于计算MAX值
• 覆盖所有查询字段，无需回表查询原数据

2. 改写查询语句（适配框架限制） #

方案A：利用索引有序性提前过滤 #

SELECT MAX(race_racer_id) AS race_racer_id
FROM (
    SELECT race_racer_id, log_id, race_id
    FROM race_racer
    WHERE racer_id = 10002093
      AND created_at < '2024-08-01'
    ORDER BY log_id DESC
) AS filtered
GROUP BY log_id, race_id
ORDER BY log_id DESC
LIMIT 25;

方案B：使用Postgres特有语法DISTINCT ON #

DISTINCT ON可按指定分组字段取排序后的第一条记录，刚好匹配“取每个(log_id, race_id)分组中最新记录”的需求，且能完美利用复合索引：

SELECT DISTINCT ON (log_id, race_id) race_racer_id
FROM race_racer
WHERE racer_id = 10002093
  AND created_at < '2024-08-01'
ORDER BY log_id DESC, race_id, race_racer_id DESC
LIMIT 25;

3. 临时应急优化 #

如果暂时无法创建新索引，可强制数据库使用racer_id索引，避免扫描大量无关数据：

SELECT MAX(p2.race_racer_id) race_racer_id
FROM race_racer p2
WHERE p2.racer_id = 10002093
AND p2.created_at < '2024-08-01'
GROUP BY p2.log_id, p2.race_id
ORDER BY p2.log_id DESC
LIMIT 25
-- 强制使用racer_id索引
INDEX race_racer_racer_id_idx;

注：此方式效果远不如复合索引，仅作临时过渡使用。

创建复合索引后，查询可直接通过索引定位目标数据，过滤记录数大幅减少，执行时间可降至毫秒级，与单场查询速度接近。

内容的提问来源于stack exchange，提问作者WebDevz