PostgreSQL中`my_variable IN (<长项数组>)`何时查询效率高？

1. 触发索引查找（嵌套循环）的场景 #

• IN列表元素数量较少：具体阈值由PostgreSQL的成本模型和统计数据决定，Postgres 10通常在几百条以内，15因成本模型优化阈值略有提升
• 目标表过滤列存在高效索引：比如主键、唯一索引，或选择性极高的B-tree索引，此时单条索引查找的成本远低于构建哈希表的开销
• 典型例子：用主键过滤小批量ID列表，执行计划会显示Index Scan using [主键索引名] on [表名]，逐个通过索引定位数据

2. 触发哈希表（哈希连接）的场景 #

• IN列表元素数量较多：当列表元素达到上千条时，Postgres会判断构建哈希表的总成本低于多次索引查找的累加成本
• 目标表过滤列无合适索引，或索引选择性差：比如低基数列（如性别、状态），此时顺序扫描全表+哈希匹配的效率远高于索引扫描
• Postgres 15对哈希连接的优化更显著：支持并行哈希连接，处理大列表时的性能比10版本的单进程哈希连接提升明显

另外，你常用的「创建IN列表临时表再关联原表」的方法，本质是引导优化器选择哈希连接——临时表的统计信息更明确，Postgres能更精准判断哈希连接的成本优势，避免超长IN列表可能导致的计划判断偏差。

3. Postgres 10与15的核心差异 #

• 成本模型优化：15版本对大IN列表的成本计算更精准，相比10会更早且更稳定地选择哈希连接
• 并行支持：15新增并行哈希连接，处理大表关联时能利用多核资源，速度提升明显
• 临时表统计：15会自动收集临时表的统计数据，无需手动执行ANALYZE；而10版本可能需要手动执行ANALYZE [临时表名]，才能让优化器获取准确的行数信息

4. 实用调试与优化技巧 #

• 用EXPLAIN ANALYZE查看执行计划：确认是Hash Join还是Nested Loop，以及数据扫描方式
• 超长IN列表优先用临时表关联：比直接写IN子句更稳定，且便于后续扩展（比如新增过滤条件）
• 无需给大列表临时表加索引：哈希连接的效率通常高于嵌套循环+临时表索引的组合

内容的提问来源于stack exchange，提问作者Peter Mølgaard Pallesen