Postgres大表关联查询索引被忽略，耗时超10分钟如何排查

问题根因分析 #

从你提供的执行计划和表结构信息来看，索引未被使用主要有以下几个明确原因：

• 统计信息严重失真：执行计划里PostgreSQL估算project_raw_data表符合过滤条件的行数为517万，但实际执行时符合条件的行数为0。优化器基于错误的统计信息判断：过滤后剩余行数占总表比例超过10%，走索引随机读取的成本远高于顺序扫描全表，因此选择了并行全表扫描。
• 可见性映射(VM)未更新：你创建的索引本身支持索引仅扫描，但如果project_raw_data表长时间没有运行autovacuum，可见性映射没有标记页面的可见状态，优化器会判定走索引需要额外回表查询堆数据，进一步拉高了索引的预估成本。
• IO成本参数配置不符合实际存储：PostgreSQL默认random_page_cost=4、seq_page_cost=1，是针对机械硬盘的配置。如果你使用的是SSD存储，随机IO成本远低于机械盘，过高的random_page_cost会让优化器过度倾向于顺序扫描。
• 索引过滤后预估行数占比过高：你的索引前两个字段都是boolean类型，如果实际数据中ind_requires_processing=True、processing_error=False的占比很高，优化器会判定这个索引的过滤效果不足，不值得走。

修复方案 #

1. 首先更新表的统计信息，解决统计失真问题：

ANALYZE VERBOSE project_raw_data;

执行后再重新生成执行计划，绝大部分统计信息失真导致的索引失效问题都会被解决。
2. 如果使用SSD存储，调整实例的IO成本参数，匹配实际硬件性能：

ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET seq_page_cost = 1;
SELECT pg_reload_conf();

3. 手动触发vacuum更新可见性映射，降低索引回表的预估成本：

VACUUM ANALYZE project_raw_data;

4. 可以将现有联合索引优化为部分索引，进一步降低索引体积，提升优化器选择索引的优先级：

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_project_raw_data_processing_active ON  
project_raw_data 
USING btree (processing_job_id)
WHERE ind_requires_processing = True AND processing_error = False AND processing_job_id IS NOT NULL;

因为查询过滤条件是固定的，部分索引比你当前的联合索引体积小很多，优化器选择的概率会大幅提升。

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