AlloyDB の EXPLAIN プランを取得して分析する

実行プランを取得して分析することで、パフォーマンスのボトルネックを特定し、AlloyDB for PostgreSQL データベースオペレーションを最適化できます。実行プラン（EXPLAIN プラン）は、AlloyDB データベースエンジンが SQL クエリをどのように実行するかを詳細に表現したものです。実行プランは、リクエストされたデータを取得するために AlloyDB データベースが実行するオペレーション（テーブルスキャン、結合、並べ替え、集計など）のシーケンスを示すノードのツリーで構成されています。このプランの各ステップはノードと呼ばれます。

実行プランは EXPLAIN コマンドを使用して取得します。このコマンドは、AlloyDB クエリプランナーが特定の SQL ステートメントに対して生成したプランを返します。クエリプランナー（オプティマイザーとも呼ばれます）は、特定の SQL クエリを実行する最も効率的な方法を決定します。

実行プランは、次のコンポーネントで構成されます。

プランノード: クエリ実行の各ステップ（スキャン、結合、並べ替えオペレーションなど）を表します。
実行時間: EXPLAIN プランには、各ステップの推定実行時間または実際の実行時間が含まれています。これにより、データベースのボトルネックを特定できます。
バッファ使用量: ディスクから読み取られたデータとキャッシュから読み取られたデータの量を示します。これにより、ディスク読み取りの問題を特定できます。
パラメータ設定: クエリ実行時に有効なパラメータ設定を示します。

PostgreSQL（および拡張機能である AlloyDB）は、次のステートメントの実行プランをサポートしています。

SELECT
INSERT
UPDATE
DECLARE CURSOR
CREATE AS
CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
EXECUTE

始める前に

AlloyDB クラスタとインスタンスが必要です。詳細については、クラスタとそのプライマリインスタンスを作成するをご覧ください。

実行プランを生成する

実行プランは、psql、pgAdmin、DBeaver などのクライアントアプリケーションから生成します。AlloyDB は、テキスト形式または JSON 形式での実行プランの生成をサポートしています。

実行プランを生成する手順は次のとおりです。

psql クライアントをインスタンスに接続します。

テキスト形式で実行プランを生成するには、次のコマンドを実行します。

SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format text)

JSON 形式で実行プランを生成するには、次のコマンドを実行します。
```
SET enable_ultra_fast_cache_explain_output TO ON; /* AlloyDB Specific Command */
EXPLAIN (analyze, verbose, columnar_engine, costs, settings, buffers, wal, timing, summary, format json)
```
EXPLAIN コマンドには、使用可能なすべてのオプション（analyze、verbose、columnar_engine、costs、settings、buffers、wal、timing、summary）が含まれており、特定のクエリの詳細な実行プランをテキスト形式または JSON 形式で生成できます。analyze オプションを使用すると、クエリプランナーの推定値だけでなく、クエリを実行することで実際のランタイム統計情報も提供されます。

EXPLAIN プランのデータを表示して分析する

実行プランを取得したら、結果を表示して分析します。

デフォルトでは、EXPLAIN 出力にはサーバーサイドのクエリアクティビティが表示されます。エンドツーエンドのラウンドトリップ時間を測定するには、psql で /timing オプションを使用し、結果を /dev/null にダンプします。

生成した実行プランを表示するには、SQL クエリの前に EXPLAIN コマンドを使用します。

EXPLAIN SELECT...: クエリを実行せずにオプティマイザーが選択するプランを表示します。
EXPLAIN ANALYZE SELECT...: クエリを実行し、予測プランと実際の実行統計（実際の実行時間や行数など）の両方を表示します。

ANALYZE なしの EXPLAIN

クエリプランナーの推定クエリ費用を表示するには、ANALYZE オプションなしで EXPLAIN ステートメントを実行します。

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27)
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(3 rows)

プランの出力には次のデータが含まれます。

