如何将查询计划与 Amazon Redshift 中的查询报告关联?
上次更新时间:2020 年 10 月 28 日
我正在尝试将查询计划与 Amazon Redshift 集群中的查询报告关联起来。该如何操作?
简短描述
要确定在 Amazon Redshift 中运行查询所需的用法,请使用 EXPLAIN 命令。EXPLAIN 命令将显示查询语句的执行计划,而不会实际运行查询。执行计划概述了涉及的查询计划和执行步骤。
然后,使用 SVL_QUERY_REPORT 系统视图在集群切片级别查看查询信息。您可以使用切片级信息来检测整个集群中的不均衡数据分布,这会影响查询性能。
注意:在 SVL_QUERY_REPT 中,行列指示每个集群切片正在处理的行数。rows_pre_filter 列指示筛选标记为删除的行之前发出的总行数。
Amazon Redshift 将处理查询计划,并将计划转换为步骤、区段和流。有关详细信息,请参阅查询规划和执行工作流。
解决方法
创建表并获取查询的解释计划和 SVL 查询报告
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)
select datediff (ms, exec_start_time, exec_end_time) from stl_wlm_query where query= 940715;
date_diff
-----------
34
(1 row)
两个查询的执行时间差异确认查询计划已正确关联到查询报告。