Amazon Redshift のクエリレポートとクエリプランを関連付けるにはどうすればよいですか?

最終更新日: 2020 年 10 月 28 日

Amazon Redshift クラスターのクエリレポートとクエリプランを関連付けようとしています。どうすればそれができますか?

簡単な説明

Amazon Redshift でクエリを実行するために必要な使用量を判断するには、EXPLAIN コマンドを使用します。EXPLAIN コマンドは、実際にクエリを実行せずに、クエリ文の実行プランを表示します。実行プランには、関連するクエリ計画と実行ステップの概要が示されます。

次に、SVL_QUERY_REPORT システムビューを使用して、クラスタースライスレベルでクエリ情報を表示します。スライスレベルの情報を使用して、クエリのパフォーマンスに影響する、クラスター全体の不均一なデータ分散を検出できます。

注: SVL_QUERY_REPORT では、rows 列はクラスタースライスごとに処理される行数を示します。rows_pre_filter 列は、削除対象としてマークされた行をフィルタリングする前に生成される行の総数を示します。

Amazon Redshift はクエリプランを処理し、プランをステップ、セグメント、およびストリームに変換します。詳細については、クエリの計画と実行のワークフローを参照してください。

解決方法

テーブルを作成し、クエリの説明プランと SVL クエリレポートを取得する

1.   ソートキーと分散キーが異なる 2 つのテーブルを作成します。

2.    分散キーに対して join が実行されなかった場所で次のクエリを実行します。

select eventname, sum (pricepaid) from sales, event where sales.eventid = event.eventid group by eventname order by 2 desc;

このクエリは、内部テーブルをすべてのコンピュートノードに配布します。

3.    クエリプランを取得します。

EXPLAIN <query>;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1002815368414.24..1002815368415.67 rows=571 width=27)
   Merge Key: sum(sales.pricepaid)
   ->  XN Network  (cost=1002815368414.24..1002815368415.67 rows=571 width=27)
         Send to leader
         ->  XN Sort  (cost=1002815368414.24..1002815368415.67 rows=571 width=27)
               Sort Key: sum(sales.pricepaid)
               ->  XN HashAggregate  (cost=2815368386.67..2815368388.10 rows=571 width=27)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=109.98..2815367496.05 rows=178125 width=27)
                           Hash Cond: ("outer".eventid = "inner".eventid)
                           ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=14)
                           ->  XN Hash  (cost=87.98..87.98 rows=8798 width=21)
                                 ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=21)
(12 rows)

4.    次のクエリを実行します。

select * from svl_query_report where query = <query_id> order by segment, step, elapsed_time, rows;

クエリプランとクエリレポートのマッピング

1.    次のクエリを実行して、svl_query_report を取得します。

select query,slice,segment,step,start_time,end_time,elapsed_time,rows,bytes,label from svl_query_report where query = 938787 and segment = 0 order by segment, step, elapsed_time, rows;

EXPLAIN <query>;
->  XN Hash  (cost=87.98..87.98 rows=8798 width=21)
   ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=21)

この出力例を次に示します。

 query  | slice | segment | step |         start_time         |         end_time          | elapsed_time | rows | bytes  |            label              
--------+-------+---------+------+----------------------------+---------------------------+--------------+------+--------+------------------------------
 938787 |     0 |       0 |    0 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4383 | 128626 | scan   tbl=278788 name=event 
 938787 |     1 |       0 |    0 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4415 | 128918 | scan   tbl=278788 name=event 
 938787 |     0 |       0 |    1 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4383 |      0 | project                      
 938787 |     1 |       0 |    1 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4415 |      0 | project                      
 938787 |     0 |       0 |    2 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4383 | 126660 | bcast                        
 938787 |     1 |       0 |    2 | 2020-05-22 11:11:48.828309 | 2020-05-22 11:11:48.82987 |         1561 | 4415 | 126920 | bcast                        

 (6 rows)

この出力は、セグメント値が 0 のとき、Amazon Redshift がシーケンシャルスキャン操作を実行してイベントテーブルをスキャンすることを示しています。

2.    次のクエリを実行して、セグメント 1 のクエリレポートを取得します。

select query,slice,segment,step,start_time,end_time,elapsed_time,rows,bytes,label from svl_query_report where query = 938787 and segment = 1 order by segment, step, elapsed_time, rows;

この出力例を次に示します。

query  | slice | segment | step |       start_time          |          end_time          | elapsed_time | rows | bytes  |     label           
--------+-------+---------+------+----------------------------+----------------------------+--------------+------+--------+-------------------------------------------
938787 |     1 |       1 |    0 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.830037 |         3173 |    0 |      0 | scan   tbl=376297 name=Internal Worktable 
 38787 |     0 |       1 |    0 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.831142 |         4278 | 8798 | 253580 | scan   tbl=376297 name=Internal Worktable 
