PostgreSQL单查询中Top-N与Last-N查询优化相关问题

1. LATERAL使用合理性判断 #

你的用法完全合理。
当前你只需要针对WHERE筛选出的1.9万条figure记录关联对应的step，LATERAL的执行逻辑是逐行匹配符合条件的figure，仅查询对应figure_id的step记录，完全避免了7000万行figure_step表的全量关联，比直接两次LEFT JOIN全表关联的执行效率高得多，和你的需求场景高度匹配。

2. 优化方案 #

2.1 基础优化（性价比最高）

先给figure_step创建联合覆盖索引：

CREATE INDEX idx_figure_step_status_num ON figure_step (figure_id, status, number);

该索引可以直接覆盖两个LATERAL子查询的过滤、排序、取值逻辑，不需要回表查询，单条子查询的耗时可以降到微秒级，这是当前场景下投入最小收益最大的优化手段，优化后原有SQL的性能已经足够优秀。

2.2 子查询复用方案

如果要避免两次查询figure_step，可以用窗口函数的方案，单次查询即可拿到两个目标step：

SELECT * FROM figure f
LEFT JOIN LATERAL (
    SELECT
        -- 按需提取DELAYED step的字段
        MAX(id) FILTER (WHERE status = 'DELAYED' AND rn_asc = 1) AS delayed_step_id,
        MAX(number) FILTER (WHERE status = 'DELAYED' AND rn_asc = 1) AS delayed_step_number,
        -- 按需提取FINISHED step的字段
        MAX(id) FILTER (WHERE status = 'FINISHED' AND rn_desc = 1) AS finished_step_id,
        MAX(number) FILTER (WHERE status = 'FINISHED' AND rn_desc = 1) AS finished_step_number
    FROM (
        SELECT 
            id, number, status,
            ROW_NUMBER() OVER (PARTITION BY status ORDER BY number ASC) AS rn_asc,
            ROW_NUMBER() OVER (PARTITION BY status ORDER BY number DESC) AS rn_desc
        FROM figure_step fs
        WHERE fs.figure_id = f.id
          AND fs.status IN ('DELAYED', 'FINISHED')
    ) t
    WHERE rn_asc = 1 OR rn_desc = 1
) fs ON TRUE
WHERE ...
LIMIT ... OFFSET ...

该方案仅针对每个符合条件的figure查询一次符合状态要求的step记录，通过窗口函数计算排序后直接取两个目标值，减少了一次索引查询，单figure对应step数量越多，收益越明显。

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