数组索引与性能

数组列能不能建索引、什么样的查询能走索引、更新数组为什么比更新标量贵得多——这些决定了数组在生产环境中的实际可用性。本节把 GIN 索引、intarray 扩展、更新代价与建模取舍讲清楚，并给出可验证的 EXPLAIN 示例。

前置知识

阅读本节前，建议先了解：多维数组

基础概念

GIN 索引（Generalized Inverted Index，通用倒排索引）

GIN 是 PostgreSQL 为"一个列里包含多个可索引值"设计的索引类型。数组正是这种场景：一个数组列有多个元素，每个元素都值得被索引。

GIN 的内部结构类似书末的"倒排索引"：

text

元素 → 包含该元素的行号列表
'postgresql' → [1, 2, 5, 9]
'index'      → [2, 7]
'mysql'      → [3]

查询 tags @> ARRAY['postgresql'] 时，直接取 'postgresql' 的链表即可定位候选行，不需要全表扫描。

操作符类（Operator Class）

GIN 只是索引的"框架"，真正决定"哪些操作符能走索引"的是操作符类。数组默认的操作符类是 array_ops。

索引类型	操作符类	支持的操作符	适用场景
GIN	`array_ops`（默认）	`@>` `<@` `&&` `=`	通用数组元素检索
GIN	`array_ops` + `intarray` 扩展	`@>` `<@` `&&` `=`	整数数组，更快更小
GIN	`gin__int_ops`（`intarray` 提供）	`@>` `<@` `&&`	整数数组专用
GiST	`array_ops`	`@>` `<@` `&&` `=`	极少使用

索引能力与限制对照

查询形式	能否走 GIN	说明
`tags @> ARRAY['a']`	是	最典型的索引查询
`tags <@ ARRAY['a','b']`	是	需要显式写操作符
`tags && ARRAY['a','b']`	是	重叠查询
`tags = ARRAY['a','b']`	是	精确相等
`'a' = ANY(tags)`	通常否	优化器改写能力有限，建议改写为 `@>`
`array_length(tags,1) = 3`	否	函数调用无法下推
`tags[1] = 'a'`	否	单个下标访问无法用 GIN
`tags::text LIKE '%a%'`	否	转成文本后语义已变

术语提示

倒排索引（Inverted Index）、操作符类（Operator Class）、扩展（Extension）、顺序扫描（Sequential Scan）、位图索引扫描（Bitmap Index Scan）、整行重写（Row Rewrite）。本节频繁使用这些术语。

语法与用法

创建数组 GIN 索引的语法：

text

-- 基本形式
CREATE INDEX index_name ON table_name USING GIN (array_column);

-- 指定操作符类
CREATE INDEX index_name ON table_name USING GIN (array_column array_ops);

-- 并发创建（不阻塞写入）
CREATE INDEX CONCURRENTLY index_name ON table_name USING GIN (array_column);

-- 整数数组优化：先装扩展
CREATE EXTENSION IF NOT EXISTS intarray;
CREATE INDEX index_name ON table_name USING GIN (int_array_column gin__int_ops);

准备示例数据（生成一定量数据才能看出索引效果）：

sql

DROP TABLE IF EXISTS articles;

CREATE TABLE articles (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title  TEXT   NOT NULL,
  tags   TEXT[] NOT NULL DEFAULT '{}'
);

-- 插入 10 万行，标签从固定词库中随机抽取
INSERT INTO articles (title, tags)
SELECT
  'article-' || g,
  ARRAY(
    SELECT t
    FROM unnest(ARRAY['postgresql','mysql','redis','index','query','array','json','gin','btree','vacuum']) AS t
    WHERE random() < 0.25
    ORDER BY t
  )
FROM generate_series(1, 100000) AS g;

-- 确保统计信息最新
ANALYZE articles;

SELECT count(*) AS total,
       count(*) FILTER (WHERE tags @> ARRAY['postgresql']) AS pg_cnt
FROM articles;

-- 结果：
--  total  | pg_cnt
-- --------+--------
--  100000 |  49512

详细说明

建立 GIN 索引并验证

先看没有索引时的执行计划：

sql

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM articles WHERE tags @> ARRAY['postgresql','gin'];

-- 结果（示意）：
--  Aggregate
--    ->  Seq Scan on articles
--          Filter: (tags @> '{postgresql,gin}'::text[])
--          Rows Removed by Filter: 87563
--          Buffers: shared read=...
--  Planning Time: ...
--  Execution Time: 20.xxx ms

