优化ROW_NUMBER查询避免全表扫描的技术方案咨询

针对你的场景——从31M行数据中快速获取每个id在kind_of_datetime <= 'some_value'条件下的最新一条完整记录，以下是几种无需用INCLUDE包含所有字段的优化思路：

1. 利用DISTINCT ON改写查询（PostgreSQL专属） #

PostgreSQL的DISTINCT ON语法天生适配这种"分组取首条"的需求，结合你的主键结构(id, kind_of_datetime)，可以直接借助主键索引快速定位数据，避免全表扫描和窗口函数的排序开销：

SELECT DISTINCT ON (id) *
FROM T0
WHERE kind_of_datetime <= 'some_value'
ORDER BY id, kind_of_datetime DESC;

主键索引本身是按id分组、kind_of_datetime排序存储的，这条查询会顺着索引遍历，每个id只取符合条件的最后一条（即kind_of_datetime最大的那条），不需要额外的排序或聚合操作，性能会大幅提升。

2. 关联子查询定位目标行（通用数据库方案） #

如果你的数据库不支持DISTINCT ON，可以先通过子查询获取每个id符合条件的最大kind_of_datetime，再关联原表取出完整记录：

SELECT t0.*
FROM T0 t0
INNER JOIN (
    SELECT id, MAX(kind_of_datetime) AS max_kind
    FROM T0
    WHERE kind_of_datetime <= 'some_value'
    GROUP BY id
) t1 
    ON t0.id = t1.id 
    AND t0.kind_of_datetime = t1.max_kind;

你之前创建的索引index_name (id, kind_of_datetime DESC NULLS LAST)可以被子查询利用，快速计算每个id的最大kind_of_datetime；而主键(id, kind_of_datetime)能让关联时直接定位到对应的行，避免全表扫描。

3. 维护汇总表（高查询频率场景） #

如果这个查询调用频率很高，且对实时性要求不是极端严格，可以单独维护一张汇总表，存储每个id的最新（按kind_of_datetime）完整记录：

• 创建汇总表：

CREATE TABLE T0_latest (
    id text PRIMARY KEY,
    kind_of_datetime text,
    -- 其余10个text字段
);

• 定时刷新（比如用 cron 或数据库定时任务）：

INSERT INTO T0_latest
SELECT DISTINCT ON (id) *
FROM T0
ORDER BY id, kind_of_datetime DESC
ON CONFLICT (id) DO UPDATE SET
    kind_of_datetime = EXCLUDED.kind_of_datetime,
    -- 更新其余10个字段
;

• 查询时直接访问汇总表：

SELECT * FROM T0_latest WHERE kind_of_datetime <= 'some_value';

这种方式把查询的开销转移到了后台刷新任务上，前端查询几乎是瞬时的，适合查询远多于写入的场景。

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