I have a set of Top 6 queries I like to run when troubleshooting problems with respect to slow query performance on AWS Redshift.
This will likely help you in finding the root cause of the issue which could be a culprit SQL query that needs tuning.
But in a lot of cases the root cause could end up being a larger underlying issue in terms of slowness due to table structure definition or a case where the tables need to have a full vacuum done or statistics updated. Those can be identified by queries below as well.
1) View currently running active queries:select pid, user_name, starttime, query from stv_recents where status=’Running’;
2) Check on locks:select * from stv_locks;
3) Total capacity vs used:select sum(capacity)/1024 as capacity_gbytes, sum(used)/1024 as used_gbytes, (sum(capacity) – sum(used))/1024 as free_gbytes from stv_partitions where part_begin=0;
4) Identify tables in need of updating statistics:select “schema”, “table” from svv_table_info where stats_off>10;
5) Identify tables that need to be vacuumed:select “schema”, “table” from svv_table_info where unsorted>10
6) Identify queries that are top candidates for tuning:select trim(database) as db, count(query) as n_qry, max(substring (qrytext,1,80)) as qrytext, min(run_minutes) as “min” , max(run_minutes) as “max”, avg(run_minutes) as “avg”, sum(run_minutes) as total, max(query) as max_query_id, max(starttime)::date as last_run, sum(alerts) as alerts, aborted from (select userid, label, stl_query.query, trim(database) as database, trim(querytxt) as qrytext, md5(trim(querytxt)) as qry_md5, starttime, endtime, (datediff(seconds, starttime,endtime)::numeric(12,2))/60 as run_minutes, alrt.num_events as alerts, aborted from stl_query left outer join (select query, 1 as num_events from stl_alert_event_log group by query ) as alrt on alrt.query = stl_query.query where userid <> 1 and starttime >= dateadd(day, -7, current_date)) group by database, label, qry_md5, aborted order by total desc limit 50;
What has helped me a lot in learning these is the AWS documentation available which is source for many of the queries summarized above.