PostgreSQL中使用ANY查询UUID数组能否命中对应索引？

根因分析 #

你创建的GIN索引默认使用array_ops操作符族，仅支持数组级别的操作符（如@>包含、<@被包含、&&交集等），而你使用的'some-uuid'=ANY(duplicate_ids)是元素与数组的比较操作，不在GIN默认支持的操作符范围内，因此无法命中索引。
BTREE索引本身仅支持数组的整体排序比较（如数组全等、大小比较），完全不支持元素级的查询场景，因此也无法生效。

解决方案 #

方案1：修改查询语句（成本最低，推荐） #

将原有查询等价改写为数组包含语法，即可直接命中你已经创建的GIN索引，无需调整索引结构：

SELECT * FROM my_table WHERE duplicate_ids @> ARRAY['some-uuid']::uuid[];

该写法和原=ANY写法的返回结果完全一致，执行效率更高，适配PostgreSQL 12及以上所有版本。修改后执行EXPLAIN ANALYZE即可查看到索引命中情况，即使配置SET enable_seqscan TO off;强制测试也能正常走索引。

方案2：保留原查询语法的适配方案 #

如果受业务逻辑限制无法修改查询语句，可以安装btree_gin扩展后创建适配元素查询的GIN索引：

-- 先安装扩展
CREATE EXTENSION IF NOT EXISTS btree_gin;
-- 创建适配索引
CREATE INDEX duplicate_ids_any_idx ON my_table USING GIN (duplicate_ids array_ops);

该方案的查询性能略低于方案1，适合无法调整业务SQL的场景。

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