PostgreSQL数组列首元素查询场景下GIN索引的优化使用

• 直接为ancestor_ids列建立普通GIN数组索引，完全无法对当前查询起到加速效果，本质属于无效索引。
• 在不改动原有表结构（不新增存储字段、不调整原有字段类型/语义）的前提下，有明确可落地的优化方案，不需要对原有表设计做任何重构。

PostgreSQL的普通GIN数组索引，核心设计目标是支持数组类专用运算符的快速匹配，比如数组重叠（&&）、数组包含（@>）、被包含（<@）这类运算。这类索引在拆分存储数组元素时，不会记录元素在原数组中的位置信息，因此根本无法响应“数组固定下标位置的元素做等值匹配”这类过滤逻辑。
你当前的查询本质是从数组中提取固定位置（第1位）的标量值做IN匹配，整个过滤逻辑和数组专用运算符无关，自然无法触发普通GIN索引的扫描流程，建了索引也还是会走全表扫描，一点性能提升都没有。

因为要求不能重新设计表结构（也就是不能调整原有列的存储方式、不能新增业务字段、不能拆表做规范化设计），最适合的优化手段是建立针对固定数组下标的表达式B树索引。很多人会误以为建索引属于“修改表结构”，实际上这类表达式索引只是独立的访问路径，既不会新增存储类字段，也不会调整原有字段的类型、语义和存储方式，完全不触发表结构变更的约束。
具体的索引创建语句为：

CREATE INDEX idx_table_ancestor_first_elem ON table ((ancestor_ids[1]));

这类索引的工作逻辑是，在构建索引时逐行提取每一行ancestor_ids[1]的实际值，按照B树结构排序存储，和你查询时的过滤表达式完全一致。当你执行原有查询：

select * from table where ancestor_ids[1] in ('multiple', 'input', 'values');

数据库可以直接走索引扫描快速定位匹配行，不需要逐行扫描全表提取数组元素做比对，通常能带来几个数量级的性能提升。

• 不要为了“用上数组专用运算符”强行改写查询逻辑，比如写成ancestor_ids @> ARRAY['multiple', 'input', 'values']，这类写法会把数组任意位置匹配到输入值的行都捞出来，根本不符合“只匹配第一个元素”的业务需求，返回结果会出现大量错误匹配。
• 不需要为了适配数组运算符刻意修改业务逻辑，只要索引的表达式和查询的过滤条件完全一致，哪怕没有用数组专用运算符，也能正常命中索引，获得索引加速的效果。
• 建索引时不要在表达式外侧加任何类型转换或者函数处理（比如大小写转换、无必要的类型强转），否则会导致索引失效，保持和查询表达式完全一致的写法即可。

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