PostgreSQL含UNION的百万级查询性能优化求助

先直接回答你的核心问题：删除现有的单列B-tree索引对当前查询的优化帮助很小，甚至可能让情况变糟。我来结合你的执行计划和查询逻辑一步步分析：

问题根源分析 #

从执行计划可以看到，耗时的主要环节有两个：

1. 第二个分支的Hash Anti Join部分：这里做了并行全表扫描+重复的Bitmap索引扫描，而且Hash操作因为内存不足用到了磁盘（Batches: 64），导致大量IO耗时。
2. 最后的Unique+外部排序：UNION默认会去重并排序，这里用到了磁盘排序（external merge Disk: 142696kB），也是耗时大户。

而你现有的单列索引中，只有filtertype的索引被用到了，另外两个单列索引在当前查询中完全没发挥作用。

具体优化方案 #

1. 创建针对性的联合索引 #

这是最有效的长期优化手段，针对你的查询逻辑创建覆盖索引：

• 对于查询中filtertype = 9的分支，创建覆盖索引避免回表：

  CREATE INDEX idx_unf_filtertype_id_by ON echo_sm.usernotificationfilters (filtertype, filterid, filterby);
  

  这个索引可以直接返回查询需要的三列数据，不需要再去堆表读取，大幅减少IO。
• 对于NOT EXISTS的分支，创建联合索引加速存在性判断：

  CREATE INDEX idx_unf_id_filtertype ON echo_sm.usernotificationfilters (filterid, filtertype);
  

  这个索引可以快速判断某个filterid是否存在filtertype=9的记录，比扫表或单独索引高效得多。

2. 重构查询语句，避免重复扫描和冗余排序 #

你的UNION可以改成UNION ALL（因为两个分支的结果不会重复），同时用CTE复用查询结果，避免重复扫描表：

WITH filter9_records AS (
    SELECT filterid, filterby, filtertype
    FROM echo_sm.usernotificationfilters
    WHERE filtertype = 9
),
all_distinct_filterids AS (
    SELECT DISTINCT filterid
    FROM echo_sm.usernotificationfilters
)
-- 取所有filtertype=9的记录
SELECT * FROM filter9_records
UNION ALL
-- 取没有filtertype=9的filterid对应的固定行
SELECT 
    filterid, 
    '-1'::varchar(50) AS filterby, 
    9::integer AS filtertype
FROM all_distinct_filterids df
WHERE NOT EXISTS (
    SELECT 1 FROM filter9_records f9 
    WHERE f9.filterid = df.filterid
)
-- 如果业务不需要排序可以去掉这一行
ORDER BY filterid, filterby, filtertype;

这样做的好处：

• CTE复用了filtertype=9的查询结果，避免执行计划中重复的Bitmap扫描。
• UNION ALL不需要去重，省去了Unique和大量排序时间。

3. 临时调整内存参数缓解IO压力 #

从执行计划看，你的work_mem设置太小，导致Hash和排序操作不得不使用磁盘。可以临时调大这个参数：

-- 会话级别调整，根据服务器内存情况设置，比如64MB或128MB
SET work_mem = '64MB';

调整后，Hash操作可以在内存中完成，排序也可能变成内存排序，大幅降低IO耗时。如果效果明显，可以考虑在postgresql.conf中全局调整（注意不要设置过大影响其他查询）。

关于删除现有单列索引的建议 #

• 不要删除filtertype的单列索引：当前查询正在使用它，删除后会导致全表扫描，性能更差。
• 可以删除filterid和filterby的单列索引：这两个索引在当前查询中没用到，删除它们可以节省磁盘空间和索引维护成本（写入时的开销），但对当前查询的速度没有提升。

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