PostgreSQL 10中IN与ANY性能差异异常问题咨询

这事儿其实是PostgreSQL执行计划的选择逻辑差异导致的，咱们直接从你给出的两个执行计划里拆解原因：

1. ANY + ARRAY的执行逻辑（快的核心） #

先看这个查询的执行计划：

Index Scan using cca_data_hours_pri on cca_data_hours (cost=0.28..576.79 rows=15 width=188) (actual time=0.035..0.998 rows=6 loops=1)
 Index Cond: (datetime = '2018-01-07 19:00:00'::timestamp without time zone)
 Filter: (id_web_page = ANY ('{1,2,8,3, (...))")
 Rows Removed by Filter: 5
 Buffers: shared hit=3
Planning time: 57.625 ms
Execution time: 1.065 ms

• 它先通过datetime的主键索引快速定位到仅11条符合时间条件的数据（实际返回11条，过滤掉5条后剩6条）。
• 之后直接在内存里对这11条数据做轻量过滤：检查id_web_page是否在给定数组里。这个操作几乎没额外开销，所以整体执行时间才1ms左右。

2. IN + VALUES的执行逻辑（慢的根源） #

再看这个查询的执行计划：

Hash Join (cost=439.77..472.66 rows=8 width=188) (actual time=90.806..90.858 rows=6 loops=1)
 Hash Cond: (cca_data_hours.id_web_page = "*VALUES*".column1)
 Buffers: shared hit=3
 -> Index Scan using cca_data_hours_pri on cca_data_hours (cost=0.28..33.06 rows=15 width=188) (actual time=0.035..0.060 rows=11 loops=1)
 Index Cond: (datetime = '2018-01-07 19:00:00'::timestamp without time zone)
 Buffers: shared hit=3
 -> Hash (cost=436.99..436.99 rows=200 width=4) (actual time=90.742..90.742 rows=4 loops=1)
 Buckets: 1024 Batches: 1 Memory Usage: 9kB
 -> HashAggregate (cost=434.99..436.99 rows=200 width=4) (actual time=90.709..90.717 rows=4 loops=1)
 Group Key: "*VALUES*".column1
 -> Values Scan on "*VALUES*" (cost=0.00..362.49 rows=28999 width=4) (actual time=0.008..47.056 rows=28999 loops=1)
Planning time: 53.607 ms
Execution time: 91.681 ms

• PostgreSQL把IN (VALUES (...))转换成了Hash Join操作：它先把你提供的近50k条VALUES数据做HashAggregate（去重），然后构建Hash表，再把索引扫描出来的11条数据和这个Hash表做关联匹配。
• 问题核心就在这里：虽然你实际有效匹配的ID只有4个，但PostgreSQL还是要完整处理28999行的VALUES列表，光是做HashAggregate就花了90ms左右——这完全是不必要的额外开销！毕竟咱们已经通过datetime索引把主表结果集缩小到11条了，根本没必要先处理这么大的VALUES列表。
• 而且对于只有11条的极小结果集来说，Hash Join这种重型关联操作，比直接内存过滤低效得多，这就导致整体执行时间直接飙升到91ms。

为什么PostgreSQL会做出这种选择？ #

• 当你用IN (VALUES (...))时，PostgreSQL会把VALUES当成独立的临时表，默认假设这个临时表数据量很大，所以选择了适合大表关联的Hash Join计划。但在你的场景里，主表结果集已被datetime索引大幅缩小，这个计划就显得非常笨重。
• 而ANY + ARRAY的方式，PostgreSQL会把数组当成常量值，在索引扫描之后直接做内存过滤，这种逻辑完美适配“主表结果集小+过滤列表大”的场景。

小建议 #

如果你的ID列表是动态生成的，ANY + ARRAY通常比IN + VALUES更适合这种主表结果集已被大幅缩小的场景；如果ID列表本身很小，两种方式差异不大，但当列表达到几万条级别时，ANY的优势会非常明显。

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