PostgreSQL运行缓慢查询的优化思路咨询

业务目标：筛选出id在b表中的目标行，匹配每个目标行同hashed分组下row_num+1的相邻行详情，将两行字段合并为单行返回。以下仅提供优化思路，不给出完整重写SQL。

原问题查询语句 #

SELECT rd.row_num,
    rd.id,
    rd.date,
    rd.hashed,
    rd.af,
    rd.le,
    rd.ti,
    rd.co,
    rd.row_num + 1 AS partner_row_number,
    (SELECT date FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_date,
    (SELECT id FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_id,
    (SELECT af FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_af,
    (SELECT len FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_le,
    (SELECT ti FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_ti,
    (SELECT co FROM rowedDESC
     WHERE hashed = rd.hashed AND row_num = rd.row_num + 1) AS partner_co
    FROM rowedDESC rd
    WHERE rd.id IN (SELECT id FROM b)

现有语句的核心性能瓶颈 #

• 逐字段使用关联子查询是最大开销来源：主查询每返回1行，就会独立触发6次rowedDESC表查询，主查询返回N行就会产生6N次表查找，大表场景下IO和计算开销会随结果集规模线性放大。
• 部分数据库优化器无法对IN (子查询)写法生成最优执行计划，可能出现子查询重复执行、关联顺序选择错误的问题，进一步拉高负载。
• 缺少适配索引时，过滤、关联操作都会触发全表扫描，千万级以上数据量下查询延迟会达到不可用的程度。

可落地优化方向 #

• 消除重复表访问，降低查表次数
  放弃逐字段写子查询取相邻行的模式，通过一次匹配拿到相邻行所有字段：可以使用同表自关联，一次连接匹配hashed相等、row_num为当前行+1的记录；也可以直接使用窗口函数LEAD()，按hashed分组、row_num排序直接取当前行的下一行字段值，无需额外自连接，执行开销更低。
• 前置过滤，缩小计算范围
  检查IN (SELECT id FROM b)的执行计划，如果出现子查询反复执行的情况，替换为EXISTS匹配或者内连接的方式做id过滤；如果b表id存在重复值，先做去重再关联，提前把不需要参与相邻行匹配的数据过滤掉，减少后续计算的数据基数。
• 搭建覆盖索引，避免回表开销
  为rowedDESC表建立联合索引，字段顺序按「关联条件>过滤条件>返回字段」优先级排列：将hashed、row_num放在索引最前列，再把查询需要返回的id、date、af、le、ti、co字段加入索引作为覆盖列，让关联、取值操作都可以直接在索引上完成，不需要回表查询主键数据。如果b表数据量较大，也可以为b表的id字段建立索引，加速过滤匹配效率。
• 执行计划校验，避免低效执行路径
  调整逻辑和索引后检查执行计划，确认不存在rowedDESC全表扫描、嵌套循环关联次数过高、不必要的文件排序/临时表生成的问题；如果rowedDESC为分区表，确认过滤条件可以触发分区裁剪，避免扫描全部分区数据。

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