建立 GIN 索引：

sql

CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
ANALYZE articles;

再对比同一查询：

sql

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM articles WHERE tags @> ARRAY['postgresql','gin'];

-- 结果（示意）：
--  Aggregate
--    ->  Bitmap Heap Scan on articles
--          Recheck Cond: (tags @> '{postgresql,gin}'::text[])
--          Heap Blocks: exact=...
--          ->  Bitmap Index Scan on idx_articles_tags
--                Index Cond: (tags @> '{postgresql,gin}'::text[])
--  Execution Time: 2.xxx ms

关键变化：Seq Scan 变成 Bitmap Index Scan + Bitmap Heap Scan，执行时间通常下降 5~10 倍。

注意

GIN 索引的 Recheck Cond 是必要的。GIN 是"有损"索引，可能返回假阳性行，PostgreSQL 会回表重新验证每一行。这不是性能问题，而是正确性保障。

哪些查询能走索引

依次验证各操作符：

sql

-- 1. @> 包含：走索引
EXPLAIN (COSTS OFF) SELECT id FROM articles WHERE tags @> ARRAY['redis'];
--  Bitmap Heap Scan on articles
--    Recheck Cond: (tags @> '{redis}'::text[])
--    ->  Bitmap Index Scan on idx_articles_tags
--          Index Cond: (tags @> '{redis}'::text[])

-- 2. && 重叠：走索引
EXPLAIN (COSTS OFF) SELECT id FROM articles WHERE tags && ARRAY['redis','mysql'];
--  Bitmap Heap Scan on articles
--    Recheck Cond: (tags && '{redis,mysql}'::text[])
--    ->  Bitmap Index Scan on idx_articles_tags

-- 3. <@ 被包含：走索引
EXPLAIN (COSTS OFF) SELECT id FROM articles WHERE tags <@ ARRAY['redis','mysql'];
--  Bitmap Heap Scan on articles
--    ->  Bitmap Index Scan on idx_articles_tags

-- 4. = 相等：走索引
EXPLAIN (COSTS OFF) SELECT id FROM articles WHERE tags = ARRAY['redis'];
--  Bitmap Heap Scan on articles
--    ->  Bitmap Index Scan on idx_articles_tags

以下查询不走索引：

sql

-- 函数包裹：不走索引
EXPLAIN (COSTS OFF) SELECT id FROM articles WHERE array_length(tags, 1) = 2;
--  Seq Scan on articles
--    Filter: (array_length(tags, 1) = 2)

-- 单元素下标：不走索引
EXPLAIN (COSTS OFF) SELECT id FROM articles WHERE tags[1] = 'redis';
--  Seq Scan on articles
--    Filter: (tags[1] = 'redis'::text)

-- 转文本匹配：不走索引（且语义已变）
EXPLAIN (COSTS OFF) SELECT id FROM articles WHERE tags::text LIKE '%redis%';
--  Seq Scan on articles
--    Filter: ((tags)::text ~~ '%redis%'::text)

`= ANY` 与 `@>` 的索引差异

这是一个容易踩坑的地方。

sql

-- 形式 A：= ANY（可能不走索引）
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM articles WHERE 'redis' = ANY (tags);
--  Aggregate
--    ->  Seq Scan on articles
--          Filter: ('redis'::text = ANY (tags))

-- 形式 B：@>（稳定走索引）
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM articles WHERE tags @> ARRAY['redis'];
--  Aggregate
--    ->  Bitmap Heap Scan on articles
--          Recheck Cond: (tags @> '{redis}'::text[])
--          ->  Bitmap Index Scan on idx_articles_tags

结论：需要索引加速时，把 = ANY 改写成 @>。两者语义等价，但只有后者能稳定匹配 GIN 的操作符类。

`intarray` 扩展与 `gin__int_ops`

对 integer[] / smallint[] 这类整数数组，intarray 扩展提供了专用的操作符类，索引更小、查询更快，还支持一些额外操作符。

sql

-- 安装扩展（需要超级用户或已授权的角色）
CREATE EXTENSION IF NOT EXISTS intarray;

-- 查看扩展提供的操作符类
SELECT opcname, opcintype::regtype
FROM pg_opclass
WHERE opcname IN ('array_ops', 'gin__int_ops', 'gist__int_ops', 'gist__intbig_ops');

