PostgreSQL含关联与子查询的慢查询优化求助

原查询核心问题 #

你的查询存在多个性能瓶颈：子查询关联逻辑冗余、JSON字段处理重复转换、连接类型选择不合理、缺少针对性索引，导致数据量较大时触发超时。

具体优化措施 #

1. 添加针对性索引
   为过滤条件和连接字段创建索引，直接提升数据检索效率：

   -- tb1：覆盖过滤条件与常用连接字段
   CREATE INDEX idx_tb1_status_updated_at ON tb1(status, updated_at);
   CREATE INDEX idx_tb1_appointment_id ON tb1(appointment_id);
   CREATE INDEX idx_tb1_patient_id ON tb1(patient_id);
   
   -- tb2：覆盖主键与关联字段
   CREATE INDEX idx_tb2_id_owner_id ON tb2(id, appointment_owner_id);
   
   -- tb3：关联字段索引
   CREATE INDEX idx_tb3_account_id ON tb3(account_id);
   
   -- tb4：过滤非空手机号+主键
   CREATE INDEX idx_tb4_id_phone ON tb4(id) WHERE "phoneNumbers" <> '';
   

2. 替换NOT IN为NOT EXISTS
   NOT IN在处理NULL值时存在逻辑隐患，且NOT EXISTS的关联查询性能更优，能有效减少子查询的执行开销。

3. 简化JSON字段处理
   原查询中to_json(Ac.expertise::json->0->'id')::text可直接简化为(Ac.expertise::json->0->>'id')，->>运算符直接返回text类型，避免不必要的函数转换。

4. 调整连接类型
   WHERE子句中AP."phoneNumbers" <> ''会过滤掉tb4未匹配的行，因此LEFT JOIN AP可改为INNER JOIN，减少无效行的处理流程。

优化后的查询语句 #

SELECT
    AR1.patient_id,
    CONCAT(AC."firstName", ' ', AC."lastName") AS doctor_full_name,
    (AC.expertise::json->0->>'id') AS expertise_id,
    (AC.expertise::json->0->>'title') AS expertise_title,
    AP."phoneNumbers" AS mobile,
    AC.account_id,
    AC.city_id
FROM
    tb1 AS AR1
JOIN tb2 AS AA ON AR1.appointment_id = AA.id
JOIN tb3 AS AC ON AC.account_id = AA.appointment_owner_id
JOIN tb4 AS AP ON AP.id = AR1.patient_id
WHERE
    AR1.status = 'canceled'
    AND AR1.updated_at BETWEEN '2022-12-30 00:00:00' AND '2022-12-30 23:59:59'
    AND AP."phoneNumbers" <> ''
    AND NOT EXISTS (
        SELECT 1
        FROM tb1 AS AR2
        JOIN tb2 AS AA2 ON AR2.appointment_id = AA2.id
        JOIN tb3 AS AC2 ON AC2.account_id = AA2.appointment_owner_id
        WHERE
            AR2.status = 'submited'
            AND AR2.created_at >= '2022-12-30 00:00:00'
            AND AR2.patient_id = AR1.patient_id
            AND (
                (AC2.expertise::json->0->>'id') = (AC.expertise::json->0->>'id')
                OR AC2.account_id = AC.account_id
            )
    )

额外建议 #

• 若expertise字段频繁被查询，建议将其拆解为独立的关系表（如expertise表），通过外键关联tb3，彻底避免JSON解析的性能开销。
• 运行EXPLAIN ANALYZE查看查询执行计划，确认索引是否被有效利用，排查剩余性能瓶颈。

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