JSONBデータのインデックス作成： GINと BTREEインデックスの使い方

PostgreSQLのインデックス作成って、データベース内のデータをサクッと探すための方法なんだ。もしテーブルのデータが本だったら、インデックスは図書館のカタログみたいなもので、タイトルや著者で本をすぐ見つけられる感じ。JSONBの場合はちょっとトリッキーで、データが構造化されて保存されてるから、普通の行やカラムみたいに単純じゃないんだよね。

JSONBデータが「イラストなしのハリーポッター本」くらいデカくなってくると、その中身を探すのがめっちゃ遅くなることがある。たとえば、特定のキー「status」が「delivered」になってる注文を全部探したいとき、PostgreSQLは全部のレコードをなめて探さなきゃいけない。これ、手作業でやるのは絶対イヤだよね？

そこでGINとBTREEインデックスがヒーローみたいに登場して、長い待ち時間から救ってくれるってわけ！

JSONB用のインデックスタイプ

GIN（Generalized Inverted Index）

GINインデックスは、配列やオブジェクトみたいな構造化データ用に作られてるから、JSONBにピッタリ。オブジェクト全体じゃなくて、中のキーや値ごとにインデックスを作ってくれるんだ。つまり、GINを使えば、特定のキーや値、またはその組み合わせをすぐ見つけられる。

たとえば、こんなJSONBカラムがあるとする：

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

GINインデックスは、"name"、"age"、"city"ってキーとその値を内部で紐付けてくれる。だから"name": "Alice"を探すとき、PostgreSQLはどこを見ればいいかすでに分かってる。全テーブルを走り回る必要なし！

BTREE

BTREEインデックスはもっと伝統的なやつ。値を順番に並べて、特定の値をすぐ探せるようにしてくれる。JSONBの場合、BTREEインデックスはデータが完全一致するか、固定のキーを探すときに使える（たとえば、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の中のキーや値で検索するクエリが速くなるよ。

たとえば、ordersテーブルにdataカラム（JSONB）があるとする：

インデックスなしのクエリ：

-- statusが"delivered"の注文を全部探す
SELECT * FROM orders WHERE data @> '{"status": "delivered"}';

テーブルが大きいと、このクエリはめっちゃ時間かかる。でもGINインデックスがあれば、かなり速くなるよ。

BTREEインデックスの作り方

BTREEインデックスを作るには、ちょっと工夫が必要。たいていの場合、JSONBの一部だけをインデックスしたいから、こうやる：

-- 特定のキー用に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を選ぼう。特定の値や固定キーを探すだけなら、BTREEがベストかも。

JSONBインデックスでハマりがちな罠とよくあるミス

JSONBインデックスは強力だけど、いくつか気をつけたいポイントがあるよ。

1. 必要な場所にインデックスがない。 JSONBデータをWHEREでよく使うのに、インデックスを作ってないとクエリが遅くなる。
2. インデックスの作りすぎ。 JSONBの全キーにインデックスを作りまくると、INSERTやUPDATEが遅くなる。
3. インデックスタイプの選択ミス。 クエリが複雑で@>や?を使ってるのに、BTREEインデックスを作っても意味ない。
4. パス指定の知識不足。 ネストした値をよく使うのに、特定パス（例：data->>'some_key'）用のインデックスを作ってないと、やっぱり遅い。

まとめ：どのインデックスをいつ使う？

• 配列や複雑なオブジェクトで、キーや値検索が多いならGINを使おう。
• 完全一致や特定キーだけよく使うならBTREEがオススメ。