List top queries by total runtime, execution time, wait/queue time in Redshift?

Question

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

Answer 1

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