938787 |     1 |       1 |    1 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.830037 |         3173 |    0 |      0 | project                                   
938787 |     0 |       1 |    1 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.831142 |         4278 | 8798 |      0 | project                                   
938787 |     1 |       1 |    2 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.830037 |         3173 |    0 |      0 | hash   tbl=439                            
938787 |     0 |       1 |    2 | 2020-05-22 11:11:48.826864 | 2020-05-22 11:11:48.831142 |         4278 | 8798 | 359156 | hash   tbl=439                            
(6 rows)

クエリは、セグメント値が 1 になり、結合内の内部テーブルに対してハッシュテーブル操作が実行されるまで実行され続けます。

3.    次のクエリを実行して、セグメント値が 2 のクエリの SVL_QUERY_REPORT を取得します。

EXPLAIN <query>;
->  XN Hash Join DS_BCAST_INNER  (cost=109.98..2815367496.05 rows=178125 width=27)
                           Hash Cond: ("outer".eventid = "inner".eventid)
   ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=14)

4.    次のクエリを実行します。

select query,slice,segment,step,start_time,end_time,elapsed_time,rows,bytes,label from svl_query_report where query = 938787 and segment = 2 order by segment, step, elapsed_time, rows;

この出力例を次に示します。

  query  | slice | segment | step |         start_time         |          end_time          | elapsed_time | rows  |  bytes  |            label             
--------+-------+---------+------+----------------------------+----------------------------+--------------+-------+---------+------------------------------
 938787 |     1 |       2 |    0 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 | 1730380 | scan   tbl=278792 name=sales 
 938787 |     0 |       2 |    0 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 | 1718740 | scan   tbl=278792 name=sales 
 938787 |     1 |       2 |    1 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 |       0 | project                      
 938787 |     0 |       2 |    1 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 |       0 | project                      
 938787 |     1 |       2 |    2 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 |       0 | project                      
 938787 |     0 |       2 |    2 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 |       0 | project                      
 938787 |     1 |       2 |    3 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 |       0 | hjoin  tbl=439               
 938787 |     0 |       2 |    3 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 |       0 | hjoin  tbl=439               
 938787 |     1 |       2 |    4 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 |       0 | project                      
 938787 |     0 |       2 |    4 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 |       0 | project                      
 938787 |     1 |       2 |    5 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 | 86519 |       0 | project                      
 938787 |     0 |       2 |    5 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 | 85937 |       0 | project                      
 938787 |     1 |       2 |    6 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 |   576 |   34916 | aggr   tbl=448               
 938787 |     0 |       2 |    6 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 |   576 |   34916 | aggr   tbl=448               
 938787 |     1 |       2 |    7 | 2020-05-22 11:11:48.839297 | 2020-05-22 11:11:48.865857 |        26560 |   576 |   17728 | dist                         
 938787 |     0 |       2 |    7 | 2020-05-22 11:11:48.838371 | 2020-05-22 11:11:48.865857 |        27486 |   576 |   17768 | dist                         
(16 rows)

この出力例では、セグメント値が 2 のときにクエリが実行され、シーケンシャルスキャン操作を実行して sales テーブルをスキャンします。同じセグメントでは、集計操作を実行して結果を集計し、ハッシュ結合操作を実行してテーブルを結合します。いずれかのテーブルの結合列は、分散キーまたはソートキーではありません。その結果、内部テーブルは DS_BCAST_INNER としてすべてのコンピュートノードに分散されます。これは EXPLAIN プランで見ることができます。 

5.    次のクエリを実行して、セグメント値が 3 のクエリの SVL_QUERY_REPORT を取得します。

select query,slice,segment,step,start_time,end_time,elapsed_time,rows,bytes,label from svl_query_report where query = 938787 and segment = 3 order by segment, step, elapsed_time, rows;

->  XN Sort  (cost=1002815368414.24..1002815368415.67 rows=571 width=27)
               Sort Key: sum(sales.pricepaid)
   ->  XN HashAggregate  (cost=2815368386.67..2815368388.10 rows=571 width=27)

この出力例を次に示します。

 query  | slice | segment | step |         start_time         |          end_time          | elapsed_time | rows | bytes |                   label                   
