如何优化含十万级数据的SQL IN查询性能

我需要在应用中查询特定数据集里某张表的记录，筛选条件包含多达100,000个无序且不可预测的table_id值，因此使用了IN操作符。

第一个查询（执行时间约10秒） #

SELECT col1, col2, col3, ...
FROM table 
WHERE created_at BETWEEN date1 AND date2 
AND table_id IN (1, ..., 100000)

EXPLAIN ANALYZE执行结果 #

Gather (cost=1002.79..3322893.01 rows=823578 width=24) (actual time=761.500..5453.013 rows=219908 loops=1)

Workers Planned: 2

Workers Launched: 2

Planning Time: 2.433 ms

Execution Time: 5463.276 ms

第二个查询（执行时间约4秒） #

SELECT col1, col2, col3, ... 
FROM table 
WHERE created_at BETWEEN date1 AND date2 
AND table_id = ANY (
    SELECT value 
    FROM UNNEST(ARRAY[1,......,100000]) as value 
)

EXPLAIN ANALYZE执行结果 #

Nested Loop (cost=14.52..2879232.45 rows=872265 width=24) (actual time=0.485..548.387 rows=219908 loops=1)

Planning Time: 0.938 ms

Execution Time: 558.358 ms

我已为table_id和created_at创建了BTREE索引。

疑问 #

1. 如何进一步加快该查询的速度？
2. 第二个查询是否已是最优方案？
3. 如果第二个是最优方案，使用ANY (SELECT value FROM UNNEST(ARRAY[]))是否存在未注意到的权衡或潜在问题？

第一个查询的EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS )结果 #

QUERY PLAN 
Gather  (cost=1002.79..3324157.83 rows=823891 width=24) (actual time=2059.074..5390.528 rows=219908 loops=1) 
Output: col1, col2, col3 
Workers Planned: 2 
Workers Launched: 2 
Buffers: shared hit=315848 read=522241 dirtied=35 
I/O Timings: read=1103.008 
->  Parallel Seq Scan on table_name  (cost=2.79..3240768.73 rows=343288 width=24) (actual time=2056.255..5363.859 rows=73303 loops=3) 
        Output: col1, col2, col3 
        Filter: (table_id = ANY ('{1,......, 100000}'::integer[])) AND (created_at >= date1::timestamp with time zone) AND (created_at <= date2::timestamp with time zone) 
        Rows Removed by Filter: 33444492 
        Buffers: shared hit=315848 read=522241 dirtied=35 
        I/O Timings: read=1103.008 
        Worker 0:  actual time=2054.371..5372.919 rows=69788 loops=1 
        Buffers: shared hit=105747 read=174198 dirtied=3 
        I/O Timings: read=369.176 
        Worker 1:  actual time=2055.867..5369.968 rows=78986 loops=1 
        Buffers: shared hit=105794 read=173495 
        I/O Timings: read=367.726 
Settings: effective_cache_size = '32568248kB', jit = 'off', search_path = 'public' 
Query Identifier: -2771525684851677483 
Planning Time: 1.892 ms 
Execution Time: 5403.505 ms 

第二个查询的EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS )结果 #

QUERY PLAN 
Nested Loop  (cost=14.52..2880414.99 rows=872605 width=24) (actual time=0.432..553.665 rows=219908 loops=1) 
Output: col1, col2, col3 
Buffers: shared hit=392247 dirtied=8 
->  HashAggregate  (cost=13.95..15.95 rows=200 width=4) (actual time=0.407..0.849 rows=1116 loops=1) 
        Output: value.value 
        Group Key: value.value 
        Batches: 1  Memory Usage: 145kB 
        ->  Function Scan on pg_catalog.unnest value  (cost=0.00..11.16 rows=1116 width=4) (actual time=0.071..0.143 rows=1116 loops=1) 
            Output: value.value 
            Function Call: unnest('{1,....,100000}'::integer[]) 
->  Index Scan using table_name_table_id_created_at_idx on table_name  (cost=0.57..14328.73 rows=782 width=16) (actual time=0.047..0.409 rows=197 loops=1116) 
        Output: col1, col2, col3 
        Index Cond: (ss.vehicle_id = value.value) 
        Filter: (created_at >= date1::timestamp with time zone) AND (created_at <= date2::timestamp with time zone) 
        Rows Removed by Filter: 229 
        Buffers: shared hit=392247 dirtied=8 
Settings: effective_cache_size = '32568248kB', jit = 'off', search_path = 'public' 
Query Identifier: 328163023760902820 
Planning Time: 0.374 ms 
Execution Time: 566.648 ms 

先分析两个查询的执行计划差异：

1. 第一个查询采用并行全表扫描，过滤掉3300多万条不符合条件的记录，大部分时间消耗在I/O和数据过滤上——PostgreSQL对超大IN列表的处理逻辑是转为数组后做全表过滤，未使用索引。
2. 第二个查询采用嵌套循环+索引扫描：先对数组去重（HashAggregate步骤将10万ID压缩为1116个唯一值），再对每个唯一ID通过table_name_table_id_created_at_idx索引查询，最后过滤时间范围，整体I/O开销大幅降低，执行效率提升显著。

是否为最优方案及潜在问题 #

第二个查询已是较优方案，但需注意以下几点：

• 数组去重开销：如果ID列表重复值极少，HashAggregate的开销可忽略；若重复值极多，该步骤会占用少量内存（当前仅145kB，无影响）。
• 索引顺序：从索引名table_name_table_id_created_at_idx来看，若索引顺序为(table_id, created_at)，则完全适配当前查询——先定位table_id，再在该ID下过滤时间范围，能直接命中目标记录；若索引顺序为(created_at, table_id)，效率会打折扣，建议调整为(table_id, created_at)。
• 数组大小限制：PostgreSQL对数组无硬限制，但当ID数量达百万级时，可能导致查询计划生成变慢或内存占用过高，当前10万级完全没问题。

进一步优化方向 #

1. 创建覆盖索引：若查询的col1,col2,col3未包含在现有索引中，创建包含这些字段的覆盖索引，避免回表查询主表：

CREATE INDEX idx_table_id_created_at_include ON table_name (table_id, created_at) INCLUDE (col1, col2, col3);

2. 应用层去重：在应用端先对10万ID去重，再传入查询，减少数据库端HashAggregate的开销，同时降低索引扫描次数。
3. 临时表替代数组：若ID数量持续增长至百万级，可将ID插入临时表后执行JOIN查询，临时表会自动创建索引，执行计划更稳定：

CREATE TEMP TABLE temp_ids (id INT PRIMARY KEY);
INSERT INTO temp_ids VALUES (1), (2), ..., (100000);

SELECT t.col1, t.col2, t.col3
FROM table_name t
JOIN temp_ids ti ON t.table_id = ti.id
WHERE t.created_at BETWEEN date1 AND date2;

4. 调整work_mem参数：若HashAggregate出现批量处理（Batches>1），可临时调大work_mem（如SET work_mem = '512kB';），让去重操作在内存完成，避免磁盘开销。当前Batches为1，无需调整。

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