PostgreSQL中JOIN关联SELECT DISTINCT ON致查询过慢的优化求助

我曾查找过相关问题但未获得有效解答，目前在PostgreSQL中执行以下查询时速度极慢——生产环境中setup_part_setting表有超1万条数据，查询耗时约10秒：

SELECT *
FROM setup s
FULL JOIN
  (SELECT *
   FROM part_model) pm ON s.p_m_part_id = pm.p_m_id
   FULL JOIN
  (SELECT *
   FROM part) p ON s.s_id = p.p_setup_id
JOIN setup_part_setting AS sps ON sps.created_at =
  (SELECT DISTINCT ON (created_at) created_at
   FROM setup_part_setting AS sps
   WHERE s.s_id = sps.s_setup_id
     AND p.p_id = sps.part_id
   ORDER BY sps.created_at DESC
   LIMIT 1)
WHERE ((brand_name ilike ANY (ARRAY [['%%']])))
  AND s.s_id IS NOT NULL
ORDER BY s.created_at DESC
LIMIT 5;

移除以下关联子查询后，查询速度大幅提升至约80ms，因此需要替换这段逻辑，实现关联setup_part_setting表中匹配setup.s_id（对应sps.s_setup_id）和part.p_id（对应sps.part_id）、且created_at最新的单行数据：

JOIN setup_part_setting AS sps ON sps.created_at =
  (SELECT DISTINCT ON (created_at) created_at
   FROM setup_part_setting AS sps
   WHERE s.s_id = sps.s_setup_id
     AND p.p_id = sps.part_id
   ORDER BY sps.created_at DESC
   LIMIT 1)

高效解决方案 #

方案1：使用窗口函数提前筛选最新记录

通过ROW_NUMBER()窗口函数，先给每个(s_setup_id, part_id)分组的记录按created_at降序排序，取每组第一条（最新）记录，再关联主查询：

WITH latest_sps AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY s_setup_id, part_id ORDER BY created_at DESC) AS rn
    FROM setup_part_setting
)
SELECT *
FROM setup s
FULL JOIN part_model pm ON s.p_m_part_id = pm.p_m_id
FULL JOIN part p ON s.s_id = p.p_setup_id
JOIN latest_sps sps ON sps.s_setup_id = s.s_id 
                    AND sps.part_id = p.p_id
                    AND sps.rn = 1
WHERE brand_name ilike ANY (ARRAY ['%%'])
  AND s.s_id IS NOT NULL
ORDER BY s.created_at DESC
LIMIT 5;

方案2：使用LATERAL横向连接

LATERAL连接允许子查询引用主查询的列，适合这种一对一的最新记录关联场景：

SELECT *
FROM setup s
FULL JOIN part_model pm ON s.p_m_part_id = pm.p_m_id
FULL JOIN part p ON s.s_id = p.p_setup_id
LEFT JOIN LATERAL (
    SELECT *
    FROM setup_part_setting sps
    WHERE sps.s_setup_id = s.s_id
      AND sps.part_id = p.p_id
    ORDER BY sps.created_at DESC
    LIMIT 1
) sps ON true
WHERE brand_name ilike ANY (ARRAY ['%%'])
  AND s.s_id IS NOT NULL
  -- 如果需要强制关联到sps记录，保留下面的条件；如果允许无匹配则移除
  AND sps.s_setup_id IS NOT NULL
ORDER BY s.created_at DESC
LIMIT 5;

关键优化建议 #

• 创建复合索引加速筛选：给setup_part_setting表创建包含关联字段和排序字段的索引，能让窗口函数或LATERAL子查询快速定位最新记录：

  CREATE INDEX idx_sps_setup_part_created ON setup_part_setting (s_setup_id, part_id, created_at DESC);
  

• 原查询中DISTINCT ON (created_at)属于冗余写法，因为已经通过ORDER BY created_at DESC LIMIT 1取到唯一的最新时间，核心性能问题在于原关联方式会触发大量嵌套循环查询，而上述两种方案都是批量处理最新记录，大幅减少查询次数。

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