ここ１年くらいでPostgreSQLのパフォーマンスチューニングをする機会が増えてきたので、学んだことの中で特に大事だと思ったポイントをいくつかピックアップして整理してみました。
心構え：インデックスチューニングが初手
「SQLチューニング」というと、SQLそのものを書き換えてしまいそうになりますが、個人の経験上、SQLの書き方を変えるだけでパフォーマンス向上が期待できる場面は稀です。（ChatGPTに聞くとSQLの書き方を変えることをよく勧めてきたりしますが、それで改善したことがないです。。）
手軽で効果が出やすいのは インデックスの見直しと活用 です。
PostgreSQLのデフォルトインデックスは B-tree です。これは、キー順にソートされた木構造であるため、等価・範囲条件やソートを含む検索に強く、順序が意味を持つデータによく適しています。
B-treeインデックスは、インデックスの先頭列からの一致や順序性が最も重要であり、インデックス設計時はその順番に注意を払う必要があります。
ORDER BYを狙ったインデックス
あるテーブル１つに対して、検索 → 並び替え → 上位N件を取得するクエリに対して有効なインデックスを使いたい場合、以下の順序で列を並べるのが基本です：
-
主の検索条件（WHERE）
-
ソート対象の列（ORDER BY）
インデックス作成の例：
-- start_date でフィルタし、price でソートするクエリに対して
CREATE INDEX idx_event_search ON events (start_date, price);
適用例：
SELECT * FROM events
WHERE start_date = '2025-07-01'
ORDER BY price ASC
LIMIT 20;
このように、WHERE と ORDER BY の両方をカバーするインデックスにすることで、実行計画からSORT処理が省かれ、効率的な Index Scan が可能となります。
関数インデックスの活用
ORDER BYを狙ったインデックスの場合、インデックスが最大限有効に働くのはWHERE句の条件が等価条件(=)の場合です。
一方、範囲指定(>=やBETWEENなど)の場合は条件に該当する全てのデータを取得した後に全体をソートする処理が発生してしまい、インデックスの効果が薄くなってしまいます。
このようなケースでは関数インデックスを使ってWHERE句を等価条件に置き換えられないかを検討するとよいです。
関数インデックス作成の例：
-- start_dateの月 でフィルタし、price でソートするクエリに対して
CREATE INDEX idx_event_search ON events (EXTRACT(YEAR FROM start_date), EXTRACT(MONTH FROM start_date), price);
適用例：
SELECT * FROM events
WHERE EXTRACT(YEAR FROM start_date) = 2025
AND EXTRACT(MONTH FROM start_date) = 7
ORDER BY price ASC
LIMIT 20;
上記例は start_date BETWEEN '2025-07-01' and '2025-07-31' と同等のクエリですが、関数インデックスを使うことで条件を等価にできているため、インデックスの効果が最大限発揮されます。
テーブル結合がある場合
複数テーブルを結合するクエリの場合でも、駆動表側にORDER BYを狙ったインデックスがあり、かつNested Loop Joinが選択される場合においてはORDER BYを狙ったインデックスは有効となる場合があります。（上から順に結合していき、条件に合致するN件取得を取得した時点で終了するイメージ）
しかし、内部表側となってしまう場合は事前並び替えのメリットはないため、別のインデックスも用意する必要があります。それが次の項です。
テーブル結合を狙ったインデックス
テーブル結合において内部表となるテーブルでは、結合キーをインデックスの先頭に配置することが有効です。
結合キーが複数ある場合や追加の結合条件がある場合、カーディナリティの高い列を先に配置すると効率のよいインデックスとなります。
インデックス作成の例：
-- JOINキー: category_id、追加条件: published_date
CREATE INDEX idx_articles_category ON articles (category_id, published_date);
JOIN句の例：
SELECT * FROM categories c
JOIN articles a ON c.id = a.category_id AND a.published_date = '2025-08-01';
このようなケースで、articles テーブル側が内部表となる場合に idx_articles_category が活躍します。
ちなみに、JOIN句を使っていなくとも複数テーブルが関連するクエリの場合は内部的にはテーブル結合するので、大体のクエリでこのテーブル結合用インデックスは必要と思われます。
大量データの集計クエリを狙ったインデックス
Index Only Scan
大量データの集計時にインデックスが効いていても遅いケースがあります。
インデックスによって対象行を効率よく探索できていたとしても、対象行が多いとヒープ（テーブルの実データ）へのアクセスがボトルネックになっているためです。
これを避ける手段のひとつが Index Only Scan を活用することです。
Index Only Scanを可能にするには、クエリで使われるすべての列を含むカバリングインデックスを用意する必要があります。
where句だけではなく、selectで参照する列もインデックスに含める必要があるので注意です。
カバリングインデックスの例：
-- クエリで参照する列だけを含む
CREATE INDEX idx_orders_covering ON orders (order_date, is_expedited, customer_id);
SELECT count(customer_id)
FROM orders WHERE order_date >= '2025-07-01' AND is_expedited = true;
このようにすれば、実データ（Heap）にアクセスせずインデックスへのアクセスだけで結果が得られる可能性があります。
なお、カバリングインデックスを作成する場合も、主の検索条件（WHERE） で使う列を先に配置すると、効率のよいインデックススキャンとなります。
共有バッファのキャッシュヒット率を上げる
ここまで用途に応じてインデックスをそれぞれ作ると良いという話でしたが、インデックスを作りすぎると以下のようなデメリットがあります。
- データ更新(INSERT / UPDATE / DELETE)のパフォーマンス低下
- 共有バッファ(shared_buffers)の圧迫によるキャッシュヒット率の低下
1.のデータ更新のパフォーマンス低下については、データ更新頻度がそこまで高くなかったり更新の多少の遅延がクリティカルにならない場合はある程度許容できますが、2. キャッシュヒット率の低下はストレージへのアクセスを発生させ、読み取り系のクエリ実行速度に大きく影響を与えるので要注意です。
アクセスパターンマトリックス
| インデックス参照 | 実データ（Heap）参照 | |
|---|---|---|
| 共有バッファに載っている | ◎（Index Onlyだと最速） | ○ |
| ストレージから読み込む | △ | ✕ |
上記は速度を比較したものではなく、あくまで共有バッファに載っている状態が理想、ということを伝えるものです。
対策としては、サーバーコストをかけられるのであれば、データベースインスタンスのスペック増強によって共有バッファを増やせればいいですが、そうではない場合以下のような対策が考えられます。
- 不要なインデックスを削除して競合を減らす
- カラム数の多いインデックスを避ける（特に
SELECT *が多いならカバリングインデックスを使わないのも選択肢） - 部分インデックスでインデックスのサイズを縮小
- pg_repackで肥大化したデータ・インデックスを圧縮する
- B-treeではなくBRINインデックスを使う（履歴テーブルなど効果的な場面に限ります）
部分インデックスを使う
部分インデックスとは、特定の条件にマッチする行だけを対象にしたインデックスです。特に フラグ系の列が明確に偏っている場合（true/falseなど） に有効です。
部分インデックスの例：
-- is_archived = true の場合しか検索されないユースケース
CREATE INDEX idx_active_users ON users (last_login) WHERE is_archived = true;
上記例ではis_archivedがtrueのデータ範囲にのみインデックスを構築するため、インデックスサイズを削減でき、共有メモリに乗りやすくなるメリットがあります。
is_archived = trueに該当する行数が少ない場合のみ有効です。
pg_repackを使う
更新・削除を頻繁に行うテーブルを運用していると、テーブルデータやインデックスの肥大化するという事象が起きることがあります。
このようなケースにダウンタイムなしで対策できるのがpg_repack です。
使い方はWeb検索すればすぐ出てくるので割愛しますが、場合によってはデータ・インデックスサイズを半減させることも可能かと思います。
BRINインデックスを使う
BRINインデックスはかなり軽量なインデックスであるため、巨大なインデックスの代替手段として検討すると良いです。
ただし効果が最大限発揮されるテーブルには以下のような特徴が必要なため、利用場面は限られてきます。
- データが物理的に挿入順や時系列順に並んでいる（履歴テーブルなど主に追記型のテーブル）
- 非常に大きなテーブル(1000万件超）
あまり試したことはないですが、pg_repackでorder-byオプションをつけて実行すると物理的なデータ順序を任意の列で並び替えできるので、1.を満たさないテーブルであってもpg_repackと併用すれば効果的なことがあるかもしれません。
まとめ：インデックスは設計がすべて
インデックスはクエリ最適化の「初手」として強力な一方で、設計ミスや過剰な適用はパフォーマンスの足かせにもなり得ます。
以下にインデックスの使い分けを整理します。
| インデックス | 主な目的 | 注意点 |
|---|---|---|
| 並び替え検索用 | ORDER BYのソート処理を省く | 先頭一致と列順 |
| テーブル結合用 | 内部表の探索高速化 | 結合キーを先頭に配置 |
| カバリングインデックス | 大量データの集計処理など | 高い更新頻度や共有バッファ容量に注意 |
| 部分インデックス | インデックスサイズ削減 | 条件の偏りが明確な場合に適用 |
| BRINインデックス | インデックスサイズ削減 | 効果が高いテーブルが限定的 |
最後に、データベースのチューニングを色々学んでいると、データベースの気持ちが少しずつわかってくるようになりました。（まだまだですが。。。）
パフォーマンスチューニングにおいて銀の弾丸はないですが、データベースとのシンクロ率を上げて様々な課題に対応できるように励みたいと思います。
業界最大手高速バス料金比較サイト「バス比較なび」や、LCCなど飛行機との比較ができる「格安移動」を運営している、株式会社LCLのエンジニアの個人記事です。
Discussion