cost = 0.00..1735481.00: 最初の数値は、最初の行を取得するための費用を示します。2 番目の数値は、最後の行を取得するための費用を示します。
rows = 100000000: クエリが返す行数の推定値です。
width = 27: これは、返された行の推定幅です。アクセスされたブロック数を把握するのに役立ちます。

ANALYZE オプション

実際の実行統計と実行の推定値を表示するには、ANALYZE オプションを追加します。

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.165..9342.424 rows=100000001 loops=1)
 Planning Time: 0.025 ms
 Execution Time: 13674.794 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483
(5 rows)

プランの出力には次のデータが含まれます。

actual time (in ms) = 0.165..9342.424: 最初の行を返すのに実際にかかった時間と、すべての行を返すのにかかった合計時間を示します。
rows = 100000001: 実際に返された行数です。
loops = 1: ネストされたループノードで重要な意味を持つ値です。loops が 1 より大きい場合は、ループあたりの平均時間を示します。
Planning Time: 0.025 ms: プランナーが実行パスを決定するのにかかった時間を示します。
Execution Time: 13674.794 ms: これは、プランナーがパスを決定してから実行にかかった時間を示します。
Total Execution Time: Planning Time と Execution Time の合計です。（0.025 + 13674.794 = 13674.819）

VERBOSE オプション

実行プランに詳細情報を追加するには、VERBOSE オプションを使用します。次の例では、VERBOSE を使用してテーブル名にスキーマ修飾子を追加するほか、クエリを他のモニタリングツールと関連付けるために使用できる内部クエリ ID を示しています。

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.164..6568.938 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10875.894 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

プランの出力には次のデータが含まれます。

Output Node: クエリに含まれる列を一覧表示します。プランナーは、列を残しておく方がコストが高くなると判断した場合、リクエストされた列よりも多くの列を含めることがあります。
Query Identifier: pg_stat_statements にマッピングされる PostgreSQL の ID。
AlloyDB query id: クエリ分析情報の関連付けに使用できる AlloyDB のクエリ ID

COLUMNAR ENGINE オプション

AlloyDB のカラム型エンジンの情報を表示するには、COLUMNAR_ENGINE オプションを追加します。

テーブルがカラム型エンジンに存在しない場合は、次の実行プランの Columnar Check 列でステータスを確認します。

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.009..6328.154 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10673.310 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

テーブルがカラム型エンジンに存在し、カラム型が使用されている場合、カラム型エンジンの使用状況に関する統計情報とともに、Custom Scan が示されます。

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE) select * from public.index_advisor_test where product_id = 1;
                                   QUERY PLAN
---------------------------------------------------------------------------------
Append  (cost=20.00..27438.78 rows=1166668 width=27) (actual time=0.066..377.029 rows=1000290 loops=1)
   ->  Custom Scan (columnar scan) on public.index_advisor_test  (cost=20.00..27437.66 rows=1166667 width=27) (actual time=0.065..296.904 rows=1000290 loops=1)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
         Rows Removed by Columnar Filter: 98999711
         Bytes fetched from storage cache: 774835915
         Columnar cache search mode: native
         Swap-in Time: 92.708 ms
   ->  Seq Scan on public.index_advisor_test  (cost=0.00..1.11 rows=1 width=27) (never executed)
         Output: id, value, product_id, effective_date
         Filter: (index_advisor_test.product_id = 1)
 Query Identifier: -4660018746142248761
 Planning Time: 0.217 ms
 Execution Time: 421.114 ms
 AlloyDB query id: 13855683355620344431
 AlloyDB plan id: 2126918133221480510

プランの出力には次のデータが含まれます。

Filter（述語）: フィルタを使用している場合は、適用されたフィルタが表示されます。
Rows Removed by Columnar Filter: カラム型フィルタによって削除された行数が表示されます。
Bytes fetched from storage cache: ストレージキャッシュから取得されたバイト数が表示されます。
Swap-in Time: リレーションがメモリに収まらない場合に、カラム型のスピルキャッシュ（SSD）からデータをスワップするのにかかる時間です。

