Redshift Observatory

System Table Tracker

System view pg_catalog.svcs_query_metrics version 1.0.41465 / 2022-09-10

schema name column data type
pg_catalog svcs_query_metrics cpu_skew numeric(38,2)
pg_catalog svcs_query_metrics dimension varchar(24)
pg_catalog svcs_query_metrics io_skew numeric(38,2)
pg_catalog svcs_query_metrics join_row_count int8
pg_catalog svcs_query_metrics nested_loop_join_row_count int8
pg_catalog svcs_query_metrics query int4
pg_catalog svcs_query_metrics query_blocks_read int8
pg_catalog svcs_query_metrics query_cpu_time int8
pg_catalog svcs_query_metrics query_cpu_usage_percent numeric(38,2)
pg_catalog svcs_query_metrics query_execution_time int8
pg_catalog svcs_query_metrics query_queue_time int8
pg_catalog svcs_query_metrics query_temp_blocks_to_disk int8
pg_catalog svcs_query_metrics return_row_count int8
pg_catalog svcs_query_metrics scan_row_count int8
pg_catalog svcs_query_metrics segment int4
pg_catalog svcs_query_metrics segment_execution_time int8
pg_catalog svcs_query_metrics service_class int4
pg_catalog svcs_query_metrics service_class_name char(64)
pg_catalog svcs_query_metrics spectrum_scan_row_count int8
pg_catalog svcs_query_metrics spectrum_scan_size_mb int8
pg_catalog svcs_query_metrics step int4
pg_catalog svcs_query_metrics step_label varchar(30)
pg_catalog svcs_query_metrics userid int4

View Text

