DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

EXPLAIN ANALYZEの習得：PostgreSQLクエリプラン最適化ガイド

PostgreSQLのパフォーマンスを引き出す包括的なEXPLAIN ANALYZEガイド。クエリ実行計画の解釈、ボトルネックの特定、SQLクエリの最適化方法を学びます。このガイドでは、基本概念、ノードタイプ、出力の解釈、実践的な最適化戦略を具体的な例とともに解説します。PostgreSQLがどのようにクエリを実行するかを理解し、データベースのパフォーマンスをマスターしましょう。

EXPLAIN ANALYZEの習得：PostgreSQLクエリプラン最適化ガイド

EXPLAIN ANALYZEは、PostgreSQLのクエリが遅く、通常の推測では不十分な場合に私が頼りにするツールです。アプリケーションコードではクエリが無害に見えるかもしれません。テーブルにインデックスがあり、誰もがデータベースがそれを使っていると思っているかもしれません。ステージングでは速いが本番では遅いクエリかもしれません。計画は、それらの前提が成り立つか、崩れるかを示します。

有用な習慣は、計画をPostgreSQLが行った作業の物語として読むことです：どの行に触れると予想したか、実際にどの行に触れたか、どこで結合したか、どこでソートしたか、メモリ内に収まったか、ディスクから読み取る必要があったか。これが有用になる前にすべての計画ノードを暗記する必要はありません。推定値と現実を比較するために、ゆっくりと時間をかける必要があります。

EXPLAINとEXPLAIN ANALYZEの理解

EXPLAINとEXPLAIN ANALYZEの違いは重要です。一方は予測であり、もう一方は測定だからです。

EXPLAIN

EXPLAINを前置したクエリを実行すると、PostgreSQLはクエリを実際に実行せずに意図された実行計画を生成します。これは以下の場合に有用です：

  • 計画のプレビュー： PostgreSQLがクエリを実行する最も安価な方法として何を期待しているかを確認できます。
  • コストの推定： 計画内の各ノードのコスト推定値を提供し、リソース使用量の相対的なアイデアを与えます。

例：

EXPLAIN SELECT * FROM users WHERE registration_date > '2023-01-01';

EXPLAIN ANALYZE

EXPLAIN ANALYZEはさらに一歩進んでいます。計画された実行を示すだけでなく、クエリを実行し、実際の実行統計を報告します。つまり、以下の情報が得られます：

  • 実際の実行時間： 各ステップが実際にどれだけかかったか。
  • 実際の行数： 各ノードで実際に処理された行数。
  • 推定の確認： 推定行数と実際の行数を比較して、PostgreSQLのプランナーが正確な予測を行っているかどうかを確認できます。

これにより、EXPLAIN ANALYZEは実際のチューニングに適したツールですが、鋭いエッジがあります：クエリを実行します。SELECTは、大量のデータをスキャンしたり、ロックを取得したり、キャッシュを競合したりする可能性があるため、依然として高コストになる可能性があります。UPDATE、DELETE、INSERTは、トランザクションでラップしてロールバックしない限り、実際にデータを変更します：

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE accounts SET status = 'archived' WHERE last_seen_at < now() - interval '2 years';
ROLLBACK;

このパターンは、メンテナンスウィンドウやステージングコピーで有用です。忙しい本番データベースで危険なステートメントを実行するための自由なパスではありません。

例：

EXPLAIN ANALYZE SELECT * FROM users WHERE registration_date > '2023-01-01';

EXPLAIN ANALYZEの出力の解読

EXPLAIN ANALYZEの出力は最初は密集して見えるかもしれませんが、その主要な構成要素を理解することが基本です。

