JSONB資料的索引：使用 GIN和 BTREE索引

在PostgreSQL裡做索引，就是讓你能更快找到資料。如果你把資料表想像成一堆書，索引就像圖書館的目錄，讓你可以很快找到書名或作者。遇到JSONB就有點tricky，因為資料是結構化的，不是單純的行和欄。

當你的JSONB資料膨脹到像「哈利波特小說但沒插圖」那麼大時，要在這堆結構裡找東西就會變慢。比如你想找所有"status"這個key等於"delivered"的訂單，PostgreSQL得一筆一筆翻，這種事你應該也不想自己手動做吧？

這時GIN和BTREE索引就像英雄一樣來救你，讓你不用等到天荒地老！

JSONB的索引類型

GIN（Generalized Inverted Index）

GIN索引就是為了處理像陣列或物件這種結構化資料設計的，超適合JSONB。它不是把整個物件索引起來，而是把裡面的key和value都拆開來索引。這樣你就能很快找到包含特定key、value或組合的紀錄。

想像一下有個JSONB欄位長這樣：

{"name": "Alice", "age": 25, "city": "Berlin"}

GIN索引會在內部建立一個結構，把"name"、"age"、"city"這些key和它們的值都記起來。所以當你查"name": "Alice"時，PostgreSQL早就知道要去哪裡找，不用整張表亂翻。

BTREE

BTREE索引比較傳統，它會建立一個有順序的結構，讓你能很快找到特定值。對JSONB來說，BTREE通常用在你要找完全一樣的資料，或是你有固定的key（像你想比對整個JSONB物件）。

如果你的欄位裡有像這樣的JSONB物件：

{"name": "Bob", "age": 30}

當你要找整個物件完全一樣的紀錄時，BTREE索引就很有用。

{"name": "Bob", "age": 30}

怎麼幫JSONB建立索引

先來看怎麼建立GIN索引 你只需要一行魔法指令CREATE INDEX，像這樣：

-- 建立JSONB欄位的GIN索引
CREATE INDEX idx_jsonb_data ON orders USING GIN (data);

這裡：

• idx_jsonb_data — 索引的名字。
• orders — 資料表的名字。
• data — 放JSONB資料的欄位。

建立好這個索引後，查詢JSONB裡key或value的SQL都會快很多。

假設我們有個orders表，裡面有個data欄位放JSONB：

沒索引的查詢：

-- 找出所有status是"delivered"的訂單
SELECT * FROM orders WHERE data @> '{"status": "delivered"}';

如果表很大，這查詢會超慢。但有GIN索引後，速度就會快很多。

怎麼建立BTREE索引

要建立BTREE索引，做法要稍微改一下。大部分情況下，你要指定只索引JSONB裡的某個部分，不是整個物件。像這樣：

-- 幫特定key建立BTREE索引
CREATE INDEX idx_jsonb_total ON orders ((data->>'total'));

注意(data->>'total')這段。它會把JSONB物件裡的total值抓出來，然後只對這個值做索引。這樣你要找total = 100的訂單時，PostgreSQL就會用這個索引。

用同樣的資料來舉例：

查詢：

-- 找出所有total = 100的訂單
SELECT * FROM orders WHERE data->>'total' = '100';

有data->>'total'的BTREE索引，這查詢就會快很多。

GIN和BTREE比較

如果你的JSONB結構很複雜，常用@>或?這種運算子，就選GIN。如果你只查特定值或固定key，BTREE會比較適合。

JSONB索引常見陷阱和錯誤

JSONB索引很強大，但還是有幾個地雷要注意。

1. 該有索引卻沒建。 如果你常在WHERE裡用JSONB查詢，卻沒建索引，查詢會慢到爆。
2. 索引建太多。 如果你每個可能的key都建索引，反而會拖慢insert和update。
3. 選錯索引類型。 如果你的查詢很複雜，用@>或?這種運算子，卻建了BTREE索引，根本沒加速效果。
4. 不懂路徑。 如果你常查巢狀的值，卻沒針對那個路徑（像data->>'some_key'）建索引，查詢還是慢。

總結：什麼時候用哪種索引

• 如果你有陣列或複雜物件，常查key和value，就用GIN。
• 如果你只查精確值或常查固定key，就用BTREE。