SELECT qm.userid,
       qm.query,
       qm.service_class,
       CAST(CASE
              WHEN qm.segment = -1
               AND qm.step_type = -1
               AND qm.step = -1
                THEN CAST('query' AS text)
              WHEN qm.segment > -1
               AND qm.step_type = -1
               AND qm.step = -1
                THEN CAST('segment' AS text)
              WHEN qm.segment > -1
               AND qm.step_type > -1
               AND qm.step > -1
                THEN CAST('step' AS text)
              ELSE CAST(NULL AS text)
            END AS varchar(24)) AS dimension,
       CASE
         WHEN qm.segment = -1
           THEN CAST(NULL AS integer)
         ELSE qm.segment
       END AS segment,
       CASE
         WHEN qm.step = -1
           THEN CAST(NULL AS integer)
         ELSE qm.step
       END AS step,
       CAST(CASE
              WHEN qm.step_type = 1
                THEN CAST('scan' AS text)
              WHEN qm.step_type = 2
                THEN CAST('insert' AS text)
              WHEN qm.step_type = 3
                THEN CAST('aggr' AS text)
              WHEN qm.step_type = 4
                THEN CAST('return' AS text)
              WHEN qm.step_type = 6
                THEN CAST('sort' AS text)
              WHEN qm.step_type = 7
                THEN CAST('merge' AS text)
              WHEN qm.step_type = 8
                THEN CAST('dist' AS text)
              WHEN qm.step_type = 9
                THEN CAST('bcast' AS text)
              WHEN qm.step_type = 10
                THEN CAST('hjoin' AS text)
              WHEN qm.step_type = 11
                THEN CAST('mjoin' AS text)
              WHEN qm.step_type = 12
                THEN CAST('save' AS text)
              WHEN qm.step_type = 14
                THEN CAST('hash' AS text)
              WHEN qm.step_type = 15
                THEN CAST('nloop' AS text)
              WHEN qm.step_type = 16
                THEN CAST('project' AS text)
              WHEN qm.step_type = 17
                THEN CAST('limit' AS text)
              WHEN qm.step_type = 18
                THEN CAST('unique' AS text)
              WHEN qm.step_type = 20
                THEN CAST('delete' AS text)
              WHEN qm.step_type = 26
                THEN CAST('limit' AS text)
              WHEN qm.step_type = 29
                THEN CAST('window' AS text)
              WHEN qm.step_type = 32
                THEN CAST('udf' AS text)
              WHEN qm.step_type = 33
                THEN CAST('unique' AS text)
              WHEN qm.step_type = 37
                THEN CAST('returnclient' AS text)
              WHEN qm.step_type = 38
                THEN CAST('returnleader' AS text)
              WHEN qm.step_type = 40
                THEN CAST('spectrumscan' AS text)
              ELSE CAST(NULL AS text)
            END AS varchar(30)) AS step_label,
       CAST(CASE
              WHEN qm.segment = -1
               AND qm.step_type = -1
               AND qm.step = -1
                THEN ceiling(CASE
                               WHEN CAST(CAST(max(qm.cpu_time) AS numeric) AS numeric(38, 6)) = CAST(-1 AS numeric(38, 6))
                                 THEN CAST(NULL AS numeric)
                               ELSE CAST(CAST(max(qm.cpu_time) AS numeric) AS numeric(38, 6))
                             END / CAST(CAST(1000000 AS numeric) AS numeric(38, 6)))
              ELSE CAST(NULL AS numeric)
            END AS bigint) AS query_cpu_time,
       CASE
         WHEN qm.segment = -1
          AND qm.step_type = -1
          AND qm.step = -1
           THEN CASE
                  WHEN max(qm.blocks_read) = -1
                    THEN CAST(NULL AS bigint)
                  ELSE max(qm.blocks_read)
                END
         ELSE CAST(NULL AS bigint)
       END AS query_blocks_read,
       CAST(ceiling(CAST(CAST(q.total_exec_time AS numeric) AS numeric(38, 6)) / CAST(CAST(1000000 AS numeric) AS numeric(38, 6))) AS bigint) AS query_execution_time,
       CAST(round(CASE
                    WHEN qm.segment = -1
                     AND qm.step_type = -1
                     AND qm.step = -1
                      THEN CAST(100 AS numeric) * max((CAST(CASE
                                                              WHEN qm.cpu_time = -1
                                                                THEN CAST(NULL AS bigint)
                                                              ELSE qm.cpu_time
                                                            END AS numeric) + 0.00001) / (CAST(CASE
                                                                                                 WHEN qm.run_time = -1
                                                                                                   THEN CAST(NULL AS bigint)
                                                                                                 ELSE qm.run_time
                                                                                               END AS numeric) + 0.00001))
                    ELSE CAST(NULL AS numeric)
                  END,
                  2) AS numeric(38, 2)) AS query_cpu_usage_percent,
       CASE
         WHEN qm.segment = -1
          AND qm.step_type = -1
          AND qm.step = -1
           THEN CASE
                  WHEN max(qm.blocks_to_disk) = -1
                    THEN CAST(NULL AS bigint)
                  ELSE max(qm.blocks_to_disk)
                END
         ELSE CAST(NULL AS bigint)
       END AS query_temp_blocks_to_disk,
       CAST(CASE
              WHEN qm.segment > -1
               AND qm.step_type = -1
                THEN ceiling(CAST(CAST(CASE
                                         WHEN max(qm.max_run_time) = -1
                                           THEN CAST(NULL AS bigint)
                                         ELSE max(qm.max_run_time)
                                       END AS numeric) AS numeric(38, 6)) / CAST(CAST(1000000 AS numeric) AS numeric(38, 6)))
              ELSE CAST(NULL AS numeric)
            END AS bigint) AS segment_execution_time,
       CAST(round(CASE
                    WHEN qm.segment > -1
                     AND qm.step_type = -1
                     AND max(qm.max_cpu_time) > 0
                     AND max(qm.cpu_time) > 0
                      THEN CAST(qm.slices AS numeric) * max((CAST(CASE
                                                                    WHEN qm.max_cpu_time = -1
                                                                      THEN CAST(NULL AS bigint)
                                                                    ELSE qm.max_cpu_time
                                                                  END AS numeric) + 0.00001) / (CAST(CASE
                                                                                                       WHEN qm.cpu_time = -1
                                                                                                         THEN CAST(NULL AS bigint)
                                                                                                       ELSE qm.cpu_time
                                                                                                     END AS numeric) + 0.00001))
                    ELSE CAST(NULL AS numeric)
                  END,
                  2) AS numeric(38, 2)) AS cpu_skew,
       CAST(round(CASE
                    WHEN qm.segment > -1
                     AND qm.step_type = -1
                     AND max(qm.max_blocks_read) > 0
                     AND max(qm.blocks_read) > 0
                      THEN CAST(qm.slices AS numeric) * max((CAST(CASE
                                                                    WHEN qm.max_blocks_read = -1
                                                                      THEN CAST(NULL AS integer)
                                                                    ELSE qm.max_blocks_read
                                                                  END AS numeric) + 0.00001) / (CAST(CASE
                                                                                                       WHEN qm.blocks_read = -1
                                                                                                         THEN CAST(NULL AS bigint)
                                                                                                       ELSE qm.blocks_read
                                                                                                     END AS numeric) + 0.00001))
                    ELSE CAST(NULL AS numeric)
                  END,
                  2) AS numeric(38, 2)) AS io_skew,
       CASE
         WHEN qm.segment > -1
          AND qm.step_type = 1
          AND qm.step > -1
           THEN CASE
                  WHEN max(qm.rows) = -1
                    THEN CAST(NULL AS bigint)
                  ELSE max(qm.rows)
                END
         ELSE CAST(NULL AS bigint)
       END AS scan_row_count,
       CASE
         WHEN qm.segment > -1
          AND (qm.step_type = 10
            OR qm.step_type = 11
            OR qm.step_type = 15)
          AND qm.step > -1
           THEN CASE
                  WHEN max(qm.rows) = -1
                    THEN CAST(NULL AS bigint)
                  ELSE max(qm.rows)
                END
         ELSE CAST(NULL AS bigint)
       END AS join_row_count,
       CASE
         WHEN qm.segment > -1
          AND qm.step_type = 15
          AND qm.step > -1
           THEN CASE
                  WHEN max(qm.rows) = -1
                    THEN CAST(NULL AS bigint)
                  ELSE max(qm.rows)
                END
         ELSE CAST(NULL AS bigint)
       END AS nested_loop_join_row_count,
       CASE
         WHEN qm.segment > -1
          AND qm.step_type = 37
          AND qm.step > -1
           THEN CASE
                  WHEN max(qm.rows) = -1
                    THEN CAST(NULL AS bigint)
                  ELSE max(qm.rows)
                END
         ELSE CAST(NULL AS bigint)
       END AS return_row_count,
       CASE
         WHEN qm.segment > -1
          AND qm.step_type = 40
          AND qm.step > -1
           THEN CASE
                  WHEN max(qm.rows) = -1
                    THEN CAST(NULL AS bigint)
                  ELSE max(qm.rows)
                END
         ELSE CAST(NULL AS bigint)
       END AS spectrum_scan_row_count,
       CASE
         WHEN qm.segment > -1
          AND qm.step_type = 40
          AND qm.step > -1
           THEN CASE
                  WHEN max(qm.query_scan_size) = -1
                    THEN CAST(NULL AS bigint)
                  ELSE max(qm.query_scan_size)
                END
         ELSE CAST(NULL AS bigint)
       END AS spectrum_scan_size_mb,
       CAST(CASE
              WHEN qm.segment = -1
               AND qm.step_type = -1
               AND qm.step = -1
                THEN ceiling(CAST(CAST(CASE
                                         WHEN max(qm.query_queue_time) = -1
                                           THEN CAST(NULL AS bigint)
                                         ELSE max(qm.query_queue_time)
                                       END AS numeric) AS numeric(38, 6)) / CAST(CAST(1000000 AS numeric) AS numeric(38, 6)))
              ELSE CAST(NULL AS numeric)
            END AS bigint) AS query_queue_time,
       qm.service_class_name
