ハクソク

世界を動かす技術を、日本語で。

PostgreSQLインデックス入門

120日前原文(dlt.github.io)

概要

この記事は PostgreSQLのインデックス について、内部構造や種類、トレードオフ、最適化手法を直感的に理解している開発者向けに解説。 インデックスの基本、ディスク上でのデータ保存方式、インデックスによるクエリ高速化の仕組みを説明。 インデックス利用時のコスト や注意点、各インデックスタイプの特徴を整理。 B-Tree を中心に、他のインデックスタイプも簡単に紹介。 実際のクエリ例やコマンドも交えて、 実践的な知識 を提供。

PostgreSQLインデックスの内部構造と最適化

  • 対象読者 ：インデックスの直感的理解はあるが、内部動作やトレードオフ、PostgreSQL固有のインデックス種類・最適化手法を知りたい開発者
  • インデックスの役割 ：データアクセス高速化のための特別なデータベースオブジェクト
    • ディスクから読むデータ量削減
    • 主キーやユニークキーなどの制約実現
  • インデックス利用の注意点 ：クエリがインデックス対象カラム・型に一致しないと効果なし
  • インデックスの有効範囲 ：一般的にテーブルの15～20%未満を返すクエリで有効
    • それ以上はシーケンシャルスキャンが選ばれやすい
    • クエリプランナーは統計情報とコストを元に最適戦略を決定
  • テーブル構造 ：PostgreSQLでは各テーブルが1つ以上のファイル（ヒープ）で管理
    • 8KBページ単位で分割
    • 行（タプル）は順序なく保存
    • インデックスはツリー構造で、インデックスカラムと行位置（ctid）を紐付け
  • ctid ：各行の物理位置を示す内部フィールド（(ページ番号, オフセット)形式）

データ保存とctidの確認

  • データディレクトリ の確認：show data_directory;
  • テーブルファイルの特定 ：pg_classやpg_databaseテーブルを利用
  • ctidの取得 ：select ctid, * from テーブル名;で行の物理位置を確認

インデックスによる高速化の仕組み

  • インデックスなしの場合 ：全ページ・全行を順次読み込みフィルタリング（シーケンシャルスキャン）
    • 大量データではI/Oコストと実行時間が増加
  • インデックス追加後 ：インデックススキャンにより最小限のページアクセスで目的行に到達
    • 実行時間とI/O大幅削減
  • インデックスサイズ ：インデックス自体もディスク容量を消費
    • 大規模テーブルではインデックスサイズがテーブル本体と同等以上になることも

インデックス利用時のコスト

  • ディスク容量増加 ：インデックスごとに追加ストレージが必要
    • バックアップやレプリケーション、リカバリ時間への影響
  • 書き込みコスト増加 ：UPDATE/INSERT/DELETE時にインデックス更新が発生
    • 書き込み性能低下
  • クエリプランナー負荷 ：インデックス数が多いほど最適化計算が複雑化
  • メモリ使用量増加 ：共有バッファや作業メモリをインデックスで消費
    • 大きなカラムや複雑なインデックスではツリーが深くなり、メモリ消費増加
    • メタデータや統計情報、メンテナンス（VACUUM/REINDEX）にもメモリ必要

PostgreSQL標準インデックスタイプ

  • B-Treeインデックス

    • 汎用性が高く、ほぼすべてのDBMSで採用

    • O(log n)の高速検索・挿入・削除

    • データがRAMを超えても効率的なディスクアクセス

    • PostgreSQLのデフォルトかつ最も一般的なインデックスタイプ

    • 主キー・ユニークキー制約にも利用

    • バランス木構造で、全てのリーフノードがルートから同じ距離

    • 内部ノードは下位ノードへのポインタ、リーフノードはキーとヒープへのポインタ

    • ノード間の左右リンクにより順方向・逆方向の走査が容易

    • 値はリーフノードのみに保存、ツリーのコンパクト化と高速性を実現

    • その他のインデックスタイプ （概要のみ）

      • Hashインデックス ：等価比較専用、B-Treeより用途限定
      • GIN（Generalized Inverted Index） ：配列や全文検索向け
      • GiST（Generalized Search Tree） ：空間データや類似検索向け
      • SP-GiST ：空間分割型データ向け
      • BRIN（Block Range INdex） ：大規模・連続データ向け、スペース効率重視

    • 拡張による追加インデックス も利用可能

