Redshift Observatory

System Table Tracker

System view pg_catalog.svcs_s3query_summary version 1.0.70695 / 2024-07-11

View Text

SELECT e.userid,
       e.query,
       q.xid,
       q.pid,
       e.segment,
       e.step,
       min(e.starttime) AS starttime,
       max(e.endtime) AS endtime,
       date_diff(CAST('us' AS text),
                 min(e.starttime),
                 max(e.endtime)) AS elapsed,
       q.aborted,
       CAST(e.external_table_name AS varchar) AS external_table_name,
       e.file_format,
       CAST(max(CAST(e.is_partitioned AS text)) AS varchar) AS is_partitioned,
       CAST(max(CAST(e.is_rrscan AS text)) AS varchar) AS is_rrscan,
       CAST(max(CAST(e.is_nested AS text)) AS varchar) AS is_nested,
       sum(e.s3_scanned_rows) AS s3_scanned_rows,
       sum(e.s3_scanned_bytes) AS s3_scanned_bytes,
       sum(e.s3query_returned_rows) AS s3query_returned_rows,
       sum(e.s3query_returned_bytes) AS s3query_returned_bytes,
       sum(e.files) AS files,
       max(e.files) AS files_max,
       avg(e.files) AS files_avg,
       sum(e.splits) AS splits,
       max(e.splits) AS splits_max,
       avg(e.splits) AS splits_avg,
       sum(e.total_split_size) AS total_split_size,
       max(e.max_split_size) AS max_split_size,
       CASE
         WHEN sum(e.splits) > 0
           THEN sum(e.total_split_size) / sum(e.splits)
         ELSE CAST(NULL AS bigint)
       END AS avg_split_size,
       sum(e.total_retries) AS total_retries,
       max(e.max_retries) AS max_retries,
       max(e.max_request_duration) AS max_request_duration,
       avg(e.avg_request_duration) AS avg_request_duration,
       max(e.max_request_parallelism) AS max_request_parallelism,
       round(avg(e.avg_request_parallelism),
             CAST(1 AS numeric)) AS avg_request_parallelism,
       sum(e.slowdown_count) AS total_slowdown_count,
       max(e.slowdown_count) AS max_slowdown_count
FROM svcs_s3query AS e, stl_query AS q
WHERE e.query = q.query
  AND e.scan_type = 2
  AND (EXISTS (SELECT 1
               FROM pg_user
               WHERE pg_user.usename = CAST("current_user"() AS name)
                 AND pg_user.usesuper = TRUE)
    OR EXISTS (SELECT 1
               FROM pg_shadow_extended
               WHERE pg_shadow_extended.sysid = current_user_id()
                 AND pg_shadow_extended.colnum = 2
                 AND pg_shadow_extended.value = CAST(-1 AS text))
    OR e.userid = current_user_id())
GROUP BY e.userid, e.query, q.xid, q.pid, e.segment, e.step, q.aborted, e.external_table_name, e.file_format

UNION ALL

SELECT svl_s3query_summary.userid,
       svl_s3query_summary.query,
       svl_s3query_summary.xid,
       svl_s3query_summary.pid,
       svl_s3query_summary.segment,
       svl_s3query_summary.step,
       svl_s3query_summary.starttime,
       svl_s3query_summary.endtime,
       svl_s3query_summary.elapsed,
       svl_s3query_summary.aborted,
       svl_s3query_summary.external_table_name,
       svl_s3query_summary.file_format,
       svl_s3query_summary.is_partitioned,
       svl_s3query_summary.is_rrscan,
       svl_s3query_summary.is_nested,
       svl_s3query_summary.s3_scanned_rows,
       svl_s3query_summary.s3_scanned_bytes,
       svl_s3query_summary.s3query_returned_rows,
       svl_s3query_summary.s3query_returned_bytes,
       svl_s3query_summary.files,
       svl_s3query_summary.files_max,
       svl_s3query_summary.files_avg,
       svl_s3query_summary.splits,
       svl_s3query_summary.splits_max,
       svl_s3query_summary.splits_avg,
       svl_s3query_summary.total_split_size,
       svl_s3query_summary.max_split_size,
       svl_s3query_summary.avg_split_size,
       svl_s3query_summary.total_retries,
       svl_s3query_summary.max_retries,
       svl_s3query_summary.max_request_duration,
       svl_s3query_summary.avg_request_duration,
       svl_s3query_summary.max_request_parallelism,
       svl_s3query_summary.avg_request_parallelism,
       svl_s3query_summary.total_slowdown_count,
       svl_s3query_summary.max_slowdown_count
FROM svl_s3query_summary

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