コアコンポーネント：

  • ノードタイプ： 実行される操作を識別します（例：Seq Scan、Index Scan、Hash Join、Nested Loop、Sort、Aggregate）。
  • コスト： (startup_cost .. total_cost)として表示されます。
    • startup_cost：最初の行を取得するためのコスト。
    • total_cost：すべての行を取得するためのコスト。
    • 注：コストは比較のための任意の単位であり、時間やメモリを直接示すものではありません。
  • 行： プランナーがこのノードから返すと予想する行数の推定値。
  • 幅： このノードから返される行の推定平均幅（バイト単位）。
  • 実際の時間： (startup_time .. total_time)として表示されます。これは、このノードを実行するための実際の時間（ミリ秒）です。
    • startup_time：最初の行を返すまでの実際の時間。
    • total_time：すべての行を返すまでの実際の時間。
  • 実際の行数： このノードから返された実際の行数。
  • ループ： このノードが実行された回数。トップレベルのノードでは通常1です。ネストされた操作では、より高くなる可能性があります。

出力解釈の例：

大きなテーブルでのSeq Scan（シーケンシャルスキャン）の簡略化された例を考えてみましょう：

Seq Scan on users  (cost=0.00..15000.00 rows=1000000 width=100) (actual time=0.020..150.500 rows=950000 loops=1)
  Filter: (registration_date > '2023-01-01')
  Rows Removed by Filter: 50000

解釈：

  • Seq Scan on users：データベースがusersテーブルのすべての行を読み取っています。
  • cost=0.00..15000.00：プランナーは総コストを約15000単位と推定しました。
  • rows=1000000：プランナーはテーブルに100万行あると推定しました。
  • actual time=0.020..150.500：スキャンとフィルタリングの完了に実際に150.5ミリ秒かかりました。
  • rows=950000：実際に950,000行を返しました（フィルタリング後）。
  • loops=1：このスキャンは1回実行されました。
  • Filter: (registration_date > '2023-01-01')：これは行をフィルタリングするために適用された条件です。
  • Rows Removed by Filter: 50000：フィルタによって50,000行が破棄されました。

ボトルネックの特定： 最大のactual timeだけを見ないでください。また、何度も実行されるノードを探してください。0.2ミリ秒かかるネストループ内部スキャンは、loops=50000になるまでは無害に見えるかもしれません。その場合、実際のコストはおおよそループあたりの時間にループ数を掛けたものになります。

内側から外側へ読む

PostgreSQLの計画はツリーです。トップノードは最終結果をクライアントに返しますが、作業は通常計画のより深いところから始まります。クエリがorders、customers、order_itemsを結合する場合、トップラインはAggregateかもしれませんが、実際の痛みはその下のスキャンや結合にあるかもしれません。

私は通常、次の順序で計画を読みます：

  1. 最も深いスキャンノードから始めて、PostgreSQLがクエリが返すよりもはるかに多くの行を読み取ったかどうかを尋ねます。
  2. 推定rowsと実際のrowsを比較します。
  3. 高コストのノードに高いloopsがあるかどうかを確認します。
  4. ディスクにあふれ出るSort、Hash、またはMaterializeノードを探します。
  5. BUFFERSを使用して、クエリが主にCPU/キャッシュ作業かディスクI/Oかを判断します。

以下は一般的な例です：

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

数百万のorders行に対するシーケンシャルスキャン、次にソート、そして制限が表示される場合、データベースは要求された20行を返す前にあまりにも多くの作業を行っています。実用的なインデックスは次のようになります：

CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);

その後、適切な計画はインデックスを使用して、その顧客の最新の注文に直接移動し、20行後に停止する可能性があります。正確な計画は、テーブルサイズ、統計、PostgreSQLのバージョン、データ分布によって異なりますが、原則は安定しています：実際に使用するフィルタと順序付けパターンにインデックスを一致させます。

一般的なクエリプランノードと最適化戦略

さまざまなタイプのノードとその最適化方法を理解することは、クエリパフォーマンスを習得するための鍵です。

1. シーケンシャルスキャン（Seq Scan）

  • 概要： テーブルのすべての行を読み取ります。これは、特に特定の条件でフィルタリングする場合、大きなテーブルでは非効率的であることがよくあります。
  • 適切な場合： 小さなテーブルの場合、またはテーブルの行の大部分を取得する必要がある場合。シーケンシャルスキャンは自動的に悪いわけではありません。
  • 最適化： 選択的なフィルタ列にインデックスを作成しますが、計画で確認します。述語がテーブルの大部分を返す場合、PostgreSQLは正しくシーケンシャルスキャンを引き続き使用する可能性があります。