-- 结果：
--      opcname     | opcintype
-- -----------------+-----------
--  array_ops       | anyarray
--  gin__int_ops    | integer[]
--  gist__int_ops   | integer[]
--  gist__intbig_ops| integer[]

为整数数组建专用索引：

sql

DROP TABLE IF EXISTS events;

CREATE TABLE events (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    TEXT      NOT NULL,
  room_ids INTEGER[] NOT NULL DEFAULT '{}'   -- 关联的会议室 ID
);

INSERT INTO events (name, room_ids)
SELECT 'event-' || g,
       ARRAY(SELECT (random()*100)::int FROM generate_series(1, 3))
FROM generate_series(1, 50000) AS g;

-- 通用 GIN 索引
CREATE INDEX idx_events_rooms_gin ON events USING GIN (room_ids array_ops);

-- 整数专用索引（更快）
CREATE INDEX idx_events_rooms_int ON events USING GIN (room_ids gin__int_ops);

ANALYZE events;

-- 对比执行计划
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM events WHERE room_ids @> ARRAY[42];

intarray 还提供了数组层面的额外函数（如 icount、sort、uniq、idx），以及 && 的整数专用实现：

sql

SELECT icount(ARRAY[1,2,3])    AS cnt;    -- 3（元素个数，等价于 cardinality）
SELECT sort(ARRAY[3,1,2])      AS sorted; -- {1,2,3}
SELECT uniq(ARRAY[1,1,2,2,3])  AS uniqed; -- {1,2,3}
SELECT idx(ARRAY[10,20,30], 20) AS pos;   -- 2（下标，找不到返回 0）

提示

idx 与内置的 array_position 不同：idx 找不到时返回 0，array_position 返回 NULL。

更新数组的代价

这是数组在写密集场景下的最大短板。原因在于 PostgreSQL 的 MVCC 机制：任何更新都会写出一整个新版本的行，而不管改的是一个字段还是一个数组元素。

sql

-- 只有 4 行，便于观察
DROP TABLE IF EXISTS small_demo;

CREATE TABLE small_demo (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tags TEXT[] NOT NULL DEFAULT '{}',
  note TEXT   NOT NULL DEFAULT 'x'
);

INSERT INTO small_demo (tags) VALUES ('{a,b,c}'), ('{d,e}'), ('{f}'), ('{g,h,i}');

用 xmin / ctid 观察整行重写：

sql

-- 查看当前行的物理信息
SELECT id, ctid, xmin, tags FROM small_demo ORDER BY id;

-- 结果：
--  id | ctid  | xmin  |   tags
-- ----+-------+-------+-----------
--   1 | (0,1) | 74921 | {a,b,c}
--   2 | (0,2) | 74921 | {d,e}
--   3 | (0,3) | 74921 | {f}
--   4 | (0,4) | 74921 | {g,h,i}

-- 只修改第 1 行的数组第 2 个元素
UPDATE small_demo SET tags[2] = 'B' WHERE id = 1;

-- 再次查看：ctid 与 xmin 都变了，说明整行被重写
SELECT id, ctid, xmin, tags FROM small_demo ORDER BY id;

-- 结果（ctid / xmin 变化，note 虽未修改也一并写入了新版本）：
--  id | ctid  | xmin  |   tags
-- ----+-------+-------+-----------
--   1 | (0,5) | 74925 | {a,B,c}
--   2 | (0,2) | 74921 | {d,e}
--   3 | (0,3) | 74921 | {f}
--   4 | (0,4) | 74921 | {g,h,i}

即使 note 字段没改，它也被复制进了新行版本。这带来三个后果：

写放大：数组越大，每次重写的字节数越多。
索引维护成本：GIN 索引需要为整个数组重新计算索引项（GIN 的"待处理列表"会记录变更）。
死元组（Dead Tuple）增多：需要更频繁的 VACUUM 回收空间，并可能触发表膨胀。

再看一个对比实验——追加元素 vs 更新标量：

sql

-- 模拟 1000 次追加操作（每次整行重写）
\timing on
DO $$
BEGIN
  FOR i IN 1..1000 LOOP
    UPDATE small_demo SET tags = tags || ('t' || i) WHERE id = 2;
  END LOOP;
END $$;

-- 结果（示意）：
-- Time: 420.xxx ms（1000 次整行重写 + 索引维护）

结论：单条 UPDATE ... SET tags = tags || x 是高效的，但循环里做 1000 次会非常慢。批量追加应该合并为一条 SQL：

