Amazon Redshift Performance Tuning - Assessing Quality of Performance Tuning

STEP 0. Record Pre-QA Exec time at LIMIT 100
 
STEP 1. Verify all tables are tables

select * from schema.tables where table_schema = 'wfs'
and table_name in (
'dates_transform'
,'dates_leap_year'
,'current_emp'
,'avp_emp'
,'svp_emp'
,'hierarchy'
,'current_prod'
,'current_store'
,'daily_mtr'
,'other_prod'
,'subcat_prod'
,'cat_prod'
,'hours'
,'mth'
,'qtr'
,'reg'
)
order by 4 ,3
;

 

STEP 2. Check Table Distribution Skew (Want Skew < 4)

select ti.schema ,ti."table" ,ti.diststyle ,ti.sortkey1 ,ti.size ,ti.tbl_rows
,CASE WHEN ti.skew_rows IS NULL THEN 0
ELSE ti.skew_rows
END as skew_rows
,count(tp.slice)
,min(tp.rows)
,max(tp.rows)
from
info ti
,cat.stv_tbl_perm tp
where ti.schema  = 'dwh'
and tp.id = ti.table_id
and tp.slice!=6411
and ti."table" in (
'dates_transform' 
,'dates_leap_year' 
,'current_emp' 
,'avp_emp' 
,'svp_emp' 
,'hierarchy' 
,'current_prod' 
,'current_store' 
,'daily_mtr' 
,'other_prod' 
,'subcat_prod' 
,'cat_prod' 
,'hours' 
,'mth' 
,'qtr' 
,'reg'
)
group by 1,2,3,4,5,6,7
--order by 1 ,7 desc, 6 desc ,2
order by 1 ,2
;

 

STEP 3. Check Table Statistics (Want stats_off at 0.00)

select ti.schema ,ti."table" ,ti.stats_off from svv_table_info ti where ti.schema = 'dwh'
and ti."table" in (
'dates_transform'
,'dates_leap_year'
,'current_emp'
,'avp_emp'
,'svp_emp'
,'hierarchy'
,'current_prod'
,'current_store'
,'daily_mtr'
,'other_prod'
,'subcat_prod'
,'cat_prod'
,'hours'
,'mth'
,'qtr'
,'reg'
)
order by 2
;

 

STEP 4. Vacuum & Analyze/Analyze Compression

vaccuum <each table>;
analyze compression <each table>;

 

STEP 5. Review query for SORTKEY candidates (join/group by/order by conditions)
 

SELECT ti.schemaname||'.'||ti.tablename AS "table", 
  ti.tbl_rows, 
  avg(r.s_rows_pre_filter) avg_s_rows_pre_filter, 
  round(1::float - avg(r.s_rows_pre_filter)::float/ti.tbl_rows::float,6) avg_prune_pct, 
  avg(r.s_rows) avg_s_rows, 
  round(1::float - avg(r.s_rows)::float/avg(r.s_rows_pre_filter)::float,6) avg_filter_pct, 
  ti.diststyle, 
  ti.sortkey_num, 
  ti.sortkey1, 
  trim(a.typname) "type", 
  count(distinct i.query) * avg(r.time) AS total_scan_secs, 
  avg(r.time) AS scan_time, 
  count(distinct i.query) AS num, 
  max(i.query) AS query, 
  trim(info) AS filter 
FROM stl_explain p 
JOIN stl_plan_info i 
ON (i.userid=p.userid AND i.query=p.query AND i.nodeid=p.nodeid ) 
JOIN stl_scan s 
ON (s.userid=i.userid AND s.query=i.query AND s.segment=i.segment AND s.step=i.step) 
JOIN (
  SELECT table_id,
    "table" tablename,
    schema schemaname,
    tbl_rows,
    unsorted,
    sortkey1,
    sortkey_num,
    diststyle 
  FROM svv_table_info) ti 
ON ti.table_id=s.tbl 
JOIN ( 
  SELECT query, 
    segment, 
    step, 
    datediff(s,min(starttime),max(endtime)) AS time, 
    sum(rows) s_rows, 
    sum(rows_pre_filter) s_rows_pre_filter, 
    round(sum(rows)::float/sum(rows_pre_filter)::float,6) filter_pct 
  FROM stl_scan 
  WHERE userid>1 AND starttime::date = current_date-1 AND starttime < endtime 
  GROUP BY 1,2,3 HAVING sum(rows_pre_filter) > 0 ) r 
ON (r.query = i.query AND r.segment = i.segment AND r.step = i.step) 
LEFT JOIN (
  SELECT attrelid,
    t.typname 
  FROM att a 
  JOIN bnzc t 
  ON t.oid=a.atttypid 
  WHERE attsortkeyord IN (1,-1)) a 
ON a.attrelid=s.tbl 
WHERE s.type = 2 AND ti.tbl_rows > 1000000 AND p.info LIKE 'Filter:%' AND p.nodeid > 0 
GROUP BY 1,2,7,8,9,10,15 
ORDER BY 1, 13 desc, 11 desc;

 

STEP 6. Deep Copy <each table> to test changes

STEP 7. Verify No Errors in stl_alert_event_log (Need to avoid BROADCAST errors, not overly concerned with 'Very selective query filter' at this point)
 

select l.event_time ,l.userid ,trim(u.usename) as user ,l.query, trim(l.event) as event, trim(q.querytxt) as querytxt, trim(l.solution) as solution
from stl_alert_event_log l, pg_user u ,stl_query q
where (u.usesysid = l.userid)
--and(l.userid!=100)
and q.query = l.query
--and l.query = 414747
and l.event_time>'2017-09-01 14:20:00'
order by l.event_time desc;

 

STEP 8. Record exec time and Swap tables for Strategy validation.