Query Performance Analysis
About 20 min
Query Performance Analysis
1. Overview
Query analysis helps users understand the execution mechanism and performance bottlenecks of queries, enabling query optimization and performance tuning. The IoTDB table model provides two types of query analysis statements:
EXPLAIN: Previews the execution plan of a query SQL, showing how IoTDB organizes data retrieval and processing.EXPLAIN ANALYZE: Actually executes the query on top ofEXPLAIN, and displays the time, resource consumption, and operator statistics during query execution.
The default output of EXPLAIN and EXPLAIN ANALYZE is designed for human reading and is suitable for direct viewing in the CLI. For scenarios that require stable parsing of query plans or execution statistics, such as Web Console, CI regression testing, and automated performance diagnosis systems, you can use the JSON output format to obtain structured results. The JSON format is supported since V2.0.11.1.
1.1 Comparison of Query Analysis Methods
Compared with monitoring panels, Arthas sampling, and other troubleshooting methods, EXPLAIN ANALYZE requires no additional components and can perform distributed tracing on a single SQL, making it more suitable for locating performance issues of specific queries.
| Method | Installation Difficulty | Business Impact | Functional Scope |
|---|---|---|---|
EXPLAIN ANALYZE statement | Low. No additional components required, built-in IoTDB SQL statement | Low. Only affects the single query being analyzed, no impact on other online workloads | Supports distributed, can trace a single SQL |
| Monitoring panel | Medium. Requires installing the IoTDB monitoring panel tool and enabling the IoTDB monitoring service | Medium. IoTDB monitoring service recording metrics brings additional overhead | Supports distributed, only supports analysis of overall query load and time consumption of the database |
| Arthas sampling | Medium. Requires installing the Java Arthas tool | High. CPU sampling may affect online business response speed | Does not support distributed, only supports analysis of overall query load and time consumption of the database |
2. EXPLAIN
2.1 Syntax
The EXPLAIN command is used to view the distributed execution plan of a SQL query. The execution plan is displayed as an operator tree, describing how IoTDB will execute the query.
EXPLAIN [(FORMAT { GRAPHVIZ | JSON })] <SELECT_STATEMENT>Where:
| Parameter | Description |
|---|---|
SELECT_STATEMENT | The query statement to be analyzed |
FORMAT GRAPHVIZ | Outputs the distributed plan in the default graphical text form, suitable for human reading |
FORMAT JSON | Outputs the distributed plan as a JSON object, suitable for programmatic parsing (supported since V2.0.11.1) |
The format name after FORMAT is case-insensitive, for example, FORMAT json is equivalent to FORMAT JSON.
2.2 Default GRAPHVIZ Format
The default format of EXPLAIN is GRAPHVIZ. After execution, you will get the distribution plan result column.
Taking table1 in the sample data as an example, the following two statements are equivalent:
EXPLAIN SELECT * FROM table1;
EXPLAIN (FORMAT GRAPHVIZ) SELECT * FROM table1;After executing the above statements, the output is as follows: IoTDB reads data from different data partitions through DeviceTableScanNode nodes, and aggregates and returns the results through the Collect operator.
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| distribution plan|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ |
| │OutputNode-4 │ |
| │OutputColumns-[time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time] │ |
| │OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│ |
| └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ |
| │ |
| │ |
| ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ |
| │Collect-33 │ |
| │OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│ |
| └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ |
| ┌───────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────┐ |
| │ │ |
| ┌───────────┐ ┌───────────┐ |
| │Exchange-40│ │Exchange-41│ |
| └───────────┘ └───────────┘ |
| │ │ |
| │ │ |
|┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐|
|│DeviceTableScanNode-32 │ │DeviceTableScanNode-31 │|
|│QualifiedTableName: database1.table1 │ │QualifiedTableName: database1.table1 │|
|│OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│ │OutputSymbols: [time, region, plant_id, device_id, model_id, maintenance, temperature, humidity, status, arrival_time]│|
|│DeviceNumber: 4 │ │DeviceNumber: 2 │|
|│ScanOrder: ASC │ │ScanOrder: ASC │|
|│PushDownOffset: 0 │ │PushDownOffset: 0 │|
|│PushDownLimit: 0 │ │PushDownLimit: 0 │|
|│PushDownLimitToEachDevice: false │ │PushDownLimitToEachDevice: false │|
|│RegionId: 1 │ │RegionId: 2 │|
|└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+2.3 JSON Format
Using EXPLAIN (FORMAT JSON) returns the JSON representation of the distributed plan tree. The result set is a single-column output, and in JSON format returns a single row with a single JSON object, making it convenient for JDBC clients or automation tools to read directly.
The top-level structure of EXPLAIN (FORMAT JSON) varies with the query type:
| Scenario | Top-level Structure | Description |
|---|---|---|
| Regular query | plan node JSON object | The top-level is directly a plan node object such as OutputNode |
| Query containing materialized CTE | wrapper JSON object | The top-level contains cteQueries and mainQuery, used to retain both the CTE subquery plan and the main query plan |
Each plan node is output as a JSON object. The basic fields are as follows:
| Field | Type/Occurrence | Description |
|---|---|---|
name | string | The display name formed by concatenating the node type and plan node id, for example OutputNode-4 |
id | string | plan node id |
properties | object | Node properties. Only output when the node has displayable properties |
children | array | Array of child nodes. Only output when the node has child nodes |
Common plan node properties include:
| Node Type | Main Fields/Properties |
|---|---|
OutputNode | OutputColumns, OutputSymbols |
TableScanNode / DeviceTableScanNode | QualifiedTableName, OutputSymbols, DeviceNumber, ScanOrder, TimePredicate, PushDownPredicate, PushDownOffset, PushDownLimit, PushDownLimitToEachDevice, RegionId |
TreeDeviceViewScanNode | In addition to table scan properties, adds TreeDB, MeasurementToColumnName |
AggregationNode | OutputSymbols, Aggregators, GroupingKeys, Streamable, PreGroupedSymbols, Step |
FilterNode | Predicate |
ProjectNode | OutputSymbols, Expressions |
LimitNode / OffsetNode | Count |
SortNode / MergeSortNode | OrderBy |
JoinNode | JoinType, Criteria, OutputSymbols |
UnionNode | OutputSymbols |
ExplainAnalyzeNode | ChildPermittedOutputs |
When the query contains a materialized CTE, the JSON top-level is no longer a single plan node directly, but is wrapped as:
{
"cteQueries": [
{
"name": "cte1",
"plan": {
"name": "OutputNode-<id>",
"id": "<id>"
}
}
],
"mainQuery": {
"name": "OutputNode-<id>",
"id": "<id>"
}
}Where cteQueries[].plan and mainQuery are both plan node JSON objects. This structure is used to retain both the CTE subquery plan and the main query plan.
Taking table1 in the sample data as an example:
EXPLAIN (FORMAT JSON) SELECT * FROM table1;+-----------------+
|distribution plan|
+-----------------+
{
"name": "OutputNode-4",
"id": "4",
"properties": {
"OutputColumns": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
]
},
"children": [
{
"name": "CollectNode-33",
"id": "33",
"children": [
{
"name": "ExchangeNode-40",
"id": "40",
"children": [
{
"name": "DeviceTableScanNode-32",
"id": "32",
"properties": {
"QualifiedTableName": "database1.table1",
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"DeviceNumber": "4",
"ScanOrder": "ASC",
"PushDownOffset": "0",
"PushDownLimit": "0",
"PushDownLimitToEachDevice": "false",
"RegionId": "1"
}
}
]
},
{
"name": "ExchangeNode-41",
"id": "41",
"children": [
{
"name": "DeviceTableScanNode-31",
"id": "31",
"properties": {
"QualifiedTableName": "database1.table1",
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"DeviceNumber": "2",
"ScanOrder": "ASC",
"PushDownOffset": "0",
"PushDownLimit": "0",
"PushDownLimitToEachDevice": "false",
"RegionId": "2"
}
}
]
}
]
}
]
}
+-----------------+3. EXPLAIN ANALYZE
3.1 Syntax
EXPLAIN ANALYZE is a performance analysis SQL built into the IoTDB query engine. Unlike EXPLAIN, it actually executes the corresponding query plan and collects execution information, which can be used to track the specific performance distribution of a query, assisting in resource observation, performance tuning, and exception analysis.
EXPLAIN ANALYZE [VERBOSE] [(FORMAT { TEXT | JSON })] <SELECT_STATEMENT>Where:
| Parameter | Description |
|---|---|
SELECT_STATEMENT | The query statement to be analyzed |
VERBOSE | Prints more detailed analysis results. When not specified, some fine-grained statistics are omitted |
FORMAT TEXT | Outputs analysis results in the default text form, suitable for human reading |
FORMAT JSON | Outputs analysis results as a JSON object, suitable for programmatic parsing (supported since V2.0.11.1) |
3.2 Default TEXT Format
The default format of EXPLAIN ANALYZE is TEXT. After execution, you will get the Explain Analyze result column. The results consist of query planning statistics, fragment instance statistics, data reading statistics, and operator tree statistics.
- Query Planning Statistics
QueryStatistics contains query-level statistics:
| Field | Type/Occurrence | Description |
|---|---|---|
Analyze Cost | Query planning statistics | Time consumed in the SQL analysis phase |
Fetch Partition Cost | Query planning statistics | Time consumed fetching the partition table |
Fetch Schema Cost | Query planning statistics | Time consumed fetching metadata and permission validation |
Logical Plan Cost | Query planning statistics | Time consumed building the logical plan |
Logical Optimization Cost | Query planning statistics | Time consumed in logical plan optimization |
Distribution Plan Cost | Query planning statistics | Time consumed building the distributed plan |
Dispatch Cost | Query planning statistics | Time consumed dispatching fragment instances |
Fragment Instances Count | Query planning statistics | Total number of query fragments; each fragment's information is output in sequence |
- Fragment Instance Statistics
FragmentInstance is the encapsulation of an IoTDB query fragment. Each query fragment outputs a copy of execution information, mainly including fragment statistics and operator information.
Fragment statistics include:
| Field | Type/Occurrence | Description |
|---|---|---|
Total Wall Time | Fragment statistics | Physical time from when the fragment starts execution to when it finishes |
Cost of initDataQuerySource | Fragment statistics | Time consumed building the query file list |
Seq File(unclosed) | Fragment statistics | Number of unclosed sequential files |
Seq File(closed) | Fragment statistics | Number of closed sequential files |
UnSeq File(unclosed) | Fragment statistics | Number of unclosed out-of-order files |
UnSeq File(closed) | Fragment statistics | Number of closed out-of-order files |
ready queued time | Fragment statistics | Total time all tasks of the query fragment spent in the ready queue |
blocked queued time | Fragment statistics | Total time all tasks of the query fragment spent in the blocked queue |
- Data Reading Statistics
Query Statistics detail fields include:
| Field | Type/Occurrence | Description |
|---|---|---|
loadBloomFilterFromCacheCount | BloomFilter | Number of BloomFilterCache hits |
loadBloomFilterFromDiskCount | BloomFilter | Number of times BloomFilter was read from disk |
loadBloomFilterActualIOSize | BloomFilter | Disk IO generated when reading BloomFilter from disk, in bytes |
loadBloomFilterTime | BloomFilter | Total time spent reading BloomFilter and computing whether a series exists, in ms |
loadTimeSeriesMetadataDiskSeqCount | TimeSeriesMetadata | Number of TimeSeriesMetadata loaded from closed sequential files |
loadTimeSeriesMetadataDiskUnSeqCount | TimeSeriesMetadata | Number of TimeSeriesMetadata loaded from closed out-of-order files |
loadTimeSeriesMetadataDiskSeqTime | TimeSeriesMetadata | Time consumed loading TimeSeriesMetadata from closed sequential files |
loadTimeSeriesMetadataDiskUnSeqTime | TimeSeriesMetadata | Time consumed loading TimeSeriesMetadata from closed out-of-order files |
loadTimeSeriesMetadataFromCacheCount | TimeSeriesMetadata | Number of TimeSeriesMetadataCache hits |
loadTimeSeriesMetadataFromDiskCount | TimeSeriesMetadata | Number of times TimeSeriesMetadata was read from disk |
loadTimeSeriesMetadataActualIOSize | TimeSeriesMetadata | Disk IO generated when reading TimeSeriesMetadata from disk, in bytes |
TimeSeriesMetadataModificationTime | Mods file | Time consumed reading mods files |
constructAlignedChunkReadersDiskCount | Chunk | Number of times ChunkReader was constructed |
constructAlignedChunkReadersDiskTime | Chunk | Total time consumed constructing ChunkReader, including disk IO and decompression |
pageReadersDecodeAlignedDiskCount | Chunk | Number of pages decoded |
pageReadersDecodeAlignedDiskTime | Chunk | Total time consumed decoding pages |
loadChunkFromCacheCount | Chunk | Number of ChunkCache hits |
loadChunkFromDiskCount | Chunk | Number of times Chunk was read from disk |
loadChunkActualIOSize | Chunk | Disk IO generated when reading Chunk from disk, in bytes |
- Operator Tree Statistics
Operator tree statistics include:
| Field | Type/Occurrence | Description |
|---|---|---|
CPU Time | Operator tree statistics | CPU execution time of the current operator |
output | Operator tree statistics | Number of output rows of the current operator |
HasNext() Called Count | Operator tree statistics | Number of times the current operator's HasNext() interface was called |
Next() Called Count | Operator tree statistics | Number of times the current operator's Next() interface was called |
Estimated Memory Size | Operator tree statistics | Estimated memory usage of the current operator |
| Node-specific statistics | Operator tree statistics | Custom statistics fields output by different operators, such as DeviceNumber, CurrentDeviceIndex, OutputPlanNodeId, size_in_bytes, etc. |
Common node-specific statistics fields include:
| Field | Type/Occurrence | Description |
|---|---|---|
DeviceNumber | TableScan related nodes | Number of devices involved in the current table scan |
CurrentDeviceIndex | TableScan related nodes | Index of the device currently being scanned |
OutputPlanNodeId | sink node | The downstream data-receiving node corresponding to the sink node |
size_in_bytes | exchange node | Number of bytes of TsBlock received by the exchange node, only counting data size |
TimeSeriesIndexFilteredRows | table scan node when filter is pushed down to table scan | Number of data rows filtered out by series metadata |
ChunkIndexFilteredRows | table scan node when filter is pushed down to table scan | Number of data rows filtered out by chunk metadata |
PageIndexFilteredRows | table scan node when filter is pushed down to table scan | Number of data rows filtered out by page header internal statistics |
RowScanFilteredRows | table scan node when filter is pushed down to table scan and VERBOSE is used | Number of rows filtered out during row-by-row data checking |
The actual output fields vary with the query type, the operators involved, whether VERBOSE is used, and whether filter pushdown is triggered. When the corresponding logic is not triggered, the relevant fields may not appear in the result.
- Text Output Example
Taking table1 in the sample data as an example, the following two statements are equivalent:
EXPLAIN ANALYZE SELECT * FROM table1;
EXPLAIN ANALYZE (FORMAT TEXT) SELECT * FROM table1;+-------------------------------------------------------------------------------------------------------------------------+
| Explain Analyze|
+-------------------------------------------------------------------------------------------------------------------------+
|Analyze Cost: 3.862 ms |
|Fetch Partition Cost: 0.785 ms |
|Fetch Schema Cost: 5.473 ms |
|Logical Plan Cost: 37.350 ms |
|Logical Optimization Cost: 4.340 ms |
|Distribution Plan Cost: 1.287 ms |
|Dispatch Cost: 8.711 ms |
|Fragment Instances Count: 3 |
| |
|FRAGMENT-INSTANCE[Id: 20260722_064147_00096_1.2.0][IP: 127.0.0.1:10730][DataRegion: virtual_data_region][State: FINISHED]|
| Total Wall Time: 64 ms |
| Cost of initDataQuerySource: 0.000 ms |
| Seq File(unclosed): 0, Seq File(closed): 0 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 0 |
| ready queued time: 0.428 ms, blocked queued time: 11.410 ms |
| Query Statistics: |
| timeSeriesIndexFilteredRows: 0 |
| chunkIndexFilteredRows: 0 |
| pageIndexFilteredRows: 0 |
| [PlanNodeId 69]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 28.691 ms |
| output: 18 rows |
| HasNext() Called Count: 9 |
| Next() Called Count: 8 |
| Estimated Memory Size: 131072 |
| DownStreamPlanNodeId: 66 |
| [PlanNodeId 55]: CollectNode(CollectOperator) |
| CPU Time: 28.656 ms |
| output: 18 rows |
| HasNext() Called Count: 9 |
| Next() Called Count: 8 |
| Estimated Memory Size: 131072 |
| [PlanNodeId 64]: ExchangeNode(ExchangeOperator) |
| CPU Time: 13.310 ms |
| output: 12 rows |
| HasNext() Called Count: 5 |
| Next() Called Count: 4 |
| Estimated Memory Size: 131072 |
| size_in_bytes: 3264 |
| [PlanNodeId 65]: ExchangeNode(ExchangeOperator) |
| CPU Time: 15.286 ms |
| output: 6 rows |
| HasNext() Called Count: 3 |
| Next() Called Count: 2 |
| Estimated Memory Size: 131072 |
| size_in_bytes: 1632 |
| |
|FRAGMENT-INSTANCE[Id: 20260722_064147_00096_1.3.0][IP: 127.0.0.1:10730][DataRegion: 1][State: FINISHED] |
| Total Wall Time: 32 ms |
| Cost of initDataQuerySource: 7.652 ms |
| Seq File(unclosed): 0, Seq File(closed): 0 |
| UnSeq File(unclosed): 2, UnSeq File(closed): 0 |
| ready queued time: 0.089 ms, blocked queued time: 0.000 ms |
| Query Statistics: |
| timeSeriesIndexFilteredRows: 0 |
| chunkIndexFilteredRows: 0 |
| pageIndexFilteredRows: 0 |
| [PlanNodeId 67]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 2.007 ms |
| output: 12 rows |
| HasNext() Called Count: 9 |
| Next() Called Count: 8 |
| Estimated Memory Size: 327680 |
| DownStreamPlanNodeId: 64 |
| [PlanNodeId 54]: DeviceTableScanNode(TableScanOperator) |
| CPU Time: 1.981 ms |
| output: 12 rows |
| HasNext() Called Count: 9 |
| Next() Called Count: 8 |
| Estimated Memory Size: 327680 |
| DeviceNumber: 4 |
| CurrentDeviceIndex: 3 |
| |
|FRAGMENT-INSTANCE[Id: 20260722_064147_00096_1.4.0][IP: 127.0.0.1:10730][DataRegion: 2][State: FINISHED] |
| Total Wall Time: 48 ms |
| Cost of initDataQuerySource: 0.933 ms |
| Seq File(unclosed): 0, Seq File(closed): 0 |
| UnSeq File(unclosed): 1, UnSeq File(closed): 0 |
| ready queued time: 0.141 ms, blocked queued time: 0.000 ms |
| Query Statistics: |
| timeSeriesIndexFilteredRows: 0 |
| chunkIndexFilteredRows: 0 |
| pageIndexFilteredRows: 0 |
| [PlanNodeId 68]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 1.846 ms |
| output: 6 rows |
| HasNext() Called Count: 5 |
| Next() Called Count: 4 |
| Estimated Memory Size: 327680 |
| DownStreamPlanNodeId: 65 |
| [PlanNodeId 53]: DeviceTableScanNode(TableScanOperator) |
| CPU Time: 1.755 ms |
| output: 6 rows |
| HasNext() Called Count: 5 |
| Next() Called Count: 4 |
| Estimated Memory Size: 327680 |
| DeviceNumber: 2 |
| CurrentDeviceIndex: 1 |
+-------------------------------------------------------------------------------------------------------------------------+- VERBOSE Output Example
Using VERBOSE you can view finer-grained data reading and filtering statistics:
EXPLAIN ANALYZE VERBOSE SELECT * FROM table1;
EXPLAIN ANALYZE VERBOSE (FORMAT TEXT) SELECT * FROM table1;+-------------------------------------------------------------------------------------------------------------------------+
| Explain Analyze|
+-------------------------------------------------------------------------------------------------------------------------+
|Analyze Cost: 1.016 ms |
|Fetch Partition Cost: 0.555 ms |
|Fetch Schema Cost: 3.789 ms |
|Logical Plan Cost: 0.491 ms |
|Logical Optimization Cost: 2.625 ms |
|Distribution Plan Cost: 0.393 ms |
|Dispatch Cost: 37.041 ms |
|Fragment Instances Count: 3 |
| |
|FRAGMENT-INSTANCE[Id: 20260722_064913_00105_1.2.0][IP: 127.0.0.1:10730][DataRegion: virtual_data_region][State: FINISHED]|
| Total Wall Time: 43 ms |
| Cost of initDataQuerySource: 0.000 ms |
| Seq File(unclosed): 0, Seq File(closed): 0 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 0 |
| ready queued time: 0.248 ms, blocked queued time: 23.757 ms |
| Query Statistics: |
| loadBloomFilterFromCacheCount: 0 |
| loadBloomFilterFromDiskCount: 0 |
| loadBloomFilterActualIOSize: 0 |
| loadBloomFilterTime: 0.000 |
| loadTimeSeriesMetadataFromCacheCount: 0 |
| loadTimeSeriesMetadataFromDiskCount: 0 |
| loadTimeSeriesMetadataActualIOSize: 0 |
| loadChunkFromCacheCount: 0 |
| loadChunkFromDiskCount: 0 |
| loadChunkActualIOSize: 0 |
| timeSeriesIndexFilteredRows: 0 |
| chunkIndexFilteredRows: 0 |
| pageIndexFilteredRows: 0 |
| rowScanFilteredRows: 0 |
| [PlanNodeId 69]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 0.988 ms |
| output: 18 rows |
| HasNext() Called Count: 9 |
| Next() Called Count: 8 |
| Estimated Memory Size: 131072 |
| DownStreamPlanNodeId: 66 |
| [PlanNodeId 55]: CollectNode(CollectOperator) |
| CPU Time: 0.954 ms |
| output: 18 rows |
| HasNext() Called Count: 9 |
| Next() Called Count: 8 |
| Estimated Memory Size: 131072 |
| [PlanNodeId 64]: ExchangeNode(ExchangeOperator) |
| CPU Time: 0.569 ms |
| output: 12 rows |
| HasNext() Called Count: 5 |
| Next() Called Count: 4 |
| Estimated Memory Size: 131072 |
| size_in_bytes: 3264 |
| [PlanNodeId 65]: ExchangeNode(ExchangeOperator) |
| CPU Time: 0.354 ms |
| output: 6 rows |
| HasNext() Called Count: 3 |
| Next() Called Count: 2 |
| Estimated Memory Size: 131072 |
| size_in_bytes: 1632 |
| |
|FRAGMENT-INSTANCE[Id: 20260722_064913_00105_1.3.0][IP: 127.0.0.1:10730][DataRegion: 1][State: FINISHED] |
| Total Wall Time: 32 ms |
| Cost of initDataQuerySource: 0.224 ms |
| Seq File(unclosed): 0, Seq File(closed): 2 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 0 |
| ready queued time: 0.025 ms, blocked queued time: 0.000 ms |
| Query Statistics: |
| loadBloomFilterFromCacheCount: 2 |
| loadBloomFilterFromDiskCount: 0 |
| loadBloomFilterActualIOSize: 0 |
| loadBloomFilterTime: 0.018 |
| loadTimeSeriesMetadataAlignedDiskSeqCount: 4 |
| loadTimeSeriesMetadataAlignedDiskSeqTime: 0.189 |
| loadTimeSeriesMetadataFromCacheCount: 18 |
| loadTimeSeriesMetadataFromDiskCount: 2 |
| loadTimeSeriesMetadataActualIOSize: 0 |
| alignedTimeSeriesMetadataModificationTime: 0.026 |
| constructAlignedChunkReadersDiskCount: 4 |
| constructAlignedChunkReadersDiskTime: 3.251 |
| loadChunkFromCacheCount: 18 |
| loadChunkFromDiskCount: 0 |
| loadChunkActualIOSize: 0 |
| pageReadersDecodeAlignedDiskCount: 4 |
| pageReadersDecodeAlignedDiskTime: 0.210 |
| timeSeriesIndexFilteredRows: 0 |
| chunkIndexFilteredRows: 0 |
| pageIndexFilteredRows: 0 |
| rowScanFilteredRows: 0 |
| [PlanNodeId 67]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 5.150 ms |
| output: 12 rows |
| HasNext() Called Count: 9 |
| Next() Called Count: 8 |
| Estimated Memory Size: 327680 |
| DownStreamPlanNodeId: 64 |
| [PlanNodeId 54]: DeviceTableScanNode(TableScanOperator) |
| CPU Time: 5.136 ms |
| output: 12 rows |
| HasNext() Called Count: 9 |
| Next() Called Count: 8 |
| Estimated Memory Size: 327680 |
| DeviceNumber: 4 |
| CurrentDeviceIndex: 3 |
| |
|FRAGMENT-INSTANCE[Id: 20260722_064913_00105_1.4.0][IP: 127.0.0.1:10730][DataRegion: 2][State: FINISHED] |
| Total Wall Time: 32 ms |
| Cost of initDataQuerySource: 0.397 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 0 |
| ready queued time: 0.075 ms, blocked queued time: 0.000 ms |
| Query Statistics: |
| loadBloomFilterFromCacheCount: 0 |
| loadBloomFilterFromDiskCount: 0 |
| loadBloomFilterActualIOSize: 0 |
| loadBloomFilterTime: 0.000 |
| loadTimeSeriesMetadataAlignedDiskSeqCount: 2 |
| loadTimeSeriesMetadataAlignedDiskSeqTime: 0.313 |
| loadTimeSeriesMetadataFromCacheCount: 10 |
| loadTimeSeriesMetadataFromDiskCount: 0 |
| loadTimeSeriesMetadataActualIOSize: 0 |
| alignedTimeSeriesMetadataModificationTime: 0.046 |
| constructAlignedChunkReadersDiskCount: 2 |
| constructAlignedChunkReadersDiskTime: 2.307 |
| loadChunkFromCacheCount: 10 |
| loadChunkFromDiskCount: 0 |
| loadChunkActualIOSize: 0 |
| pageReadersDecodeAlignedDiskCount: 2 |
| pageReadersDecodeAlignedDiskTime: 0.129 |
| timeSeriesIndexFilteredRows: 0 |
| chunkIndexFilteredRows: 0 |
| pageIndexFilteredRows: 0 |
| rowScanFilteredRows: 0 |
| [PlanNodeId 68]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 4.187 ms |
| output: 6 rows |
| HasNext() Called Count: 5 |
| Next() Called Count: 4 |
| Estimated Memory Size: 327680 |
| DownStreamPlanNodeId: 65 |
| [PlanNodeId 53]: DeviceTableScanNode(TableScanOperator) |
| CPU Time: 4.172 ms |
| output: 6 rows |
| HasNext() Called Count: 5 |
| Next() Called Count: 4 |
| Estimated Memory Size: 327680 |
| DeviceNumber: 2 |
| CurrentDeviceIndex: 1 |
+-------------------------------------------------------------------------------------------------------------------------+3.3 JSON Format
Using EXPLAIN ANALYZE (FORMAT JSON) returns structured information such as plan phase time consumption, fragment instance statistics, and operator statistics. The JSON format is supported since V2.0.11.1.
3.3.1 Default JSON Output
EXPLAIN ANALYZE (FORMAT JSON) SELECT * FROM t1;The top-level JSON structure is as follows:
| Field | Type/Occurrence | Description |
|---|---|---|
planStatistics | object | Time consumption of query analysis, partition fetching, schema fetching, logical planning, logical optimization, distributed planning, dispatch, and other phases |
fragmentInstancesCount | number | Number of fragment instances for which statistics are returned |
fragmentInstances | array | Execution statistics of each fragment instance |
The main fields of planStatistics are as follows:
| Field | Type/Occurrence | Description |
|---|---|---|
analyzeCostMs | planStatistics | SQL analysis time, in ms |
fetchPartitionCostMs | planStatistics | Time to fetch partition information, in ms |
fetchSchemaCostMs | planStatistics | Time to fetch schema information, in ms |
logicalPlanCostMs | planStatistics | Logical plan generation time, in ms |
logicalOptimizationCostMs | planStatistics | Logical optimization time, in ms |
distributionPlanCostMs | planStatistics | Distributed plan generation time, in ms |
dispatchCostMs | planStatistics | Fragment dispatch time, in ms |
The main fields of fragmentInstances[] are as follows:
| Field | Type/Occurrence | Description |
|---|---|---|
id | fragmentInstances[] | fragment instance id |
ip | fragmentInstances[] | IP of the node that executed this fragment instance |
dataRegion | fragmentInstances[] | Corresponding DataRegion |
state | fragmentInstances[] | Final state of the fragment instance |
totalWallTimeMs | fragmentInstances[] | Total wall time, in ms |
initDataQuerySourceCostMs | fragmentInstances[] | Data source initialization time, in ms |
initDataQuerySourceRetryCount | fragmentInstances[] | Number of data source initialization retries. Only output when greater than 0 |
seqFileUnclosed, seqFileClosed, unseqFileUnclosed, unseqFileClosed | fragmentInstances[] | Number of sequential and out-of-order files involved |
readyQueuedTimeMs, blockQueuedTimeMs | fragmentInstances[] | Ready / block queue waiting time, in ms |
queryStatistics | fragmentInstances[] | Query statistics such as scanning, filtering, chunk/page reader |
operators | fragmentInstances[] | Operator tree and execution statistics of each operator |
operators is a tree structure corresponding to the plan tree. The main fields are as follows:
| Field | Type/Occurrence | Description |
|---|---|---|
planNodeId | operators | Corresponding plan node id |
nodeType | operators | plan node type |
operatorType | operators | Actual executed operator type |
count | operators | Operator statistics aggregation count. Only output when present |
cpuTimeMs | operators | Operator CPU execution time, in ms |
outputRows | operators | Number of output rows |
hasNextCalledCount, nextCalledCount | operators | Number of operator iteration calls |
estimatedMemorySize | operators | Estimated memory usage. Only output when non-zero |
specifiedInfo | operators | Operator-specific statistics. Only output when non-empty |
children | operators | Child operator array |
Taking table1 in the sample data as an example:
EXPLAIN ANALYZE (FORMAT JSON) SELECT * FROM table1;+---------------+
|Explain Analyze|
+---------------+
{
"planStatistics": {
"analyzeCostMs": 4.948,
"fetchPartitionCostMs": 2.104,
"fetchSchemaCostMs": 13.321,
"logicalPlanCostMs": 2.264,
"logicalOptimizationCostMs": 7.941,
"distributionPlanCostMs": 1.434,
"dispatchCostMs": 9.023
},
"fragmentInstancesCount": 3,
"fragmentInstances": [
{
"id": "20260722_064302_00101_1.2.0",
"ip": "127.0.0.1:10730",
"dataRegion": "virtual_data_region",
"state": "FINISHED",
"totalWallTimeMs": 97,
"initDataQuerySourceCostMs": 0.0,
"seqFileUnclosed": 0,
"seqFileClosed": 0,
"unseqFileUnclosed": 0,
"unseqFileClosed": 0,
"readyQueuedTimeMs": 0.858,
"blockQueuedTimeMs": 71.752,
"queryStatistics": {
"timeSeriesIndexFilteredRows": 0,
"chunkIndexFilteredRows": 0,
"pageIndexFilteredRows": 0
},
"operators": {
"planNodeId": "69",
"nodeType": "IdentitySinkNode",
"operatorType": "IdentitySinkOperator",
"cpuTimeMs": 6.082,
"outputRows": 18,
"hasNextCalledCount": 9,
"nextCalledCount": 8,
"estimatedMemorySize": 131072,
"specifiedInfo": {
"DownStreamPlanNodeId": "66"
},
"children": [
{
"planNodeId": "55",
"nodeType": "CollectNode",
"operatorType": "CollectOperator",
"cpuTimeMs": 6.065,
"outputRows": 18,
"hasNextCalledCount": 9,
"nextCalledCount": 8,
"estimatedMemorySize": 131072,
"children": [
{
"planNodeId": "64",
"nodeType": "ExchangeNode",
"operatorType": "ExchangeOperator",
"cpuTimeMs": 1.119,
"outputRows": 12,
"hasNextCalledCount": 5,
"nextCalledCount": 4,
"estimatedMemorySize": 131072,
"specifiedInfo": {
"size_in_bytes": "3264"
}
},
{
"planNodeId": "65",
"nodeType": "ExchangeNode",
"operatorType": "ExchangeOperator",
"cpuTimeMs": 4.909,
"outputRows": 6,
"hasNextCalledCount": 3,
"nextCalledCount": 2,
"estimatedMemorySize": 131072,
"specifiedInfo": {
"size_in_bytes": "1632"
}
}
]
}
]
}
},
{
"id": "20260722_064302_00101_1.3.0",
"ip": "127.0.0.1:10730",
"dataRegion": "1",
"state": "FINISHED",
"totalWallTimeMs": 63,
"initDataQuerySourceCostMs": 0.338,
"seqFileUnclosed": 0,
"seqFileClosed": 2,
"unseqFileUnclosed": 0,
"unseqFileClosed": 0,
"readyQueuedTimeMs": 0.273,
"blockQueuedTimeMs": 0.0,
"queryStatistics": {
"timeSeriesIndexFilteredRows": 0,
"chunkIndexFilteredRows": 0,
"pageIndexFilteredRows": 0
},
"operators": {
"planNodeId": "67",
"nodeType": "IdentitySinkNode",
"operatorType": "IdentitySinkOperator",
"cpuTimeMs": 57.338,
"outputRows": 12,
"hasNextCalledCount": 9,
"nextCalledCount": 8,
"estimatedMemorySize": 327680,
"specifiedInfo": {
"DownStreamPlanNodeId": "64"
},
"children": [
{
"planNodeId": "54",
"nodeType": "DeviceTableScanNode",
"operatorType": "TableScanOperator",
"cpuTimeMs": 57.248,
"outputRows": 12,
"hasNextCalledCount": 9,
"nextCalledCount": 8,
"estimatedMemorySize": 327680,
"specifiedInfo": {
"DeviceNumber": "4",
"CurrentDeviceIndex": "3"
}
}
]
}
},
{
"id": "20260722_064302_00101_1.4.0",
"ip": "127.0.0.1:10730",
"dataRegion": "2",
"state": "FINISHED",
"totalWallTimeMs": 79,
"initDataQuerySourceCostMs": 0.231,
"seqFileUnclosed": 0,
"seqFileClosed": 1,
"unseqFileUnclosed": 0,
"unseqFileClosed": 0,
"readyQueuedTimeMs": 0.038,
"blockQueuedTimeMs": 0.0,
"queryStatistics": {
"timeSeriesIndexFilteredRows": 0,
"chunkIndexFilteredRows": 0,
"pageIndexFilteredRows": 0
},
"operators": {
"planNodeId": "68",
"nodeType": "IdentitySinkNode",
"operatorType": "IdentitySinkOperator",
"cpuTimeMs": 66.387,
"outputRows": 6,
"hasNextCalledCount": 5,
"nextCalledCount": 4,
"estimatedMemorySize": 327680,
"specifiedInfo": {
"DownStreamPlanNodeId": "65"
},
"children": [
{
"planNodeId": "53",
"nodeType": "DeviceTableScanNode",
"operatorType": "TableScanOperator",
"cpuTimeMs": 66.362,
"outputRows": 6,
"hasNextCalledCount": 5,
"nextCalledCount": 4,
"estimatedMemorySize": 327680,
"specifiedInfo": {
"DeviceNumber": "2",
"CurrentDeviceIndex": "1"
}
}
]
}
}
]
}
+---------------+3.3.2 VERBOSE JSON Output
When using EXPLAIN ANALYZE VERBOSE (FORMAT JSON), the top-level structure is the same as the non-verbose JSON, but queryStatistics supplements finer-grained statistics beyond the basic filtered row counts, including bloom filter, time series metadata, chunk, page reader, modification, and actual IO size information.
The main differences between VERBOSE and non-VERBOSE JSON output are as follows:
| Output Mode | Top-level Structure | queryStatistics Field Granularity | Typical Fields |
|---|---|---|---|
EXPLAIN ANALYZE (FORMAT JSON) | planStatistics, fragmentInstancesCount, fragmentInstances | Default statistics fields | timeSeriesIndexFilteredRows, chunkIndexFilteredRows, pageIndexFilteredRows |
EXPLAIN ANALYZE VERBOSE (FORMAT JSON) | Same as non-VERBOSE | More detailed scanning, reading, and filtering statistics | loadBloomFilterFromCacheCount, loadTimeSeriesMetadataFromCacheCount, loadChunkFromDiskCount, pageReadersDecodeAlignedMemCount, rowScanFilteredRows |
Taking table1 in the sample data as an example:
EXPLAIN ANALYZE VERBOSE (FORMAT JSON) SELECT * FROM table1;+---------------+
|Explain Analyze|
+---------------+
{
"planStatistics": {
"analyzeCostMs": 3.505,
"fetchPartitionCostMs": 2.003,
"fetchSchemaCostMs": 9.198,
"logicalPlanCostMs": 1.481,
"logicalOptimizationCostMs": 6.684,
"distributionPlanCostMs": 0.711,
"dispatchCostMs": 8.32
},
"fragmentInstancesCount": 3,
"fragmentInstances": [
{
"id": "20260722_064502_00103_1.2.0",
"ip": "127.0.0.1:10730",
"dataRegion": "virtual_data_region",
"state": "FINISHED",
"totalWallTimeMs": 48,
"initDataQuerySourceCostMs": 0.0,
"seqFileUnclosed": 0,
"seqFileClosed": 0,
"unseqFileUnclosed": 0,
"unseqFileClosed": 0,
"readyQueuedTimeMs": 0.209,
"blockQueuedTimeMs": 17.997,
"queryStatistics": {
"loadBloomFilterFromCacheCount": 0,
"loadBloomFilterFromDiskCount": 0,
"loadBloomFilterActualIOSize": 0,
"loadBloomFilterTimeMs": 0.0,
"loadTimeSeriesMetadataFromCacheCount": 0,
"loadTimeSeriesMetadataFromDiskCount": 0,
"loadTimeSeriesMetadataActualIOSize": 0,
"loadChunkFromCacheCount": 0,
"loadChunkFromDiskCount": 0,
"loadChunkActualIOSize": 0,
"timeSeriesIndexFilteredRows": 0,
"chunkIndexFilteredRows": 0,
"pageIndexFilteredRows": 0,
"rowScanFilteredRows": 0
},
"operators": {
"planNodeId": "69",
"nodeType": "IdentitySinkNode",
"operatorType": "IdentitySinkOperator",
"cpuTimeMs": 26.368,
"outputRows": 18,
"hasNextCalledCount": 9,
"nextCalledCount": 8,
"estimatedMemorySize": 131072,
"specifiedInfo": {
"DownStreamPlanNodeId": "66"
},
"children": [
{
"planNodeId": "55",
"nodeType": "CollectNode",
"operatorType": "CollectOperator",
"cpuTimeMs": 26.32,
"outputRows": 18,
"hasNextCalledCount": 9,
"nextCalledCount": 8,
"estimatedMemorySize": 131072,
"children": [
{
"planNodeId": "64",
"nodeType": "ExchangeNode",
"operatorType": "ExchangeOperator",
"cpuTimeMs": 0.47,
"outputRows": 12,
"hasNextCalledCount": 5,
"nextCalledCount": 4,
"estimatedMemorySize": 131072,
"specifiedInfo": {
"size_in_bytes": "3264"
}
},
{
"planNodeId": "65",
"nodeType": "ExchangeNode",
"operatorType": "ExchangeOperator",
"cpuTimeMs": 25.802,
"outputRows": 6,
"hasNextCalledCount": 3,
"nextCalledCount": 2,
"estimatedMemorySize": 131072,
"specifiedInfo": {
"size_in_bytes": "1632"
}
}
]
}
]
}
},
{
"id": "20260722_064502_00103_1.3.0",
"ip": "127.0.0.1:10730",
"dataRegion": "1",
"state": "FINISHED",
"totalWallTimeMs": 21,
"initDataQuerySourceCostMs": 0.232,
"seqFileUnclosed": 0,
"seqFileClosed": 2,
"unseqFileUnclosed": 0,
"unseqFileClosed": 0,
"readyQueuedTimeMs": 0.007,
"blockQueuedTimeMs": 0.0,
"queryStatistics": {
"loadBloomFilterFromCacheCount": 2,
"loadBloomFilterFromDiskCount": 0,
"loadBloomFilterActualIOSize": 0,
"loadBloomFilterTimeMs": 0.02,
"loadTimeSeriesMetadataAlignedDiskSeqCount": 4,
"loadTimeSeriesMetadataAlignedDiskSeqTimeMs": 0.483,
"loadTimeSeriesMetadataFromCacheCount": 18,
"loadTimeSeriesMetadataFromDiskCount": 2,
"loadTimeSeriesMetadataActualIOSize": 0,
"alignedTimeSeriesMetadataModificationTimeMs": 0.061,
"constructAlignedChunkReadersDiskCount": 4,
"constructAlignedChunkReadersDiskTimeMs": 2.693,
"loadChunkFromCacheCount": 18,
"loadChunkFromDiskCount": 0,
"loadChunkActualIOSize": 0,
"pageReadersDecodeAlignedDiskCount": 4,
"pageReadersDecodeAlignedDiskTimeMs": 0.22,
"timeSeriesIndexFilteredRows": 0,
"chunkIndexFilteredRows": 0,
"pageIndexFilteredRows": 0,
"rowScanFilteredRows": 0
},
"operators": {
"planNodeId": "67",
"nodeType": "IdentitySinkNode",
"operatorType": "IdentitySinkOperator",
"cpuTimeMs": 5.002,
"outputRows": 12,
"hasNextCalledCount": 9,
"nextCalledCount": 8,
"estimatedMemorySize": 327680,
"specifiedInfo": {
"DownStreamPlanNodeId": "64"
},
"children": [
{
"planNodeId": "54",
"nodeType": "DeviceTableScanNode",
"operatorType": "TableScanOperator",
"cpuTimeMs": 4.94,
"outputRows": 12,
"hasNextCalledCount": 9,
"nextCalledCount": 8,
"estimatedMemorySize": 327680,
"specifiedInfo": {
"DeviceNumber": "4",
"CurrentDeviceIndex": "3"
}
}
]
}
},
{
"id": "20260722_064502_00103_1.4.0",
"ip": "127.0.0.1:10730",
"dataRegion": "2",
"state": "FINISHED",
"totalWallTimeMs": 32,
"initDataQuerySourceCostMs": 0.253,
"seqFileUnclosed": 0,
"seqFileClosed": 1,
"unseqFileUnclosed": 0,
"unseqFileClosed": 0,
"readyQueuedTimeMs": 0.027,
"blockQueuedTimeMs": 0.0,
"queryStatistics": {
"loadBloomFilterFromCacheCount": 0,
"loadBloomFilterFromDiskCount": 0,
"loadBloomFilterActualIOSize": 0,
"loadBloomFilterTimeMs": 0.0,
"loadTimeSeriesMetadataAlignedDiskSeqCount": 2,
"loadTimeSeriesMetadataAlignedDiskSeqTimeMs": 0.188,
"loadTimeSeriesMetadataFromCacheCount": 10,
"loadTimeSeriesMetadataFromDiskCount": 0,
"loadTimeSeriesMetadataActualIOSize": 0,
"alignedTimeSeriesMetadataModificationTimeMs": 0.037,
"constructAlignedChunkReadersDiskCount": 2,
"constructAlignedChunkReadersDiskTimeMs": 1.807,
"loadChunkFromCacheCount": 10,
"loadChunkFromDiskCount": 0,
"loadChunkActualIOSize": 0,
"pageReadersDecodeAlignedDiskCount": 2,
"pageReadersDecodeAlignedDiskTimeMs": 0.62,
"timeSeriesIndexFilteredRows": 0,
"chunkIndexFilteredRows": 0,
"pageIndexFilteredRows": 0,
"rowScanFilteredRows": 0
},
"operators": {
"planNodeId": "68",
"nodeType": "IdentitySinkNode",
"operatorType": "IdentitySinkOperator",
"cpuTimeMs": 3.652,
"outputRows": 6,
"hasNextCalledCount": 5,
"nextCalledCount": 4,
"estimatedMemorySize": 327680,
"specifiedInfo": {
"DownStreamPlanNodeId": "65"
},
"children": [
{
"planNodeId": "53",
"nodeType": "DeviceTableScanNode",
"operatorType": "TableScanOperator",
"cpuTimeMs": 3.633,
"outputRows": 6,
"hasNextCalledCount": 5,
"nextCalledCount": 4,
"estimatedMemorySize": 327680,
"specifiedInfo": {
"DeviceNumber": "2",
"CurrentDeviceIndex": "1"
}
}
]
}
}
]
}4. Scenario Examples
4.1 CTE Query Plan
When EXPLAIN (FORMAT JSON) analyzes a query containing a materialized CTE, the output uses the wrapped structure of cteQueries + mainQuery.
Taking table1 and table2 in the sample data as an example:
EXPLAIN (FORMAT JSON)
WITH cte1 AS MATERIALIZED (SELECT * FROM table2)
SELECT * FROM table1
WHERE table1.device_id IN (SELECT device_id FROM cte1);+-----------------+
|distribution plan|
+-----------------+
{
"cteQueries": [
{
"name": "cte1",
"plan": {
"name": "OutputNode-4",
"id": "4",
"properties": {
"OutputColumns": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
]
},
"children": [
{
"name": "CollectNode-33",
"id": "33",
"children": [
{
"name": "ExchangeNode-40",
"id": "40",
"children": [
{
"name": "DeviceTableScanNode-32",
"id": "32",
"properties": {
"QualifiedTableName": "database1.table2",
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"DeviceNumber": "3",
"ScanOrder": "ASC",
"PushDownOffset": "0",
"PushDownLimit": "0",
"PushDownLimitToEachDevice": "false",
"RegionId": "1"
}
}
]
},
{
"name": "ExchangeNode-41",
"id": "41",
"children": [
{
"name": "DeviceTableScanNode-31",
"id": "31",
"properties": {
"QualifiedTableName": "database1.table2",
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"DeviceNumber": "3",
"ScanOrder": "ASC",
"PushDownOffset": "0",
"PushDownLimit": "0",
"PushDownLimitToEachDevice": "false",
"RegionId": "2"
}
}
]
}
]
}
]
}
}
],
"mainQuery": {
"name": "OutputNode-12",
"id": "12",
"properties": {
"OutputColumns": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
]
},
"children": [
{
"name": "ProjectNode-35",
"id": "35",
"properties": {
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"Expressions": [
"\"time\"",
"\"region\"",
"\"plant_id\"",
"\"device_id\"",
"\"model_id\"",
"\"maintenance\"",
"\"temperature\"",
"\"humidity\"",
"\"status\"",
"\"arrival_time\""
]
},
"children": [
{
"name": "FilterNode-138",
"id": "138",
"properties": {
"Predicate": "\"expr\""
},
"children": [
{
"name": "SemiJoinNode-109",
"id": "109",
"children": [
{
"name": "ExchangeNode-218",
"id": "218",
"children": [
{
"name": "MergeSortNode-197",
"id": "197",
"properties": {
"OrderBy": "{orderBy\u003d[device_id], orderings\u003d{device_id\u003dASC NULLS LAST}}"
},
"children": [
{
"name": "ExchangeNode-216",
"id": "216",
"children": [
{
"name": "DeviceTableScanNode-196",
"id": "196",
"properties": {
"QualifiedTableName": "database1.table1",
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"DeviceNumber": "4",
"ScanOrder": "ASC",
"PushDownOffset": "0",
"PushDownLimit": "0",
"PushDownLimitToEachDevice": "true",
"RegionId": "1"
}
}
]
},
{
"name": "ExchangeNode-217",
"id": "217",
"children": [
{
"name": "DeviceTableScanNode-195",
"id": "195",
"properties": {
"QualifiedTableName": "database1.table1",
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"DeviceNumber": "2",
"ScanOrder": "ASC",
"PushDownOffset": "0",
"PushDownLimit": "0",
"PushDownLimitToEachDevice": "true",
"RegionId": "2"
}
}
]
}
]
}
]
},
{
"name": "ExchangeNode-219",
"id": "219",
"children": [
{
"name": "SortNode-137",
"id": "137",
"properties": {
"OrderBy": "{orderBy\u003d[device_id_3], orderings\u003d{device_id_3\u003dASC NULLS FIRST}}"
},
"children": [
{
"name": "ProjectNode-6",
"id": "6",
"properties": {
"OutputSymbols": [
"device_id_3"
],
"Expressions": [
"\"device_id_3\""
]
},
"children": [
{
"name": "CteScanNode-2",
"id": "2"
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
}
+-----------------+4.2 Prepared Statement and Dynamic SQL
When analyzing prepared statements and dynamic SQL via EXECUTE or EXECUTE IMMEDIATE, the specified output format continues to take effect after the statement is expanded, and will not fall back to the default GRAPHVIZ or TEXT format.
PREPARE explain_json_stmt FROM SELECT * FROM table1 WHERE device_id = ?;
EXPLAIN (FORMAT JSON) EXECUTE explain_json_stmt USING '101';
EXPLAIN ANALYZE (FORMAT JSON) EXECUTE IMMEDIATE 'SELECT * FROM table1 WHERE device_id = ?' USING '101';Where:
EXPLAIN (FORMAT JSON) EXECUTE ...returns a plan node JSON object.EXPLAIN ANALYZE (FORMAT JSON) EXECUTE IMMEDIATE ...returns a JSON object containingplanStatistics,fragmentInstancesCount, andfragmentInstances.
EXPLAIN (FORMAT JSON) EXECUTE ...output structure example:
+-----------------+
|distribution plan|
+-----------------+
{
"name": "OutputNode-5",
"id": "5",
"properties": {
"OutputColumns": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
]
},
"children": [
{
"name": "DeviceTableScanNode-46",
"id": "46",
"properties": {
"QualifiedTableName": "database1.table1",
"OutputSymbols": [
"time",
"region",
"plant_id",
"device_id",
"model_id",
"maintenance",
"temperature",
"humidity",
"status",
"arrival_time"
],
"DeviceNumber": "3",
"ScanOrder": "ASC",
"PushDownOffset": "0",
"PushDownLimit": "0",
"PushDownLimitToEachDevice": "false",
"RegionId": "1"
}
}
]
}
+-----------------+EXPLAIN ANALYZE (FORMAT JSON) EXECUTE IMMEDIATE ...output structure example:
+---------------+
|Explain Analyze|
+---------------+
{
"planStatistics": {
"analyzeCostMs": 2.904,
"fetchPartitionCostMs": 1.35,
"fetchSchemaCostMs": 5.259,
"logicalPlanCostMs": 1.123,
"logicalOptimizationCostMs": 6.157,
"distributionPlanCostMs": 0.571,
"dispatchCostMs": 5.909
},
"fragmentInstancesCount": 1,
"fragmentInstances": [
{
"id": "20260722_065558_00118_1.2.0",
"ip": "127.0.0.1:10730",
"dataRegion": "1",
"state": "FINISHED",
"totalWallTimeMs": 10,
"initDataQuerySourceCostMs": 1.768,
"seqFileUnclosed": 0,
"seqFileClosed": 2,
"unseqFileUnclosed": 0,
"unseqFileClosed": 0,
"readyQueuedTimeMs": 0.059,
"blockQueuedTimeMs": 0.0,
"queryStatistics": {
"timeSeriesIndexFilteredRows": 0,
"chunkIndexFilteredRows": 0,
"pageIndexFilteredRows": 0
},
"operators": {
"planNodeId": "74",
"nodeType": "IdentitySinkNode",
"operatorType": "IdentitySinkOperator",
"cpuTimeMs": 9.193,
"outputRows": 10,
"hasNextCalledCount": 7,
"nextCalledCount": 6,
"estimatedMemorySize": 327680,
"specifiedInfo": {
"DownStreamPlanNodeId": "73"
},
"children": [
{
"planNodeId": "68",
"nodeType": "DeviceTableScanNode",
"operatorType": "TableScanOperator",
"cpuTimeMs": 9.055,
"outputRows": 10,
"hasNextCalledCount": 7,
"nextCalledCount": 6,
"estimatedMemorySize": 327680,
"specifiedInfo": {
"DeviceNumber": "3",
"CurrentDeviceIndex": "2"
}
}
]
}
}
]
}
+---------------+4.3 JSON Display in CLI
The CLI outputs regular query results in tabular format by default. For JSON-format query analysis results, if the JSON body continues to be placed in table cells, each row would carry | borders, which is not conducive to copying, saving, and JSON parsing.
The CLI switches to raw JSON display when the following conditions are met:
| Condition | Description |
|---|---|
| The result set has only one column | Avoids affecting normal multi-column query display |
The column name is distribution plan or Explain Analyze | Only matches the result columns of EXPLAIN / EXPLAIN ANALYZE |
The first result value trimmed starts with { or [ | Only enables raw output for JSON object / array content |
Raw JSON display rules:
The CLI still outputs the column name header and outer separator line, so users can identify the current column as
distribution planorExplain Analyze.The JSON body is output line by line as-is, without adding
|table borders.After the JSON ends, a separator line and row count statistics are output.
Non-JSON
EXPLAIN, default textEXPLAIN ANALYZE, and regular query results still use the original table output.
The differences between raw JSON output and regular table output are as follows:
| Output Type | Applicable Result | Display Method | Copy and Parse |
|---|---|---|---|
| Regular table output | Regular query results, default EXPLAIN, default EXPLAIN ANALYZE | Each row's content is placed inside table borders | Suitable for human viewing, not suitable for direct JSON copy parsing |
| Raw JSON output | EXPLAIN (FORMAT JSON), EXPLAIN ANALYZE (FORMAT JSON) | Retains column name header, JSON body has no table borders | Suitable for direct copying, saving, or handing to a JSON parser |
Example:
start-cli.sh -sql_dialect table -e "EXPLAIN (FORMAT JSON) SELECT time, device_id, temperature FROM database1.table1"+-----------------+
|distribution plan|
+-----------------+
{
"name": "OutputNode-4",
"id": "4",
"properties": {
"OutputColumns": [
"time",
"device_id",
"temperature"
],
"OutputSymbols": [
"time",
"device_id",
"temperature"
]
},
"children": [
{
"name": "CollectNode-42",
"id": "42",
"children": [
{
"name": "ExchangeNode-49",
"id": "49",
"children": [
{
"name": "DeviceTableScanNode-41",
"id": "41",
"properties": {
"QualifiedTableName": "database1.table1",
"OutputSymbols": [
"time",
"device_id",
"temperature"
],
"DeviceNumber": "4",
"ScanOrder": "ASC",
"PushDownOffset": "0",
"PushDownLimit": "0",
"PushDownLimitToEachDevice": "false",
"RegionId": "1"
}
}
]
},
{
"name": "ExchangeNode-50",
"id": "50",
"children": [
{
"name": "DeviceTableScanNode-40",
"id": "40",
"properties": {
"QualifiedTableName": "database1.table1",
"OutputSymbols": [
"time",
"device_id",
"temperature"
],
"DeviceNumber": "2",
"ScanOrder": "ASC",
"PushDownOffset": "0",
"PushDownLimit": "0",
"PushDownLimitToEachDevice": "false",
"RegionId": "2"
}
}
]
}
]
}
]
}
+-----------------+4.4 Illegal Format Combinations
EXPLAIN and EXPLAIN ANALYZE support different formats. Illegal format combinations return a clear error.
| Statement | Default Format | Supported Formats | Unsupported Formats |
|---|---|---|---|
EXPLAIN | GRAPHVIZ | GRAPHVIZ, JSON | TEXT, XML, other unknown formats |
EXPLAIN ANALYZE | TEXT | TEXT, JSON | GRAPHVIZ, XML, other unknown formats |
Example:
EXPLAIN (FORMAT TEXT) SELECT * FROM table1;
EXPLAIN ANALYZE (FORMAT GRAPHVIZ) SELECT * FROM table1;
EXPLAIN (FORMAT XML) SELECT * FROM table1;Corresponding errors:
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Invalid EXPLAIN format: TEXT. Supported formats: GRAPHVIZ, JSON
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Invalid EXPLAIN ANALYZE format: GRAPHVIZ. Supported formats: TEXT, JSON
Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Invalid EXPLAIN format: XML. Supported formats: GRAPHVIZ, JSON5. FAQ
5.1 What to do when a query times out?
EXPLAIN ANALYZE actually executes the query, so it may also fail to return complete results when the query times out. To assist troubleshooting, IoTDB writes the current analysis results to a dedicated log in text form at certain time intervals; the log interval is calculated based on the query timeout, ensuring at least two result records before timeout.
When encountering a timeout, handle it in the following order:
Check the staged analysis results for the corresponding query in
logs/log_explain_analyze.log.If there are no results in the log, check whether only the lib package was replaced during the upgrade, and
conf/logback-datanode.xmlwas not replaced synchronously.After replacing the configuration, no restart is required. Wait for the configuration hot reload to complete, then execute
EXPLAIN ANALYZE VERBOSE.
5.2 What is the difference between WALL TIME and CPU TIME?
CPU time refers to the time the program actually occupies the CPU for computation during execution, representing the processor resources actually consumed by the program.
Wall time refers to the real physical time from when the program starts execution to when it ends, including resource waiting time.
WALL TIME < CPU TIME is common in parallel execution scenarios. For example, if a query fragment is executed in parallel by two threads, 10 seconds of physical time pass, but the two threads each occupy one CPU core running for 10 seconds, then the CPU TIME is about 20 seconds, and the WALL TIME is about 10 seconds.
WALL TIME > CPU TIME is common in resource waiting scenarios. For example, a query fragment enters the blocked queue due to insufficient memory or waiting for upstream data, or enters the ready queue due to insufficient query thread resources. During the wait, no CPU is occupied, but physical time still passes.
5.3 Does EXPLAIN ANALYZE have additional overhead?
The statistics collection itself has almost no significant additional overhead. The EXPLAIN ANALYZE operator collects the statistics already available from the original query, and traverses via next without printing the query results, so there is usually no significant difference in time consumption compared to directly executing the same query.
Note that the analyzed query is still fully executed and normally consumes CPU, memory, and IO resources. When evaluating the impact on the production environment, refer to the resource consumption of the original query itself.
5.4 Which metrics should I focus on for IO time consumption?
Metrics involving IO time consumption mainly include loadBloomFilterActualIOSize, loadBloomFilterTime, loadTimeSeriesMetadataAlignedDiskSeqTime, loadTimeSeriesMetadataAlignedDiskUnseqTime, loadTimeSeriesMetadataActualIOSize, alignedTimeSeriesMetadataModificationTime, constructAlignedChunkReadersDiskTime, and loadChunkActualIOSize.
TimeSeriesMetadata loading statistics are separated for sequential and out-of-order files, but Chunk reading is not yet separately counted for sequential and out-of-order proportions; this can be estimated through the sequential/out-of-order ratio of TimeSeriesMetadata.
5.5 How to observe the impact of out-of-order data on query performance?
Out-of-order data mainly produces two types of impact:
During query, an additional merge sort needs to be done in memory.
Out-of-order data produces time range overlaps between data blocks, causing some statistics to be unusable for directly skipping data blocks that do not meet conditions, or for directly calculating aggregate values.
Currently, there is no direct observation metric specifically for the impact of out-of-order data. Usually, you can execute a query once when out-of-order data exists, and then execute the query again after the out-of-order data is merged, and evaluate by comparing the time consumption before and after.