Product DocumentationAppearance
sys.query_analyze
This view records runtime statistics for each execution step (or node) in the query plan. A unique row in this table is identified by a combination of the query ID, worker ID, and node ID. All of the reported statistics are per plan node per worker.
| Column Name | Data Type | Description |
|---|---|---|
| query_id | bigint | Query execution ID |
| worker_id | text | Worker ID (this column does not exist in sys.log_query_analyze) |
| node_id | integer | Node ID within the query plan |
| rows_planned | bigint | Estimated number of rows that will be output by this query node |
| rows_actual | bigint | Actual number of rows output from this query node |
| memory_planned_bytes | bigint | Estimated amount of memory required for this query node |
| memory_actual_bytes | bigint | Amount of memory consumed by this query node |
| io_read_bytes | bigint | Number of bytes read |
| io_write_bytes | bigint | Number of bytes written |
| io_network_bytes | bigint | Number of bytes sent over the network |
| io_network_count | bigint | Number of network calls made |
| runtime_ms | decimal(18,3) | Wall clock time in milliseconds: how long the plan node was running on the worker nodes |
| skew | double precision | A value between 0 and 1 that indicates how evenly distributed the input is to the query node: 0 indicates perfect distribution and 1 indicates all data is on a single node.Important: Values above 0.3 should be investigated. |
| detail | varchar(1024) | Textual data that describes additional statistics for this query node. |
Examples
premdb=# select query_id, worker_id, node_id, memory_actual_bytes, io_read_bytes, io_write_bytes, io_network_bytes, io_network_count, runtime_ms, rows_actual as rows
from sys.query_analyze order by worker_id, query_id, node_id;
query_id | worker_id | node_id | memory_bytes | io_read_bytes | io_write_bytes | io_network_bytes | io_network_calls | runtime_ms | rows
----------+--------------------------------------+---------+--------------+---------------+----------------+------------------+------------------+------------+-----------
25832 | 00000000-0000-0000-0000-000000000002 | 0 | 3145936 | 0 | 0 | 8091360528 | 9078 | 0 | 157391700
25832 | 00000000-0000-0000-0000-000000000002 | 1 | 17301376 | 0 | 0 | 0 | 0 | 9 | 8606
25832 | 00000000-0000-0000-0000-000000000002 | 2 | 262144 | 0 | 0 | 0 | 0 | 2973 | 8606
25832 | 00000000-0000-0000-0000-000000000002 | 3 | 262144 | 0 | 0 | 0 | 0 | 3009 | 8606
25832 | 00000000-0000-0000-0000-000000000002 | 4 | 61440 | 131072 | 0 | 0 | 0 | 3066 | 8606
25832 | 00000000-0000-0000-0000-000000000002 | 5 | 1048576 | 0 | 0 | 0 | 0 | 2973 | 8606
25832 | 00000000-0000-0000-0000-000000000002 | 7 | 1310864 | 0 | 0 | 0 | 1 | 3006 | 0
25832 | 00000000-0000-0000-0000-000000000002 | 8 | 1310720 | 0 | 0 | 0 | 0 | 3006 | 0
25832 | 00000000-0000-0000-0000-000000000002 | 9 | 2097152 | 0 | 0 | 0 | 0 | 3 | 157391700
25832 | 00000000-0000-0000-0000-000000000002 | 10 | 524288 | 0 | 0 | 0 | 0 | 4 | 756196
25832 | 00000000-0000-0000-0000-000000000002 | 11 | 262144 | 0 | 0 | 0 | 0 | 4 | 756196
25832 | 00000000-0000-0000-0000-000000000002 | 12 | 1078272 | 0 | 0 | 0 | 0 | 5 | 763136
25832 | 00000000-0000-0000-0000-000000000002 | 13 | 1048576 | 0 | 0 | 0 | 0 | 4 | 756196
25832 | 00000000-0000-0000-0000-000000000002 | 15 | 1310828 | 0 | 0 | 0 | 1 | 375 | 0
25832 | 00000000-0000-0000-0000-000000000002 | 16 | 1310720 | 0 | 0 | 0 | 0 | 375 | 0
...The following example joins sys.query_analyze to sys.query and sys.query_explain. The output shown here is only the first few rows for the first worker node:
yellowbrick=# select a.*, e.query_plan
from sys.query_analyze a
left outer join sys.query q
on a.query_id = q.query_id
left outer join sys.query_explain e
on e.plan_id = q.plan_id and e.node_id = a.node_id
order by a.query_id, a.worker_id, a.node_id;
from sys.query_analyze a left outer join sys.query q on a.query_id = q.query_id left outer join sys.query_explain e on e.plan_id = q.plan_id and e.node_id = a.node_id order by a.query_id, a.worker_id, a.node_id;
-[ RECORD 1 ]--------+-----------------------------------------------------------------------------------------------------------------
query_id | 19798710
worker_id | 00000000-0000-0000-0000-38b8ebd000cd
node_id | 0
rows_planned | 774540
rows_actual | 0
memory_planned_bytes | 0
memory_actual_bytes | 0
io_read_bytes | [NULL]
io_write_bytes | [NULL]
io_network_bytes | 0
io_network_count | 0
runtime_ms | 0.000
skew | 0
detail | [NULL]
query_plan | SEQUENCE +
| distribute none
-[ RECORD 2 ]--------+-----------------------------------------------------------------------------------------------------------------
query_id | 19798710
worker_id | 00000000-0000-0000-0000-38b8ebd000cd
node_id | 1
rows_planned | 774540
rows_actual | 0
memory_planned_bytes | 33554784
memory_actual_bytes | 352
io_read_bytes | [NULL]
io_write_bytes | [NULL]
io_network_bytes | 0
io_network_count | 0
runtime_ms | 0.000
skew | 0
detail | [NULL]
query_plan | WRITE ROW STORE +
| (newmatchstats.seasonid, newmatchstats.matchday, newmatchstats.htid, newmatchstats.atid, newmatchstats.moment) +
| distribute (newmatchstats.seasonid)
-[ RECORD 3 ]--------+-----------------------------------------------------------------------------------------------------------------
query_id | 19798710
worker_id | 00000000-0000-0000-0000-38b8ebd000cd
node_id | 2
rows_planned | 774540
rows_actual | 0
memory_planned_bytes | 3221225472
memory_actual_bytes | 1048576
io_read_bytes | [NULL]
io_write_bytes | [NULL]
io_network_bytes | 0
io_network_count | 0
runtime_ms | 0.000
skew | 0
detail | [NULL]
query_plan | INSERT INTO newmatchstats +
| (newmatchstats.seasonid, newmatchstats.matchday, newmatchstats.htid, newmatchstats.atid, newmatchstats.moment) +
| distribute (newmatchstats.seasonid)
-[ RECORD 4 ]--------+-----------------------------------------------------------------------------------------------------------------
query_id | 19798710
worker_id | 00000000-0000-0000-0000-38b8ebd000cd
node_id | 3
rows_planned | 774540
rows_actual | 0
memory_planned_bytes | 8388608
memory_actual_bytes | 786432
io_read_bytes | [NULL]
io_write_bytes | [NULL]
io_network_bytes | 0
io_network_count | 0
runtime_ms | 0.000
skew | 0.154261304864766
detail | [NULL]
query_plan | APPEND SCAN +
| (newmatchstats.seasonid, newmatchstats.matchday, newmatchstats.htid, newmatchstats.atid, newmatchstats.moment) +
| distribute (newmatchstats.seasonid)
-[ RECORD 5 ]--------+-----------------------------------------------------------------------------------------------------------------
query_id | 19798710
worker_id | 00000000-0000-0000-0000-38b8ebd000cd
node_id | 4
rows_planned | 774540
rows_actual | 0
memory_planned_bytes | 285212672
memory_actual_bytes | 8126464
io_read_bytes | [NULL]
io_write_bytes | [NULL]
io_network_bytes | 0
io_network_count | 29
runtime_ms | 0.000
skew | 0.154261304864766
detail | [NULL]
query_plan | DISTRIBUTE ON HASH(newmatchstats.seasonid) (degraded only) +
| (newmatchstats.seasonid, newmatchstats.matchday, newmatchstats.htid, newmatchstats.atid, newmatchstats.moment) +
| distribute (newmatchstats.seasonid)
...