数组索引：创建 GIN 和 BTREE 索引

想象一下，你有个有几百万条记录的表，其中有一列是存数组的。比如说，我们有个 products 表，每个产品可以属于多个分类：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- 用来存产品分类的字符串数组
);

假如你想找出所有属于 electronics 分类的产品。直接用 @> 操作符查找，可能会导致全表扫描：

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

全表扫描（Seq Scan）真的很慢，尤其是表很大的时候。这个时候索引就能帮你把查询变快。

数组的索引类型

PostgreSQL 支持两种主要的数组索引：

1. GIN（Generalized Inverted Index） — 适合需要快速查找数组元素或者判断交集的场景。
2. BTREE（二叉树） — 适合做数组精确比较等其他操作。

下面咱们详细聊聊这两种索引。

1. GIN 索引：速度飞快

GIN（Generalized Inverted Index）这种索引特别适合下面这些操作符：

• @>（数组包含某个元素或另一个数组），
• <@（数组被包含在另一个数组里），
• &&（数组有交集）。

像这样给 categories 列建个 GIN 索引：

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

建好索引后，查询速度会明显提升。比如这个查询：

SELECT *
FROM products 
WHERE categories @> ARRAY['electronics'];

就会用到你的 GIN 索引。

有趣的小知识：GIN 索引其实就是倒排表——它记录了每个元素（比如字符串）在哪些记录里出现过。就像你在书后面看到的主题索引，可以根据页码快速找到内容。是不是很方便？

2. BTREE 索引：顺序很重要的时候

BTREE（Binary Tree）是数据库里最常见的索引类型。它适合需要精确比较数组的操作，比如：

• 判断数组是否相等 =，
• 按元素顺序比较数组（>、<）。

像这样给数组建 BTREE 索引：

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

比如下面这个查询就能用到 BTREE 索引：

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

不过要注意，BTREE 索引不适合 @> 或 <@ 这些操作符，这种情况还是用 GIN 更好。

索引的使用例子

现在咱们结合理论和实际，来看看几个例子。

1. 查找数组交集

比如我们想找出所有和 electronics、smartphones 这两个分类有关的产品，用 &&（数组交集）操作符：

SELECT *
FROM products
WHERE categories && ARRAY['electronics', 'smartphones'];

这种情况用你之前建的 GIN 索引就很合适：

CREATE INDEX idx_categories_gin
ON products USING gin(categories);

有了这个索引，查询会快很多，因为用的是倒排表。

2. 数组相等比较

如果你想找只属于 electronics 和 gadgets（顺序也要一样）这两个分类的产品，这时候用 BTREE 索引更合适：

SELECT *
FROM products
WHERE categories = ARRAY['electronics', 'gadgets'];

建个对应的索引：

CREATE INDEX idx_categories_btree
ON products USING btree(categories);

索引的性能

索引能让查询变快，但也有一些副作用。比如：

• 创建索引 需要时间和资源。如果你的表特别大，建索引可能会挺慢的。
• 更新表。每次插入新行或者更新数据，索引也要跟着更新。这会让 INSERT 和 UPDATE 操作变慢。

不过，大多数情况下，查询提速带来的好处远大于这些成本。

怎么选：GIN 还是 BTREE？

下面这张小表格可以帮你选用哪种索引：