2. インデックススキャン（Index Scan）

  • 概要： インデックスを使用してWHERE句に一致する行を見つけます。PostgreSQLはインデックスをトラバースし、次にテーブルから対応する行をフェッチします。
  • 最適化： インデックスがクエリの形状と一致していることを確認します。複合インデックスの場合、列の順序が重要です。(tenant_id, created_at)のインデックスは、tenant_idでフィルタリングしcreated_atでソートするクエリに役立ちますが、created_atのみでフィルタリングするクエリにはあまり役立たない可能性があります。

3. インデックスオンリースキャン（Index Only Scan）

  • 概要： 最適化されたIndex Scanで、クエリに必要なすべてのデータがインデックス内で直接利用可能です。PostgreSQLはテーブルヒープにアクセスする必要がありません。
  • 効率的な場合： 選択されたすべての列がインデックスから利用可能で、可視性マップによりPostgreSQLが多くのヒープチェックを回避できる場合。
  • 最適化： 読み取りが多いパスにはINCLUDEを使用したカバリングインデックスを検討しますが、「念のため」すべての列を追加しないでください。インデックスが大きいと、書き込み時のメンテナンスコストが高くなります。

4. 結合操作（Nested Loop、Hash Join、Merge Join）

  • Nested Loop： 外部リレーションの各行について、PostgreSQLは内部リレーションをスキャンします。外部リレーションが小さい場合、または内部リレーションにインデックスを介して迅速にアクセスできる場合に効率的です。
  • Hash Join： 一方のリレーション（ビルド側）からハッシュテーブルを作成し、もう一方のリレーション（プローブ側）の行でそれをプローブします。結合条件にインデックスが有益でない大きなテーブルに効率的です。
  • Merge Join： 両方のリレーションが結合キーでソートされている必要があります。ソートされたリストをマージします。大きく、すでにソートされた入力に効率的です。
  • 最適化：
    • 結合列にインデックスが存在することを確認します。
    • 不適切な行推定が貧弱な結合選択を引き起こしたかどうかを確認します。PostgreSQLは一部のデータベースと同じスタイルのネイティブオプティマイザヒントをサポートしていないため、通常の修正は、より良い統計、より良いインデックス、またはクエリの書き換えです。
    • 結合ノードで大きなloops数や高いactual timeがないかEXPLAIN ANALYZEを確認します。

5. ソート（Sort）

  • 概要： 行を順序付けます。特に大規模なデータセットでは、計算コストが高くなる可能性があります。
  • 最適化：
    • クエリが十分に選択的である場合、列の順序がORDER BYパターンと一致するインデックスを追加します。
    • より制限的なWHERE句を追加して、ソートされる行数を減らします。
    • ソートがディスクではなくメモリ内で行われるように、十分なwork_memが設定されていることを確認します。

6. 集約（Aggregate）

  • 概要： COUNT()、SUM()、AVG()、GROUP BYなどの操作を実行します。
  • 最適化：
    • WHERE句が効率的で、集約前の行数を減らしていることを確認します。
    • 集約が頻繁で遅い操作である場合は、事前集約データにマテリアライズドビューを使用することを検討します。
    • GROUP BY句で使用される列にインデックスを作成します。

オプションを使用したEXPLAIN ANALYZE

EXPLAIN ANALYZEには、さらに詳細な情報を提供できるいくつかの便利なオプションがあります。

VERBOSE

  • 機能： スキーマ修飾テーブル名や出力列名など、クエリ計画に関する追加情報を表示します。
EXPLAIN (ANALYZE, VERBOSE) SELECT u.name FROM users u WHERE u.id = 1;

COSTS

  • 機能： 出力に推定コストを含めます。これはデフォルトの動作ですが、明示的にオフにすることができます。
EXPLAIN (ANALYZE, COSTS FALSE) SELECT COUNT(*) FROM orders;