SETTINGS オプション

プランナーが使用するデフォルト以外のセッション設定、データベース設定、グローバル設定を表示するには、SETTINGS オプションを追加します。

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS) select * from public.index_advisor_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.index_advisor_test  (cost=0.00..1735481.00 rows=100000000 width=27) (actual time=0.007..6366.249 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5175350694118300766
 Planning Time: 0.027 ms
 Execution Time: 10727.068 ms
 AlloyDB query id: 7588555592527881263
 AlloyDB plan id: 16134093550604823483

BUFFERS オプション

データソースに関する情報を表示するには、BUFFERS キーワードを使用します。BUFFERS のカウントは、プランの特定のステップだけでなく、すべてのステップから累積されます。

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.111..10007.193 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2588.597
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning:
   Buffers: shared hit=58 read=2, ultra fast cache hit=2
   I/O Timings: shared read=0.215
 Planning Time: 0.410 ms
 Execution Time: 14825.271 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

バッファ情報は次の内容で構成されます。

shared hit: PostgreSQL のメイン共有バッファキャッシュで見つかった 8 KB のデータブロックの数。
shared read: オペレーティングシステムから読み取られた 8 KB のデータブロックの数。多くの場合、ディスク I/O を示します。
dirtied: 未変更であったブロックのうち、クエリによって変更された（可視性マップの変更）ブロックの数。
written: ダーティ状態であったブロックのうち、クエリ処理中にこのバックエンドによってキャッシュから強制排除されたブロックの数。通常はヒントビットまたは可視性マップの変更が原因で、ディスクにフラッシュされます。
ultra fast cache hit: 超高速キャッシュから取得されたブロックの数。
I/O Timings: ディスクまたは SSD の I/O 実行時間（ミリ秒単位）。
Planning: カタログテーブルからメタデータや統計情報を読み取るなど、計画フェーズでのバッファアクティビティ。
Time spent on I/O during planning: ディスクからの読み取りが必要なメタデータがある場合、I/O 時間が表示されます。

WAL オプション

write-ahead log 書き込み（WAL）アクティビティに関する情報を提供するには、WAL オプションを使用します。

(postgres@10.3.1.17:5432) [postgres] > EXPLAIN (ANALYZE,VERBOSE, COLUMNAR_ENGINE,COSTS,SETTINGS, BUFFERS, WAL) select * from public.effective_io_concurrency_test;

                                   QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on public.effective_io_concurrency_test  (cost=0.00..1844356.96 rows=100058296 width=31) (actual time=0.010..10147.314 rows=100000001 loops=1)
   Output: id, value, product_id, effective_date
   Buffers: shared hit=1185 read=842625 dirtied=1, ultra fast cache hit=842625
   I/O Timings: shared read=2590.410
   WAL: records=18 bytes=5178
   Columnar Check: table is not in the columnar store
 Settings: effective_cache_size = '19690120kB', random_page_cost = '1.1', work_mem = '256MB'
 Query Identifier: -5987221491374921441
 Planning Time: 0.030 ms
 Execution Time: 15033.004 ms
 AlloyDB query id: 12754549874439071326
 AlloyDB plan id: 13656575252012920931

WAL 情報は次の内容で構成されます。

WAL: records: 整合性を維持するために読み取られた WAL レコードの数。
WAL: bytes: 整合性を維持するために WAL から読み取られたバイト数。
変更済みでチェックポイントは未設定のレコード: 変更済みで、チェックポイントはまだ設定されていないレコードを示します。

次のステップ

実行プランのノードタイプを確認する。

AlloyDB の EXPLAIN プランを取得して分析する コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

始める前に

実行プランを生成する

EXPLAIN プランのデータを表示して分析する

ANALYZE なしの EXPLAIN

ANALYZE オプション

VERBOSE オプション

COLUMNAR ENGINE オプション

SETTINGS オプション

BUFFERS オプション

WAL オプション

次のステップ

AlloyDB の EXPLAIN プランを取得して分析する