为何PostgreSQL在关联列已建索引时仍选择顺序扫描？

问题背景 #

执行以下关联查询，所有关联列已创建索引，但PostgreSQL仍对ZHREPORTFILEHCSVIOLATIONMAPPER表执行顺序扫描：

SELECT * 
FROM ZHREPORTFILEHCSVIOLATIONMAPPER 
JOIN ZHREPORTFILE ON ZHREPORTFILE.REPORT_FILE_AUTO_ID = ZHREPORTFILEHCSVIOLATIONMAPPER.REPORT_FILE_AUTO_ID 
JOIN ZHHCSVIOLATION ON ZHREPORTFILEHCSVIOLATIONMAPPER.HCS_VIOLATION_AUTO_ID = ZHHCSVIOLATION.HCS_VIOLATION_AUTO_ID 
WHERE ZHREPORTFILE.BUILD_AUTO_ID = 2230691610251

执行计划：

Nested Loop  (cost=6764.26..2418291.59 rows=3949 width=439) (actual time=0.664..9689.203 rows=876 loops=1)
  Buffers: shared hit=9539 read=1233401
  ->  Hash Join  (cost=6763.83..2416447.54 rows=3949 width=171) (actual time=0.621..9686.408 rows=876 loops=1)
        Hash Cond: (zhreportfilehcsviolationmapper.report_file_auto_id = zhreportfile.report_file_auto_id)
        Buffers: shared hit=6024 read=1233401
        ->  Seq Scan on zhreportfilehcsviolationmapper  (cost=0.00..2090480.16 rows=85110416 width=24) (actual time=0.018..5667.177 rows=85746659 loops=1)
              Buffers: shared hit=5975 read=1233401
        ->  Hash  (cost=6707.39..6707.39 rows=4515 width=147) (actual time=0.480..0.480 rows=1076 loops=1)
              Buckets: 8192  Batches: 1  Memory Usage: 259kB
              Buffers: shared hit=46
              ->  Index Scan using zhreportfile_fk1_idx on zhreportfile  (cost=0.57..6707.39 rows=4515 width=147) (actual time=0.053..0.294 rows=1076 loops=1)
                    Index Cond: (build_auto_id = '2230691610251'::bigint)
                    Buffers: shared hit=46
  ->  Index Scan using zhhcsviolation_pk on zhhcsviolation  (cost=0.43..0.46 rows=1 width=268) (actual time=0.003..0.003 rows=1 loops=876)
        Index Cond: (hcs_violation_auto_id = zhreportfilehcsviolationmapper.hcs_violation_auto_id)
        Buffers: shared hit=3515
Planning time: 1.802 ms
Execution time: 9689.289 ms

核心原因 #

1. 顺序IO与随机IO的成本权衡 #

ZHREPORTFILEHCSVIOLATIONMAPPER是一张拥有8500多万行的超大表。如果走REPORT_FILE_AUTO_ID索引，需要先扫描索引定位匹配行，再回表读取数据，这会产生大量随机IO；而顺序扫描是连续读取全表数据，虽然要遍历所有行，但顺序IO的单位操作成本远低于随机IO，优化器判断这种方式的整体执行成本更低。

2. 统计信息偏差影响成本估算 #

执行计划中，优化器预估关联后返回3949行，但实际仅返回876行。如果表的统计信息过时，优化器对匹配行数的预估会出现偏差，进而错误判断：认为需要从大表中筛选出较多行，顺序扫描全表后过滤的成本比走索引更低。

3. 索引选择性不足 #

如果REPORT_FILE_AUTO_ID列的重复度极高（大量行共享同一个值），该索引的选择性就很差。对于选择性差的索引，索引扫描带来的过滤收益不足以抵消随机IO的额外开销，优化器会倾向于选择顺序扫描。

4. Hash Join的执行策略适配 #

优化器选择将ZHREPORTFILE的查询结果（1076行）构建成Hash表，再与大表做Hash Join。这种场景下，顺序扫描大表并逐行匹配Hash表，比用索引扫描大表再做嵌套循环的效率更高，因为Hash Join在处理大表连接时的开销更低。

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