PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：設計とパフォーマンス － Day17 インデックス応用

SQL PostgreSQL
スポンサーリンク

Day17 前半のゴール

「“B-tree 以外のインデックスが必要になる場面”をイメージできるようにする」

ここからはインデックス応用編です。
Day17 のテーマは、PostgreSQL の強みでもある「GIN インデックス」と「GiST インデックス」。

前半のゴールはこうです。
ふつうのインデックス（B-tree）が得意なこと・苦手なことを整理できる。
GIN が「配列・JSON・全文検索みたいな“中身が複数あるデータ”に強い」ことを理解する。
GiST が「距離・範囲・空間みたいな“あいまいな近さ”に強い」ことをざっくりイメージできる。

ここでは、まず GIN を中心に、「なぜ必要なのか」「どんなクエリで効くのか」を具体的に見ていきます。

まずは前提：B-treeインデックスの得意・不得意

「“＝・＜・＞・ORDER BY”は得意、“中身が複数ある”のは苦手」

SQLite や MySQL でおなじみのインデックスは、ほぼ B-tree です。
PostgreSQL でも、何も指定しなければ B-tree が使われます。

B-tree が得意なのは、こういうやつです。

WHERE id = 123
WHERE created_at >= '2024-01-01'
ORDER BY created_at DESC LIMIT 10

つまり、「1つの値に対して、＝・＜・＞・範囲・並び替え」をするクエリです。
主キー・ユニークキー・日付・数値の検索は、ほぼこれで十分です。

逆に、B-tree が苦手なのは、「1つのカラムの中に“複数の要素”が入っている」タイプのデータです。

tags TEXT[]（配列）
properties JSONB（JSON）
search_vector TSVECTOR（全文検索用ベクトル）

こういう「中にたくさん要素を持つ」カラムに対して、

WHERE 'premium' = ANY(tags)
WHERE properties @> '{"os":"iOS"}'::jsonb
WHERE search_vector @@ plainto_tsquery('english', 'postgres')

みたいなクエリを高速化したいとき、B-tree では限界があります。
そこで出てくるのが GIN / GiST です。

GINインデックスとは何か

「“中身が複数あるデータ”を逆引きするためのインデックス」

GIN は「Generalized Inverted Index」の略です。
ざっくり言うと、「中身の要素 → それを含む行」を逆引きするためのインデックスです。

イメージで言うと、こうです。

tags = {“premium”,”beta_user”}
search_vector = ‘postgresql’ ‘databas’ ‘power’

こういう「複数の要素」を持つカラムに対して、

「’premium’ を含む行はどれ？」
「’postgres’ を含む行はどれ？」

という問いに、インデックスだけで素早く答えられるようにするのが GIN です。

B-tree が「1行につき1つの値」を前提にしているのに対して、
GIN は「1行につき複数の要素」を前提にしています。

だからこそ、配列・JSON・全文検索と相性がいい。

例題1：配列カラム（tags TEXT[]）に対する GIN インデックス

「= ANY や @> を一気に速くする」

Day13 で出てきた users.tags TEXT[] を思い出してください。

CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  tags  TEXT[] NOT NULL
);
SQL

「タグ ‘premium’ を持っているユーザー」を探すクエリはこうでした。

SELECT *
FROM users
WHERE 'premium' = ANY(tags);
SQL

データが少ないうちはこれでもいいですが、
ユーザーが何十万・何百万と増えてくると、全件なめるのはつらくなります。

ここで GIN インデックスの出番です。

CREATE INDEX idx_users_tags_gin
ON users
USING GIN (tags);
SQL

これだけで、

'premium' = ANY(tags)
tags @> ARRAY['premium','beta_user']

といったクエリが、インデックスを使って一気に速くなります。

GIN は、「配列の中の各要素」をインデックスに登録しておき、
「’premium’ を含む行のリスト」を逆引きできるようにしてくれます。

重要なのは、「配列全体」ではなく「配列の中身」に対して効くインデックスだ、という感覚です。

例題2：JSONBカラムに対する GIN インデックス

「@> で“このJSONを含むか”を高速に判定する」

Day11・12 でやった JSONB も、GIN のおいしいターゲットです。

例えば、こういうテーブルがあるとします。

