The following queries can be used to diagnose performance:
SELECT query, substring(event,0,25) as event,
substring(solution,0,25) as solution,
trim(event_time) as event_time from stl_alert_event_log order by query;
select trim(s.perm_table_name) as table , (sum(abs(datediff(seconds, coalesce(b.starttime,d.starttime,s.starttime), coalesce(b.endtime,d.endtime,s.endtime))))/60)::numeric(24,0) as minutes,
sum(coalesce(b.rows,d.rows,s.rows)) as rows, trim(split_part(l.event,':',1)) as event, substring(trim(l.solution),1,60) as solution , max(l.query) as sample_query, count(*)
from stl_alert_event_log as l
left join stl_scan as s on s.query = l.query and s.slice = l.slice and s.segment = l.segment
left join stl_dist as d on d.query = l.query and d.slice = l.slice and d.segment = l.segment
left join stl_bcast as b on b.query = l.query and b.slice = l.slice and b.segment = l.segment
where l.userid >1
and l.event_time >= dateadd(day, -7, current_Date)
group by 1,4,5 order by 2 desc,6 desc;
For more diagnostic queries refer to the following documentation:
http://docs.aws.amazon.com/redshift/latest/dg/diagnostic-queries-for-query-tuning.htmlThe following table recommends solutions for each event that may result from the queries run in Amazon Redshift:
