pg_stat_statements と pg_stat_activity を使ってクエリのパフォーマンスを分析する

2024-04-05

PostgreSQL EXPLAIN の詳細解説

テーブルの読み取り方法: シーケンシャルスキャン、インデックススキャン、ビットマップスキャンなど
テーブル間の結合方法: ネストループ結合、マージ結合、ハッシュ結合など
クエリの実行にかかるコストの見積もり: 行の読み取り、書き込み、ソートなどに必要なコスト
各ノードの処理時間: 各ステップの実行にかかった時間 (EXPLAIN ANALYZE 使用時)

EXPLAIN の出力は、以下の要素で構成されています。

ノード: クエリプランの各ステップを表す
オペレータ: 各ノードで実行される操作
コスト: 各ノードの実行コスト
行数: 各ノードで処理される行数
幅: 各ノードで処理されるデータの幅 (バイト単位)

EXPLAIN の出力例:

┌────────────────────────────────────────────────────────────┐
│ QUERY PLAN                                                     │
├────────────────────────────────────────────────────────────┤
│ Seq Scan on public.users (cost=0.00..100.00 rows=100 width=144) │
│   Filter: (id = 1)                                            │
└────────────────────────────────────────────────────────────┘

この例では、public.users テーブルをシーケンシャルスキャンし、id が 1 の行を 1 行のみ返すクエリの実行計画が表示されています。

クエリのパフォーマンスを向上させる: クエリの実行計画を分析することで、ボトルネックとなっている部分を見つけ、改善することができます。
インデックスの効果を確認する: インデックスを作成/削除した後に EXPLAIN を実行することで、インデックスがクエリのパフォーマンスにどのように影響しているかを確認することができます。
複雑なクエリの理解を深める: EXPLAIN を使用することで、複雑なクエリの処理の流れを理解することができます。

ANALYZE: 実際のクエリ実行時間を含めた詳細な情報を表示します。
VERBOSE: 詳細な情報を表示します。
BUFFERS: バッファ使用量に関する情報を表示します。
COSTS: コストのみを表示します。
FORMAT: 出力形式を指定します (TEXT, JSON, XML など)。

EXPLAIN コマンドは、PostgreSQL クエリのパフォーマンスを理解し、改善するための強力なツールです。このコマンドを使いこなすことで、クエリの実行計画を分析し、ボトルネックとなっている部分を見つけ、最適化することができます。

-- すべてのユーザーの情報を取得するクエリ

EXPLAIN SELECT * FROM public.users;

-- ID が 1 のユーザー情報を取得するクエリ

EXPLAIN SELECT * FROM public.users WHERE id = 1;

-- 商品テーブルと注文テーブルを結合するクエリ

EXPLAIN SELECT *
FROM public.products p
INNER JOIN public.orders o ON p.id = o.product_id;

-- 商品テーブルのすべての商品を価格で降順に並べ替えるクエリ

EXPLAIN SELECT *
FROM public.products
ORDER BY price DESC;

-- 商品テーブルのすべての商品の合計金額を計算するクエリ

EXPLAIN SELECT SUM(price)
FROM public.products;

-- 実際のクエリ実行時間を含めた詳細な情報を表示する

EXPLAIN ANALYZE SELECT * FROM public.users;

-- 詳細な情報を表示する

EXPLAIN VERBOSE SELECT * FROM public.users;

-- バッファ使用量に関する情報を表示する

EXPLAIN BUFFERS SELECT * FROM public.users;

-- コストのみを表示する

EXPLAIN COSTS SELECT * FROM public.users;

-- 出力形式を JSON 形式で表示する

EXPLAIN FORMAT JSON SELECT * FROM public.users;

これらのオプションを使用することで、EXPLAIN 出力からさらに詳細な情報を取得することができます。

PostgreSQL クエリのパフォーマンスを分析する他の方法

pg_stat_statements は、PostgreSQL によって実行されたすべての SQL 文に関する統計情報を提供するビューです。このビューには、以下の情報が含まれます。

クエリの実行回数
クエリによって読み取られた行数

pg_stat_activity は、現在実行中のすべての PostgreSQL セッションに関する情報を提供するビューです。このビューには、以下の情報が含まれます。

セッションが実行しているクエリ
セッションが使用している CPU 時間

PostgreSQL ツール:

pgAdmin: PostgreSQL データベースを管理するための GUI ツールです。pgAdmin には、クエリの分析機能も含まれています。
pgBadger: PostgreSQL クエリのパフォーマンスを分析するためのツールです。pgBadger は、pg_stat_statements ビューからの情報を収集して、わかりやすいレポートを生成します。

EXPLAIN コマンドは、PostgreSQL クエリのパフォーマンスを分析するための強力なツールです。しかし、他にもいくつかの方法があります。状況に応じて、最適な方法を選択してください。

postgresql