配列のインデックス作成: GIN・BTREEインデックスの作り方

例えば、何百万件ものレコードがあるテーブルがあって、その中の1つのカラムが配列を保存してるとするよ。たとえば、productsテーブルがあって、各商品が複数のカテゴリに属してる場合：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    categories TEXT[] -- 商品カテゴリを保存するための文字列配列
);

例えば、electronicsカテゴリに属する全ての商品を探したいとする。単純に@>演算子を使って検索すると、テーブル全体をフルスキャンすることになるかも：

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

フルスキャン（Seq Scan）はめっちゃ遅い。特にテーブルがデカいときはね。そこでインデックスの出番！インデックスを使えば、もっと速く検索できるようになるよ。

配列用インデックスの種類

PostgreSQLでは、配列に使える主なインデックスが2種類あるんだ：

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 or BTREE?

どのインデックスを使えばいいか、ざっくりまとめた表をどうぞ：