| schema | name | column | data type |
|---|---|---|---|
| pg_catalog | svl_query_metrics | cpu_skew | numeric(38,2) |
| pg_catalog | svl_query_metrics | dimension | varchar(24) |
| pg_catalog | svl_query_metrics | io_skew | numeric(38,2) |
| pg_catalog | svl_query_metrics | join_row_count | int8 |
| pg_catalog | svl_query_metrics | nested_loop_join_row_count | int8 |
| pg_catalog | svl_query_metrics | query | int4 |
| pg_catalog | svl_query_metrics | query_blocks_read | int8 |
| pg_catalog | svl_query_metrics | query_cpu_time | int8 |
| pg_catalog | svl_query_metrics | query_cpu_usage_percent | numeric(38,2) |
| pg_catalog | svl_query_metrics | query_execution_time | int8 |
| pg_catalog | svl_query_metrics | query_queue_time | int8 |
| pg_catalog | svl_query_metrics | query_temp_blocks_to_disk | int8 |
| pg_catalog | svl_query_metrics | return_row_count | int8 |
| pg_catalog | svl_query_metrics | scan_row_count | int8 |
| pg_catalog | svl_query_metrics | segment | int4 |
| pg_catalog | svl_query_metrics | segment_execution_time | int8 |
| pg_catalog | svl_query_metrics | service_class | int4 |
| pg_catalog | svl_query_metrics | service_class_name | char(64) |
| pg_catalog | svl_query_metrics | spectrum_scan_row_count | int8 |
| pg_catalog | svl_query_metrics | spectrum_scan_size_mb | int8 |
| pg_catalog | svl_query_metrics | step | int4 |
| pg_catalog | svl_query_metrics | step_label | varchar(30) |
| pg_catalog | svl_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 stl_query_metrics 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 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