Redshift Research Project

System Table Tracker

System view pg_catalog.svl_query_step_times version 1.0.40182 / 2022-07-23

View Text

SELECT st.query,
       st.segment,
       st.step,
       st.step_name,
       st.step_type,
       min(sl.duration) AS min_duration,
       avg(sl.duration) AS avg_duration,
       max(sl.duration) AS max_duration,
       trunc(min(st.agg_on / sl.agg_all) * CAST(100 AS double precision),
             CAST(2 AS numeric)) AS min_on_cpu,
       trunc((sum(st.agg_on) / sum(sl.agg_all)) * CAST(100 AS double precision),
             CAST(2 AS numeric)) AS avg_on_cpu,
       trunc(max(st.agg_on / sl.agg_all) * CAST(100 AS double precision),
             CAST(2 AS numeric)) AS max_on_cpu,
       count(sl.agg_on > CAST(0 AS double precision)) AS active_slices,
       trunc(min(st.agg_off / sl.agg_all) * CAST(100 AS double precision),
             CAST(2 AS numeric)) AS min_off_cpu,
       trunc((sum(st.agg_off) / sum(sl.agg_all)) * CAST(100 AS double precision),
             CAST(2 AS numeric)) AS avg_off_cpu,
       trunc(max(st.agg_off / sl.agg_all) * CAST(100 AS double precision),
             CAST(2 AS numeric)) AS max_off_cpu,
       count(sl.agg_off > CAST(0 AS double precision)) AS waiting_slices
FROM (SELECT stl_query_step_times.query,
             stl_query_step_times.segment,
             stl_query_step_times.slice,
             max(stl_query_step_times.duration) AS duration,
             CAST(sum(stl_query_step_times.on_cpu) AS double precision) / CAST(1000 AS double precision) AS agg_on,
             CAST(sum(stl_query_step_times.off_cpu) AS double precision) / CAST(1000 AS double precision) AS agg_off,
             (CAST(sum(stl_query_step_times.on_cpu) AS double precision) / CAST(1000 AS double precision)) + (CAST(sum(stl_query_step_times.off_cpu) AS double precision) / CAST(1000 AS double precision)) AS agg_all
      FROM stl_query_step_times
      GROUP BY stl_query_step_times.query, stl_query_step_times.segment, stl_query_step_times.slice) AS sl,
     (SELECT stl_query_step_times.query,
             stl_query_step_times.segment,
             stl_query_step_times.step,
             stl_query_step_times.slice,
             stl_query_step_times.step_name,
             stl_query_step_times.step_type,
             CAST(sum(stl_query_step_times.on_cpu) AS double precision) / CAST(1000 AS double precision) AS agg_on,
             CAST(sum(stl_query_step_times.off_cpu) AS double precision) / CAST(1000 AS double precision) AS agg_off,
             (CAST(sum(stl_query_step_times.on_cpu) AS double precision) / CAST(1000 AS double precision)) + (CAST(sum(stl_query_step_times.off_cpu) AS double precision) / CAST(1000 AS double precision)) AS agg_all
      FROM stl_query_step_times
      GROUP BY stl_query_step_times.query, stl_query_step_times.segment, stl_query_step_times.step, stl_query_step_times.slice, stl_query_step_times.step_name, stl_query_step_times.step_type) AS st
WHERE st.query = sl.query
  AND st.segment = sl.segment
  AND st.slice = sl.slice
  AND sl.agg_all > CAST(0 AS double precision)
GROUP BY st.query, st.segment, st.step, st.step_name, st.step_type

Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers