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