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

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

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

実行プランには次のコンポーネントが含まれます。

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

PostgreSQL(および AlloyDB)は、次のステートメントの実行プランをサポートしています。

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

始める前に

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

実行プランを生成する

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

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

  1. psql クライアントをインスタンスに接続する
  2. 実行プランをテキスト形式で生成するには、次のコマンドを実行します。

    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)
    
  3. 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 コマンドには、使用可能なすべてのオプション(analyzeverbosecolumnar_enginecostssettingsbufferswaltimingsummary)が含まれており、指定されたクエリの詳細な実行プランをテキスト形式または 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)

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

  • 実際の時間(ミリ秒)= 0.165..9342.424: 最初の行を返すまでの実際の時間と、すべての行を返すまでの合計時間を示します。
  • rows = 100000001: 実際に返された行数。
  • loops = 1: この値はネストされたループノードで重要です。loops が 1 より大きい場合、ループあたりの平均時間が表示されます。
  • Planning Time: 0.025 ms: プランナーが実行パスを決定するのにかかった時間を示します。
  • Execution Time: 13674.794 ms: これは、プランナーがパスを決定した後に実行にかかった時間を示します。
  • 合計実行時間: Planning TimeExecution 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

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

  • 出力ノード: クエリに含まれる列を一覧表示します。プランナーは、列を残す方がコストが高いと判断した場合、リクエストされた列よりも多くの列を含めることがあります。
  • クエリ ID: pg_stat_statements にマッピングされる PostgreSQL ID。
  • AlloyDB クエリ ID: クエリ分析情報の関連付けに使用できる AlloyDB クエリ識別子

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

テーブルがカラム型エンジンに存在し、カラム型が使用されている場合、カラム型エンジンの使用方法に関する統計情報とともに、カスタム スキャンが示されます。

(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

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

  • クエリフィルタ(述語): フィルタを使用している場合は、適用されたフィルタが表示されます。
  • Rows Removed by Columnar Filter: カラム型フィルタによって削除された行数を示します。
  • ストレージ キャッシュから取得されたバイト数: ストレージ キャッシュから取得されたバイト数を示します。
  • スワップイン時間: 関係がメモリに収まらない場合に、列形式のスピル キャッシュ(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: クエリによって変更された、以前に変更されていないブロックの数(可視性マップの変更)。
  • 書き込み: クエリ処理中にこのバックエンドによってキャッシュから削除された、以前にダーティになったブロックの数。通常、ヒントビットまたは可視性マップの変更が原因で、ディスクにフラッシュされます。
  • 超高速キャッシュ ヒット: 超高速キャッシュから取得されたブロックの数。
  • I/O タイミング: ディスクまたは SSD の I/O の時間(ミリ秒単位)。
  • 計画: カタログ テーブルからメタデータや統計情報を読み取るなど、計画フェーズでのバッファ アクティビティ。
  • プランニング中の I/O に費やされた時間: ディスクからの読み取りが必要なメタデータがある場合、I/O 時間が表示されます。

WAL オプション

Write-Ahead Logging(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 レコード: 整合性を維持するために読み取られた WAL レコードの数。
  • WAL バイト数: 一貫性を維持するために WAL から読み取られたバイト数。
  • 変更されたがチェックポイントがまだ設定されていないレコード: 変更されたがチェックポイントがまだ設定されていないレコードを示します。

次のステップ