PostgreSQL：自引用表实体链首尾事件查询及性能优化

我有一张存储事件的表，每个事件属于一个分组（grp）。插入新事件时，会通过prior字段（自引用外键）关联同分组的上一个事件。表中还包含事件ID、所属分组，以及标记事件是否删除的deleted字段（已删除事件需排除）。

我需要获取每个分组的首尾事件，尝试了以下查询，结果正确但在包含7500条事件记录、4900个分组的数据库中，执行耗时约40秒。我希望了解该查询性能低下的原因，并获得基于链分析的通用优化方案（不依赖自增ID或时间戳的临时方案）：

WITH ev_priorposterior AS (SELECT grp, id, prior, (SELECT ev2.id FROM events ev2 WHERE ev2.prior = ev.id AND deleted = false) posterior
FROM events ev
WHERE deleted = FALSE
ORDER BY ev.id)

SELECT events.grp, 
(SELECT id FROM ev_priorposterior epp WHERE prior IS NULL AND events.grp = epp.grp) first_event, 
(SELECT id FROM ev_priorposterior epp WHERE posterior IS NULL AND events.grp = epp.grp) last_event
FROM events
GROUP BY grp

性能低下的原因 #

• 嵌套子查询的重复扫描：原CTE中posterior字段使用了关联子查询，会对每一条未删除的事件单独执行一次子查询，相当于7500次独立的表查询，开销极大。
• 外层分组的冗余计算：外层从events表做GROUP BY grp，但events表存在大量重复分组记录；之后每个分组又要执行两次子查询去匹配首尾事件，相当于对4900个分组各做两次全表扫描，重复计算严重。
• 缺少针对性索引：如果prior、grp、deleted字段没有组合索引，数据库每次查询都要做全表扫描，进一步放大了子查询的性能损耗。

优化方案（基于链分析的通用解法） #

核心思路是避免嵌套子查询的重复扫描，改用JOIN或聚合操作一次性定位首尾事件：

方案1：LEFT JOIN + 聚合定位首尾

SELECT
    grp,
    MAX(CASE WHEN prior IS NULL THEN id END) AS first_event,
    MAX(CASE WHEN next_id IS NULL THEN id END) AS last_event
FROM (
    SELECT
        ev.grp,
        ev.id,
        ev.prior,
        ev2.id AS next_id
    FROM events ev
    LEFT JOIN events ev2 
        ON ev2.prior = ev.id 
        AND ev2.deleted = false
    WHERE ev.deleted = false
) AS event_chain
GROUP BY grp;

将原CTE中的关联子查询改为LEFT JOIN，仅需两次表扫描（主表+关联表），再通过聚合函数一次性提取每个分组的首尾事件，彻底避免了多次子查询的重复扫描。

方案2：EXISTS判断 + 聚合（适合支持现代SQL语法的数据库）

WITH event_chain AS (
    SELECT
        grp,
        id,
        prior,
        -- 快速判断当前事件是否有后续事件
        EXISTS (
            SELECT 1 FROM events ev2 
            WHERE ev2.prior = ev.id AND ev2.deleted = false
        ) AS has_next
    FROM events ev
    WHERE ev.deleted = false
)
SELECT
    grp,
    MAX(CASE WHEN prior IS NULL THEN id END) AS first_event,
    MAX(CASE WHEN has_next = false THEN id END) AS last_event
FROM event_chain
GROUP BY grp;

用EXISTS替代原有的子查询获取后续事件，EXISTS找到匹配记录后会立即停止扫描，比SELECT id更高效；再通过聚合分组提取首尾，减少重复计算。

关键索引优化 #

无论采用哪种方案，都需要添加以下组合索引来加速查询：

• 加速后续事件的关联查询：CREATE INDEX idx_events_prior_deleted ON events(prior, deleted);
• 加速分组和首尾事件的过滤：CREATE INDEX idx_events_grp_prior_deleted ON events(grp, prior, deleted);
  这些索引能让数据库快速定位关联事件、过滤已删除记录，避免全表扫描带来的性能损耗。

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