PostgreSQLインデックス入門

 (dlt.github.io)
30 ポイント 投稿者 GN⁺ 2026-01-26 | 1件のコメント | WhatsAppで共有
  • PostgreSQLインデックスは、データアクセス速度を高めるための中核的な構造であり、ディスクから読み込む必要のあるデータ量を減らしてクエリ性能を向上させる
  • インデックスは Btree, Hash, BRIN, GIN, GiST, SP-GiST など多様な形で提供され、それぞれ異なるデータ特性やクエリパターンに最適化されている
  • インデックスには ディスク容量、書き込み性能、クエリプランナの複雑さ、メモリ使用量 など、さまざまなコストが伴う
  • 部分インデックス、複数列インデックス、カバリングインデックス、式インデックス などの高度な機能により、特定の状況で効率を最大化できる
  • 適切なインデックスの選択と管理が、PostgreSQLの性能最適化における重要な要素として強調されている

インデックスの基本概念

  • インデックスは、データベースがディスクから読み込むデータ量を減らし、クエリ速度を高めるための構造
    • 主キー、一意キー、排他制約などもインデックスによって実装される
    • クエリ結果がテーブル全体の 15〜20% 未満であればインデックスは効果的であり、それを超えると シーケンシャルスキャン のほうが効率的な場合がある
  • PostgreSQL は標準で 6種類のインデックスタイプ を提供しており、拡張によってさらに多くの種類を利用できる
    • 各インデックスはキー値と対応するデータ位置（TID）を結び付ける

ディスクに保存されるデータ構造

  • PostgreSQL のテーブルは ヒープ（heap） ファイルとして保存され、8KB ページ単位で構成される
  • 各行（tuple）は特定の順序なしに保存され、内部アドレスは ctid (current tuple id) で識別される
    • 例: (0,1) はページ 0 の最初のタプルを意味する
  • インデックスは、こうしたヒープ内の位置（ctid）をツリー構造で結び付けることで高速な検索を支援する

インデックスがデータアクセスを高速化する仕組み

  • インデックスがない場合、PostgreSQL はすべてのページを読む シーケンシャルスキャン を実行する
    • 例のクエリで name='Ronaldo' を探す場合、6272 ページを読み、265ms を要した
  • インデックスを追加すると Index Scan に切り替わり、4 ページだけを読み、0.077ms で完了する
    • インデックスは値と ctid を対応付けることで、必要な行だけをすばやく見つける
  • インデックスファイルのサイズはテーブルサイズに近くなることがある（例: 30MB のテーブル → 30MB のインデックス）

インデックスのコスト要素

  • インデックスは性能向上だけでなく、さまざまな負担要素も伴う

ディスク容量

  • インデックスは別途ストレージ領域を消費し、テーブルより大きくなることもある
    • バックアップ、レプリケーション、障害復旧時に追加コストが発生する
    • 部分インデックス、複数列インデックス、BRIN などにより、容量効率を改善できる

書き込み処理

  • UPDATE, INSERT, DELETE 時にインデックス対象の列が変更されると、インデックス更新のオーバーヘッド が発生する

クエリプランナ

  • インデックスが多いほど、プランナが考慮すべき選択肢が増え、クエリ計画の作成時間が長くなる

メモリ使用量

  • インデックスページは shared buffer に読み込まれてキャッシュされるため、インデックスが多いほどメモリ負荷が増える
  • btree ノードサイズの制限 により、列が大きいほどツリーの深さが増す
  • ソート、複数列スキャン、vacuum、reindex などでも work memory が追加で使われる

インデックスの主要な種類

Btree

  • PostgreSQL の 標準インデックス構造 であり、多くの DBMS で使われる汎用インデックス
    • O(log n) の時間計算量で高速な検索を実現する
    • すべてのリーフノードが同じ深さを持つ 平衡木構造
    • ORDER BY, JOIN に有利で、主キー・一意キー制約 に使われる
  • 内部ノードは下位ノードへのポインタを、リーフノードはキーとヒープポインタを格納する
  • 左・右ノードポインタ により双方向探索が可能
複数インデックスの利用
  • PostgreSQL は複数のインデックスを ビットマップ AND/OR 演算 で組み合わせ、複合条件を処理できる
    • 例: age=30 AND login_count=100 条件では 2 つのインデックスのビットマップを結合する
複数列インデックス
  • 複数の列を 1 つのインデックスにまとめることで、容量削減と高速化 が可能
    • ただし 列順が重要 であり、左側から一致する条件に対してのみインデックスを利用できる
部分インデックス
  • 条件式を使って特定の行だけをインデックス化する
    • インデックスサイズの縮小、RAM 適合性の向上、検索速度の改善につながる
    • 例: create index on rules(status) where status='enabled';
    • 値の分布が偏っている場合に有用（status <> 'TODO' など）
カバリングインデックス
  • クエリに必要なすべての列がインデックスに含まれていれば、heap にアクセスせず結果を返す（index-only scan） ことができる
    • create index abc_cov_idx on bar(a, b) including c;
    • 複数列インデックスより 容量効率が高い
