| 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 |
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