Posted inデータベース

PostgreSQLインデックス：B-Treeだけでは不十分な理由

Database tutorial - IT technology blog
Database tutorial - IT technology blog

「何でもB-Tree」という罠

開発者なら誰しも経験があるはずです。ローカル環境では5ミリ秒で終わるクエリが、本番環境では突如5秒もかかるようになる。かつて私が携わった分析プラットフォームでは、フラットなスキーマからJSONBを多用するモデルに移行した際、ダッシュボードの動作が極端に重くなりました。原因は、すべてをデフォルトのB-Treeインデックスに頼っていたことでした。

PostgreSQLは非常に柔軟ですが、その柔軟さが仇となって開発者を悪い習慣に引きずり込むことがあります。標準的なインデックスは単純な検索には魔法のような効果を発揮します。しかし、テーブルが1万行から1,000万行に増えたり、地理座標のような複雑なデータを保存し始めたりすると、B-Treeは足かせとなります。ディスク容量を浪費し、クエリプランナから完全に無視されることさえあります。

パフォーマンス低下の多くは、インデックスがないからではなく、適切なツールを使っていないことが原因です。PostgreSQLには、B-Tree、GIN、GiST、BRINといった専門的なツールキットが用意されています。適切なものを選択できるかどうかが、15ミリ秒の軽快なレスポンスか、ユーザーを苛立たせる30秒のタイムアウトかの分かれ道となります。

セットアップ：適切なツールの準備

ほとんどのインデックスタイプは標準で利用可能です。ただし、曖昧なテキスト検索を実行したり、異なるインデックスロジックを組み合わせたりする場合は、いくつかの標準拡張機能を有効にする必要があります。最新のGINやBRINのパフォーマンス向上を享受するために、PostgreSQL 12以降を使用していることを確認してください。

# バージョンを確認
psql -c "SELECT version();"

# 接続して必要な拡張機能を有効化
psql -d my_project_db

-- 曖昧なテキスト検索用
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- B-TreeのロジックとGiSTを組み合わせる用
CREATE EXTENSION IF NOT EXISTS btree_gist;

武器の選択：インデックスタイプの解説

各インデックスには特定のアーキテクチャ上の目的があります。推測で選ぶのではなく、クエリのパターンに合わせてインデックスを使い分けましょう。

1. B-Tree：信頼できるデフォルト

B-Treeは基本中の基本です。データをソートされた状態に保つため、完全一致（=）や範囲クエリ（<, >, BETWEEN）に最適です。主キー、外部キー、および特定の値や日付範囲を検索する必要があるカラムに使用します。

-- カーディナリティの高いルックアップに最適
CREATE INDEX idx_users_email ON users USING btree (email);

-- 特定の日付範囲をクエリ
SELECT * FROM orders WHERE created_at > '2024-01-01';

2. GIN：JSONBと配列の強力な味方

B-Treeを使ってJSONBカラムの中身を検索するのは、手を縛られた状態で干し草の山から針を探すようなものです。GIN（Generalized Inverted Indexes：汎用転置インデックス）は、複数の値を含むデータ向けに設計されています。タグでのフィルタリングやJSONドキュメントの深い階層の検索を行う場合、GINは必須です。100万行のテーブルにおいて、GINインデックスは1.2秒のシーケンシャルスキャンを5ミリ秒のルックアップに変えることができます。

-- パフォーマンス向上のため path_ops を使用してJSONB検索を最適化
CREATE INDEX idx_user_prefs ON users USING GIN (preferences jsonb_path_ops);

-- ダークモードが有効な全ユーザーを即座に検索
SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';

3. GiST：空間データと重複データのマスター

GiST（Generalized Search Tree）は、複雑な幾何学的形状や範囲を扱います。PostGISを使用する場合や、重複する時間間隔を検索する必要がある場合、GiSTが唯一の現実的な選択肢です。データを「バウンディングボックス（境界ボックス）」に整理することで、データベースは無関係なデータの大部分を素早く除外できます。

-- 店舗検索用に地理座標をインデックス化
CREATE INDEX idx_stores_location ON stores USING GIST (location);

-- 半径5km以内の店舗を検索
SELECT name FROM stores 
WHERE ST_DWithin(location, ST_MakePoint(10.7, 106.6)::geography, 5000);

4. BRIN：巨大なテーブルのための効率化

かつて私が管理していた500GBのログテーブルでは、タイムスタンプカラムに標準のB-Treeインデックスを張ったところ、45GBものRAMを消費していました。これは大きな無駄です。BRIN（Block Range Indexes）は、自然にソートされているデータに対して非常に賢く機能します。すべての行にインデックスを張るのではなく、ページブロックごとの最小値/最大値を保存します。その結果、45GBあったインデックスは、クエリ速度をほぼ維持したまま、わずか60MBまで縮小されました。

-- 時系列データやログにBRINを使用
CREATE INDEX idx_logs_created_at ON system_logs USING BRIN (created_at);

-- このインデックスは非常に小さく、数テラバイト規模のテーブルに最適です。

検証：信頼せず、確認せよ

インデックスを作成したからといって、データベースがそれを使うとは限りません。リソースを無駄にしていないか、実行計画を確認する必要があります。

Testing with EXPLAIN ANALYZE

常に EXPLAIN ANALYZE を付けてクエリを実行してください。「Index Scan」または「Bitmap Index Scan」が表示されるのが理想です。「Seq Scan」が表示されている場合、インデックスは無視されています。

EXPLAIN ANALYZE 
SELECT * FROM users WHERE preferences @> '{"theme": "dark"}';

プランナがインデックスをスキップする場合、テーブルが小さすぎるか、クエリがインデックスの定義と一致していない可能性があります。PostgreSQLは、テーブルが数千行未満の場合、シーケンシャルスキャンの方が速いと判断することがよくあります。

インデックスの肥大化（Bloat）の追跡

インデックスは無料ではありません。INSERT や UPDATE の操作を遅くします。以下のクエリを使用して、インデックスのサイズをテーブルサイズと比較し、実際にどれが使われているかを確認しましょう。

SELECT
    t.relname AS table_name,
    i.relname AS index_name,
    pg_size_pretty(pg_relation_size(t.oid)) AS table_size,
    pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
    idx_scan AS times_used
FROM pg_class t
JOIN pg_index x ON t.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_stat_all_indexes s ON s.indexrelid = i.oid
WHERE t.relkind = 'r'
ORDER BY pg_relation_size(i.oid) DESC;

10GBのインデックスがあってもスキャン回数が0であれば、削除してください。それはデッドウェイト（無駄な重荷）です。頻繁な更新によって肥大化したインデックスについては、CONCURRENTLY オプション付きの再インデックス作成を行うことで、ユーザーをシステムから締め出すことなくスペースを回収できます。

-- ダウンタイムなしで再構築
REINDEX INDEX CONCURRENTLY idx_users_email;

インデックス作成は「一度設定すれば終わり」のタスクではありません。IDにはB-Tree、JSONにはGINを使い、巨大なログにはBRINを活用しましょう。データ型に合わせてインデックスを使い分けることで、データベースを軽量に保ち、アプリケーションの規模が拡大しても高速な動作を維持できます。

Share:
Tags: