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

SQL PostgreSQL
スポンサーリンク

Day17 後半のゴール

「“どのクエリにどのインデックスを張るか”を自分で選べるようになる」

後半では、前半で触れた GIN / GiST を、もう一歩だけ「実務寄り」にします。
具体的なクエリと実行計画をイメージしながら、「このパターンなら GIN」「このパターンなら GiST」と選べる感覚を作っていきます。

GINインデックスを使うときの具体的な判断軸

「“中身を条件にするクエリ”があるなら、まず GIN を疑え」

前半で見た通り、GIN は「中身が複数あるデータ」に強いインデックスです。
もう少し踏み込んで、「どんなクエリがあるなら GIN を張るべきか」を整理します。

典型的なのは、次のような条件です。

tags TEXT[] に対して
WHERE 'premium' = ANY(tags)
WHERE tags @> ARRAY['premium','beta_user']

payload JSONB に対して
WHERE payload @> '{"type":"login","os":"iOS"}'::jsonb
WHERE payload ? 'os'（キーの存在チェック）

search_vector TSVECTOR に対して
WHERE search_vector @@ plainto_tsquery('english', 'postgres database')

こういう「カラムの“中身”を条件にするクエリ」が、
頻繁に・大量データに対して走るなら、GIN を真剣に検討すべきです。

逆に、「たまにしか使わない」「データ量も少ない」なら、
GIN を張らずに様子を見る選択もアリです。
GIN は強力ですが、そのぶんインデックスサイズも大きくなりがちで、更新コストも高くなります。

「読み取りが圧倒的に多い」「検索がサービスのコア機能」というときほど、
GIN のコストを払う価値が出てきます。

GINインデックスと実行計画

「Bitmap Index Scan が見えたら“ちゃんと効いている”サイン」

GIN を張ったあと、実行計画で「ちゃんと使われているか」を確認するのは大事です。
例えば、tags に GIN を張った状態で、次のクエリを EXPLAIN ANALYZE します。

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

うまくいっていると、実行計画にこういうノードが出てきます。

Bitmap Heap Scan on users
  Recheck Cond: ('premium' = ANY(tags))
  ->  Bitmap Index Scan on idx_users_tags_gin
        Index Cond: ('premium' = ANY(tags))

ここで注目したいのは、Bitmap Index Scan on idx_users_tags_gin です。
これが、「GIN インデックスを使って、条件に合う行の候補を一気に拾っている」証拠です。

その上にある Bitmap Heap Scan は、「インデックスで拾った候補行を実際のテーブルから読み直して、条件を再チェックしている」処理です。
GIN は「候補を広めに拾う」性質があるので、Recheck（再チェック）が入るのは正常です。

もしここが Seq Scan on users になっていたら、
GIN が使われていないので、条件や統計・設定を疑う必要があります。

JSONB＋GINで“柔らかいスキーマ”を支える

「RDBの中に“半構造データ”を持つときの現実解」

JSONB＋GIN は、実務でかなりよく出てくる組み合わせです。
例えば、「イベントログ」「トラッキングデータ」「外部APIレスポンスの保存」など。

完全に正規化するとテーブルが爆発するし、
かといって TEXT として持つと検索がつらい――
その中間解として、「コアな部分だけカラムにし、残りを JSONB に逃がす」という設計がよく使われます。

例えば、こんなテーブルです。

