PostgreSQLでインデックスが使われない理由を徹底解説！初心者でもわかるデータベース高速化

1. インデックスが存在していない

1. インデックスが存在していない

まず基本ですが、テーブルにインデックスがそもそも作成されていなければ、検索時に利用されません。PostgreSQLでは以下のようにインデックスを作ります。

CREATE INDEX idx_users_age
ON users(age);

このコマンドで、usersテーブルのage列にインデックスが作成されます。作成していない列を検索してもインデックスは使われません。

2. WHERE条件がインデックスに合わない

2. WHERE条件がインデックスに合わない

インデックスは条件に合った列にのみ有効です。例えば部分一致や関数で加工した値を検索すると、インデックスが無視されることがあります。

SELECT *
FROM users
WHERE LOWER(name) = '山田太郎';

この場合、name列に通常のB-treeインデックスがあっても、LOWER関数がかかっているためインデックスは使われません。関数に対応するインデックス（関数インデックス）を作る必要があります。

3. データ量が少ない場合

3. データ量が少ない場合

テーブルのレコード数が少ない場合、PostgreSQLはインデックスを使わずにテーブルスキャン（全件検索）を選ぶことがあります。理由は単純で、少ないデータならテーブル全件を読む方が早いからです。

SELECT *
FROM users
WHERE age = 25;

レコードが3件しかない場合、インデックスを使わずに全件検索してもほとんど時間は変わりません。

4. データ分布が偏っている場合

4. データ分布が偏っている場合

特定の値にデータが集中していると、インデックスを使っても効率が悪くなることがあります。例えばage列で90%以上が20歳の場合、20歳を検索するのにインデックスを使うより全件検索の方が高速です。

5. 組み合わせ条件（複合条件）が合わない

5. 組み合わせ条件（複合条件）が合わない

複合インデックスは作った順番が大事です。例えばageとnameの複合インデックスを作った場合：

CREATE INDEX idx_users_age_name
ON users(age, name);

WHERE name = '山田太郎'だけでは、このインデックスは使われません。age列から先に検索する順序を期待しているからです。

6. インデックスが古くなっている

6. インデックスが古くなっている

PostgreSQLではデータ更新や削除でインデックスが断片化することがあります。この場合、インデックスの効率が落ちて使われにくくなることがあります。

REINDEX INDEX idx_users_age;

このコマンドでインデックスを再作成し、効率を回復できます。

7. 統計情報が古い

7. 統計情報が古い

PostgreSQLは統計情報をもとに検索プランを作ります。統計が古いと、インデックスを使うべきなのに使わない判断をすることがあります。

ANALYZE users;

ANALYZEコマンドで統計情報を更新すると、検索プランが改善され、インデックスが使われやすくなります。

8. LIKE検索のワイルドカード位置

8. LIKE検索のワイルドカード位置

LIKE検索でワイルドカードの位置によってはインデックスが無効になります。先頭に%があるとインデックスは使えません。

SELECT *
FROM users
WHERE name LIKE '%太郎';

前方一致（'太郎%'）ならインデックスを使えますが、後方一致や中間一致だと全件検索になります。

9. NULL値の検索

9. NULL値の検索

PostgreSQLではNULL値を含む列はB-treeインデックスで注意が必要です。NULLの検索はインデックスを使わないことがあります。

SELECT *
FROM users
WHERE email IS NULL;

NULL専用の部分インデックスを作ると高速化できます。

10. インデックスを使うかどうかはPostgreSQLが判断する

10. インデックスを使うかどうかはPostgreSQLが判断する

最終的にインデックスを使うかどうかはPostgreSQLのクエリプランナーが判断します。条件によってはテーブルスキャンの方が速いと判断され、インデックスが使われないことがあります。

まとめ

まとめ

PostgreSQLでインデックスが使われない原因は多岐にわたります。基本的には、テーブルにインデックスが存在しない場合や、WHERE条件がインデックスに合わない場合、またデータ量が少ない場合やデータ分布が偏っている場合など、状況に応じてPostgreSQLのクエリプランナーが最適な検索方法を選択しています。さらに複合条件のインデックスの順序、インデックスの断片化、統計情報の古さ、LIKE検索におけるワイルドカードの位置、NULL値の扱いなどもインデックスが利用されない大きな要因です。

効率的にインデックスを活用するには、適切な列にインデックスを作成すること、必要に応じて関数インデックスや部分インデックスを活用すること、複合インデックスの順序を意識すること、インデックスや統計情報のメンテナンスを定期的に行うことが重要です。また、PostgreSQLは常に最適な実行計画を選ぶため、テーブルスキャンの方が速い場合にはインデックスが使用されないことも理解しておく必要があります。

実際の例として、usersテーブルのage列にインデックスを作成し、関数を使用した検索を行う場合は関数インデックスを作ることが推奨されます。例えばLOWER関数を使用して名前を検索する場合は以下のようにインデックスを作成します。

CREATE INDEX idx_users_lower_name
ON users(LOWER(name));
    

また、統計情報が古い場合にはANALYZEコマンドで更新し、断片化したインデックスはREINDEXで再構築することで、検索性能を改善できます。

ANALYZE users;
REINDEX INDEX idx_users_age;
    

LIKE検索を効率化する場合は、前方一致を意識してインデックスを活用し、後方一致や中間一致は全件検索になることを理解して設計することが重要です。NULL値の検索には部分インデックスを用いると検索速度が向上します。