【ITニュース解説】Using AI to Investigate a Slow Query: Why Django icontains Skipped Our pg_trgm Index

多くのシステム開発プロジェクトでは、データベースの移行は重要な節目だ。新しいデータベースに切り替えた後、思わぬパフォーマンス問題に直面することがある。今回取り上げるのは、まさにそうしたケースで、データベースをMySQLからPostgreSQLへ移行後、製品検索が数秒かかるほど遅くなった。開発チームはDjangoという人気のWebフレームワークを使い、製品名の部分一致検索にicontainsフィルターを使用していた。さらに、PostgreSQLの強力な高速化機能であるpg_trgmを使ったインデックスも設定済みだった。しかし、期待に反して検索は劇的に遅延し、ユーザー体験を損なっていた。

この遅延の根本的な原因は、Djangoのicontainsフィルターが生成するSQLクエリと、開発チームが作成したインデックスの定義が一致していなかったことにある。icontainsは、大文字・小文字を区別しない検索を実現するための便利な機能だが、内部的にPostgreSQLに対して、検索対象の列（product_name）の値を全て大文字に変換し、検索キーワードも大文字に変換してから比較するというSQL文を生成していた。具体的には、UPPER(product_name::text) LIKE UPPER('%検索語%')のような形式のSQL文が発行されていたのだ。一方で、高速化のために設定されたpg_trgmインデックスは、product_nameという元の列そのものに対して作成されていた。データベースは、UPPER()で加工された列に対するインデックスを持っていなかったため、せっかくのインデックスを使うことができなかった。この結果、データベースは、大量のデータを全て読み込んで一行ずつ検索条件と突き合わせるという非常に非効率な方法（シーケンシャルスキャン）を選択してしまい、処理が劇的に遅くなったのだ。インデックスが存在するだけでなく、クエリがそのインデックスを「使える形」で発行されているかが、データベースのパフォーマンスには極めて重要だと判明した。

このパフォーマンス問題の調査では、まずシステムの現状を客観的な数値で把握することから始まった。データベースのCPU使用率が42.7%から94.8%へと跳ね上がり、1回のリクエストにかかるデータベースの処理時間も72ミリ秒から444ミリ秒へと大幅に悪化していることが確認された。特に製品リストの検索にかかるデータベース処理時間は、112ミリ秒から4.67秒へと異常な遅延を示していた。次に、New Relicのようなパフォーマンス監視ツールや、クラウドサービス提供者の監視ツール（CloudWatchやRDSメトリクス）を使い、具体的にどの操作が遅延しているのかを特定し、それに対応するアプリケーションのコード、データベースに実際に発行されているSQLクエリ、そしてデータベースがそのクエリをどう処理するかを示す実行計画（EXPLAINコマンドで確認できる）を詳細に調査した。調査の過程では、AIチャットボット（Claude）も強力な調査支援ツールとして活用された。AIに協力を求める際は、単に「検索が遅い」といった漠然とした質問ではなく、「クエリのフィルター条件とインデックスの定義を比較し、インデックスが使われない原因を特定せよ」といった、具体的な情報（生成されたSQL、インデックス定義、実行計画など）を添えて、検証可能な問いを投げかけることが効果的だった。これにより、アプリケーションの抽象化されたコードの裏側で何が起きているのかを、より効率的に解き明かすことができた。

原因が特定された後、解決策はクエリとインデックスの整合性を取ることに集中した。まず、SQLクエリからUPPER()変換をなくすため、Djangoのicontainsフィルターをcontainsフィルターに変更した。containsフィルターは、デフォルトではUPPER()変換を行わないため、生成されるSQLはproduct_name LIKE '%検索語%'という形になり、product_name列に作成された既存のpg_trgmインデックスをデータベースが直接利用できるようになる。しかし、この変更だけでは、icontainsが提供していた「大文字・小文字を区別しない検索」というユーザー体験が失われてしまう。ユーザーは「Apple」と検索しても「apple」という商品を見つけられることを期待するため、この機能は維持する必要があった。そこで、PostgreSQLのproduct_name列に対して「ケースインセンシティブ（大文字・小文字を区別しない）」な照合順序（collation）を設定した。照合順序とは、文字列の比較方法を定義するもので、これを設定することで、データベース側で大文字・小文字を気にせず比較が行われるようになり、パフォーマンス改善と機能維持の両立が実現した。

修正後の検証では、大規模なテストデータ（約115万件の製品を持つ会社）を使って、修正が本当に効果があるのか、そして期待通りの動作を維持しているかを確認した。結果として、以前は12秒のデータベースタイムアウトを超えていたクエリが、わずか77〜90ミリ秒で完了するようになり、少なくとも130倍以上の劇的なパフォーマンス改善が確認された。この結果は、修正が正しかったことを明確に示している。今回の経験から得られた重要な教訓は、インデックスが存在するだけでなく、クエリがそれを実際に利用できる形になっているかを確認することの重要性だ。また、システムの問題調査では、表面的な現象やアプリケーションコードといった抽象的なレイヤーだけでなく、その下の実際のSQLやデータベースの実行計画、インデックスの使われ方まで深く掘り下げて理解する姿勢が不可欠となる。AIは強力な調査支援ツールとなり得るが、その力を最大限に引き出すには、具体的な証拠に基づいた的確な質問と、最終的な検証を怠らないフィードバックループが欠かせない。これらのアプローチは、未知のシステムエラーに直面した際に、効率的かつ確実に問題解決へと導くための普遍的な方法となるだろう。