SHOW_NODE_INFO

SHOW_NODE_INFO returns a snapshot of the current query plan, similarly to the EXPLAIN ANALYZE function used in 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

Column name	Description
stmt_id	The ID for the statement
node_id	This node’s ID in this execution plan
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

Node types

This is a full list of node types:

Node types

Column name	Execution location	Description
AddChunkId		Used during insert operations
AddSequences		Used during insert operations, with identity columns
AddMinMaxMetadata		Used to calculate ranges for the chunk metadata system
AddTopSortFilters		An operation to optimize LIMIT when used alongside ORDER BY
Compress	CPU and GPU	Compress data with both CPU and GPU schemes
CpuDecompress	CPU	Decompression operation, common for longer TEXT types
CpuLoopJoin	CPU	A non-indexed nested loop join, performed on the CPU
CpuReduce	CPU	A reduce process performed on the CPU, primarily with DISTINCT aggregates (e.g. COUNT(DISTINCT ...))
CpuToGpu, GpuToCpu		An operation that moves data to or from the GPU for processing
CpuTransform	CPU	A transform operation performed on the CPU, usually a scalar function
CrossJoin		A join without a join condition
DeferredGather	CPU	Merges the results of GPU operations with a result set [1]
Distinct	GPU	Removes duplicate rows (usually as part of the DISTINCT operation)
Distinct_Merge	CPU	The merge operation of the Distinct operation
Filter	GPU	A filtering operation, such as a WHERE or JOIN clause
GpuCopy	GPU	Copies data between GPUs or within a single GPU
GpuDecompress	GPU	Decompression operation
GpuReduceMerge	GPU	An operation to optimize part of the merger phases in the GPU
GpuTransform	GPU	A transformation operation such as a type cast or scalar function
Hash	CPU	Hashes the output result. Used internally.
JoinSideMarker		Used internally.
LiteralValues	CPU	Creates a virtual relation (table), when VALUES is used
LocateFiles	CPU	Validates external file paths for foreign data wrappers, expanding directories and GLOB patterns
LoopJoin	GPU	A non-indexed nested loop join, performed on the GPU
MarkMatchedJoinRows		Used in outer joins, matches rows for larger join operations
NullifyDuplicates		Replaces duplicate values with NULL to calculate distinct aggregates
NullSink	CPU	Used internally
ParseCsv	CPU	A CSV parser, used after ReadFiles to convert the CSV into columnar data
PopNetworkQueue	CPU	Fetches data from the network queue (e.g. when used with INSERT)
PushToNetworkQueue	CPU	Sends result sets to a client connected over the network
ReadCatalog	CPU	Converts the catalog into a relation (table)
ReadFiles	CPU	Reads external flat-files
ReadOrc	CPU	Reads data from an ORC file
ReadParquet	CPU	Reads data from a Parquet file
ReadTable	CPU	Reads data from a standard table stored on disk
ReadTableMetadata	CPU	Reads only table metadata as an optimization
Rechunk		Reorganize multiple small chunks into a full chunk. Commonly found after joins and when HIGH_SELECTIVITY is used
Reduce	GPU	A reduction operation, such as a GROUP BY
ReduceMerge	GPU	A merge operation of a reduction operation, helps operate on larger-than-RAM data
ReorderInput		Change the order of arguments in preparation for the next operation
SeparatedGather	GPU	Gathers additional columns for the result
Sort	GPU	Sort operation [2]
SortMerge	CPU	A merge operation of a sort operation, helps operate on larger-than-RAM data
SortMergeJoin	GPU	A sort-merge join, performed on the GPU
TakeRowsFromChunk		Take the first N rows from each chunk, to optimize LIMIT when used alongside ORDER BY
Top		Limits the input size, when used with LIMIT (or its alias TOP)
UdfTransform	CPU	Executes a user defined function
UnionAll		Combines two sources of data when UNION ALL is used
VarCharJoiner		Used internally
VarCharSplitter		Used intenrally
Window	GPU	Executes a non-ranking window function
WindowRanking	GPU	Executes a ranking window function
WriteTable	CPU	Writes the result set to a standard table stored on disk

Footnotes

[1]

Gathers columns which should be returned. This node typically spends most of the time on decompressing additional columns.

[2]

A GPU sort operation can be added by the statement compiler before GROUP BY or JOIN operations.

Statement statuses

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; | 2019-12-25 23:53:13 | Executing       | 2019-12-25 23:53:13
sqream  |            |           151 | 192.168.1.91 |       5000 | t            | sqream    | 192.168.0.1  |         177 | SELECT show_server_status()                                     | 2019-12-25 23:51:31 | Executing       | 2019-12-25 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