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

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; | 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