含多LEFT JOIN的PostgreSQL求职申请SQL查询速度优化咨询

问题定位 #

当前查询耗时3秒的核心原因不是LEFT JOIN数量多，是执行逻辑顺序不合理，结合你提供的执行计划可以明确：

• 优化器当前选择从job_application表开始扫描，先取出指定client_id下所有状态为rejected的申请记录（执行计划里显示共232条），再逐层关联详情、主副档案、SSN表，最后才做SSN值匹配过滤，大量不匹配SSN的申请记录也走完了全链路关联，做了很多无用计算。
• 原SQL存在逻辑bug：将主副档案的SSN匹配条件写在WHERE子句中，会把LEFT JOIN强制转换为INNER JOIN，如果求职者只有主档案没有副档案，副档案SSN值为NULL，OR条件会直接过滤掉这类合法记录，不符合兼容主副双档案的业务规则。
• 原SQL关联条件存在笔误：job_application_id.id = applicant_details.job_application_id应为job_application.id = applicant_details.job_application_id，执行前需修正。

你提到的「先查询匹配SSN值再向上关联求职申请记录」的思路是完全正确的，是当前场景下收益最高的优化方向。

具体优化方案 #

1. 改写查询执行顺序，替换OR逻辑为UNION ALL #

SSN是高选择性字段，匹配指定加密值的SSN记录最多只有2条（主、副档案各1条），从SSN表开始过滤可以把扫描量级从数百条直接降到个位数。同时将OR条件拆为两个独立分支用UNION ALL合并，避免OR条件导致的索引失效，也不会出现LEFT JOIN被降级的问题。

优化后SQL如下：

SELECT ja.id
FROM (
    -- 匹配主档案SSN的申请记录
    SELECT ad.job_application_id
    FROM applicant_ssn ssn
    INNER JOIN applicant_details ad 
        ON ad.primary_profile_id = ssn.profile_id
    WHERE ssn.number = '[encrypted value]'

    UNION ALL

    -- 匹配副档案SSN的申请记录
    SELECT ad.job_application_id
    FROM applicant_ssn ssn
    INNER JOIN applicant_details ad 
        ON ad.secondary_profile_id = ssn.profile_id
    WHERE ssn.number = '[encrypted value]'
) matched_app
INNER JOIN job_application ja 
    ON ja.id = matched_app.job_application_id
WHERE ja.client_id = 1023
  AND ja.status = 'rejected'
ORDER BY ja.id DESC;

2. 校验索引配置 #

确保以下索引存在，就能让整个查询走全索引扫描，无需回表：

• applicant_ssn表的number字段建普通索引：用于快速定位匹配目标SSN的profile_id
• applicant_details表的primary_profile_id、secondary_profile_id字段分别建索引：用于通过profile_id快速反向定位关联的申请ID
• job_application表建(client_id, status, id)联合索引：用于快速过滤指定客户下的被拒申请，同时支持按id排序

预期效果 #

改写后执行路径会变为：先通过SSN索引定位到1-2条匹配的档案记录，再通过2-3次索引扫描拿到对应申请ID，最后从申请表过滤符合客户、状态条件的记录，正常耗时会降到10毫秒以内。

从原执行计划的成本估算来看，当前查询的总成本在6600左右，改写后总成本会降到10以内，性能提升幅度超过两个数量级。

内容的提问来源于stack exchange，提问作者Shaun Gallagher