PostgreSQL中能否用数组操作实现IN/ANY运算符及性能差异

前提说明 #

测试用的表结构如下，name作为主键默认会创建唯一B树索引，也是该类表常规的索引配置：

CREATE TABLE tag (
  "name" VARCHAR(255) NOT NULL,
  PRIMARY KEY (name)
);

性能差异结论 #

四种写法存在明显性能差异，PostgreSQL优化器不能在所有场景下都选择最优执行路径，具体差异如下：

• 方案3（ANY运算符）、方案4（IN运算符）：性能最优
  这两种写法逻辑等价，优化器会直接识别为等值匹配条件，走主键B树的定点扫描，仅需要查询2个索引键，执行效率最高，无论表数据量多大都能保持微秒级响应速度。

  -- 方案3
  select * from tag where name = ANY('{"tag1","tag2"}');
  -- 方案4
  select * from tag where name IN ('tag1', 'tag2');
  

• 方案1（数组包含运算符<@）、方案2（数组重叠运算符&&）：性能远差于前两者
  这两种写法对字段name做了ARRAY[name]的函数转换，默认规则下优化器无法将这种数组运算推导为等值匹配，无法直接使用主键B树索引，只能走全表扫描。当表内数据量达到百万级时，这两种写法的执行耗时会是前两者的数百甚至上千倍。
  除非你专门为ARRAY[name]创建了GIN索引，这两种写法才能走索引，但即使如此，GIN索引的扫描成本也远高于主键B树的等值扫描，性能依然不如IN/ANY写法。

  -- 方案1
  select * from tag where ARRAY[name] <@ '{"tag1","tag2"}';
  -- 方案2
  select * from tag where ARRAY[name] && '{"tag1","tag2"}';
  

关于优化器能力的说明 #

PostgreSQL的优化器等价改写是基于固定的规则集实现的，并不会无限度做语义推导。对于这种对查询字段做了函数/运算符封装的写法，只要没有对应的规则匹配，优化器就不会自动改写为更优的执行路径，因此不要过度依赖优化器的智能性，写查询时尽量直接对原生字段写等值/范围条件，避免不必要的字段封装。

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