CREATE TABLE events (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL,
  event_type TEXT   NOT NULL,
  payload    JSONB  NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
SQL

ここで、

user_id・event_type・created_at は B-tree インデックス。
payload には GIN インデックス。

という構成にすると、

「特定ユーザーの login イベントで、os が iOS のものだけ」
「特定期間内で、country が JP のイベントだけ」

といったクエリを、B-tree＋GIN の合わせ技で高速に取れるようになります。

セキュリティ的な観点で言うと、「payload に何を入れていいか」をちゃんと決めておくことが重要です。
何でもかんでも突っ込むと、「どのキーが必須か」「どのキーを検索に使っているか」が分からなくなり、
インデックス設計も破綻しやすくなります。

GiSTインデックスの具体例

「位置情報・範囲・近さを“SQLでそれっぽく”扱う」

GiST は少し抽象度が高いので、具体例でイメージを固めます。

まずは位置情報の例です。
PostGIS などの拡張を入れると、geometry/geography 型に対して GiST を張れます。

例えば、店舗の位置情報を持つテーブル。

CREATE TABLE shops (
  id    SERIAL PRIMARY KEY,
  name  TEXT NOT NULL,
  loc   geometry(Point, 4326) NOT NULL
);
SQL

ここに GiST インデックスを張ります。

CREATE INDEX idx_shops_loc_gist
ON shops
USING GiST (loc);
SQL

すると、

「この緯度経度から半径1km以内の店舗を探す」
「あるポリゴン（エリア）の中にある店舗を探す」

といったクエリが、インデックスを使って高速に実行できます。

範囲型（int4range, tsrange など）でも同じです。
例えば、会議室の予約テーブル。

CREATE TABLE room_reservations (
  id        SERIAL PRIMARY KEY,
  room_id   INTEGER NOT NULL,
  period    tsrange NOT NULL  -- 開始・終了時刻の範囲
);
SQL

ここに GiST を張ります。

CREATE INDEX idx_room_reservations_period_gist
ON room_reservations
USING GiST (period);
SQL

すると、

「この時間帯と重なっている予約を探す」

SELECT *
FROM room_reservations
WHERE period && tstzrange('2024-06-01 10:00', '2024-06-01 11:00');
SQL

のようなクエリが、インデックスで効くようになります。

ここでのポイントは、「＝ ではなく、“重なっているか”“近いか”」という条件を、
インデックスで扱えるようにしていることです。

GINとGiSTの選び方の感覚

「“要素の集合”か、“距離・範囲”か」

ここまでの話を、選び方の観点で整理します。

カラムの中身が「要素の集合」なら GIN を疑う。
配列（tags）、JSONB（payload）、tsvector（search_vector）など。
クエリが「この要素を含むか」「この部分構造を含むか」なら GIN が候補。

カラムの中身が「距離・範囲・空間」なら GiST を疑う。
位置情報（Point, geometry）、期間（tsrange）、数値レンジ（int4range）など。
クエリが「重なっているか」「近いか」「内側にあるか」なら GiST が候補。

どちらも「B-tree では表現しづらい世界」をカバーするためのインデックスですが、
得意分野が違います。

「このカラムは“集合”っぽいか？」「“距離・範囲”っぽいか？」と自分に問いかけると、
どちらを選ぶかの当たりが付きやすくなります。

インデックス応用とセキュリティ・運用の視点

「“何にインデックスを張るか”は“何を信頼するか”でもある」

セキュリティ・運用の視点も少しだけ混ぜておきます。

GIN / GiST は強力ですが、「インデックスを張る」ということは、
「そのカラムを検索のキーとして“信頼する”」という宣言でもあります。

例えば、JSONB に GIN を張るなら、

「このキーは必ず存在する」
「このキーは検索に使う前提で設計している」

といった前提を、チーム内で共有しておくべきです。

そうしないと、

キー名の揺れ（os / OS / client_os）
型の揺れ（文字列で入れたり数値で入れたり）

などが起きて、インデックスが効かない・バグる・意図しないデータが混ざる、という事故につながります。

また、GIN は更新コストが高いので、

「本当にこのカラムに GIN が必要か？」
「この検索は別の仕組み（ログ基盤・検索エンジン）に逃がせないか？」

といった線引きも、設計段階で考える価値があります。

Day17 後半のまとめ

GIN は、「配列・JSONB・tsvector のような“要素の集合”を持つカラム」に対して、'premium' = ANY(tags)・payload @> '{"type":"login","os":"iOS"}'::jsonb・search_vector @@ plainto_tsquery(...) といった“中身を条件にした検索”を高速化するための逆引きインデックスであり、実行計画に Bitmap Index Scan on ..._gin が出ていれば、ちゃんと効いているサインになる。
GiST は、「位置情報（Point, geometry）」「範囲型（tsrange, int4range）」などの“距離・範囲・重なり”を扱うカラムに対して、「半径1km以内」「この期間と重なる予約」といったあいまいな近さをインデックスで処理するための検索木で、loc や period に GiST を張ることで && や距離条件のクエリが現実的な速度になる。
最終的に、「このカラムは“要素の集合”だから GIN か」「“距離・範囲”だから GiST か」と考え、さらに“そのカラムを検索キーとして信頼するか”“更新コストとストレージコストを払う価値があるか”まで含めてインデックスを選べるようになることが、Day17 後半の着地点になる。

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