PostgreSQLインデックスが効かない？初心者向けパフォーマンス改善とチューニング徹底解説

PostgreSQLでインデックスが効かない原因と対策

先生と生徒の会話形式で理解しよう

生徒

「データベースの速度を速くするために『インデックス』を作ったんですけど、全然速くならないんです。何が原因なんでしょうか？」

先生

「それは『インデックスが効いていない』状態かもしれませんね。実は、索引を作っても使い方が間違っていると、データベースはそれを使ってくれないんです。」

生徒

「えっ、作っただけじゃダメなんですか？せっかく設定したのに悲しいです。」

先生

「辞書を引くときに、端から順番にページをめくっていたら時間がかかりますよね。でも、調べ方を間違えると辞書の目次すら使えなくなるのと同じです。今日はその理由を一緒に見ていきましょう。」

1. そもそもインデックスとは何か？

データベース、特に今回扱う「PostgreSQL（ポストグレスキューエル）」におけるインデックスとは、本でいうところの「索引」や「目次」のような役割を果たす機能です。データベースには大量のデータが保存されていますが、特定のデータを探す際に、最初から最後まで一行ずつ確認していくと、データの量が増えれば増えるほど時間がかかってしまいます。この「端から順番に全部見る」ことを専門用語で「フルスキャン（全件走査）」と呼びます。

インデックスを作成すると、あらかじめデータの場所を整理した「地図」のようなものが作成されます。これによって、データベースは一瞬で目的のデータにたどり着くことができるようになり、結果として検索スピードが劇的に向上します。これを「インデックスが効く」や「インデックススキャン」と呼びます。

しかし、非常に便利なインデックスですが、書き方を少し間違えるだけで、データベースの管理システムが「あ、この探し方なら索引を使わずに全部見たほうが早いな」と判断してしまい、せっかくの索引が無視されてしまうことがあります。これが「インデックスが効かない」という現象です。パフォーマンスの低下は、多くの場合この原因によって引き起こされます。

2. インデックスが効かない原因：列の加工（演算や関数）

最も多い原因の一つが、検索条件（WHERE句）の左側で、列に対して計算を行ったり、関数を使ったりしてしまうことです。データベースのインデックスは「列の値そのもの」に対して作成されています。そのため、値を加工してしまうと、索引のデータと一致しなくなり、使えなくなってしまいます。

例えば、会員のポイントを管理するテーブルがあるとします。現在、下記のようなデータが入っています。


id | name     | points | member_status
---+----------+--------+--------------
1  | 山田太郎 | 1000   | gold
2  | 佐藤花子 | 2500   | platinum
3  | 鈴木一郎 | 500    | silver
4  | 田中次郎 | 3000   | gold
5  | 伊藤美咲 | 1200   | silver

ここで、「ポイントを2倍にしたら2000以上になる人」を探そうとして、次のようなSQLを書いてしまうとインデックスが効きません。


-- これはインデックスが効かない悪い例です
SELECT *
FROM users
WHERE points * 2 >= 2000;

この場合、データベースは「pointsを2倍にした後の結果」という新しいデータを作り出さなければならず、既存のpoints列に設定されたインデックスを捨てて、全件を計算しながら調べることになります。対策としては、次のように右側（検索する値の方）で計算を完結させるのが正解です。


-- こちらはインデックスが効く良い例です
SELECT *
FROM users
WHERE points >= 1000;

実行結果はどちらも同じですが、処理速度には天と地ほどの差が出ることがあります。


id | name     | points | member_status
---+----------+--------+--------------
1  | 山田太郎 | 1000   | gold
2  | 佐藤花子 | 2500   | platinum
4  | 田中次郎 | 3000   | gold
5  | 伊藤美咲 | 1200   | silver

3. 文字列の検索での落とし穴：後方一致と部分一致

次に注意が必要なのは、文字列を検索する「LIKE」という命令の使い方です。日本語の住所や名札から特定の文字が含まれる人を探すときによく使われます。インデックスは基本的に「頭から順番に並んでいる」ため、前方一致（〇〇で始まる）という検索には非常に強いですが、途中や後ろの文字で探そうとすると効かなくなります。

例えば、次のようなメールアドレスのデータがあるとします。


id | name     | email
---+----------+---------------------
1  | 山田太郎 | yamada@example.com
2  | 佐藤花子 | sato@test.jp
3  | 鈴木一郎 | suzuki@example.com
4  | 田中次郎 | tanaka@sample.net
5  | 高橋健太 | ken@example.com

ここで、「example.com」で終わるメールアドレスを探したい場合、次のようなSQLを書くことがあります。


-- 後方一致（%が前にある）はインデックスが効きません
SELECT *
FROM users
WHERE email LIKE '%example.com';

「%」という記号は「どんな文字が入ってもいいよ」という意味です。この書き方だと、データベースは「最後がどうなっているかを確認するには、全部見ないと分からない」と判断し、辞書を最後から逆向きに引くような非効率な作業は行わず、全件走査を選択します。

