Redshift Observatory

System Table Tracker

System view pg_catalog.svv_query_state version 1.0.61191 / 2023-12-18

schema name column data type
pg_catalog svv_query_state avgtime int8
pg_catalog svv_query_state bytes int8
pg_catalog svv_query_state cpu int8
pg_catalog svv_query_state is_delayed_scan char(1)
pg_catalog svv_query_state is_diskbased char(1)
pg_catalog svv_query_state is_rrscan char(1)
pg_catalog svv_query_state label char(256)
pg_catalog svv_query_state maxtime int8
pg_catalog svv_query_state memory int8
pg_catalog svv_query_state num_parts int4
pg_catalog svv_query_state query int4
pg_catalog svv_query_state rate_byte float8
pg_catalog svv_query_state rate_row float8
pg_catalog svv_query_state rows int8
pg_catalog svv_query_state seg int4
pg_catalog svv_query_state step int4
pg_catalog svv_query_state userid int4
pg_catalog svv_query_state workmem int8

View Text

SELECT stv_exec_state.userid,
       stv_exec_state.query,
       stv_exec_state.segment AS seg,
       stv_exec_state.step,
       max(date_diff(CAST('microseconds' AS text),
                     stv_exec_state.starttime,
                     stv_exec_state.currenttime)) AS maxtime,
       avg(date_diff(CAST('microseconds' AS text),
                     stv_exec_state.starttime,
                     stv_exec_state.currenttime)) AS avgtime,
       sum(stv_exec_state.rows) AS rows,
       sum(stv_exec_state.bytes) AS bytes,
       sum(((stv_proc_stat.utime * 1000) / CASE
                                             WHEN stv_proc_stat.elapsed = 0
                                               THEN CAST(NULL AS integer)
                                             ELSE stv_proc_stat.elapsed
                                           END) / 10) AS cpu,
       sum(stv_proc_stat.memory) AS memory,
       round(CAST(sum(stv_exec_state.rows) AS double precision) / CASE
                                                                    WHEN date_part(CAST('epoch' AS text),
                                                                                   max(stv_exec_state.currenttime - stv_exec_state.starttime)) = CAST(0 AS double precision)
                                                                      THEN CAST(NULL AS double precision)
                                                                    ELSE date_part(CAST('epoch' AS text),
                                                                                   max(stv_exec_state.currenttime - stv_exec_state.starttime))
                                                                  END) AS rate_row,
       round(CAST(sum(stv_exec_state.bytes) AS double precision) / CASE
                                                                     WHEN date_part(CAST('epoch' AS text),
                                                                                    max(stv_exec_state.currenttime - stv_exec_state.starttime)) = CAST(0 AS double precision)
                                                                       THEN CAST(NULL AS double precision)
                                                                     ELSE date_part(CAST('epoch' AS text),
                                                                                    max(stv_exec_state.currenttime - stv_exec_state.starttime))
                                                                   END) AS rate_byte,
       stv_exec_state.label,
       stv_exec_state.is_diskbased,
       sum(stv_exec_state.workmem) AS workmem,
       stv_exec_state.num_parts,
       stv_exec_state.is_rrscan,
       stv_exec_state.is_delayed_scan
FROM stv_exec_state
     LEFT JOIN stv_proc_stat ON stv_exec_state.query = stv_proc_stat.query
                            AND stv_exec_state.segment = stv_proc_stat.segment
                            AND stv_exec_state.slice = stv_proc_stat.slice
GROUP BY stv_exec_state.userid, stv_exec_state.query, stv_exec_state.segment, stv_exec_state.step, stv_exec_state.label, stv_exec_state.is_diskbased, stv_exec_state.num_parts, stv_exec_state.is_rrscan, stv_exec_state.is_delayed_scan


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