CREATE TABLE events (
  id        SERIAL PRIMARY KEY,
  payload   JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
SQL

payload の中には、こんな JSON が入っているイメージです。

{"type": "login", "os": "iOS", "country": "JP"}

「iOS からの login イベントだけを取りたい」とき、こう書けます。

SELECT *
FROM events
WHERE payload @> '{"type":"login","os":"iOS"}'::jsonb;
SQL

この @> を速くするために、GIN インデックスを張ります。

CREATE INDEX idx_events_payload_gin
ON events
USING GIN (payload);
SQL

これで、「payload の中に {"type":"login","os":"iOS"} を含む行」を、
インデックスから一気に引けるようになります。

ここでもやはり、「JSON全体」ではなく「JSONの中のキー・値の組み合わせ」に対して効くインデックスです。

JSON を TEXT として持って LIKE 検索しているだけだと、この世界には来られません。
「JSONB＋GIN」で初めて、「構造を理解した高速検索」ができるようになります。

例題3：全文検索（tsvector）に対する GIN インデックス

「@@ を“検索エンジン並みの速さ”に近づける」

Day14 でやった全文検索も、GIN の代表的な用途です。

ALTER TABLE articles
ADD COLUMN search_vector tsvector;

UPDATE articles
SET search_vector =
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''));
SQL

この search_vector に対して、GIN インデックスを張ります。

CREATE INDEX idx_articles_search_vector_gin
ON articles
USING GIN (search_vector);
SQL

これで、

SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgres database')
ORDER BY ts_rank(search_vector, plainto_tsquery('english', 'postgres database')) DESC
LIMIT 20;
SQL

のようなクエリが、「全文検索エンジンっぽい速さ」に近づきます。

ここでも GIN は、「単語 → その単語を含む記事IDのリスト」という逆引き表を持っているイメージです。
plainto_tsquery で作られた検索語に含まれる単語ごとに、
対応する記事をインデックスから拾ってきてくれます。

全文検索で GIN を張らずに運用するのは、
正直かなりもったいないです。

GiSTインデックスとは何か（前半のざっくり版）

「“距離・範囲・近さ”を扱うための柔らかいインデックス」

GiST は「Generalized Search Tree」の略です。
こちらは、GIN と違って「中身の要素を逆引きする」というより、
「いろんな“近さ”や“範囲”を表現できる汎用的な木構造」です。

ざっくりイメージだけ持っておきましょう。

位置情報（geometry / geography）
→ 「この地点から半径1km以内」みたいな検索。

範囲型（int4range, tsrange など）
→ 「期間が重なっている予約」「数値レンジがかぶっているプラン」。

全文検索の“ランキング用”や、類似検索系

こういう、「単純な＝・＜・＞では表現しづらい“近さ”」を扱うときに、
GiST が使われます。

前半では、「GIN は“中身が複数あるデータ”」「GiST は“距離や範囲のような近さ”」というざっくりした役割分担だけ覚えておけば十分です。
後半で、もう少し具体的な例を出します。

GIN / GiST を使うときの“設計の感覚”

「“B-treeで頑張る”か“専用インデックスに任せる”かを選べる」

ここまでをまとめると、インデックス設計の選択肢はこうなります。

単純なキー検索・範囲検索・ソート
→ B-tree（デフォルト）。

配列・JSONB・全文検索（tsvector）
→ GIN（中身の要素を逆引き）。

距離・範囲・空間・あいまいな近さ
→ GiST（柔らかい検索木）。

「とりあえず全部 B-tree」ではなく、
「データの性質とクエリのパターンに合わせてインデックスの種類を選ぶ」という発想が持てると、
PostgreSQL らしい設計に一気に近づきます。

Day17 前半のまとめ

B-tree インデックスは「1行につき1つの値」に対する =・範囲・ORDER BY に強い一方、配列・JSON・全文検索のような「1カラムの中に複数の要素を持つ」データには向いていない。
GIN インデックスは、「配列の各要素」「JSONB のキー・値」「tsvector の単語」をインデックスに登録し、'premium' = ANY(tags) や payload @> '{"type":"login","os":"iOS"}'::jsonb、search_vector @@ plainto_tsquery('english', 'postgres database') のような“中身を条件にした検索”を高速化するための「逆引きインデックス」として機能する。
GiST インデックスは、「距離・範囲・空間・あいまいな近さ」を扱うための汎用的な検索木で、前半では「GIN＝中身が複数あるデータ」「GiST＝距離や範囲のような近さ」という役割分担をざっくり押さえ、後半で具体例とチューニングの感覚に踏み込んでいく――これが Day17 前半の着地点になる。

次へ
12
スポンサーリンク
タイトルとURLをコピーしました