PostgreSQL含text与text数组列的多列索引创建及操作符类咨询

1. 单个文本列（from）的索引 #

from是单个text类型，针对精确匹配需求，直接用B-tree索引即可，操作符类用默认的text_ops（支持=精确匹配操作）：

CREATE INDEX idx_email_from ON emails USING btree ("from" text_ops);

注：from是PostgreSQL关键字，创建索引时需要用双引号包裹列名。

2. 数组列（to、cc）的索引 #

to和cc是text[]类型，要匹配数组中是否包含指定邮箱，需用@>数组包含操作符，此时GIN索引是最优选择，对应的操作符类为text[]_ops（PostgreSQL默认自带，无需额外扩展）：

-- 给to列创建GIN索引
CREATE INDEX idx_email_to ON emails USING gin ("to" text[]_ops);
-- 给cc列创建GIN索引
CREATE INDEX idx_email_cc ON emails USING gin (cc text[]_ops);

如果你的PostgreSQL版本较新（9.4+），甚至可以省略操作符类，因为text[]类型默认的GIN操作符类就是text[]_ops，直接写USING gin ("to")即可生效。

对应的查询语句 #

使用以下SQL即可高效匹配所有包含指定邮箱的行：

SELECT * FROM emails
WHERE "from" = 'target@example.com'
OR "to" @> ARRAY['target@example.com']::text[]
OR cc @> ARRAY['target@example.com']::text[];

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