优化PostgreSQL子查询转JOIN查询：解决1.5分钟慢查询问题

核心思路 #

针对你的需求（每个客户仅返回一行，包含客户全量字段、消息总数、两类表单的最新状态），优化方向是预聚合统计+窗口函数筛选最新记录+条件聚合合并结果，避免子查询重复计算或JOIN后产生多行数据。

优化后SQL示例 #

WITH client_message_stats AS (
    -- 预统计每个客户的消息总数，避免重复计算
    SELECT 
        client_id,
        COUNT(*) AS inboundMessages
    FROM v_message
    GROUP BY client_id
),
latest_form_status AS (
    -- 筛选每个客户、每个表单类型的最新记录
    SELECT 
        client_id,
        formTypeId,
        isReceived,
        ROW_NUMBER() OVER (
            PARTITION BY client_id, formTypeId 
            ORDER BY created_at DESC -- 按创建时间取最新，若无created_at可改用自增id
        ) AS row_rank
    FROM v_form
    WHERE formTypeId IN (1, 2)
)
SELECT 
    c.*,
    COALESCE(cms.inboundMessages, 0) AS inboundMessages,
    -- 条件聚合将两类表单状态合并到一行
    MAX(CASE WHEN lfs.formTypeId = 1 THEN lfs.isReceived END) AS isIntakeReceived,
    MAX(CASE WHEN lfs.formTypeId = 2 THEN lfs.isReceived END) AS isFollowUpReceived
FROM v_client c
LEFT JOIN client_message_stats cms 
    ON c.id = cms.client_id
LEFT JOIN latest_form_status lfs 
    ON c.id = lfs.client_id AND lfs.row_rank = 1 -- 仅关联最新的表单记录
GROUP BY c.id, cms.inboundMessages; -- 按客户ID分组，确保单条结果

关键优化点说明 #

• 预聚合消息统计：用CTE client_message_stats 一次性计算所有客户的消息总数，比嵌套子查询减少重复扫描表的次数。
• 窗口函数筛选最新表单：ROW_NUMBER() 按客户+表单类型分组，倒序排序后取第一条（row_rank=1），确保每个客户每个表单类型仅返回最新的状态。
• 条件聚合合并结果：通过MAX(CASE...)把两类表单的状态合并到同一行，避免JOIN后产生多行数据。
• LEFT JOIN+COALESCE：保证无消息/无表单的客户也能被返回，COALESCE将NULL消息数转为0，符合业务逻辑。

索引优化建议（进一步提速） #

为关联字段和过滤字段添加索引，大幅减少查询的扫描范围：

1. 消息表的客户ID索引：

CREATE INDEX idx_v_message_client_id ON v_message(client_id);

2. 表单表的复合覆盖索引（包含筛选、排序、返回字段）：

CREATE INDEX idx_v_form_client_formtype ON v_form(client_id, formTypeId) INCLUDE (created_at, isReceived);

3. 确保v_client.id是主键（默认自带唯一索引）。

内容的提问来源于stack exchange，提问作者Philipp A.