SHOW_NODE_INFO

SHOW_NODE_INFO returns a snapshot of the current query plan, similar to EXPLAIN ANALYZE from other databases.

The snapshot provides information about execution which can be used for monitoring and troubleshooting slow running statements by helping identify long-running execution nodes (components that process data), etc.

See also EXPLAIN, SHOW_SERVER_STATUS.

Permissions

The role must have the SUPERUSER permissions.

Syntax

show_node_info_statement ::=
    SELECT SHOW_NODE_INFO(stmt_id)
    ;

stmt_id ::= bigint

Parameters

Parameter Description
stmt_id The statement ID to explore

Returns

This utility returns details of the execution nodes, if the statement is still running.

If the statement has finished, or the statment ID does not exist, the utility returns an empty result set.

Result columns
stmt_id The ID for the statement
node_id This node’s ID
node_type The node type
rows Total number of rows this node has processed
chunks Number of chunks this node has processed
avg_rows_in_chunk Average amount of rows that this node processes in each chunk (rows / chunks)
time Timestamp for this node’s creation
parent_node_id The node_id of this node’s parent
read Total data read from disk
write Total data written to disk
comment Additional information (e.g. table name for ReadTable)
timesum Total elapsed time for this execution node’s processing

Notes

  • This utility shows the execution information for active statements. Once a query has finished execution, the information is no longer available using this utility. See Logging for more information about extracting the information from the logs.
  • This utility is primarily intended for troubleshooting with SQream support.

Examples

Getting execution details for a statement

t=> SELECT SHOW_SERVER_STATUS();
service | instanceid | connection_id | serverip     | serverport | database_name | user_name | clientip     | statementid | statement                                                       | statementstarttime  | statementstatus | statementstatusstart
--------+------------+---------------+--------------+------------+---------------+-----------+--------------+-------------+-----------------------------------------------------------------+---------------------+-----------------+---------------------
sqream  |            |           152 | 192.168.1.91 |       5000 | t            | sqream    | 192.168.1.91 |         176 | SELECT "Name" FROM nba WHERE REGEXP_COUNT("Name", '( )+', 8)>1; | 25-12-2019 23:53:13 | Executing       | 25-12-2019 23:53:13
sqream  |            |           151 | 192.168.1.91 |       5000 | t            | sqream    | 192.168.0.1  |         177 | SELECT show_server_status()                                     | 25-12-2019 23:51:31 | Executing       | 25-12-2019 23:53:13

The statement ID we want to reserach is 176, running on worker 192.168.1.91.

The query text is SELECT "Name" FROM nba WHERE REGEXP_COUNT("Name", '( )+', 8)>1;

t=> SELECT SHOW_NODE_INFO(176);
stmt_id | node_id | node_type          | rows | chunks | avg_rows_in_chunk | time                | parent_node_id | read | write | comment    | timeSum
--------+---------+--------------------+------+--------+-------------------+---------------------+----------------+------+-------+------------+--------
    176 |       1 | PushToNetworkQueue |    1 |      1 |                 1 | 2019-12-25 23:53:13 |             -1 |      |       |            |  0.0025
    176 |       2 | Rechunk            |    1 |      1 |                 1 | 2019-12-25 23:53:13 |              1 |      |       |            |       0
    176 |       3 | GpuToCpu           |    1 |      1 |                 1 | 2019-12-25 23:53:13 |              2 |      |       |            |       0
    176 |       4 | ReorderInput       |    1 |      1 |                 1 | 2019-12-25 23:53:13 |              3 |      |       |            |       0
    176 |       5 | Filter             |    1 |      1 |                 1 | 2019-12-25 23:53:13 |              4 |      |       |            |  0.0002
    176 |       6 | GpuTransform       |  457 |      1 |               457 | 2019-12-25 23:53:13 |              5 |      |       |            |  0.0002
    176 |       7 | GpuDecompress      |  457 |      1 |               457 | 2019-12-25 23:53:13 |              6 |      |       |            |       0
    176 |       8 | CpuToGpu           |  457 |      1 |               457 | 2019-12-25 23:53:13 |              7 |      |       |            |  0.0003
    176 |       9 | Rechunk            |  457 |      1 |               457 | 2019-12-25 23:53:13 |              8 |      |       |            |       0
    176 |      10 | CpuDecompress      |  457 |      1 |               457 | 2019-12-25 23:53:13 |              9 |      |       |            |       0
    176 |      11 | ReadTable          |  457 |      1 |               457 | 2019-12-25 23:53:13 |             10 | 4MB  |       | public.nba |  0.0004