Postgres中IN查询条目过多触发顺序扫描，需强制使用索引

索引创建背景 #

已在users_8116表的userID（character varying(1000)类型）列创建BTREE索引，定义如下：

CREATE INDEX user_userid_8116_index
    ON public.users_8116 USING btree
    ("userID" COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

案例1：IN查询条目数≤12时 #

查询语句：

EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT * FROM users_8116 WHERE "userID" in ('4', '9678', '19036', '24930', '31669', '43604', '46754', '51422', '63743', '69826', '84860', '98225') 

执行计划显示使用Bitmap Index Scan，执行耗时8.101ms：

"Gather  (cost=53214.43..947715.53 rows=554610 width=3716) (actual time=7.833..8.004 rows=0 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared hit=55 read=5"
"  I/O Timings: shared read=3.953"
"  ->  Parallel Bitmap Heap Scan on users_8116  (cost=52214.43..891254.53 rows=231088 width=3716) (actual time=1.445..1.446 rows=0 loops=3)"
"        Recheck Cond: ((""userID"")::text = ANY ('{4,9678,19036,24930,31669,43604,46754,51422,63743,69826,84860,98225}'::text[]))"
"        Buffers: shared hit=55 read=5"
"        I/O Timings: shared read=3.953"
"        ->  Bitmap Index Scan on user_userid_8116_index  (cost=0.00..52075.75 rows=554610 width=0) (actual time=4.057..4.057 rows=0 loops=1)"
"              Index Cond: ((""userID"")::text = ANY ('{4,9678,19036,24930,31669,43604,46754,51422,63743,69826,84860,98225}'::text[]))"
"              Buffers: shared hit=55 read=5"
"              I/O Timings: shared read=3.953"
"Settings: effective_cache_size = '1879920kB', jit = 'off'"
"Planning Time: 0.098 ms"
"Execution Time: 8.101 ms"

案例2：IN查询条目数>12时 #

查询语句：

EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT * FROM users_8116 WHERE "userID" in ('4', '41', '9678', '19036', '24930', '31669', '43604', '46754', '51422', '63743', '69826', '84860', '98225')

执行计划触发Parallel Seq Scan，执行耗时长达51319.331ms：

"Gather  (cost=1000.03..957253.58 rows=600827 width=3716) (actual time=51317.205..51319.302 rows=0 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared hit=96 read=838303"
"  I/O Timings: shared read=148403.770"
"  ->  Parallel Seq Scan on users_8116  (cost=0.03..896170.88 rows=250345 width=3716) (actual time=51312.410..51312.411 rows=0 loops=3)"
"        Filter: ((""userID"")::text = ANY ('{4,41,9678,19036,24930,31669,43604,46754,51422,63743,69826,84860,98225}'::text[]))"
"        Rows Removed by Filter: 3081165"
"        Buffers: shared hit=96 read=838303"
"        I/O Timings: shared read=148403.770"
"Settings: effective_cache_size = '1879920kB', jit = 'off'"
"Planning Time: 0.104 ms"
"Execution Time: 51319.331 ms"

强制使用索引的解决方案 #

1. 使用索引提示（Index Hint） #

直接在查询中指定要使用的索引，强制规划器选择目标索引：

EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 
SELECT * FROM users_8116 
WHERE "userID" in ('4', '41', '9678', '19036', '24930', '31669', '43604', '46754', '51422', '63743', '69826', '84860', '98225')
INDEX (user_userid_8116_index);

2. 临时禁用顺序扫描 #

通过事务级参数临时关闭顺序扫描，仅对当前事务有效，不会影响其他查询：

BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 
SELECT * FROM users_8116 
WHERE "userID" in ('4', '41', '9678', '19036', '24930', '31669', '43604', '46754', '51422', '63743', '69826', '84860', '98225');
COMMIT;

注意：不要全局设置enable_seqscan = off，否则会导致所有查询强制走索引，可能引发性能问题。

3. 更新表统计信息 #

若规划器选择全表扫描是因为统计信息过时，执行ANALYZE更新统计信息，帮助规划器做出更准确的成本判断：

ANALYZE public.users_8116;

4. 调整成本参数 #

降低random_page_cost参数（默认值为4），让规划器认为索引扫描的成本更低，可在会话级别临时调整：

SET random_page_cost = 1.1;

该参数表示随机读取一页的成本相对于顺序读取的倍数，设置为接近seq_page_cost（默认1）的值，会让规划器更倾向于选择索引扫描。

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