【入門編】範囲型(Range Types)のインデックス – PostgreSQL

こんにちは！データベースをいじくり回すのが大好きなエンジニアです。

皆さんは、予約システムやイベントのスケジュール管理を作ったことはありますか？例えば、「会議室の予約が重なっていないかチェックしたい」とか、「ある期間内に発生した注文を抽出したい」といったケースです。

普通に考えると、`start_at`（開始日時）と `end_at`（終了日時）という2つのカラムを用意しますよね。でも、これだと「重なり」を調べるクエリが地味に面倒で、かつインデックスがうまく効かなくて頭を悩ませることが多いんです。

そこで今日は、PostgreSQLが誇る隠れた名機能「範囲型（Range Types）」と、それを爆速にする「GiSTインデックス」という魔法について、少しだけお話しさせてください。

—

データベースの世界で「期間」を扱うとき、開始と終了の2つの数字（または日時）をバラバラに管理するのは、実はちょっと不器用なやり方なんです。

PostgreSQLには、これらを「ひとまとめの塊」として扱う範囲型（Range Types）という型があります。

例えば、「10時から12時まで」という予約を、ひとつのデータとして `[10, 12)` のように格納できるんです。これを使うと、「この期間と重なるデータはある？」という質問が、驚くほどシンプルに書けるようになります。

もし範囲型を使わずに、開始時刻と終了時刻を別々のカラムで持っていたらどうなるでしょう。「重なり判定」のクエリを書くと、こんな感じになります。

— 従来のやり方（条件が複雑になりがち）
WHERE start_at < '12:00' AND end_at > ’10:00′

これ、条件が増えたり、「完全に含まれているか？」なんていう複雑な条件になると、脳がこんがらがってきますよね。しかも、このクエリに対して普通のインデックス（B-tree）を張っても、なかなか思うようなパフォーマンスが出ないんです。

ここで登場するのがGiST（Generalized Search Tree）インデックスです。

イメージしてみてください。図書館で本を探すとき、普通のインデックスは「あいうえお順」に並んでいるから見つけやすいですよね。でも、「重なり」を調べるのは、「同じ棚にある本を全部探し出す」ようなもの。B-treeだと、この「広がり」を扱うのが少し苦手なんです。

GiSTインデックスは、データを「グループ化」して、まるで「このエリアにはこの期間の予約が入っていますよ」という地図を作るようなインデックスです。

これを使えば、`&&`（重なり演算子）や `@>`（包含演算子）といった範囲型専用の演算子を使ったクエリが、一瞬で結果を返してくれるようになります。

PostgreSQLでこれを使うのは、驚くほど簡単です。テーブルを作るときに、範囲型のカラムを指定して、GiSTインデックスを張るだけ。

— 範囲型を使ったテーブル作成
CREATE TABLE reservations (
id serial PRIMARY KEY,
period tsrange
);

— GiSTインデックスを張る！
CREATE INDEX idx_reservations_period ON reservations USING GIST (period);

これだけで、PostgreSQLという優秀な助手が、裏でせっせと「期間の地図」を更新し続けてくれます。あとは、クエリで `period && ‘[2023-10-01, 2023-10-02)’` のように書くだけ。驚くほどコードがスッキリして、検索も爆速になります。

—

データベース設計って、最初は難しく感じるかもしれません。「インデックス？演算子クラス？何それ？」という状態でも大丈夫です。

まずは「このデータの塊を、一つの型として扱えないかな？」と発想を広げてみてください。PostgreSQLには、今回紹介した範囲型のように、皆さんの困りごとを解決するための「魔法の道具」がたくさん用意されています。

もし今、複雑な期間計算のクエリで悩んでいるなら、ぜひ一度この「範囲型 × GiSTインデックス」を試してみてください。きっと、「もっと早く知りたかった！」と思っていただけるはずですよ。

また次回のブログでお会いしましょう。ハッピーなデータベースライフを！