PostgreSQL关联最新日期行并GROUP BY查询过慢优化求助

问题描述 #

关联setup_part_setting表获取created_at最新行的逻辑导致查询速度极慢，无法正常使用。尝试过DISTINCT ON方法，性能依然很差。以下是完整查询及对应的EXPLAIN ANALYZE结果，寻求优化方案。

原查询代码 #

SELECT *
FROM setup s
FULL JOIN
  (SELECT part_type AS f_part_type,
          p_m_id AS f_p_m_id,
          YEAR AS f_year,
                  model_name AS f_model_name,
                  brand_name AS f_brand_name
   FROM part_model) pm ON s.p_m_part_id = pm.f_p_m_id
FULL JOIN
  (SELECT p_id AS p_id_1,
          p_setup_id AS p_setup_id_1,
          part_type AS part_type_1
            FROM part) p ON s.s_id = p.p_setup_id_1
AND p.part_type_1 = 'Fork'
-- 以下部分导致查询变慢
  LEFT JOIN(
                    SELECT recent.* FROM setup_part_setting
                    INNER JOIN
                    (SELECT s_p_s_id as s_p_s_id_1, 
                    part_id as part_id_1,
                    s_setup_id as s_setup_id_1, 
                    MAX("created_at") as created_at_1
                    FROM setup_part_setting sps_inner_1 GROUP BY 1) recent
                    on recent.s_p_s_id_1 = setup_part_setting.s_p_s_id
                    WHERE
                      setup_part_setting.created_at = recent.created_at_1
                    ) u
                    ON s.s_id = u.s_setup_id_1 AND p.p_id_1 = u.part_id_1   
-- 以上部分导致查询变慢         
WHERE ((f_brand_name ilike ANY (ARRAY [['%%']])
        AND f_model_name ilike ANY (ARRAY [['%%']])
        AND f_year ilike ANY (ARRAY [['%%']]))
        OR (f_brand_name IS NULL
            OR f_model_name IS NULL
            OR f_year IS NULL))
ORDER BY s.created_at DESC
LIMIT 5;

原查询执行计划（EXPLAIN ANALYZE） #

Limit  (cost=694.55..694.57 rows=5 width=277) (actual time=206.186..206.191 rows=5 loops=1)
→  Sort  (cost=694.55..710.39 rows=6334 width=277) (actual time=206.184..206.189 rows=5 loops=1)
Sort Key: s.created_at DESC
Sort Method: top-N heapsort  Memory: 27kB
→  Hash Left Join  (cost=220.19..589.35 rows=6334 width=277) (actual time=150.088..204.156 rows=6478 loops=1)
Hash Cond: ((s.s_id = sps_inner_1.s_setup_id) AND (part.p_id = sps_inner_1.part_id))
→  Hash Full Join  (cost=209.74..531.38 rows=6334 width=237) (actual time=149.736..201.489 rows=6403 loops=1)
Hash Cond: (s.s_id = part.p_setup_id)
Join Filter: (part.part_type = 'Fork'::text)
Rows Removed by Join Filter: 51
Filter: (((part_model.brand_name ~~* ANY ('{{%%}}'::text[])) AND (part_model.model_name ~~* ANY ('{{%%}}'::text[])) AND (part_model.year ~~* ANY ('{{%%}}'::text[]))) OR (part_model.brand_name IS NULL) OR (part_model.model_name IS NULL) OR (part_model.year IS NULL))
→  Hash Full Join  (cost=205.54..207.00 rows=6335 width=216) (actual time=149.685..151.961 rows=6352 loops=1)
Hash Cond: (s.p_m_part_id = part_model.p_m_id)
→  Seq Scan on setup s  (cost=0.00..1.37 rows=37 width=171) (actual time=0.012..0.022 rows=39 loops=1)
→  Hash  (cost=126.35..126.35 rows=6335 width=45) (actual time=149.643..149.644 rows=6316 loops=1)
Buckets: 8192  Batches: 1  Memory Usage: 568kB
→  Seq Scan on part_model  (cost=0.00..126.35 rows=6335 width=45) (actual time=1.005..147.254 rows=6316 loops=1)
→  Hash  (cost=2.98..2.98 rows=98 width=21) (actual time=0.039..0.040 rows=76 loops=1)
Buckets: 1024  Batches: 1  Memory Usage: 12kB
→  Seq Scan on part  (cost=0.00..2.98 rows=98 width=21) (actual time=0.012..0.026 rows=76 loops=1)
→  Hash  (cost=10.44..10.44 rows=1 width=32) (actual time=0.331..0.333 rows=191 loops=1)
Buckets: 1024  Batches: 1  Memory Usage: 20kB
→  Hash Join  (cost=6.40..10.44 rows=1 width=32) (actual time=0.184..0.289 rows=191 loops=1)
Hash Cond: ((setup_part_setting.s_p_s_id = sps_inner_1.s_p_s_id) AND (setup_part_setting.created_at = (max(sps_inner_1.created_at))))
→  Seq Scan on setup_part_setting  (cost=0.00..3.68 rows=68 width=16) (actual time=0.010..0.036 rows=191 loops=1)
→  Hash  (cost=5.38..5.38 rows=68 width=32) (actual time=0.147..0.148 rows=191 loops=1)
Buckets: 1024  Batches: 1  Memory Usage: 20kB
→  HashAggregate  (cost=4.02..4.70 rows=68 width=32) (actual time=0.085..0.114 rows=191 loops=1)
Group Key: sps_inner_1.s_p_s_id
→  Seq Scan on setup_part_setting sps_inner_1  (cost=0.00..3.68 rows=68 width=32) (actual time=0.002..0.028 rows=191 loops=1)
Planning time: 42.701 ms
Execution time: 207.047 ms

优化方案 #

1. 修正最新行获取逻辑，改用窗口函数 #

原查询按s_p_s_id分组取最新行不符合业务需求（应按s_setup_id, part_id分组），且需两次扫描setup_part_setting表。改用ROW_NUMBER()窗口函数，仅需一次扫描即可获取目标最新行：

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 
  AND p.part_type = 'Fork'
LEFT JOIN (
  SELECT 
    sps.*,
    ROW_NUMBER() OVER (PARTITION BY sps.s_setup_id, sps.part_id ORDER BY sps.created_at DESC) AS rn
  FROM setup_part_setting sps
) u ON s.s_id = u.s_setup_id AND p.p_id = u.part_id AND u.rn = 1
WHERE (
  (pm.brand_name IS NOT NULL AND pm.model_name IS NOT NULL AND pm.year IS NOT NULL)
  OR pm.brand_name IS NULL OR pm.model_name IS NULL OR pm.year IS NULL
)
ORDER BY s.created_at DESC
LIMIT 5;

2. 添加针对性索引 #

为setup_part_setting创建复合索引，让窗口函数快速定位最新行，避免全表扫描：

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

同时检查以下索引是否存在：

• setup表：s_id（主键/索引）、p_m_part_id（索引）
• part表：p_setup_id（索引）、part_type（索引，或包含p_setup_id的复合索引）
• part_model表：p_m_id（主键/索引）

3. 简化查询逻辑 #

• 去掉不必要的子查询包装，直接使用原表关联，减少查询解析开销。
• 优化WHERE条件：ilike ANY (ARRAY [['%%']])等价于IS NOT NULL，替换后避免不必要的模式匹配计算。

4. 调整JOIN类型（可选） #

如果业务允许，将FULL JOIN改为LEFT JOIN（比如setup表记录大多能匹配到part_model和part），进一步减少关联开销。

内容的提问来源于stack exchange，提问作者Anton Hörl