I know Amazon has provided various admin scripts for Redshift, such as this one:
https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminScripts/top_queries.sql
which lists the top queries by runtime, and I also found this which is similar:
https://chartio.com/learn/amazon-redshift/identifying-slow-queries-in-redshift/
however I'd like to know if there is a query which is similar to the above queries but also shows queue/wait time in addition to execution time?
From this post:
How can I get the total run time of a query in redshift, with a query?
I gather that the stl_query table includes the execution time + wait time, but that the stl_wlm_query includes the total_exec_time, which is just the execution time.
Update: I've got the following which gives me what I want, but it seems to only return the last month or so of data, any ideas how I get older data?
SELECT
w.userid,
w.query,
w.service_class_start_time AS "Day",
w.total_queue_time / 60000000 AS "Total Queue Time Minutes",
w.total_exec_time / 60000000 AS "Total Exec Time Minutes",
w.total_queue_time / 60000000 + w.total_exec_time / 60000000 AS "Total Time Minutes"
FROM
stl_wlm_query w
ORDER BY
6 DESC
The following query will list top queries by execution time, but as John mentions above, will only return two to five days of log history.
SELECT
w.userid,
w.query,
w.service_class_start_time AS "Day",
w.total_queue_time / 60000000 AS "Total Queue Time Minutes",
w.total_exec_time / 60000000 AS "Total Exec Time Minutes",
w.total_queue_time / 60000000 + w.total_exec_time / 60000000 AS "Total Time Minutes"
FROM
stl_wlm_query w
ORDER BY
6 DESC