如何通过查询获得redshift中查询的总运行时间? [英] How can I get the total run time of a query in redshift, with a query?

问题描述

我正在对Redshift中的一些查询进行基准测试，以便对自己对表所做的更改说一些聪明的话，例如添加编码和运行真空.我可以使用LIKE子句查询 stl_query 表来查找我感兴趣的查询，因此我具有查询ID，但 stv_query_summary 过于精细，我不确定如何生成所需的摘要！

I'm in the process of benchmarking some queries in redshift so that I can say something intelligent about changes I've made to a table, such as adding encodings and running a vacuum. I can query the stl_query table with a LIKE clause to find the queries I'm interested in, so I have the query id, but tables/views like stv_query_summary are much too granular and I'm not sure how to generate the summarization I need!

gui仪表板显示了我感兴趣的指标，但是该格式很难存储以便以后进行分析/比较(换句话说，我想避免截取屏幕截图).有没有一种使用sql select重建该视图的好方法?

The gui dashboard shows the metrics I'm interested in, but the format is difficult to store for later analysis/comparison (in other words, I want to avoid taking screenshots). Is there a good way to rebuild that view with sql selects?

推荐答案

要添加到Alex答案中，我想评论一下stl_query表具有以下不便之处:如果查询在运行时之前位于队列中，则队列时间将为包含在运行时中，因此运行时将不是很好的查询性能指标.

To add to Alex answer, I want to comment that stl_query table has the inconvenience that if the query was in a queue before the runtime then the queue time will be included in the run time and therefore the runtime won't be a very good indicator of performance for the query.

要了解查询的实际运行时间，请检查stl_wlm_query的total_exec_time.

To understand the actual runtime of the query, check on stl_wlm_query for the total_exec_time.

select total_exec_time
from stl_wlm_query
where query='query_id'

这篇关于如何通过查询获得redshift中查询的总运行时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！