PostgreSQL 数组列 + GIN 索引：用 @> 查询多标签交集

问题场景

给文章打标签，常见做法是三张表：article、tag、article_tag。查询“同时包含 A 和 B 两个标签的文章”时，要 JOIN 两次关联表，或者在子查询里做 GROUP BY ... HAVING count(*) = 2。前者会产生重复行需要 DISTINCT，后者可读性差，且标签数量一多，执行计划容易退化为多次嵌套循环。

如果标签只用于过滤和展示、不需要给标签本身维护独立属性（比如标签的创建人、颜色），可以把它直接建成 PostgreSQL 的文本数组列，并配一个 GIN 索引。查询时用数组包含操作符 @> 表达“同时包含这些标签”，语义清晰，也不需要连接。

最小可运行示例

以下脚本在任意 PostgreSQL 实例上都能执行，占位值为示例数据。

-- 1. 建表：标签存放为 text[]，默认空数组而不是 NULL
CREATE TABLE article (
    id     bigserial PRIMARY KEY,
    title  text   NOT NULL,
    tags   text[] NOT NULL DEFAULT '{}'::text[],
    -- 兜底：不允许 NULL 元素，避免 @> 语义混乱
    CONSTRAINT tags_no_null CHECK (array_position(tags, NULL) IS NULL)
);

-- 2. GIN 索引，使用默认的 array_ops 操作符类
CREATE INDEX idx_article_tags ON article USING gin (tags);

-- 3. 示例数据
INSERT INTO article (title, tags) VALUES
    ('PostgreSQL 数组入门',   ARRAY['postgresql','数组','入门']),
    ('GIN 索引原理',          ARRAY['postgresql','索引','gin']),
    ('多标签检索实践',        ARRAY['postgresql','索引','检索']),
    ('无关文章',              ARRAY['mysql','入门']);

-- 4. 交集查询：同时包含 postgresql 与 索引
SELECT id, title
FROM article
WHERE tags @> ARRAY['postgresql','索引']::text[]
ORDER BY id;

结果只返回第二、三行，且每篇文章只出现一次，天然没有重复行。

@> 的语义是“左数组包含右数组的所有元素”，顺序无关，允许左数组有多余元素，因此它正好对应多标签交集（AND）。如果要做“包含任一标签”（OR），用 && 重叠操作符，同样能走 GIN 索引。

验证索引命中

先更新统计信息，再看执行计划：

ANALYZE article;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM article
WHERE tags @> ARRAY['postgresql','索引']::text[];

期望看到形如 Bitmap Index Scan on idx_article_tags 的节点，接 Bitmap Heap Scan 与 Recheck Cond。如果没有出现索引扫描，常见原因是表太小（几行数据时顺序扫描本来就更便宜），此时可以用下面的方式确认索引本身可用：

BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN SELECT id FROM article
WHERE tags @> ARRAY['postgresql','索引']::text[];
ROLLBACK;

这里只是验证计划形态，不要在生产会话里长期关闭 enable_seqscan。真实数据量下应以 ANALYZE 之后的计划和实际耗时为准。

运行方法

用 psql 执行最直接：

psql "host=<主机> port=5432 dbname=<库名> user=<用户名>" -f tags_demo.sql

也可以在本地容器里试跑，不依赖外部服务：docker run --rm -e POSTGRES_PASSWORD=<占位密码> -p 5432:5432 postgres，然后连进去粘贴脚本。注意不要在脚本里硬编码账号口令。

边界与限制

• 标签重复：ARRAY['a','a'] 是合法值，@> 不受影响，但统计标签数量时要用 cardinality() 或先去重。若业务要求标签唯一，写入前在应用层整理，或加触发器 NEW.tags := ARRAY(SELECT DISTINCT unnest(NEW.tags))。
• 大小写与空白：'PostgreSQL' 与 'postgresql' 是两个不同元素。建议入库前统一小写、去首尾空格；需要模糊匹配时再考虑 pg_trgm，那不是本方案要解决的问题。
• 空数组与空查询：tags @> '{}' 对每一行都成立，退化为全表匹配。动态拼接 SQL 时要先判断用户是否真的选了标签，否则索引帮不上忙。
• 索引选择性：GIN 对“包含罕见标签”的查询收益最大；若某个标签几乎每篇文章都有，@> 仍会命中大量行，优化器可能改走顺序扫描，这是正常选择。
• 写入成本：GIN 索引更新比 B-tree 贵，高频批量导入时可关注 fastupdate 待处理列表对查询延迟的影响，批量写入后可用 VACUUM 或 gin_clean_pending_list 收尾。
• 更新粒度：数组是整列值，修改一个标签要重写整个数组；标签集合非常大（成百上千）时，关联表模型反而更合适。
• 不适用范围：如果需要标签的独立元数据、标签改名要级联、或者需要按标签做聚合统计报表，仍建议使用规范化关联表。

小结

当标签只是文章的附属属性、查询以“同时包含若干标签”为主时，text[] 列加 GIN 索引是一个改动量小、语义明确的方案：查询写成 tags @> ARRAY[...]，天然去重，并且能用 EXPLAIN 直观确认走了索引。代价集中在写入成本和标签集的规模上，评估清楚再落地即可。