PostgreSQL含OR条件慢查询优化求助：7000万行表查询耗时6分钟

问题背景 #

Postgres 15.3服务器（128GB内存）上，针对两张大表的count查询出现严重性能异常：

• big_table_70m：约7000万行，esat_last_modified和task_last_modified字段建有B-tree索引
• other_table_50m：近5000万行，esat_last_modified和last_modified字段建有B-tree索引

带OR条件的查询耗时约6分钟，但单独执行任一条件时耗时均小于70ms，最终返回结果仅数千行。尝试添加(esat_last_modified, task_last_modified)多列索引、用CTE改写查询均无效果。

慢查询SQL #

select
    count(*)
FROM
    big_table_70m
where
    big_table_70m.esat_last_modified > (
        select
            max(esat_last_modified)
        from
            other_table_50m
    )
    OR big_table_70m.task_last_modified > (
        select
            max(last_modified)
        from
            other_table_50m
    );

快查询SQL（单条件示例） #

select
    count(*)
FROM
    big_table_70m
where
    big_table_70m.esat_last_modified > (
        select
            max(esat_last_modified)
        from
            other_table_50m
    );

执行计划对比分析 #

带OR条件的执行计划问题 #

从执行计划可见，优化器选择了Parallel Index Only Scan使用多列索引，但实际是扫描了全表70394276行后逐行过滤OR条件，导致：

• 大量磁盘IO：读取848381个共享块，IO耗时380秒，是主要耗时来源
• 未启动并行工作者（Workers Launched: 0），无法利用多核加速
• 多列索引无法同时满足两个OR条件的范围过滤——B-tree索引仅按第一列排序，第二列的范围无法通过索引快速筛选

单条件执行计划优势 #

单条件查询时，优化器直接使用对应字段的单索引，通过Parallel Index Only Scan快速定位符合条件的行：

• 仅读取10个共享块，IO耗时不到7ms
• 启动了2个并行工作者，利用多核加速
• 直接通过索引条件过滤，无需扫描全表

解决方案建议 #

1. 拆分OR条件为UNION/UNION ALL查询 #

PostgreSQL对OR条件的多范围查询优化支持有限，拆分查询可以让优化器分别利用两个单索引，再合并结果：

精确计数（处理重复行）

如果两个条件的结果集有重叠，用UNION去重后计数：

WITH max_vals AS (
    SELECT
        max(esat_last_modified) AS max_esat,
        max(last_modified) AS max_last
    FROM other_table_50m
)
SELECT COUNT(*)
FROM (
    SELECT id FROM big_table_70m, max_vals WHERE esat_last_modified > max_esat
    UNION
    SELECT id FROM big_table_70m, max_vals WHERE task_last_modified > max_last
) AS combined;

（注：id为big_table_70m的主键或唯一键，确保去重准确）

快速计数（无重叠或允许近似）

如果确认两个条件无交集，用UNION ALL更快：

WITH max_vals AS (
    SELECT
        max(esat_last_modified) AS max_esat,
        max(last_modified) AS max_last
    FROM other_table_50m
)
SELECT
    (SELECT COUNT(*) FROM big_table_70m WHERE esat_last_modified > max_esat) +
    (SELECT COUNT(*) FROM big_table_70m WHERE task_last_modified > max_last) AS total
FROM max_vals;

精确计数（减去重复部分）

如果需要精确计数且存在重叠，用容斥原理：

WITH max_vals AS (
    SELECT
        max(esat_last_modified) AS max_esat,
        max(last_modified) AS max_last
    FROM other_table_50m
)
SELECT
    (SELECT COUNT(*) FROM big_table_70m WHERE esat_last_modified > max_esat) +
    (SELECT COUNT(*) FROM big_table_70m WHERE task_last_modified > max_last) -
    (SELECT COUNT(*) FROM big_table_70m WHERE esat_last_modified > max_esat AND task_last_modified > max_last) AS total
FROM max_vals;

2. 触发位图索引合并 #

PostgreSQL支持位图扫描合并两个单索引的结果，可尝试强制优化器使用该策略（默认已开启enable_bitmapscan），但拆分查询通常更稳定：

SET enable_indexscan = off; -- 临时关闭普通索引扫描，触发位图扫描
-- 执行原查询
SET enable_indexscan = on; -- 恢复默认设置

3. 更新统计信息 #

确保表的统计信息最新，避免优化器做出错误决策：

ANALYZE big_table_70m;
ANALYZE other_table_50m;

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