式インデックス
  • 列値ではなく 関数や式の結果 をインデックス化する
    • 例: CREATE INDEX idx_lower_name ON customers (lower(name));
    • LOWER(name) のような変換後の値で検索するときに有用
    • 不変（immutable） 関数のみ使用可能

Hash

  • ハッシュマップ構造 に基づくインデックスで、長い文字列や UUID などで 容量効率に優れる
    • 32 ビットのハッシュコードを保存してサイズを抑える
    • 等価比較（=） 演算のみをサポートし、ソートや複数列インデックスには対応しない
    • ハッシュ分布が均一であれば、Btree より高速な読み取り性能 を発揮する場合がある
  • 公式ドキュメントによれば、ハッシュインデックスは バケットページへの直接アクセス により、大規模テーブルで I/O を削減できる

BRIN (Block Range Index)

  • 各ブロック範囲の 最小値・最大値だけを保存 するインデックス
    • 非常に 高圧縮でキャッシュ効率が高い
    • 大規模、append-only、時系列データ に適している
  • 行が頻繁に更新されると、MVCC による重複保存 のため効率が低下する
  • pages_per_range 設定により、精度とサイズのトレードオフ を調整できる

GIN (Generalized Inverted Index)

  • 複合データ検索 に適したインデックス
    • テキスト、配列、JSONB などで特定要素の検索をサポートする
    • データ型ごとの 専用戦略（opclass） を使う
    • JSON は JSONB 列、テキストは tsvector または pg_trgm 拡張 と併用することが推奨される

GiST & SP-GiST

  • Generalized Search Tree（GiST） と Space-Partitioned GiST（SP-GiST） は、特定データ型向けインデックスを実装するためのフレームワーク
    • GiST は 平衡木、SP-GiST は 非平衡構造 をサポートする
    • 地理情報、inet、範囲、テキストベクトル などに活用される
    • GIN は高速な検索、GiST は構築・維持コストが低い
    • 全文検索 では要件に応じて両者を使い分ける

結論

  • インデックスは PostgreSQL の性能最適化の中核であり、読み取り速度向上と書き込み・保存コストのバランス が重要
  • データ特性とクエリパターンに合ったインデックスタイプを選べば、高速で効率的なデータベース運用 が可能になる
  • 適切なインデックス設計は、大規模システムの拡張性と安定性の確保 に不可欠な要素である

関連記事

1件のコメント

 
GN⁺ 2026-01-26
Hacker Newsのコメント

  • PostgreSQLの公式ドキュメントは本当によく書かれていて、読んでいて楽しいので共有する
    PostgreSQL Indexes 紹介ドキュメント

  • 複合インデックスの部分は、自分が学んだやり方とほとんど同じだった
    ただ、最新のPostgreSQLバージョンでもまだそうなのか気になっていた
    以前、3つ目の例に似たクエリで bitmap index scan が使われているのを見て、それ以来、従来の「定説」を見直すようになった
    ちなみにインデックス関連では、Use The Index, Luke のサイトと書籍は、チーム全体で読む価値のある 古典的な資料 だと思う

    • PostgreSQL 18では index skip scan が追加され、複合インデックスの下位カラムだけでも効率的な検索が可能になった
      以前のバージョンでも不可能ではなかったが、その場合はインデックス全体をスキャンする必要があり、非効率だった
      関連動画: YouTubeリンク
    • bitmap index scan は、データが存在する可能性のあるページを絞り込めるが、実際の条件判定は再度行う必要があるため、通常のインデックススキャンより性能が劣る

  • PostgreSQLで incremental view maintenance が標準サポートされるとよいと思う
    これはインデックスのように基礎データが変わるたび自動更新されるが、特定のビューに限られず任意のビューに適用できる概念だ

    • これは トランザクション処理 が絡むため、かなり難しい問題だ
      Noria, Materialize, Apache Flink, GCP Continuous Queries, Spark Streaming Tables, Delta Tables, ClickHouse streaming tables, TimescaleDB, ksqlDB, StreamSQL など関連プロジェクトは多い
      PostgreSQLでは最近、pg_ivm という拡張がこの問題に取り組み始めている
    • 時系列データであれば、TimescaleDBの continuous aggregates 機能がすでにこの役割を果たしている

  • B-tree vs Hash インデックス の議論が興味深い
    多くの人はIDカラムにはhashのほうがよいと考えるが、実際には標準のB-treeのほうが効率的だ
    特に、ほぼ連続した値の挿入ではツリー構造のほうが有利だ
    ただし今回言及されていたブログ記事では、逆にhashがベンチマークで勝ったという

  • 今回の記事はタイミングがよかった
    複合インデックスの leading column ルール はいつも混乱しやすかったが、bitmap index scanのおかげで以前ほど致命的ではなくなった
    PostgreSQL 18のskip scan機能は従来の常識を大きく変えるので、古いバージョン基準で学んだ人は メンタルモデルの更新 が必要だ

  • PostgreSQL向けの資料として本当に素晴らしい記事だと思う
    B-treeインデックス関連では、以前から Use The Index, Luke をよく参考にしてきた

  • 必読書だと思う
    単なる入門書のレベルを超えて深みがありつつ、内部構造を扱わない限り 十分に読みやすい

  • こういう シンプルで謙虚な文章スタイル が好きだ
    知識を直接伝えるやり方がよい