BUFFERS

  • 機能： バッファ使用量（共有、一時、ローカル）に関する情報を報告します。これはI/Oボトルネックの特定に役立ちます。
    • shared hit：PostgreSQLの共有バッファキャッシュで見つかったブロック。
    • shared read：ディスクから共有バッファに読み取られたブロック。
    • temp read/written：一時ファイルに読み書きされたブロック（多くの場合、work_memを超えるソートやハッシュ用）。
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE category = 'Electronics';

TIMING

  • 機能： 各ノードの実際の起動時間と合計時間を含めます。これはANALYZEのデフォルトの動作です。
EXPLAIN (ANALYZE, TIMING FALSE) SELECT * FROM logs LIMIT 10;

オプションの組み合わせ

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.order_date, COUNT(oi.product_id)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2023-01-01'
GROUP BY o.order_date;

実践的なヒントとベストプラクティス

  • EXPLAIN ANALYZEから始める： 実際のパフォーマンス分析には常にEXPLAIN ANALYZEを使用します。EXPLAINだけでは不十分です。
  • actual timeに焦点を当てる： 最も高いactual timeを持つノードの最適化を優先します。
  • rows（推定対実際）を比較する： 大きな不一致は、PostgreSQLのクエリプランナーが不正確な仮定を行っている可能性があることを示します。これは、ANALYZE <table_name>;を使用してテーブル統計を更新するか、適切なインデックスを作成することで修正できることがよくあります。
  • BUFFERSを使用する： バッファ使用量を分析して、クエリがI/Oバウンドかどうかを理解します。
  • 現実的なデータでテストする： 本番環境と同様のデータ量とデータ分布を持つデータベースでEXPLAIN ANALYZEを実行します。
  • 段階的に最適化する： 一度にすべてを最適化しようとしないでください。最初に最大のボトルネックに対処します。
  • work_memを検討する： ソートやハッシュにかなりのディスク読み取りがある場合（BUFFERSのtemp read/written）、work_memを増やす（セッションごとまたはグローバルに）ことが役立つ場合がありますが、メモリ使用量に注意してください。
  • インデックスを賢く作成する： 実際に使用され、有益なインデックスのみを作成します。インデックスが多すぎると、書き込みが遅くなり、ディスク容量を消費する可能性があります。
  • PostgreSQLのバージョンを確認する： 新しいバージョンでは、多くの場合、クエリプランナーが改善され、パフォーマンスに影響を与える可能性のある新機能が追加されています。

実践的なチューニングパス

次のクエリを考えてみましょう：

SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

計画がシーケンシャルスキャンを示す場合、emailのみのインデックスは、クエリがlower(email)を適用するため、役に立たない可能性があります。PostgreSQLは、クエリ内の式がインデックス値と異なる場合、プレーンなインデックスを常に使用できるとは限りません。より良いオプションは、式インデックスかもしれません：

CREATE INDEX CONCURRENTLY users_lower_email_idx
ON users (lower(email));

次に再実行します：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, created_at
FROM users
WHERE lower(email) = lower('[email protected]');

スキャンされる行数が少なくなり、読み取られるバッファが少なくなり、実行時間が短縮されることを確認します。計画がまだインデックスを使用しない場合は、テーブルが非常に小さいか、統計が古いか、クエリがアプリケーションが送信する方法と異なる方法で記述されていないかを確認します。

もう1つの一般的なケースは、SQLでは問題なく見えるが、計画で爆発する結合です：

SELECT o.id, p.sku
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= current_date - interval '7 days';

有用なインデックスには、orders(created_at)、order_items(order_id)、およびproducts(id)の主キーが含まれる場合があります。しかし、過去7日間にordersテーブルの大部分が含まれている場合、orders(created_at)は主要な修正ではない可能性があります。計画は、実際の問題が日付フィルター、結合ファンアウト、または子テーブルの欠落したインデックスであるかどうかを示します。

優れたPostgreSQLクエリチューニングは、「計画が変わるまでインデックスを追加する」ことではありません。それはループです：実際の計画を測定し、1つの防御可能な変更を行い、再度測定し、実際に気にするワークロードを改善する場合にのみ変更を保持します。