陣列索引與運算子（`@>`、`<@`、`&&`）快速搜尋

在 PostgreSQL 裡，陣列可以讓你在一個資料表欄位裡存一堆值。這超方便，像是你要把一篇文章的標籤（tags）或產品的分類（categories）放一起的時候。 但只要你開始搜尋、篩選或交集陣列，效能就可能掉到谷底。所以這時候，陣列索引就像救星一樣。索引可以加速這些操作，例如：

• 檢查陣列有沒有某個元素，
• 找出包含指定元素的陣列，
• 檢查陣列有沒有交集。

操作陣列的運算子

在我們深入索引之前，先來搞懂幾個操作陣列的主要運算子：

@>（contains） — 檢查一個陣列是不是包含另一個陣列的所有元素。

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

這裡我們在找有 "SQL" 這個標籤的課程。

<@（is contained by） — 檢查一個陣列是不是被另一個陣列包含。

SELECT *
FROM courses
WHERE ARRAY['PostgreSQL', 'SQL'] <@ tags;

這裡我們找的是標籤包含 ARRAY['PostgreSQL', 'SQL'] 裡所有元素的課程。

&&（overlap） — 檢查兩個陣列有沒有交集。

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

這個查詢會找到有 "NoSQL" 或 "Big Data" 其中一個標籤的課程。

索引怎麼幫忙？

想像一下，你有一個 courses 表，裡面有幾百萬筆資料，你用上面那些運算子查詢。沒有索引的話，PostgreSQL 只能一筆一筆慢慢掃——這會慢到讓你懷疑人生（尤其是你等 compile 的時候）。 有了索引就不一樣了。PostgreSQL 有兩種適合陣列的索引：

1. GIN（Generalized Inverted Index） — 陣列的首選。
2. BTREE — 用來整個陣列比大小。

範例：建立陣列索引

我們來建個小表，實際測試一下。

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

加幾筆資料：

INSERT INTO courses (name, tags)
VALUES
    ('SQL 基礎', ARRAY['SQL', 'PostgreSQL', '資料庫']),
    ('Big Data 實戰', ARRAY['Hadoop', 'Big Data', 'NoSQL']),
    ('Python 開發', ARRAY['Python', 'Web', '資料']),
    ('PostgreSQL 課程', ARRAY['PostgreSQL', 'Advanced', 'SQL']);

這張表大概長這樣：

沒索引：超慢搜尋

現在假設我們要找所有有 SQL 標籤的課程。

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

這查詢雖然能跑，但資料一多就會慢到爆。PostgreSQL 會做 Sequential Scan（逐筆掃描），每一行都要檢查。

查詢結果大概會是：

建立 GIN 索引

為了加速搜尋，我們來建個 GIN 索引：

CREATE INDEX idx_courses_tags
ON courses USING GIN (tags);

再跑一次同樣的查詢：

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

這次 PostgreSQL 會用我們剛剛建的 GIN 索引，查詢速度快超多。

原本是 Sequential Scan，現在執行計畫會看到 Bitmap Index Scan：

運算子跟索引怎麼配合？

範例 1：@> 運算子

查詢：

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

GIN 索引超適合這個運算子。Postgres 會很快找出哪些資料有這個元素，直接回傳結果。

查詢結果：

範例 2：&& 運算子

查詢：

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

這個運算子會檢查陣列有沒有交集：只要 tags 裡有傳進來陣列的任一元素就會回傳。

GIN 索引又發威了——資料再多也能快快找到。

查詢結果：

索引與優化建議

用陣列時，記得這幾點：

1. 搜尋陣列內容就用 GIN 索引。比逐筆掃描快很多。
2. 只在常查詢的欄位加索引。索引會佔空間、寫入會慢，不要亂加一通。
3. 用 EXPLAIN 跟 EXPLAIN ANALYZE 來 profile 查詢，確定你的索引真的有被用到。

範例：為陣列建立索引

來看看怎麼針對不同操作建立索引，還有實際意義。

@> 運算子的索引

假設我們已經有這張 courses 表：

要加速 @>（陣列包含元素）查詢，建個 GIN 索引：

CREATE INDEX idx_courses_tags_gin
ON courses USING GIN (tags);

然後查詢：

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

結果：

@>、<@、&& 的索引

表還是跟剛剛一樣。

因為 @>、<@ 跟 && 都能用 GIN 索引加速，所以直接建一個通用索引就好：

CREATE INDEX idx_tags
ON courses USING GIN (tags);

查詢範例跟結果：

• @> — 檢查陣列有沒有指定元素：

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

---

• <@ — 檢查陣列是不是被另一個陣列包含：

SELECT *
FROM courses
WHERE tags <@ ARRAY['SQL', 'PostgreSQL', 'Advanced', 'Big Data', 'NoSQL', 'Python'];

---

• && — 檢查陣列有沒有交集：

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

來點進階的

寫個查詢，找出標籤跟 ['Python', 'SQL', 'NoSQL'] 至少有一個交集的課程：

SELECT *
FROM courses
WHERE tags && ARRAY['Python', 'SQL', 'NoSQL'];

結果：

有 GIN 索引的話，這種查詢就算資料表有幾百萬筆也能瞬間回傳。

用陣列常見的錯誤

索引沒被用到：如果你在 EXPLAIN 裡看到 Seq Scan，檢查一下索引有沒有建好，還有你用的運算子是不是支援索引。

很少用到的陣列：如果這個欄位很少查詢或常更新，索引只會佔空間，沒什麼幫助。

索引太多：索引會吃硬碟、寫入會慢，所以只建真的會用到的索引。

現在你已經有所有 PostgreSQL 陣列操作的神兵利器了——用 @>、<@、&& 跟 GIN 索引讓查詢飛起來。快去自己資料試試看吧！