sql

-- 推荐：一次 SQL 完成批量追加
UPDATE small_demo
SET tags = tags || ARRAY(SELECT 't' || i FROM generate_series(1, 1000) AS i)
WHERE id = 2;

数组 vs 关系表的取舍

数组并不总是好选择，关键看访问模式。

维度	数组列	关系表（+ 关联表）
读取整组值	快，单行读取	慢，需 JOIN
按元素筛选	需 GIN，中等	B-tree 精确，快
元素计数统计	需 `unnest`，较慢	`GROUP BY` 原生，快
元素排序	不便	原生 `ORDER BY`
更新单个元素	整行重写	单行更新
元素数量上限	建议几十个	无限制
元数据（如加入时间）	无法附加	可在关联表加列
外键约束	不支持	支持
存储开销	低	高（每元素一行）

选择数组：

元素数量少且固定（标签、角色、权限码）
总是整组读写，极少单独更新
需要"包含/重叠"筛选

选择关系表：

元素数量大或会持续增长
需要按元素排序、分页、附加属性
需要外键约束或引用完整性
写密集，需要更新单个元素

大数组会触发 TOAST

数组列超过约 2 KB 后会被 TOAST 到单独的存储区域，读取时需要额外解压，进一步放大开销。这一点在 TOAST 存储机制中有详细说明。

sql

-- 查看数组列的存储策略
SELECT attname, atttypid::regtype AS type,
       CASE attstorage
         WHEN 'p' THEN 'plain'
         WHEN 'e' THEN 'external'
         WHEN 'm' THEN 'main'
         WHEN 'x' THEN 'extended'
       END AS storage
FROM pg_attribute
WHERE attrelid = 'articles'::regclass AND attnum > 0;

-- 结果：
--  attname |   type   | storage
-- ---------+----------+---------
--  id      | bigint   | plain
--  title   | text     | extended
--  tags    | text[]   | extended

extended 意味着允许压缩和 TOAST。如果数组元素很少，实际不会 TOAST；元素多则会。

实战示例

用"文章标签检索"做一次完整的性能优化流程。

第 1 步：建立基准

sql

DROP TABLE IF EXISTS posts;

