Redshift Observatory

System Table Tracker

System view pg_catalog.svl_query_step_times_summary version 1.0.49087 / 2023-04-12

View Text

SELECT st.query,
       st.step_name,
       st.step_type,
       min(q.starttime) AS starttime,
       date_diff(CAST('ms' AS text),
                 min(q.starttime),
                 max(q.endtime)) AS duration_ms,
       trunc((sum(st.agg_on) / sum(qt.agg_all)) * CAST(100 AS double precision),
             CAST(2 AS numeric)) AS on_cpu_perc,
       trunc((sum(st.agg_off) / sum(qt.agg_all)) * CAST(100 AS double precision),
             CAST(2 AS numeric)) AS off_cpu_perc
FROM (SELECT stl_query_step_times.query,
             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
      WHERE stl_query_step_times.step_type !~~ CAST('%ScanFabric%' AS text)
      GROUP BY stl_query_step_times.query) AS qt,
     (SELECT stl_query_step_times.query,
             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
      WHERE stl_query_step_times.step_type !~~ CAST('%ScanFabric%' AS text)
      GROUP BY stl_query_step_times.query, stl_query_step_times.step_name, stl_query_step_times.step_type) AS st,
     stl_query AS q
WHERE st.query = qt.query
  AND q.query = qt.query
  AND qt.agg_all > CAST(0 AS double precision)
GROUP BY st.query, 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