运行超时未完成的SQL查询优化方案咨询——附查询语句与执行计划

首先，我注意到一个关键矛盾：你提到查询已经运行了2小时，但提供的实际执行计划显示整个查询仅用了0.085ms就完成，且返回0行。这说明你提供的实际执行计划很可能不是那个长时间运行的查询的，或者你的数据库统计信息严重过时，导致优化器选择了完全错误的执行计划（比如预估rev表只有160行，但实际有几百万行，从而选择了低效的嵌套循环+全表扫描）。

先从最核心的问题入手，逐步排查优化：

1. 确认统计信息准确性 #

虽然你执行了VACUUM ANALYZE，但可能统计信息没有正确更新，或者针对大表的统计样本不足。执行以下命令检查各表的统计行数与实际行数是否匹配：

SELECT relname, reltuples AS estimated_rows, (SELECT COUNT(*) FROM public."$relname$") AS actual_rows
FROM pg_class 
WHERE relname IN ('rev', 'data', 'org_identifier');

如果estimated_rows和actual_rows差距极大（比如相差一个数量级以上），重新执行针对性的ANALYZE：

ANALYZE VERBOSE rev;
ANALYZE VERBOSE data;
ANALYZE VERBOSE org_identifier;

PostgreSQL的优化器严重依赖统计信息，过时的统计会导致完全错误的执行计划选择。

2. 优化查询结构，消除CTE优化栅栏 #

PostgreSQL在12版本之前，CTE默认是"优化栅栏"——即CTE会被当作独立的物化视图执行，优化器不会将其与后续查询合并优化。如果你的PostgreSQL版本低于12，或者即使是新版本但优化器没有自动合并，可以尝试将CTE改写为子查询，或者使用LATERAL JOIN避免重复计算：

改写为LATERAL JOIN版本（推荐） #

这个版本避免了重复计算etl部分，让优化器可以针对每行etl数据直接关联org_identifier：

SELECT etl.*, i.org_id, i.npi, i.pid
FROM (
    SELECT clm.patient_id, clm.npi, clm.provider, clm.start::DATE AS start_dt
    FROM rev
    JOIN data clm ON rev.patient_id = clm.patient_id AND rev.claim = clm.CLAIM
) etl
JOIN LATERAL (
    SELECT 
        org_id, 
        npi, 
        pid, 
        ROW_NUMBER() OVER(PARTITION BY org_id ORDER BY type, id) AS "order"
    FROM org_identifier
    WHERE 
        org_identifier.npi = etl.npi 
        AND org_identifier.pid = etl.provider 
        AND type ILIKE 'CARE%'
) i ON i."order" = 1;

移除CTE的子查询版本 #

如果你的PostgreSQL版本较老，也可以直接用嵌套子查询，让优化器有更多空间调整执行计划：

SELECT etl.*, i.org_id, i.npi, i.pid
FROM (
    SELECT clm.patient_id, clm.npi, clm.provider, clm.start::DATE AS start_dt
    FROM rev
    JOIN data clm ON rev.patient_id = clm.patient_id AND rev.claim = clm.CLAIM
) etl
JOIN (
    SELECT 
        org_id, 
        npi, 
        pid, 
        ROW_NUMBER() OVER(PARTITION BY org_id ORDER BY type, id) AS "order"
    FROM org_identifier
    JOIN (
        SELECT clm.patient_id, clm.npi, clm.provider, clm.start::DATE AS start_dt
        FROM rev
        JOIN data clm ON rev.patient_id = clm.patient_id AND rev.claim = clm.CLAIM
    ) etl_inner ON org_identifier.npi = etl_inner.npi AND org_identifier.pid = etl_inner.provider
    WHERE type ILIKE 'CARE%'
) i ON i.npi = etl.npi AND i.pid = etl.provider AND i."order" = 1;

3. 优化索引，消除不必要的排序和扫描 #

针对org_identifier的优化 #

• 你的查询中type ILIKE 'CARE%'是过滤条件，同时需要按org_id, type, id排序，可以创建部分索引+覆盖索引，既过滤数据又避免排序：

CREATE INDEX idx_org_identifier_care_org_type_id ON org_identifier (org_id, type, id)
INCLUDE (npi, pid)
WHERE type ILIKE 'CARE%';

这个索引直接包含了查询需要的所有字段，并且仅包含符合type ILIKE 'CARE%'的数据，能大幅减少扫描行数和避免排序操作。

• 检查org_identifier_pid_npi索引的顺序：当前索引是(pid, npi)，但你的查询是先匹配npi再匹配pid，如果npi的选择性更高，建议调整索引顺序为(npi, pid)，或者直接用上面的部分索引覆盖这个需求。

针对rev表的优化 #

如果rev表数据量很大，全表扫描（Seq Scan）会非常耗时。如果查询中没有过滤条件，考虑给rev表创建(patient_id, claim)的索引，让优化器可以用索引扫描替代全表扫描：

CREATE INDEX idx_rev_patient_claim ON rev (patient_id, claim);

4. 检查数据类型一致性 #

确保etl.provider和org_identifier.pid、etl.npi和org_identifier.npi的数据类型完全一致。如果存在隐式类型转换（比如一个是text，一个是varchar），会导致索引失效，强制数据库进行全表扫描或类型转换，大幅增加耗时。

5. 验证执行计划 #

修改后，执行EXPLAIN ANALYZE查看新的执行计划，重点关注：

• 是否避免了全表扫描（Seq Scan），改用索引扫描（Index Scan）
• 窗口函数部分是否使用了索引排序（Sort Key是否对应索引）
• 实际行数和预估行数是否接近

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