Redshift performance investigation
Not my work. I do not take credit for any of the stuff below –
Used in predicate but not a sort key
WITH predicate_column_info as ( SELECT ns.nspname AS schema_name, c.relname AS table_name, a.attnum as col_num, a.attname as col_name, s.stanullfrac as pct_null, s.stawidth as avg_width, case when s.stadistinct < 0 then null else s.stadistinct end as n_distinct, a.attisdistkey, a.attsortkeyord, CASE WHEN 10002 = s.stakind1 THEN array_to_string(stavalues1, '||') WHEN 10002 = s.stakind2 THEN array_to_string(stavalues2, '||') WHEN 10002 = s.stakind3 THEN array_to_string(stavalues3, '||') WHEN 10002 = s.stakind4 THEN array_to_string(stavalues4, '||') ELSE NULL::varchar END AS pred_ts FROM pg_statistic s JOIN pg_class c ON c.oid = s.starelid JOIN pg_namespace ns ON c.relnamespace = ns.oid JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum) SELECT schema_name, table_name, col_num, col_name, pred_ts NOT LIKE '2000-01-01%' AS is_predicate, CASE WHEN pred_ts NOT LIKE '2000-01-01%' THEN (split_part(pred_ts, '||',1))::timestamp ELSE NULL::timestamp END as first_predicate_use, CASE WHEN pred_ts NOT LIKE '%||2000-01-01%' THEN (split_part(pred_ts, '||',2))::timestamp ELSE NULL::timestamp END as last_analyze, pct_null, avg_width, n_distinct, attisdistkey as is_distkey, attsortkeyord as is_sortkey FROM predicate_column_info where pred_ts NOT LIKE '2000-01-01%'
Queries with lot of wait
SELECT w.query ,substring(q.querytxt,1,100) AS querytxt ,w.queue_start_time ,w.service_class AS class ,w.slot_count AS slots ,w.total_queue_time / 1000000 AS queue_seconds ,w.total_exec_time / 1000000 exec_seconds ,(w.total_queue_time + w.total_Exec_time) / 1000000 AS total_seconds FROM stl_wlm_query w LEFT JOIN stl_query q ON q.query = w.query AND q.userid = w.userid WHERE w.queue_start_Time >= dateadd(day,-7,CURRENT_DATE) AND w.total_queue_Time > 0 -- and q.starttime >= dateadd(day, -7, current_Date) -- and ( querytxt like 'select%' or querytxt like 'SELECT%' ) ORDER BY w.total_queue_time DESC
Find potential wrong Sort Key
SELECT t.schema AS SCHEMA, trim(s.perm_table_name) AS TABLE, (sum(abs(datediff(seconds, coalesce(b.starttime,d.starttime,s.starttime), CASE WHEN coalesce(b.endtime,d.endtime,s.endtime) > coalesce(b.starttime,d.starttime,s.starttime) THEN coalesce(b.endtime,d.endtime,s.endtime) ELSE coalesce(b.starttime,d.starttime,s.starttime) END)))/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(DISTINCT l.query), q.text AS query_text 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 LEFT JOIN (SELECT query, LISTAGG(text) WITHIN GROUP ( ORDER BY sequence) AS text FROM stl_querytext WHERE sequence < 100 GROUP BY query) AS q ON q.query = l.query LEFT JOIN svv_table_info AS t ON t.table_id = s.tbl WHERE l.userid >1 AND l.event_time >= dateadd(DAY, -7, CURRENT_DATE) AND s.perm_table_name NOT LIKE 'the_table_name%' AND SCHEMA IS NOT NULL GROUP BY 1, 2, 5, 6, query_text ORDER BY 3 DESC, 7 DESC;