PostgreSQLのEXPLAIN ANALYZEでSQL性能問題を分析する方法｜実行計画の見方と改善ポイントを解説

PostgreSQLでSQLの処理速度が遅い場合、原因を感覚だけで判断するのは難しいものです。そのようなときに役立つ機能がEXPLAIN ANALYZEです。

EXPLAIN ANALYZEを利用すると、PostgreSQLがSQLをどのように実行したのか、どの処理に時間がかかっているのか、インデックスが有効に使われているのかなどを具体的に確認できます。

この記事では、EXPLAIN ANALYZEの基本的な使い方から、実行計画の読み方、性能改善につなげるための確認ポイントまで詳しく解説します。

PostgreSQLのEXPLAIN ANALYZEとは

EXPLAIN ANALYZEとは、PostgreSQLでSQL文の実行計画を確認するためのコマンドです。通常のEXPLAINが「PostgreSQLが予定している処理方法」を表示するのに対して、EXPLAIN ANALYZEでは実際にSQLを実行した結果も表示されます。

実行計画には、テーブルへのアクセス方法、結合方法、処理時間、読み取った行数などが含まれています。そのため、SQLが遅い原因を具体的な数値で確認できます。

例えば、同じ検索SQLでも「インデックスを利用して数件だけ取得している」のか、「テーブル全体を読み込んで数百万件確認している」のかによって、処理時間は大きく変わります。

EXPLAIN ANALYZEは、確認したいSQL文の前に付けて実行します。

例えば、以下のように使用します。

EXPLAIN ANALYZE SELECT * FROM users WHERE user_id = 100;

実行すると、PostgreSQLが実際に行った処理内容が表示されます。SELECT文だけでなく、UPDATEやDELETEなどの処理でも利用できます。

ただし、EXPLAIN ANALYZEはSQLを実際に実行するため、更新系SQLで使用する場合はデータ変更が発生する可能性があります。確認時はトランザクション内で実行するなど注意が必要です。

EXPLAIN ANALYZEの結果には多くの情報が表示されますが、まずは以下の項目を確認すると性能問題の原因を見つけやすくなります。

特に重要なのはactual timeとrowsです。PostgreSQLが想定した処理量と、実際の処理量に大きな差がある場合、統計情報の問題や実行計画の選択ミスが発生している可能性があります。

EXPLAIN ANALYZEでよく確認するポイントのひとつがSeq Scanです。Seq Scanとは、テーブル全体を順番に読み取る処理です。

データ量が少ない場合はSeq Scanでも問題ありません。しかし、数百万件のデータを持つテーブルで、一部のデータだけ取得したいSQLがSeq Scanになっている場合は、性能低下の原因になることがあります。

例えば、注文履歴テーブルから特定ユーザーの注文だけ取得するSQLで、ユーザーIDにインデックスが存在しない場合、PostgreSQLは全件確認する必要があります。この場合は適切なインデックス作成によって改善できる可能性があります。

Index Scanは、インデックスを利用して目的のデータを検索する処理です。大量データの中から少数の行を取得する場合に効率的です。

一方、Bitmap Index ScanやBitmap Heap Scanは、多くの条件に一致するデータを効率よく取得するために使われる処理です。

例えば、数百万件の商品の中から特定カテゴリの商品を数万件取得する場合、1件ずつIndex ScanするよりBitmap Scanの方が効率的になることがあります。

SQL性能問題を分析するときは、単純に特定の処理が表示されたから悪いと判断するのではなく、実際の処理内容と照らし合わせることが重要です。

確認するポイントとしては以下があります。

例えば、実行計画の中で特定のJOIN処理だけ数秒かかっている場合、その部分の結合条件や利用されているインデックスを重点的に確認します。

例えば、社員情報テーブルから部署IDで検索するSQLが遅いケースを考えます。

実行計画を確認した結果、Seq Scanで全社員データを読み込んでいた場合、部署ID列へインデックスを追加することでIndex Scanへ変更され、検索時間が大幅に短縮される可能性があります。

また、不要な列をSELECTしている場合や、大量データを一度に取得している場合は、SQL自体を見直すことで改善できることもあります。

EXPLAIN ANALYZEは非常に便利ですが、実際にSQLを実行する点には注意が必要です。

大量データを処理するSQLでは、分析自体に時間がかかる場合があります。本番環境で実行する場合は、負荷やデータ更新への影響を考慮して利用することが重要です。

また、実行計画はデータ量や統計情報によって変化します。一度改善したSQLでも、データ増加後に再確認することで新たな問題を発見できます。

PostgreSQLのEXPLAIN ANALYZEを利用すると、SQLが遅い原因を実行計画と実測値から具体的に分析できます。

確認するときは、処理時間が長い部分、Seq Scanの発生、インデックス利用状況、実際の取得行数などを見ることが重要です。

SQLの高速化では、単純に特定の処理をなくすことが目的ではありません。EXPLAIN ANALYZEでPostgreSQLがどのような判断をしているかを理解し、データ量や処理内容に合わせてSQLやインデックスを改善することが、安定した性能につながります。