SQL性能优化求助：events表索引扫描效率低下及NOT EXISTS改写问题

嗨，针对你遇到的这个SQL性能瓶颈问题，我来帮你梳理下解决方案，先从你关心的NOT EXISTS改写开始，再给你一些额外的优化建议：

一、将NOT EXISTS改写为LEFT JOIN IS NULL #

原SQL里的NOT EXISTS是相关子查询，优化器很难准确估算执行行数，我们可以把它转成非相关的LEFT JOIN形式，让优化器更容易生成合理的执行计划：

SELECT DISTINCT e.eventid,
       e.objectid,
       e.clock,
       e.ns,
       e.name,
       e.severity
FROM events e
JOIN functions f ON e.objectid = f.triggerid
JOIN items i ON f.itemid = i.itemid
JOIN hosts_groups hg ON i.hostid = hg.hostid
-- 用子查询预计算所有权限不足的triggerid，再通过LEFT JOIN排除这些事件
LEFT JOIN (
    SELECT f_1.triggerid
    FROM functions f_1
    JOIN items i_1 ON f_1.itemid = i_1.itemid
    JOIN hosts_groups hgg ON i_1.hostid = hgg.hostid
    LEFT JOIN rights r ON r.id = hgg.groupid 
        AND r.groupid IN (13, 95, 129, 498, 853, 1154, 1279, 1429)
    GROUP BY f_1.triggerid, i_1.hostid
    HAVING Max(r.permission) < 2 
        OR Min(r.permission) IS NULL 
        OR Min(r.permission) = 0
) AS permission_check ON e.objectid = permission_check.triggerid
WHERE e.source = 0
  AND e.object = 0
  AND permission_check.triggerid IS NULL -- 对应原NOT EXISTS的逻辑：不存在权限不足的情况
  AND hg.groupid IN (101, 102, 191, 195, 198, 199, 200, 203, 206, 320, 324, 402, 403, 405, 406, 410, 411, 414, 415, 416, 417, 420, 421, 422, 423, 425, 426, 427, 432, 434, 435, 436, 437, 438, 441, 503, 504, 571, 1230, 1390, 1391, 1534, 1840, 1841, 2925)
  AND e.value = 1
ORDER BY e.eventid DESC
LIMIT 501;

这里我把原查询里的字符串'0'改成了数字0，避免字段类型（integer）和常量之间的隐式转换，也能帮助优化器更准确地估算行数。

二、其他优化建议 #

1. 针对性优化索引 #

• 你的events_1索引是(source, object, objectid, clock)，但主查询还用到了value=1的过滤，建议把value加到索引里，改成(source, object, value, objectid, clock)，这样索引扫描时就能直接过滤掉不符合value=1的行，减少扫描行数。
• 对子查询里的rights表，当前用的索引是rights_2，可以创建复合索引(groupid, id, permission)，这样能覆盖子查询里的过滤、关联和聚合操作，避免回表查询。
• 对hosts_groups表，子查询里需要通过hostid关联并过滤groupid，可以创建索引(hostid, groupid)，减少执行计划里的Heap Fetches开销。

2. 简化聚合逻辑，减少子查询计算量 #

原HAVING里的聚合操作可以尝试用EXISTS替代，避免GROUP BY的开销，逻辑上和原查询一致，参考写法：

SELECT f_1.triggerid
FROM functions f_1
JOIN items i_1 ON f_1.itemid = i_1.itemid
JOIN hosts_groups hgg ON i_1.hostid = hgg.hostid
WHERE 
  -- 所有权限都不足，或者没有权限记录
  NOT EXISTS (
      SELECT 1 FROM rights r 
      WHERE r.id = hgg.groupid 
        AND r.groupid IN (13, 95, 129, 498, 853, 1154, 1279, 1429)
        AND r.permission >= 2
  )
  -- 或者存在权限为0的记录
  OR EXISTS (
      SELECT 1 FROM rights r 
      WHERE r.id = hgg.groupid 
        AND r.groupid IN (13, 95, 129, 498, 853, 1154, 1279, 1429)
        AND r.permission = 0
  )

3. 调整PostgreSQL运行参数 #

• 从执行计划看，排序用了磁盘外部排序（external merge），可以临时增大work_mem参数（比如SET work_mem = '64MB';），让排序在内存中完成，减少磁盘IO开销。
• 检查effective_cache_size参数，建议设置为服务器内存的50%-70%，如果设置过小，优化器可能会选择低效的执行计划。

4. 去掉不必要的DISTINCT #

因为eventid是events表的主键，JOIN之后不会产生重复的eventid，可以直接去掉DISTINCT，减少执行计划里Unique步骤的开销。

三、关于估算不准的补充说明 #

原查询中NOT EXISTS是相关子查询，优化器很难准确估算每个外部行对应的子查询行数，转成LEFT JOIN的非相关子查询后，优化器可以先计算出所有权限不足的triggerid列表，再和主表关联，这样行数估算会更准确，进而选择更优的执行计划。

备注：内容来源于stack exchange，提问作者Umut TEKİN