PostgreSQL lag()函数异常跳过行 排查主键索引损坏问题

主键索引损坏不是该问题的诱因，你的查询逻辑存在设计缺陷才是返回假阳性结果的根本原因。

你原查询的逻辑漏洞出在services_in_context CTE的执行顺序上：

• 你先对service表加了service_date::DATE >= '2020-01-01'的过滤条件，再在过滤后的结果集上执行lag()窗口计算。窗口函数的计算范围永远是当前WHERE子句筛选后的结果集，不会包含被过滤掉的行。
• 举个实际场景：假设service_id=1200的记录创建于2019年12月（被WHERE条件过滤掉，不会出现在CTE结果里），下一条满足2020年日期条件的记录是service_id=1205，那么CTE里计算出的prev_id会是1200之前最后一条2020年之后的ID（比如1199），此时1199+1 到 1205-1的区间就会把1200、1201、1202、1203、1204全部判定为“缺失ID”，但1200本身是真实存在的，只是因为日期不符合筛选条件被排除在了CTE计算范围外。
• 这个逻辑漏洞完全匹配你观察到的现象：用GENERATE_SERIES()做全量ID序列匹配时假阳性消失，单独查询所谓“缺失”的ID能查到对应记录，0.5%的异常占比也和2020年前老记录ID穿插在2020年后ID区间的概率吻合。

除了上述过滤顺序错误，还有几个常见原因会导致窗口函数结果和预期不符：

• 窗口排序字段不唯一：如果ORDER BY的字段存在重复值，相同排序键的行返回顺序是数据库随机选择的，lag()的取值也会出现波动。你的场景中service_id是主键天然唯一，不存在这个问题。
• 事务快照不一致：如果查询运行时存在长事务未提交、或者快照过旧的情况，窗口函数计算时拿到的数据集和你后续单独查询单条记录的快照可能不一致，但这种情况是偶发的，不会稳定返回固定比例的异常结果。
• 索引损坏的概率极低：PostgreSQL的B树主键索引如果真的损坏，你会先遇到更明确的报错，比如唯一约束冲突、索引读取IO错误、主键查询无法命中明确存在的记录，不会仅在窗口函数计算时出现“漏行”。

要查找存在于service_log但主表不存在的service_id，直接用反连接写法，效率最高也不会出现逻辑错误：

SELECT DISTINCT log.service_id
FROM service_log log
WHERE NOT EXISTS (
    SELECT 1 
    FROM service s 
    WHERE s.service_id = log.service_id
)
-- 如果仅需要排查2020年之后的记录，在日志表上加过滤条件即可，不要提前过滤主表
AND log.service_date >= '2020-01-01'::DATE
ORDER BY log.service_id DESC;

如果你确实需要用lag()排查主键序列空洞，必须先对全量主表ID做窗口计算，再执行日期过滤，不能把过滤条件放在窗口函数之前：

WITH all_service_id AS (
    SELECT 
        service_id,
        service_date,
        LAG(service_id) OVER (ORDER BY service_id) AS prev_id
    FROM service
    -- 此处不加任何过滤，保证窗口计算覆盖全量主键
),
missing_gaps AS (
    SELECT 
        prev_id + 1 AS gap_start,
        service_id - 1 AS gap_end
    FROM all_service_id
    WHERE service_date >= '2020-01-01'::DATE
      AND prev_id != service_id - 1
)
SELECT DISTINCT log.service_id
FROM service_log log
INNER JOIN missing_gaps g
    ON log.service_id BETWEEN g.gap_start AND g.gap_end
ORDER BY log.service_id DESC;

如果你仍然怀疑主键索引存在损坏，可以通过官方工具校验，不需要盲目重建索引：

1. 启用amcheck扩展做在线索引一致性校验，校验过程不会锁表影响业务：

   CREATE EXTENSION IF NOT EXISTS amcheck;
   -- 将service_pkey替换为你实际的主键索引名
   SELECT bt_index_parent_check('service_pkey', true);
   

   如果上述查询无报错，即可100%确认主键索引结构健康，不存在损坏。
2. 如果校验真的发现索引损坏，执行在线重建即可：

   REINDEX INDEX CONCURRENTLY service_pkey;
   

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