--------+-------+---------+------+----------------------------+----------------------------+--------------+------+-------+-------------------------------------------
 938787 |     1 |       3 |    0 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |  546 | 28792 | scan   tbl=376298 name=Internal Worktable 
 938787 |     0 |       3 |    0 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |  606 | 31824 | scan   tbl=376298 name=Internal Worktable 
 938787 |     1 |       3 |    1 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |  273 | 16580 | aggr   tbl=451                            
 938787 |     0 |       3 |    1 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |  303 | 18336 | aggr   tbl=451                            
 938787 |     1 |       3 |    2 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |  273 |     0 | project                                   
 938787 |     0 |       3 |    2 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |  303 |     0 | project                                   
 938787 |     1 |       3 |    3 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |  273 | 14396 | sort   tbl=453                            
 938787 |     0 |       3 |    3 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |  303 | 15912 | sort   tbl=453                            
 938787 |     1 |       3 |    4 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |    0 |     0 | merge                                     
 938787 |     0 |       3 |    4 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |    0 |     0 | merge                                     
 938787 |     1 |       3 |    5 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |    0 |     0 | aggr   tbl=456                            
 938787 |     0 |       3 |    5 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |    0 |     0 | aggr   tbl=456                            
 938787 |     1 |       3 |    6 | 2020-05-22 11:11:48.836693 | 2020-05-22 11:11:48.866529 |        29836 |    0 |     0 | project                                   
 938787 |     0 |       3 |    6 | 2020-05-22 11:11:48.836654 | 2020-05-22 11:11:48.866529 |        29875 |    0 |     0 | project                                   
(14 rows)

クエリは、セグメント値が 3 になり、ハッシュ集計操作とソート操作が実行されるまで実行され続けます。ハッシュ集計操作は、ソートされていないグループ化された集計関数に対して実行されます。ソート操作は、ORDER BY 句を評価するために実行されます。

6.    次のクエリを実行して、セグメント値が 4 と 5 のクエリの SVL_QUERY_REPORT を取得します。 

select query,slice,segment,step,start_time,end_time,elapsed_time,rows,bytes,label from svl_query_report where query = 938787 and segment = 4  order by segment, step, elapsed_time, rows;

 query  | slice | segment | step |         start_time         |          end_time          | elapsed_time | rows | bytes |                 label                 
--------+-------+---------+------+----------------------------+----------------------------+--------------+------+-------+----------------------------------------
 938787 |     1 |       4 |    0 | 2020-05-22 11:11:48.915148 | 2020-05-22 11:11:48.915215 |           67 |  273 | 14396 | scan   tbl=453 name=Internal Worktable
 938787 |     0 |       4 |    0 | 2020-05-22 11:11:48.915148 | 2020-05-22 11:11:48.915424 |          276 |  303 | 15912 | scan   tbl=453 name=Internal Worktable
 938787 |     1 |       4 |    1 | 2020-05-22 11:11:48.915148 | 2020-05-22 11:11:48.915215 |           67 |  273 |  8888 | return                                 
 938787 |     0 |       4 |    1 | 2020-05-22 11:11:48.915148 | 2020-05-22 11:11:48.915424 |          276 |  303 |  8864 | return                                 
 938787 |  6411 |       5 |    1 | 2020-05-22 11:11:48.914501 | 2020-05-22 11:11:48.916049 |         1548 |  576 |     0 | project
 938787 |  6411 |       5 |    2 | 2020-05-22 11:11:48.914501 | 2020-05-22 11:11:48.916049 |         1548 |  576 | 18360 | return
 938787 |  6411 |       5 |    0 | 2020-05-22 11:11:48.914501 | 2020-05-22 11:11:48.916049 |         1548 |    0 |     0 | merge
 (7 rows)

すべてのセグメントが使用されると、クエリはセグメント 4 と 5 に対してネットワーク操作を実行し、中間結果をリーダーノードに送信します。結果は、追加の処理のためにリーダーノードに送信されます。

クエリの実行後、次のクエリを使用して、クエリの実行時間をミリ秒単位で確認します。

select datediff (ms, exec_start_time, exec_end_time) from stl_wlm_query where query= 938787;

date_diff 
-----------
 101
(1 row)

クエリの最適化

クエリプランの分析中にクエリを最適化するには、次の手順を実行します。

1.    コストが最も高いステップを特定します。

2.    高コストのソート操作があるかどうかを確認します。クエリのパフォーマンスは、データ分散方法と、クエリによってスキャンされるデータに依存することに注意してください。再配布ステップの影響を最小限に抑えるために、テーブルの適切な配布スタイルを選択してください。さらに、クエリ速度を向上させ、スキャンする必要のあるブロック数を減らすために、適切な列にソートキーを使用します。ディストリビューションキーとソートキーを選択する方法の詳細については、Amazon Redshift Engineering’s advanced table design playbook: distribution styles and distribution keys を参照してください。

次の例では、STL_ALERT_EVENT_LOG テーブルを使用して、クエリのパフォーマンスの潜在的な問題を特定し、修正します。 

select query, btrim(event) as event, btrim(solution) as solution from stl_alert_event_log where query = 940313;

 query  |              event               |          solution          
