PostgreSQL查询添加关联子查询后耗时9秒的优化方案求解

性能瓶颈原因 #

你使用的关联子查询会对主查询返回的每一条分组结果单独执行一次计数查询，若主查询分组结果量级较大，会产生大量重复的表扫描操作，最终导致耗时骤增。

优化方案 #

方案1：复用现有关联逻辑计算（最优，无额外表扫描） #

主查询已经关联了master.flip_class_master fcm，且WHERE条件已经过滤了publish_state='PUBLISHED'和is_active=true，和子查询的过滤条件完全一致，直接在聚合部分新增count(DISTINCT fcm.id)即可实现相同效果，不需要额外编写子查询。
优化后SQL：

SELECT row_number() OVER () AS id,
    count(DISTINCT fcm.id) AS total_flip_class,
    sum(
        CASE
            WHEN (fcp.viewed_duration::double precision / fcp.total_duration::double precision * 100::double precision)::integer > 20 THEN 1
            ELSE 0
        END) AS viewed_flip_class,
    avg((fcp.viewed_duration::double precision / fcp.total_duration::double precision * 100::double precision)::integer)::integer AS viewed_percentage,
    fcp.user_id,
    currm.subject_code,
    date(fcp.modified_date) AS modified_date
   FROM master.flip_class_master fcm
     JOIN master.chapter_master cm ON cm.id = fcm.chapter_id AND cm.active
     JOIN master.curriculam_master currm ON currm.id = cm.curriculam_id AND currm.active = true
     LEFT JOIN master.flipclass_progress fcp ON fcp.flip_class_masters = fcm.id
  WHERE fcm.publish_state::text = 'PUBLISHED'::text AND fcm.is_active = true
  GROUP BY fcp.user_id, currm.subject_code, (date(fcp.modified_date)), cm.id;

方案2：预聚合后关联（适合子查询过滤条件和主查询不一致的场景） #

先用CTE提前计算好每个chapter_id对应的总发布翻转课数，再和主查询的cm.id关联，整个过程仅扫描一次flip_class_master表，避免每行重复计算。
优化后SQL：

WITH chapter_flip_count AS (
    SELECT chapter_id, count(id) AS total_flip_class
    FROM master.flip_class_master
    WHERE publish_state = 'PUBLISHED' AND is_active = true
    GROUP BY chapter_id
)
SELECT row_number() OVER () AS id,
    cfc.total_flip_class,
    sum(
        CASE
            WHEN (fcp.viewed_duration::double precision / fcp.total_duration::double precision * 100::double precision)::integer > 20 THEN 1
            ELSE 0
        END) AS viewed_flip_class,
    avg((fcp.viewed_duration::double precision / fcp.total_duration::double precision * 100::double precision)::integer)::integer AS viewed_percentage,
    fcp.user_id,
    currm.subject_code,
    date(fcp.modified_date) AS modified_date
   FROM master.flip_class_master fcm
     JOIN master.chapter_master cm ON cm.id = fcm.chapter_id AND cm.active
     JOIN master.curriculam_master currm ON currm.id = cm.curriculam_id AND currm.active = true
     LEFT JOIN master.flipclass_progress fcp ON fcp.flip_class_masters = fcm.id
     LEFT JOIN chapter_flip_count cfc ON cfc.chapter_id = cm.id
  WHERE fcm.publish_state::text = 'PUBLISHED'::text AND fcm.is_active = true
  GROUP BY fcp.user_id, currm.subject_code, (date(fcp.modified_date)), cm.id, cfc.total_flip_class;

方案3：添加覆盖索引加速查询 #

无论使用哪种优化方案，都可以添加联合覆盖索引减少回表操作，进一步提升查询速度：

-- 适配所有场景的覆盖索引
CREATE INDEX idx_flip_class_chp_pub_active ON master.flip_class_master (chapter_id, publish_state, is_active) INCLUDE (id);

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