PostgreSQL已创建对应索引仍执行并行顺序扫描问题咨询

• 匹配行占比过高：执行计划显示过滤条件仅剔除约589万行，符合条件的行约415万，占总数据量的40%以上。B树索引仅适合小数据量过滤场景，当需要读取大量数据时，索引随机IO开销远高于顺序扫描的连续IO，PostgreSQL优化器判定并行顺序扫描成本更低。
• 索引未覆盖查询所需字段：你创建的联合索引仅包含ad_type、targeting_type两个字段，但查询需要返回targeting_text字段，且GROUP BY逻辑包含未在索引中的query字段，走索引后仍需要回表查询数据，额外增加大量随机IO开销，进一步降低了索引收益。
• 过滤条件逻辑不利于索引匹配：WHERE条件为NOT逻辑，等价于ad_type <> 'abc' OR targeting_type <> 'EXPRESSION'，OR条件对联合索引的利用效率极低，优化器更倾向选择全表扫描。
• 索引无法支持GROUP BY操作：当前索引的字段顺序无法匹配GROUP BY的字段顺序，走索引后仍需全量排序，无额外收益。

• 创建覆盖索引：将查询、GROUP BY所需的所有字段加入索引，避免回表开销，可直接触发索引仅扫描（Index Only Scan），创建语句参考：

CREATE INDEX idx_target_reports_covering ON public.target_reports USING btree (ad_type, targeting_type, targeting_text, query);

• 拆分OR条件优化：将OR条件拆分为两个无重叠的查询逻辑，通过UNION ALL合并结果，让每个子查询都可充分利用联合索引的前缀匹配能力，修改后SQL参考：

SELECT targeting_text, targeting_type, ad_type 
FROM target_reports 
WHERE ad_type <> 'abc'
UNION ALL
SELECT targeting_text, targeting_type, ad_type 
FROM target_reports 
WHERE ad_type = 'abc' AND targeting_type <> 'EXPRESSION'
GROUP BY 1,2,3,4;

• 调大work_mem参数：当前执行计划显示排序使用了磁盘外部排序，内存不足导致排序性能大幅下降，可临时调大当前会话的work_mem参数，让排序在内存中完成：

SET work_mem = '512MB';

如果需要全局生效可修改postgresql.conf配置文件中的work_mem参数。

• 更新表统计信息：统计信息过时会导致优化器行数估算错误，可执行如下命令更新表统计信息，让优化器生成更精准的执行计划：

ANALYZE public.target_reports;

• 表分区优化：如果该表数据量持续增长，可按照ad_type或者targeting_type字段做列表分区，查询时可直接过滤不需要的分区，大幅减少扫描的数据量。

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