【ITニュース解説】Why PostgreSQL isn’t using your index and how to investigate

PostgreSQLのようなデータベースでは、データ検索の速度を向上させるためにインデックスが非常に重要な役割を果たす。インデックスは、書籍の目次や索引のようなもので、膨大なデータの中から目的のデータを素早く見つけるための仕組みだ。しかし、せっかくインデックスを作成しても、PostgreSQLが期待通りにそれを使わず、結果としてクエリの実行が遅くなるケースがある。この問題が発生する理由と、その原因をどのように特定し、解決していくかについて理解することは、システムエンジニアを目指す上で非常に役立つ知識となるだろう。

PostgreSQLがインデックスを使わない主な理由の一つに、データ量が少ない場合がある。インデックスを使った検索は、データを直接探しに行くよりも余計な処理が必要になるため、テーブル全体のデータが非常に少ないときは、インデックスを使わずに最初から最後までデータを順に見ていった方が（これをシーケンシャルスキャンと呼ぶ）、結果的に速いとPostgreSQLの「プランナー」と呼ばれる部分が判断することがある。プランナーは、クエリの実行計画を立てる役割を担っており、どのインデックスを使うか、どのようにデータを取得するかなど、最も効率的な方法を決定する。

次に、このプランナーの見積もりが実際のデータと異なる場合も、インデックスが使われない原因となる。プランナーは、テーブルの統計情報（データの分布や種類など）に基づいて最適な実行計画を立てるが、もしこの統計情報が古かったり、データの偏りが極端だったりすると、プランナーはインデックスを使った方が速いと誤って判断したり、逆にインデックスを使わない方が速いと誤って判断したりすることがある。例えば、条件に合致するデータがテーブルの大部分を占めるような場合、インデックスを使っても多くのページを読み込むことになるため、プランナーはインデックススキャンよりもシーケンシャルスキャンを選んだ方が効率的だと判断する。これは、インデックスを利用したとしても、結局ほとんど全てのデータにアクセスする必要があるため、インデックスを介す手間を省いた方が良い、という判断だ。

クエリの書き方も、インデックス利用に大きく影響する。WHERE句の条件がインデックスの利用に適していない場合、インデックスは使われない。例えば、インデックスが貼られたカラムに対して関数を適用したり（例: WHERE substr(column_name, 1, 1) = 'A'）、型変換が暗黙的に行われたりすると、インデックスが効かなくなることがある。インデックスは特定の形式のデータに対して効率的に機能するため、関数によって値が加工されてしまうと、インデックスが指し示す元の値を直接参照できなくなるためだ。また、LIKE '%検索文字列'のように、前方一致でない部分一致検索を行った場合も、通常はインデックスが使われにくい。

複合インデックス（複数のカラムにまたがるインデックス）を作成している場合、その利用条件も重要になる。複合インデックスは、インデックスが作成されたカラムの順序が非常に重要だ。例えば、(column_A, column_B)という複合インデックスがある場合、WHERE column_A = 'X'という条件や、WHERE column_A = 'X' AND column_B = 'Y'という条件ではインデックスが利用されやすいが、WHERE column_B = 'Y'という条件だけでは、インデックスの先頭カラムが使われていないため、通常はインデックスが有効に活用されない。

これらの問題を調査するために最も強力なツールがEXPLAIN ANALYZEコマンドだ。このコマンドをクエリの前に付けることで、PostgreSQLが実際にそのクエリをどのように実行したか（実行計画）と、それぞれのステップでどれくらいの時間とリソースがかかったか（分析）を詳細に確認できる。EXPLAIN ANALYZEの出力には、各操作の「コスト」（PostgreSQLが推定する処理の重さ）や「rows」（推定される行数）、「actual rows」（実際に処理された行数）、「actual time」（実際に要した時間）などが表示される。

この出力で注目すべき点は、プランナーの「rows」の見積もりと実際の「actual rows」の間に大きな乖離がないかだ。もし、プランナーが10行と見積もったのに、実際には10万行が処理されていたとしたら、プランナーはデータの分布を正しく理解できていない可能性が高い。この乖離は、統計情報が古い、または不正確であることが原因であることが多い。その場合、ANALYZE table_name;コマンドを実行してテーブルの統計情報を最新に更新することで、プランナーがより正確な実行計画を立てられるようになる場合がある。

また、EXPLAIN ANALYZEの出力からは、クエリのどの部分で時間がかかっているか（高コストな操作）や、どのようなスキャンが行われているか（シーケンシャルスキャンかインデックススキャンかなど）も読み取れる。もし、インデックススキャンが期待される場所でシーケンシャルスキャンが行われていたり、インデックススキャンが行われていても実際の行数が極端に多いにもかかわらず見積もりが少ない場合は、そのインデックスが効率的に機能していないことを示唆している。

クエリのフィルター条件がインデックスでカバーされているかどうかの確認も重要だ。WHERE句の条件で使われているカラムがインデックスに含まれているか、そしてその条件がインデックスを使って効率的に絞り込める形になっているかを再確認する。例えば、col1 = ? AND col2 = ?という条件がある場合、(col1, col2)の複合インデックスがあれば効率的に機能する可能性が高い。しかし、col1 LIKE '%abc%'のような後方一致や部分一致の条件では、標準のB-treeインデックスは有効に活用されにくい。この場合は、全文検索インデックスなど、別の種類のインデックスを検討する必要があるかもしれない。

改善策を適用した後は、必ず再度EXPLAIN ANALYZEを実行して、変更が期待通りの効果をもたらしているかを確認することが不可欠だ。インデックスの追加、クエリの書き換え、統計情報の更新など、様々な変更が考えられるが、それぞれの変更がパフォーマンスにどのような影響を与えたかを客観的に評価することが重要だ。これにより、データベースのパフォーマンスチューニングは試行錯誤を繰り返しながら最適な状態へと近づけていく作業となる。この一連の調査と改善のサイクルを理解し実践することは、データベースを利用するアプリケーションを開発する上で、そしてシステム全体の性能を向上させる上で、非常に重要なスキルとなるだろう。