如何将查询计划与 Amazon Redshift 中的查询报告关联？

上次更新时间：2020 年 10 月 28 日

我正在尝试将查询计划与 Amazon Redshift 集群中的查询报告关联起来。该如何操作？

简短描述

要确定在 Amazon Redshift 中运行查询所需的用法，请使用 EXPLAIN 命令。EXPLAIN 命令将显示查询语句的执行计划，而不会实际运行查询。执行计划概述了涉及的查询计划和执行步骤。

然后，使用 SVL_QUERY_REPORT 系统视图在集群切片级别查看查询信息。您可以使用切片级信息来检测整个集群中的不均衡数据分布，这会影响查询性能。

注意：在 SVL_QUERY_REPT 中，行列指示每个集群切片正在处理的行数。rows_pre_filter 列指示筛选标记为删除的行之前发出的总行数。

Amazon Redshift 将处理查询计划，并将计划转换为步骤、区段和流。有关详细信息，请参阅查询规划和执行工作流。

解决方法

创建表并获取查询的解释计划和 SVL 查询报告

1.   创建两个具有不同排序键和分配键的表。

2.    运行以下查询，其中，未对分配键执行联接：

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_INNE 的形式分布到所有计算节点，这可以在 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 工程高级表设计手册：分配样式和分配键。

以下示例使用 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 命令的查询来提高查询性能，因为查询的统计信息已过时。

您还可以使用 EXLAIN 计划来查看是否正在为查询填充任何警报：

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.    检查联接类型。

注意：嵌套循环是最不理想的联接，因为它主要用于交叉联接和某些不等式连接。

以下示例显示了两个表之间的交叉联接。 正在使用嵌套循环联接，第一个成本值为 0.00。此成本值是返回交叉联接操作第一行的相对成本。第二个值 (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 Support