PostgreSQL数组搜索索引疑问：元素超5个为何走全表扫描？

现象说明 #

当IN条件的元素数量为5个时，查询走Bitmap Index Scan+Bitmap Heap Scan，执行效率较高；但元素数量增加到6个时，PostgreSQL自动切换为Seq Scan（全表扫描），执行时间变长。

对应的执行计划如下：

5个元素的查询及计划 #

explain select * from collection_attributes ca where ca."type" IN (10,20, 30, 60,70)

Bitmap Heap Scan on collection_attributes ca  (cost=373.71..1974.97 rows=26354 width=92) (actual time=0.669..4.707 rows=26542 loops=1)
  Recheck Cond: (type = ANY ('{10,20,30,60,70}'::integer[]))
  Heap Blocks: exact=1082
  Buffers: shared hit=1140
  ->  Bitmap Index Scan on collection_attributes_type_index  (cost=0.00..367.12 rows=26354 width=0) (actual time=0.571..0.572 rows=27597 loops=1)
        Index Cond: (type = ANY ('{10,20,30,60,70}'::integer[]))
        Buffers: shared hit=58
Planning Time: 0.059 ms
Execution Time: 5.557 ms

6个元素的查询及计划 #

explain select * from collection_attributes ca where ca."type" IN (10,20, 30, 60,70, 80);

Seq Scan on collection_attributes ca  (cost=0.00..2375.09 rows=43841 width=92) (actual time=0.006..8.495 rows=44114 loops=1)
  Filter: (type = ANY ('{10,20,30,60,70,80}'::integer[]))
  Rows Removed by Filter: 24831
  Buffers: shared hit=1173
Planning Time: 0.060 ms
Execution Time: 9.904 ms

核心原因 #

PostgreSQL的查询优化器完全基于成本估算选择执行计划，这里的切换逻辑本质是：

1. 匹配行数占比过高：6个元素的查询匹配了44114行，占表总行数（44114+24831=68945）的64%左右。当查询需要返回表中大部分数据时，索引扫描的成本会超过全表扫描——因为索引扫描需要先遍历索引定位数据，再回表读取行数据，大量的回表IO开销叠加后，反而不如直接全表扫描一次读取所有数据高效。
2. 成本估算逻辑：优化器根据表的统计信息（比如type字段各值的行数分布），分别计算索引扫描和全表扫描的预估成本。当全表扫描的预估成本更低时，就会自动切换执行计划。从你的执行计划也能看到：5个元素时索引扫描的预估成本（1974.97）低于全表扫描的基准成本；6个元素时全表扫描的预估成本（2375.09）已经比索引扫描的预估成本（按比例推算会更高）更划算。

强制走索引扫描的解决方案 #

如果业务需求必须让所有规模的IN查询都走索引，可以采用以下几种方法：

1. 临时禁用全表扫描（会话级） #

通过设置参数临时关闭全表扫描的开关，强制优化器选择索引扫描：

-- 临时禁用全表扫描
SET enable_seqscan = off;
-- 执行你的查询
select * from collection_attributes ca where ca."type" IN (10,20, 30, 60,70, 80);
-- 执行完成后恢复默认设置，避免影响其他查询
SET enable_seqscan = on;

2. 使用查询提示（需pg_hint_plan扩展） #

如果安装了pg_hint_plan扩展，可以直接在查询中指定使用目标索引：

/*+ IndexScan(ca collection_attributes_type_index) */
select * from collection_attributes ca where ca."type" IN (10,20, 30, 60,70, 80);

3. 调整成本参数，引导优化器选索引 #

PostgreSQL的random_page_cost参数默认值为4（针对机械硬盘），如果你的数据库使用SSD，可以降低这个值，让优化器更倾向于索引扫描：

-- 会话级临时调整
SET random_page_cost = 1.5;

这个参数代表随机读取一页的成本，SSD的随机读取速度远快于机械硬盘，降低该值后，优化器会认为索引扫描的IO成本更低。

4. 拆分长IN列表为多个小查询 #

把长IN列表拆分成多个小的IN查询，用UNION ALL合并结果，每个小查询匹配行数少，优化器会自动选择索引扫描：

select * from collection_attributes ca where ca."type" IN (10,20,30)
union all
select * from collection_attributes ca where ca."type" IN (60,70,80);

5. 更新表统计信息 #

如果表的统计信息过时，优化器的成本估算可能不准，重新收集统计信息后可能会做出更合理的决策：

ANALYZE collection_attributes;

内容的提问来源于stack exchange，提问作者İbrahim Halil