优化含子查询的PostgreSQL通知表查询请求

需求回顾 #

过滤掉存在对应取消通知（data->>'end_date'非空）的错误状态通知（status='errored'且data->>'end_date'为空），保留其余所有通知。

原查询问题分析 #

从执行计划可见：

• 主查询对notifications做全表扫描，再通过哈希子查询过滤数据
• 子查询采用嵌套循环半连接，内层重复扫描导致效率低下
• 核心瓶颈在于未利用针对性索引，且子查询层级过多

优化改写方案 #

方案1：用NOT EXISTS替代NOT IN #

PostgreSQL对EXISTS的优化更友好，逻辑更直观：

SELECT n.*
FROM notifications n
WHERE NOT EXISTS (
    SELECT 1
    FROM notifications sn
    WHERE sn.id = n.id
      AND sn.status = 'errored'
      AND sn.data->>'end_date' IS NULL
      AND EXISTS (
          SELECT 1
          FROM notifications sn2
          WHERE sn2.reference = sn.reference
            AND sn2.data->>'end_date' IS NOT NULL
      )
)

方案2：用CTE预计算需排除的引用 #

提前提取存在取消通知的reference，减少重复扫描：

WITH cancel_references AS (
    -- 先获取所有存在取消通知的唯一引用
    SELECT DISTINCT reference
    FROM notifications
    WHERE data->>'end_date' IS NOT NULL
)
SELECT n.*
FROM notifications n
WHERE NOT (
    n.status = 'errored'
    AND n.data->>'end_date' IS NULL
    AND n.reference IN (SELECT reference FROM cancel_references)
)

方案3：LEFT JOIN过滤 #

通过左连接判断需排除的记录，避免子查询嵌套：

WITH errored_candidates AS (
    -- 筛选出可能需要排除的错误通知
    SELECT id, reference
    FROM notifications
    WHERE status = 'errored'
      AND data->>'end_date' IS NULL
),
valid_cancel_refs AS (
    -- 筛选有取消通知的引用
    SELECT DISTINCT reference
    FROM notifications
    WHERE data->>'end_date' IS NOT NULL
)
SELECT n.*
FROM notifications n
LEFT JOIN errored_candidates ec ON n.id = ec.id
LEFT JOIN valid_cancel_refs vcr ON ec.reference = vcr.reference
-- 保留非错误候选，或错误候选但无对应取消通知的记录
WHERE ec.id IS NULL OR vcr.reference IS NULL

索引优化建议 #

针对查询中的过滤条件，创建以下部分索引大幅提升扫描效率：

1. 针对错误状态且无结束日期的通知：

CREATE INDEX idx_notifications_errored_no_end ON notifications (status, reference) 
WHERE data->>'end_date' IS NULL;

2. 针对有取消通知（存在结束日期）的引用：

CREATE INDEX idx_notifications_cancel_ref ON notifications (reference) 
WHERE data->>'end_date' IS NOT NULL;

效果说明 #

以上改写均简化了查询逻辑，配合针对性索引可避免全表扫描，将执行时间从秒级压缩至毫秒级。测试时可通过EXPLAIN ANALYZE对比执行计划，确认索引是否被正确命中。

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