インデックス設計のポイント

  • インデックス追加前の検討事項
    • クエリの返却行数が多い場合は設計見直しやサマリーテーブル利用を検討
    • 部分インデックスや複合インデックス、BRINなどの利用でスペース効率化
  • インデックスの管理
    • 過剰なインデックス追加は逆効果
    • 定期的な統計情報の更新やメンテナンス（VACUUM/REINDEX）が重要

まとめ

  • インデックスは高速化の切り札 だが、 コストとトレードオフ を理解した設計が必須
  • B-Treeインデックス が基本だが、用途に応じて他のタイプも検討
  • クエリ内容・データ量・運用要件 に合わせた最適化が重要
  • PostgreSQLの内部構造や統計情報 を活用し、効果的なインデックス運用を心がける

Hackerたちの意見

必読だね。入門書よりも深いけど、内部のことを扱ってる人以外には難解すぎない感じ。

Postgresにとってめっちゃ素晴らしいね。一般的なBツリーインデックスのリソースとして、ここは何年も使ってるサイトだよ。 https://use-the-index-luke.com/

PostgreSQLのドキュメントにリンクするよ。すごくよく書かれてて、意外と読みやすいんだよね。 https://www.postgresql.org/docs/current/indexes-intro.html

マルチカラムインデックスのセクションは、俺が教わったことや過去に一般的に扱ってきた方法と一致してる。でも、最近のPGバージョンでもまだそうなのかな？ 第三の例に似たインデックスとクエリがあって、確かPGはインデックスを使えたと思うけど、ビットマップインデックススキャンだった気がする。インデックススキャンのタイプ間の具体的なパフォーマンスのトレードオフについてはよく分からないけど、EXPLAINプランでそれを見たとき、ずっと自分の中でハードコーディングされてた知識に疑問を持つきっかけになったよ。さらに必読なのは、クラシックな「Use The Index, Luke」[0]のサイトで、チーム全体にとっても買う価値がある本だよ。0: https://use-the-index-luke.com/

└

マルチカラムインデックスのセクションは、俺が教わったことや過去に一般的に扱ってきた方法と一致してる。でも、最近のPGバージョンでもまだそうなのかな？ いや、違うよ。PostgreSQL 18ではインデックススキップスキャンがサポートされたんだ。 https://youtu.be/RTXeA5svapg?si=_6q3mj1sJL8oLEWC&t=1366 以前のバージョンでは、下位のカラムにだけ演算子があるクエリでマルチカラムインデックスを使うことが実際に可能だったんだ。でも、それにはフルインデックススキャンが必要で、通常はすごく非効率的なんだよね。

└

ビットマップインデックススキャンを使うと、データが含まれている可能性のあるページを絞り込むことができるけど、そのページの内容に対して条件を再チェックしなきゃいけないから、ちゃんとしたインデックススキャンほどのパフォーマンスは出ないんだよね。

PostgreSQLで、いわゆるインクリメンタルビューのメンテナンスが標準でサポートされるといいな。これは、基になるデータが変わると自動的に更新されるインデックスみたいなもので、特別扱いされる普通のデータベースインデックスだけじゃなくて、任意のビューをサポートしてるんだ。

└

難しい問題だね、特に動的なターゲットに対するトランザクションについては。記憶が正しければ、このデータベースの次元に特化したプロジェクトがいくつかあったよ：Noria、Materialize、Apache Flink、GCPのContinuous Queries、Apache Spark Streaming Tables、Delta Tables、ClickHouseのストリーミングテーブル、TimescaleDB、ksqlDB、StreamSQLとか、他にもたくさんあると思う。確か、Postgresに関しては最近この問題に取り組む拡張が作られたんだよね：pg_ivm

└

時系列データがあるなら、TimescaleDBは連続集計機能があって便利だよ。

関連して、「Use the Index Luke」 https://use-the-index-luke.com/

MongoDBに「インデックス使えよ、ルーク」みたいな機能ってあるのかな…？