PostgreSQL跨文本列与JSONB数组创建GIN索引加速邮箱查询

当然可以！你找对方向了——GIN索引配合jsonb_path_ops正是解决这个问题的完美方案，而且完全不需要改动现有表结构或者新建关联表。下面给你两种可行的实现方式，各有优劣，你可以根据自己的场景选择：

方案一：单表达式GIN索引（覆盖所有邮箱） #

这种方式把顶层email字段和metadata->'emails'数组里的所有邮箱合并成一个统一的JSONB数组，然后为这个组合结构创建GIN索引（用jsonb_path_ops优化匹配效率）。

创建索引 #

CREATE INDEX idx_contacts_all_emails ON contacts USING GIN (
  (jsonb_build_object('email', email) || (metadata -> 'emails')) jsonb_path_ops
);

这里的jsonb_build_object('email', email)把顶层文本邮箱转换成和metadata里一致的{"email": "xxx"}对象，再通过||操作符和metadata->'emails'数组合并成一个扁平数组。这个索引会自动随记录的增删改同步更新，完全不需要手动维护。

优化后的查询语句 #

为了用上这个索引，需要调整查询语句来匹配索引的结构：

SELECT id FROM contacts 
WHERE (jsonb_build_object('email', email) || (metadata -> 'emails')) @> '[{"email": "my@email.com"}]';

方案二：双索引组合（分别维护顶层和嵌套邮箱） #

如果你不想修改查询语句，也可以分别为顶层email和嵌套的metadata->'emails'创建独立索引，PostgreSQL会自动合并两个索引的查询结果。

创建索引 #

-- 为顶层text类型email建B树索引（适合精确匹配）
CREATE INDEX idx_contacts_email ON contacts (email);

-- 为metadata里的emails数组建GIN索引（用jsonb_path_ops优化）
CREATE INDEX idx_contacts_metadata_emails ON contacts USING GIN ((metadata -> 'emails') jsonb_path_ops);

使用原查询语句 #

你原来的查询语句完全不需要修改，PostgreSQL会自动使用位图索引扫描合并两个索引的结果，大幅提升查询速度：

SELECT id FROM contacts WHERE email = 'my@email.com' OR metadata -> 'emails' @> '[{"email": "my@email.com"}]';

为什么选jsonb_path_ops？ #

普通的GIN索引会对JSONB的所有键值对建立索引，而jsonb_path_ops只针对特定路径的键值（这里就是email键）创建紧凑的索引，体积更小、匹配速度更快，正好完美适配我们只需要匹配email字段的场景。

验证索引是否生效 #

不管用哪种方案，都可以用EXPLAIN ANALYZE来确认索引是否被正确使用：

EXPLAIN ANALYZE SELECT id FROM contacts WHERE email = 'my@email.com' OR metadata -> 'emails' @> '[{"email": "my@email.com"}]';

如果输出里出现Bitmap Index Scan或者Index Scan using idx_contacts_xxx，就说明索引已经在工作了。

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