CREATE TABLE posts (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      TEXT   NOT NULL,
  tags       TEXT[] NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO posts (title, tags, created_at)
SELECT
  'post-' || g,
  ARRAY(
    SELECT t
    FROM unnest(ARRAY['postgresql','mysql','redis','index','query','array','json','gin']) AS t
    WHERE random() < 0.3
    ORDER BY t
  ),
  now() - (g || ' minutes')::interval
FROM generate_series(1, 200000) AS g;

ANALYZE posts;

第 2 步：无索引基线

sql

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, title FROM posts WHERE tags @> ARRAY['postgresql','gin'] LIMIT 20;

-- 结果（示意）：
--  Limit
--    ->  Seq Scan on posts
--          Filter: (tags @> '{postgresql,gin}'::text[])
--          Rows Removed by Filter: 191xxx
--  Execution Time: 45.xxx ms

第 3 步：创建 GIN 索引

sql

CREATE INDEX CONCURRENTLY idx_posts_tags_gin ON posts USING GIN (tags);
ANALYZE posts;

CONCURRENTLY 在生产环境很重要：它不会长时间持有写锁，但耗时更长且不能在事务块内执行。

第 4 步：验证优化效果

sql

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, title FROM posts WHERE tags @> ARRAY['postgresql','gin'] LIMIT 20;

-- 结果（示意）：
--  Limit
--    ->  Bitmap Heap Scan on posts
--          Recheck Cond: (tags @> '{postgresql,gin}'::text[])
--          ->  Bitmap Index Scan on idx_posts_tags_gin
--                Index Cond: (tags @> '{postgresql,gin}'::text[])
--  Execution Time: 1.xxx ms

第 5 步：组合条件与索引选择

sql

-- 标签 + 时间范围：优化器可能选时间索引，也可能选 GIN
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT id, title
FROM posts
WHERE tags @> ARRAY['postgresql']
  AND created_at > now() - interval '1 hour';

-- 结果（示意）：可能同时出现 Bitmap Index Scan（GIN）与 Index Scan（B-tree 时间索引）

若经常按"标签 + 时间"查询，可以考虑复合策略：GIN 索引负责标签筛选，B-tree 索引负责时间筛选，让优化器按选择性选择。

第 6 步：批量更新而非循环更新

sql

-- 反例：循环 100 次单行更新
DO $$
BEGIN
  FOR i IN 1..100 LOOP
    UPDATE posts SET tags = tags || 'batch' WHERE id = i;
  END LOOP;
END $$;
-- Time: 约几十毫秒（100 次整行重写）

-- 推荐：一条 SQL 完成
UPDATE posts
SET tags = tags || 'batch'
WHERE id BETWEEN 1 AND 100;
-- Time: 显著更低（1 次批量操作）

第 7 步：检查索引体积

sql

SELECT indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'posts';

-- 结果（示意）：
--       index_name       | index_size
-- -----------------------+------------
--  posts_pkey            | 4408 kB
--  idx_posts_tags_gin    | 44 MB

GIN 索引通常比表本身还大，这是倒排结构的固有代价（每个"元素-行"对都要存一条索引项）。规划磁盘时要把这部分算进去。

第 8 步：清理

sql

DROP TABLE posts;
DROP TABLE articles;
DROP TABLE events;
DROP TABLE small_demo;

注意事项

= ANY(tags) 通常不走 GIN：需要索引时改写为 tags @> ARRAY[值]。
函数包裹会失效：array_length(a,1) = 3、array_upper(a,1) > 5 等表达式无法使用 GIN 索引。
GIN 索引必须 Recheck：这是有损索引的特性，看到 Recheck Cond 不代表索引没生效。
GIN 索引体积大：通常达到表体积的数倍，需要预留磁盘空间。
GIN 写入代价高：每次更新数组都会产生待处理索引项，写密集场景需评估。
CREATE INDEX CONCURRENTLY 不能在事务里执行：它也不能与 CREATE INDEX 的其他变体混用。
数组更新总是整行重写：没有"只改一个元素"的物理优化，数组越大代价越高。
大数组会触发 TOAST：读取时有额外解压开销。
intarray 扩展需要额外安装：且只支持整数数组（int2[] / int4[] / int8[]）。
idx() 找不到返回 0，array_position() 返回 NULL：两者不能混用。
数组元素不能有外键：需要引用完整性时必须改用关系表。
不要用数组存"会持续增长"的列表：这类场景应拆表。

最佳实践

先确认查询模式再决定建模：以"按元素筛选"为主用数组 + GIN；以"元素统计分析"为主用关系表。
高频筛选列一律建 GIN 索引：CREATE INDEX CONCURRENTLY ... USING GIN (col) 是标准做法。
索引敏感路径改写为 @> / &&：避免 = ANY 造成的顺序扫描。
整数数组使用 gin__int_ops：装上 intarray 扩展后索引更小、查询更快。
批量修改合并为单条 SQL：避免在应用层循环执行 UPDATE。
控制数组元素数量：建议单个数组不超过几十个元素，超出应考虑拆表。
规划 GIN 索引的磁盘与维护成本：定期 VACUUM，关注 pg_stat_user_indexes 的体积增长。
用 EXPLAIN (ANALYZE, BUFFERS) 验证：不要凭猜测判断索引是否生效。
大数组考虑 TOAST 影响：把大数组拆成多列或改存 JSONB，可能反而更快。
写密集场景慎用数组：如果某列每天被更新上千次，关系表通常是更好的选择。

继续学习：范围类型概述

参考链接

PostgreSQL 官方文档：GIN Indexes
PostgreSQL 官方文档：Index Types
PostgreSQL 官方文档：Arrays
PostgreSQL 官方文档：intarray
PostgreSQL 官方文档：CREATE INDEX

数组索引与性能 ​

基础概念 ​

GIN 索引（Generalized Inverted Index，通用倒排索引） ​

操作符类（Operator Class） ​

索引能力与限制对照 ​

语法与用法 ​

详细说明 ​

建立 GIN 索引并验证 ​

哪些查询能走索引 ​

= ANY 与 @> 的索引差异 ​

intarray 扩展与 gin__int_ops ​

更新数组的代价 ​

数组 vs 关系表的取舍 ​

大数组会触发 TOAST ​

实战示例 ​

第 1 步：建立基准 ​

第 2 步：无索引基线 ​

第 3 步：创建 GIN 索引 ​

第 4 步：验证优化效果 ​

第 5 步：组合条件与索引选择 ​

第 6 步：批量更新而非循环更新 ​

第 7 步：检查索引体积 ​

第 8 步：清理 ​