同様に、`LIKE '%example%'` のように前後を囲む「部分一致」もインデックスが効きません。もし、どうしても高速化したい場合は、PostgreSQL特有の「全文検索インデックス」や「pg_trgm（トリグラム）」という特殊な仕組みを導入する必要がありますが、基本的には「前方一致（例：yamada%）」で設計を考えるのがパフォーマンスの基本です。


id | name     | email
---+----------+---------------------
1  | 山田太郎 | yamada@example.com
3  | 鈴木一郎 | suzuki@example.com
5  | 高橋健太 | ken@example.com

4. NULL（ヌル）の扱いとインデックスの関係

「NULL（ヌル）」とは、データが何も入っていない、空っぽの状態を指すIT用語です。未入力の項目などに使われますが、このNULLの扱いもインデックスに大きな影響を与えます。

多くのデータベースでは、標準的な設定だと「NULLであるデータ」をインデックスの地図に載せません（設定によりますが、初心者がハマりやすいポイントです）。


id | name     | phone_number | note
---+----------+--------------+-----------
1  | 山田太郎 | 090-1111     | 会員
2  | 佐藤花子 | (NULL)       | 未登録
3  | 鈴木一郎 | 080-2222     | 会員
4  | 伊藤次郎 | (NULL)       | 紛失

次のように、電話番号が入力されていない人を効率よく探そうとして、インデックスを設定したとします。


-- NULLを探すときは、インデックスが使われない場合が多いです
SELECT *
FROM users
WHERE phone_number IS NULL;

「IS NULL」という命令は、NULLを探すための特別な言葉ですが、インデックスの仕組み上、空っぽの場所を特定するのは苦手です。対策としては、テーブルを作るときに「デフォルト値」を設定して、空の状態を作らないようにすることが推奨されます。例えば、電話番号がなければハイフン（-）を入れる、といった工夫です。これによって、確実にインデックスが使われるようになります。


id | name     | phone_number | note
---+----------+--------------+-----------
2  | 佐藤花子 | (NULL)       | 未登録
4  | 伊藤次郎 | (NULL)       | 紛失

5. データの偏り（カーディナリティ）の問題

最後は少し意外な原因です。「インデックス自体は正しく設定されているのに、あえてデータベースがそれを使わない」というケースがあります。これはデータの「バリエーション」が少ない場合に起こります。このバリエーションの多さを専門用語で「カーディナリティ」と呼びます。

例えば、「性別」のように「男性」と「女性」の2種類しかないデータにインデックスを貼っても、あまり意味がありません。なぜなら、全データの半分が「男性」なら、インデックスを使って探すよりも、最初から全部のページをめくった方が効率が良いとデータベースが賢く判断してしまうからです。


id | name     | gender | active_flag
---+----------+--------+------------
1  | 山田太郎 | 男性   | 1
2  | 佐藤花子 | 女性   | 1
3  | 鈴木一郎 | 男性   | 0
4  | 田中次郎 | 男性   | 1
5  | 伊藤美咲 | 女性   | 1
6  | 渡辺花代 | 女性   | 1

次のようなSQLで「男性」だけを絞り込もうとしても、インデックスは無視される可能性が高いです。


-- データの種類が少なすぎると、インデックスは無視されます
SELECT *
FROM users
WHERE gender = '男性';

逆に、住所や氏名、メールアドレスのように、一人ひとりが異なる値を持っているデータ（カーディナリティが高いデータ）は、インデックスが非常に効果的に働きます。チューニングを行う際は、どの列にインデックスを貼るべきか、データの種類をよく観察することが大切です。


id | name     | gender | active_flag
---+----------+--------+------------
1  | 山田太郎 | 男性   | 1
3  | 鈴木一郎 | 男性   | 0
4  | 田中次郎 | 男性   | 1

6. インデックスを正しく使うためのチェックリスト

これまで見てきたように、PostgreSQLでインデックスを効果的に使うためには、いくつかのルールを守る必要があります。パフォーマンス改善のためのチェックポイントを整理しましょう。

WHERE句の左側で計算しない： `WHERE price * 1.1 > 100` ではなく `WHERE price > 100 / 1.1` と書く。
関数を使わない： `WHERE UPPER(name) = 'TARO'` のように関数を使うとインデックスが無効化されます。
前方一致を心がける： 文字列検索は `%キーワード` ではなく `キーワード%` で探せるように設計する。
暗黙の型変換を避ける： 数字の列に対して、クォーテーションで囲った文字列 `'100'` で検索すると、型を合わせるための内部処理が走り、インデックスが効かなくなることがあります。
EXPLAIN（エクスプレイン）で確認する： 実際にインデックスが使われているかは、SQLの前に `EXPLAIN` という言葉を付けて実行することで確認できます。

データベースのチューニングは、奥が深い世界ですが、まずはこれらの基本を押さえるだけで、多くの速度遅延トラブルを解決できるようになります。初心者のうちは、「データベースにとって探しにくい書き方をしていないかな？」と一歩立ち止まって考えてみることが、エンジニアとしての成長への第一歩です。

実務の壁を乗り越える【商用データベース設計・SQL最適化特別演習】