【ITニュース解説】Optimizing PostgreSQL Queries: From 820 ms to 120 ms with Indexing

システムを開発していると、最初はスムーズに動いていたアプリケーションが、ユーザーが増えたりデータが蓄積されたりするにつれて、だんだんと動作が遅くなる経験をすることがある。特に、顧客の注文履歴を表示したり、特定の期間の活動データを集計したりするような操作で、画面の表示に時間がかかったり、データの取得がなかなか終わらなかったりといった問題に直面する。この遅延は、多くの場合、アプリケーションのプログラムコードではなく、データを保存しているデータベースの仕組みに原因があることが多い。具体的には、データベースが目的のデータを探し出す方法が非効率なために、余計な時間がかかってしまうのだ。

データベース、例えばPostgreSQLのようなシステムは、大量のデータを効率的に管理している。データが増え、特定の条件に合うデータを頻繁に探し出す必要が出てくると、その検索の仕方を工夫することが非常に重要になる。ここで「インデックス」という機能が役立つ。インデックスは、データベース内の特定の値を探しやすくするための目次のようなものだと考えるとわかりやすい。本で特定のキーワードを探すときに、目次や索引を使うとページを一枚ずつめくるよりもはるかに速く見つけられるのと同じ原理だ。

あるアプリケーションでは、ユーザーが習慣を記録する「HabitHero」というものが開発された。このアプリも、最初は問題なく動いていたが、テストユーザーが増えてタスクや連続達成記録のデータが増加するにつれて、ダッシュボードの表示やAPIからのデータ取得が遅くなるボトルネックに直面した。開発者は当初、自分の書いたプログラムコードに問題があるのではないかと考えたが、詳しく調べてみると、データベースが不必要に多くのデータを読み込んでいることが判明した。これは、データベースが目的のデータを探す際に、データが保存されている場所を最初から最後まで順に全て確認する「シーケンシャルスキャン（Seq Scan）」という非効率な方法をとっていたためだ。

この問題を解決するために、データベースのインデックス機能を活用した。具体例として、100万件の注文データを持つシンプルなordersテーブルが作成された。このテーブルには、order_id（注文ID）、customer_id（顧客ID）、order_date（注文日）、total_amount（合計金額）という情報が含まれている。

Copy
1CREATE TABLE orders (
2    order_id SERIAL PRIMARY KEY,
3    customer_id INT NOT NULL,
4    order_date TIMESTAMP NOT NULL,
5    total_amount NUMERIC(10,2) NOT NULL
6);

このテーブルに100万件のサンプルデータを挿入し、特定の顧客の過去1年間の注文を全て取得するクエリを実行した。これは、HabitHeroで「あるユーザーが過去30日間に完了したタスクをすべて取得する」といった処理に相当する。

Copy
1SELECT *
2FROM orders
3WHERE customer_id = 1234
4  AND order_date >= NOW() - INTERVAL '1 year';

インデックスを一切使用しない状態でこのクエリを実行し、EXPLAIN ANALYZEというコマンドでその実行計画と時間を測定した結果、クエリの実行には約820ミリ秒かかっていた。実行計画には「Seq Scan on orders」と表示されており、これはデータベースがordersテーブル全体を最初から最後までスキャンしていることを示している。

次に、この検索を高速化するためにインデックスを導入した。まず、検索条件として頻繁に利用されるcustomer_idカラムに対して単一のインデックスを作成した。

Copy
1CREATE INDEX idx_customer_id ON orders(customer_id);

このインデックスを作成した後、同じクエリを再実行すると、実行時間は約220ミリ秒まで短縮された。これは、customer_idを基にした目次ができたため、データベースが効率的に目的の顧客のデータを探し出せるようになったことを意味する。実行計画も「Index Scan using idx_customer_id on orders」と変わり、インデックスが使われていることが確認できる。

さらに高速化を目指し、今度はcustomer_idとorder_dateの2つのカラムを組み合わせた「複合インデックス」を作成した。

Copy
1CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

この複合インデックスを作成し、再びクエリを実行したところ、実行時間は約120ミリ秒にまで短縮された。これは、検索条件としてcustomer_idとorder_dateの両方が使われている場合、両方の条件に対応する目次があることで、データベースがよりピンポイントでデータを絞り込めるようになるためだ。単一のインデックスよりもさらに効率的な検索が可能になった。

インデックスの効果は、特定の顧客の注文履歴を探す場合だけにとどまらない。例えば、合計金額が400を超える注文を全て取得するような範囲検索のクエリでも、total_amountにインデックスを作成すると、実行時間は620ミリ秒から130ミリ秒へと大幅に改善する。これは、HabitHeroで獲得XPポイントに基づいてタスクをフィルタリングする際にも同様の効果が見られた。

また、特定の顧客の最新の注文10件を日付の新しい順に並べ替えて取得するような、検索と並べ替えを組み合わせたクエリでも、インデックスは効果を発揮する。

Copy
1SELECT *
2FROM orders
3WHERE customer_id = 1234
4ORDER BY order_date DESC
5LIMIT 10;

このクエリをインデックスなしで実行すると約710ミリ秒かかったが、先ほど作成したcustomer_idとorder_dateの複合インデックスが存在する場合、実行時間は約140ミリ秒にまで短縮された。これは、複合インデックスがcustomer_idでデータを絞り込むだけでなく、order_dateの順序も効率的に利用できるため、並べ替え処理も高速化されたからだ。HabitHeroでは「最新の完了タスク10件」を瞬時に表示するのに役立ったという。

これらの経験から、インデックスをデータベース設計に適用する上でいくつかの重要な教訓が得られた。まず、最も重要なことは、アプリケーションで頻繁にデータを検索したりフィルタリングしたりするカラムにインデックスを作成することである。HabitHeroの例では、user_idとcreated_atにインデックスを貼ることで、ほとんどの遅いクエリが解決した。しかし、インデックスを無計画に多用することは避けるべきだ。インデックスは検索を速くする一方で、データの追加（INSERT）や更新（UPDATE）、削除（DELETE）といった書き込み処理の際に、そのインデックス自体も更新する必要があるため、これらの処理が遅くなるという副作用がある。全てにインデックスを貼ろうとすると、かえってシステム全体のパフォーマンスを悪化させる可能性があるのだ。

インデックスの導入効果を正確に把握するためには、常にEXPLAIN ANALYZEコマンドを使って、インデックスを適用する前と後でクエリの実行計画と時間を測定することが不可欠である。この計測を通じて、どのインデックスが効果的であるかを具体的に確認できる。また、現在のアプリケーションのニーズだけでなく、将来的に必要になるであろうレポート作成やダッシュボード表示のためにどのようなデータ検索が行われるかを予測し、前もってインデックス設計に組み込むことも賢明なアプローチである。

結論として、データベースにおけるインデックスは、アプリケーションのパフォーマンスを劇的に改善するための非常に効果的な手段である。820ミリ秒から120ミリ秒への改善は、数字だけ見れば小さな違いに見えるかもしれない。しかし、これが数百万、数千万といった大規模なデータ処理において積み重なると、ユーザー体験の向上はもちろんのこと、サーバーリソースの節約や運用コストの削減といった、ビジネス上の大きなメリットにつながる。ユーザーが入力する情報に基づいてデータを頻繁に検索するようなアプリケーションを構築する際には、適切なインデックスの適用が、最も費用対効果の高い最適化の一つだと言える。