--------+----------------------------------+-------------------------
 940313 | Missing query planner statistics | Run the ANALYZE command
 (1 row)

この出力例では、クエリの統計情報が古くなっているため、ANALYZE コマンドのクエリを使用してクエリのパフォーマンスを向上させることができます。

EXPLAIN プランを使用して、クエリに入力されているアラートがあるかどうかを確認することもできます。

explain select eventname, sum (pricepaid) from sales, event where sales.eventid = event.eventid group by eventname order by 2 desc;
                                                QUERY PLAN                   
                                     
-----------------------------------------------------------------------------------------------------------
 XN Merge  (cost=1029210993681.72..1029210993682.22 rows=200 width=330)
   Merge Key: sum(sales.pricepaid)
   ->  XN Network  (cost=1029210993681.72..1029210993682.22 rows=200 width=330)
         Send to leader
         ->  XN Sort  (cost=1029210993681.72..1029210993682.22 rows=200 width=330)
               Sort Key: sum(sales.pricepaid)
               ->  XN HashAggregate  (cost=29210993673.57..29210993674.07 rows=200 width=330)
                     ->  XN Hash Join DS_BCAST_INNER  (cost=109.98..29210955741.88 rows=7586340 width=330)
                           Hash Cond: ("outer".eventid = "inner".eventid)
                           ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=16)
                           ->  XN Hash  (cost=87.98..87.98 rows=8798 width=322)
                                 ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=322)
 ----- Tables missing statistics: event, sales -----
 ----- Update statistics by running the ANALYZE command on these tables -----

3.    結合タイプを確認します。

注: ネストされたループは、主にクロス結合といくつかの不等式結合に使用されるため、最適な結合とは到底言えません。

次の例は、2 つのテーブル間のクロス結合を示しています。 ネストされたループ結合が使用されており、最初のコスト値は 0.00 です。このコスト値は、クロス結合操作の最初の行を返す相対コストです。2 番目の値 (3901467082.32) は、クロス結合操作を完了するための相対コストを示します。最初の行と最後の行のコスト差に注意してください。ネストされたループは、実行時間の長いクエリでキューをオーバーロードすることによって、クラスターのパフォーマンスに悪影響を及ぼします。

explain select * from sales cross join event;
                                             QUERY PLAN                   
                                    
---------------------------------------------------------------------------------------------------------
 XN Nested Loop DS_BCAST_INNER  (cost=0.00..3901467082.32 rows=1517267888 width=88)
   ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=53)
   ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=35)
 ----- Nested Loop Join in the query plan - review the join predicates to avoid Cartesian products -----
(4 rows)

注: Amazon Redshift は、テーブルの分散スタイルと必要なデータの場所に基づいて、結合演算子を選択します。

クエリのパフォーマンスを最適化するために、両方のテーブルのソートキーと分散キーが「eventid」に変更されました。次の例では、ハッシュ結合の代わりにマージ結合が使用されています。

explain select eventname, sum (pricepaid) from sales, event where sales.eventid = event.eventid group by eventname order by 2 desc;
                                           QUERY PLAN                       
                           
------------------------------------------------------------------------------------------------
 XN Merge  (cost=1000000004965.12..1000000004966.55 rows=571 width=27)
   Merge Key: sum(sales.pricepaid)
   ->  XN Network  (cost=1000000004965.12..1000000004966.55 rows=571 width=27)
         Send to leader
         ->  XN Sort  (cost=1000000004965.12..1000000004966.55 rows=571 width=27)
               Sort Key: sum(sales.pricepaid)
               ->  XN HashAggregate  (cost=4937.55..4938.98 rows=571 width=27)
                     ->  XN Merge Join DS_DIST_NONE  (cost=0.00..4046.93 rows=178125 width=27)
                           Merge Cond: ("outer".eventid = "inner".eventid)
                           ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=14)
                           ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=21)
(11 rows)

4.    コストの高い操作を実行しているブロードキャスト演算子を特定します。

注: 小さなテーブルの場合、ブロードキャスト演算子は必ずしも最適ではないとはみなされません。これは、小さなテーブルの再配布がクエリのパフォーマンスに比較的影響しないためです。

5.    次のクエリを実行して、クエリの実行時間を確認します。

select datediff (ms, exec_start_time, exec_end_time) from stl_wlm_query where query= 940715;

 date_diff 
-----------
 34
 (1 row)

両方のクエリの実行時間の違いにより、クエリプランがクエリレポートに正しく相関していることが確認されます。

クエリプランのクエリサマリーへのマッピング

クエリプランステップの確認

SVL_QUERY_REPORT ビューの使用

この記事はお役に立ちましたか?

フィードバックを送信

請求に関するサポートまたは技術サポートが必要ですか?

AWS サポートにお問い合わせください