FROM (SELECT stcs.userid,
             stcs.service_class,
             map.primary_query AS query,
             stcs.segment,
             stcs.step_type,
             stcs.slices,
             stcs.rows,
             stcs.max_cpu_time,
             stcs.cpu_time,
             stcs.max_blocks_read,
             stcs.blocks_read,
             stcs.max_run_time,
             stcs.run_time,
             stcs.blocks_to_disk,
             stcs.step,
             stcs.query_scan_size,
             stcs.query_queue_time,
             stcs.service_class_name
      FROM stcs_query_metrics AS stcs
           INNER JOIN stcs_concurrency_scaling_query_mapping AS map ON map.concurrency_scaling_query = stcs.query
      WHERE stcs.__cluster_type = CAST('cs' AS bpchar)
        AND to_date(CAST(stcs.__log_generated_date AS text),
                    CAST('YYYYMMDD' AS text)) > (getdate() - CAST('7 days' AS interval))
        AND to_date(CAST(map.__log_generated_date AS text),
                    CAST('YYYYMMDD' AS text)) > (getdate() - CAST('7 days' AS interval))
        AND CAST(map.concurrency_scaling_cluster AS text) = split_part(CAST(stcs.__path AS text),
                                                                       CAST('/' AS text),
                                                                       10)
        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 stcs.userid = current_user_id())) AS qm
     INNER JOIN stl_wlm_query AS q USING (userid, service_class, query)
GROUP BY qm.userid, qm.query, qm.service_class, qm.service_class_name, qm.slices, qm.segment, qm.step_type, qm.step, q.total_exec_time

UNION ALL

SELECT svl_query_metrics.userid,
       svl_query_metrics.query,
       svl_query_metrics.service_class,
       svl_query_metrics.dimension,
       svl_query_metrics.segment,
       svl_query_metrics.step,
       svl_query_metrics.step_label,
       svl_query_metrics.query_cpu_time,
       svl_query_metrics.query_blocks_read,
       svl_query_metrics.query_execution_time,
       svl_query_metrics.query_cpu_usage_percent,
       svl_query_metrics.query_temp_blocks_to_disk,
       svl_query_metrics.segment_execution_time,
       svl_query_metrics.cpu_skew,
       svl_query_metrics.io_skew,
       svl_query_metrics.scan_row_count,
       svl_query_metrics.join_row_count,
       svl_query_metrics.nested_loop_join_row_count,
       svl_query_metrics.return_row_count,
       svl_query_metrics.spectrum_scan_row_count,
       svl_query_metrics.spectrum_scan_size_mb,
       svl_query_metrics.query_queue_time,
       svl_query_metrics.service_class_name
FROM svl_query_metrics


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