陣列索引：建立 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 (Binary Tree) — 適合做精確比對陣列的操作。

我們來細看一下這兩種。

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？

這裡有個小表格，幫你選對的索引：