Like 谓词优化
And
执行 SQL
SQL
select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'初始值
- 执行
select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
+-----+
|total|
+-----+
| 1987|
+-----+
Total line number = 1
It costs 124.702s- explain
explain select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
+-------------------------------------------------------------------------------------------------------------------------+
| distribution plan|
+-------------------------------------------------------------------------------------------------------------------------+
|┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐|
|│AlignedSeriesAggregationScan-5 │|
|│Series: root.dacoo_log.message_log[logTraceId, msgContent, deviceId] │|
|│Aggregator-0: COUNT, SINGLE │|
|│Predicate: root.dacoo_log.message_log.deviceId = "10000481" & root.dacoo_log.message_log.msgContent LIKE '^.*?Z624.*?$'│|
|│ScanOrder: ASC │|
|│Partition: 3 │|
|└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘|
+-------------------------------------------------------------------------------------------------------------------------+
Total line number = 8
It costs 0.035s- explain analyze
explain analyze select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 166.320 ms |
|Fetch Partition Cost: 10.063 ms |
|Fetch Schema Cost: 134.414 ms |
|Logical Plan Cost: 3.062 ms |
|Logical Optimization Cost: 3.581 ms |
|Distribution Plan Cost: 1.950 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_034012_00000_1.2.0][IP: 0.0.0.0][DataRegion: 3][State: FINISHED]|
| Total Wall Time: 126055 ms |
| Cost of initDataQuerySource: 0.800 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 9.892 ms, blocked queued time: 0.000 ms |
| [PlanNodeId 8]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 126015.978 ms |
| output: 1 rows |
| HasNext() Called Count: 620 |
| Next() Called Count: 619 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 6]: AlignedSeriesAggregationScanNode(AlignedSeriesAggregationScanOperator) |
| CPU Time: 126013.671 ms |
| output: 1 rows |
| HasNext() Called Count: 620 |
| Next() Called Count: 619 |
| Estimated Memory Size: : 262161 |
+-----------------------------------------------------------------------------------------------+
Total line number = 26
It costs 126.329s- explain analyze verbose
explain analyze verbose select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 4.746 ms |
|Fetch Partition Cost: 3.402 ms |
|Fetch Schema Cost: 0.295 ms |
|Logical Plan Cost: 0.637 ms |
|Logical Optimization Cost: 0.162 ms |
|Distribution Plan Cost: 3.904 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_032131_00003_1.2.0][IP: 0.0.0.0][DataRegion: 3][State: FINISHED]|
| Total Wall Time: 122856 ms |
| Cost of initDataQuerySource: 0.353 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 8.157 ms, blocked queued time: 0.000 ms |
| Query Statistics: |
| loadTimeSeriesMetadataAlignedDiskSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskUnSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskSeqTime: 0.158 |
| loadTimeSeriesMetadataAlignedDiskUnSeqTime: 0.025 |
| constructAlignedChunkReadersDiskCount: 257 |
| constructAlignedChunkReadersDiskTime: 668.954 |
| pageReadersDecodeAlignedDiskCount: 19905 |
| pageReadersDecodeAlignedDiskTime: 54893.461 |
| [PlanNodeId 8]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 122832.920 ms |
| output: 1 rows |
| HasNext() Called Count: 605 |
| Next() Called Count: 604 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 6]: AlignedSeriesAggregationScanNode(AlignedSeriesAggregationScanOperator) |
| CPU Time: 122831.032 ms |
| output: 1 rows |
| HasNext() Called Count: 605 |
| Next() Called Count: 604 |
| Estimated Memory Size: : 262161 |
+-----------------------------------------------------------------------------------------------+
Total line number = 35
It costs 122.883s取消 Like 下推
- 执行
select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
+-----+
|total|
+-----+
| 1987|
+-----+
Total line number = 1
It costs 15.240s- explain
explain select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
+----------------------------------------------------------------------+
| distribution plan|
+----------------------------------------------------------------------+
| ┌───────────────────────────┐ |
| │RawDataAggregation-3 │ |
| │Aggregator-0: COUNT, SINGLE│ |
| └───────────────────────────┘ |
| │ |
| │ |
|┌────────────────────────────────────────────────────────────────────┐|
|│Filter-4 │|
|│Predicate: root.dacoo_log.message_log.msgContent LIKE '^.*?Z624.*?$'│|
|└────────────────────────────────────────────────────────────────────┘|
| │ |
| │ |
|┌────────────────────────────────────────────────────────────────────┐|
|│AlignedSeriesScan-1 │|
|│Series: root.dacoo_log.message_log[logTraceId, msgContent, deviceId]│|
|│Predicate: root.dacoo_log.message_log.deviceId = "10000481" │|
|│QueryAllSensors: false │|
|│ScanOrder: ASC │|
|│Partition: 3 │|
|└────────────────────────────────────────────────────────────────────┘|
+----------------------------------------------------------------------+
Total line number = 20
It costs 0.020s- explain analyze
explain analyze select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 5.524 ms |
|Fetch Partition Cost: 4.637 ms |
|Fetch Schema Cost: 0.132 ms |
|Logical Plan Cost: 0.526 ms |
|Logical Optimization Cost: 0.111 ms |
|Distribution Plan Cost: 1.142 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_033904_00004_1.2.0][IP: 0.0.0.0][DataRegion: 3][State: FINISHED]|
| Total Wall Time: 10730 ms |
| Cost of initDataQuerySource: 0.580 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 0.427 ms, blocked queued time: 0.000 ms |
| [PlanNodeId 8]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 10727.097 ms |
| output: 1 rows |
| HasNext() Called Count: 13 |
| Next() Called Count: 12 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 3]: RawDataAggregationNode(RawDataAggregationOperator) |
| CPU Time: 10727.008 ms |
| output: 1 rows |
| HasNext() Called Count: 13 |
| Next() Called Count: 12 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 5]: FilterNode(FilterAndProjectOperator) |
| CPU Time: 10726.224 ms |
| output: 1987 rows |
| HasNext() Called Count: 62 |
| Next() Called Count: 12 |
| Filtered Rows: 0 |
| [PlanNodeId 1]: AlignedSeriesScanNode(AlignedSeriesScanOperator) |
| CPU Time: 10706.472 ms |
| output: 2051 rows |
| HasNext() Called Count: 62 |
| Next() Called Count: 12 |
| Estimated Memory Size: : 262144 |
+-----------------------------------------------------------------------------------------------+
Total line number = 38
It costs 10.748s- explain analyze verbose
explain analyze verbose select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 6.820 ms |
|Fetch Partition Cost: 5.365 ms |
|Fetch Schema Cost: 0.364 ms |
|Logical Plan Cost: 1.218 ms |
|Logical Optimization Cost: 0.283 ms |
|Distribution Plan Cost: 7.356 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_033805_00003_1.2.0][IP: 0.0.0.0][DataRegion: 3][State: FINISHED]|
| Total Wall Time: 10793 ms |
| Cost of initDataQuerySource: 0.197 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 0.473 ms, blocked queued time: 0.000 ms |
| Query Statistics: |
| loadTimeSeriesMetadataAlignedDiskSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskUnSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskSeqTime: 0.145 |
| loadTimeSeriesMetadataAlignedDiskUnSeqTime: 0.026 |
| constructAlignedChunkReadersDiskCount: 257 |
| constructAlignedChunkReadersDiskTime: 600.644 |
| pageReadersDecodeAlignedDiskCount: 19905 |
| pageReadersDecodeAlignedDiskTime: 3874.271 |
| [PlanNodeId 8]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 10790.411 ms |
| output: 1 rows |
| HasNext() Called Count: 13 |
| Next() Called Count: 12 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 3]: RawDataAggregationNode(RawDataAggregationOperator) |
| CPU Time: 10790.320 ms |
| output: 1 rows |
| HasNext() Called Count: 13 |
| Next() Called Count: 12 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 5]: FilterNode(FilterAndProjectOperator) |
| CPU Time: 10789.538 ms |
| output: 1987 rows |
| HasNext() Called Count: 62 |
| Next() Called Count: 12 |
| Filtered Rows: 0 |
| [PlanNodeId 1]: AlignedSeriesScanNode(AlignedSeriesScanOperator) |
| CPU Time: 10769.789 ms |
| output: 2051 rows |
| HasNext() Called Count: 62 |
| Next() Called Count: 12 |
| Estimated Memory Size: : 262144 |
+-----------------------------------------------------------------------------------------------+
Total line number = 47
It costs 10.834s短路求值
- 执行
select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
+-----+
|total|
+-----+
| 1987|
+-----+
Total line number = 1
It costs 12.895s- explain
explain select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
IoTDB> +-------------------------------------------------------------------------------------------------------------------------+
| distribution plan|
+-------------------------------------------------------------------------------------------------------------------------+
|┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐|
|│AlignedSeriesAggregationScan-5 │|
|│Series: root.dacoo_log.message_log[logTraceId, msgContent, deviceId] │|
|│Aggregator-0: COUNT, SINGLE │|
|│Predicate: root.dacoo_log.message_log.deviceId = "10000481" & root.dacoo_log.message_log.msgContent LIKE '^.*?Z624.*?$'│|
|│ScanOrder: ASC │|
|│Partition: 3 │|
|└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘|
+-------------------------------------------------------------------------------------------------------------------------+
Total line number = 8
It costs 0.004s- explain analyze
explain analyze select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 2.709 ms |
|Fetch Partition Cost: 1.924 ms |
|Fetch Schema Cost: 0.111 ms |
|Logical Plan Cost: 0.445 ms |
|Logical Optimization Cost: 0.122 ms |
|Distribution Plan Cost: 2.553 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_034909_00004_1.2.0][IP: 0.0.0.0][DataRegion: 3][State: FINISHED]|
| Total Wall Time: 11891 ms |
| Cost of initDataQuerySource: 0.221 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 1.104 ms, blocked queued time: 0.000 ms |
| [PlanNodeId 8]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 11886.348 ms |
| output: 1 rows |
| HasNext() Called Count: 60 |
| Next() Called Count: 59 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 6]: AlignedSeriesAggregationScanNode(AlignedSeriesAggregationScanOperator) |
| CPU Time: 11886.020 ms |
| output: 1 rows |
| HasNext() Called Count: 60 |
| Next() Called Count: 59 |
| Estimated Memory Size: : 262161 |
+-----------------------------------------------------------------------------------------------+
Total line number = 26
It costs 11.919s- explain analyze verbose
explain analyze verbose select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' and msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 2.743 ms |
|Fetch Partition Cost: 1.924 ms |
|Fetch Schema Cost: 0.119 ms |
|Logical Plan Cost: 0.156 ms |
|Logical Optimization Cost: 0.099 ms |
|Distribution Plan Cost: 0.333 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_034941_00005_1.2.0][IP: 0.0.0.0][DataRegion: 3][State: FINISHED]|
| Total Wall Time: 12113 ms |
| Cost of initDataQuerySource: 0.256 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 1.199 ms, blocked queued time: 0.000 ms |
| Query Statistics: |
| loadTimeSeriesMetadataAlignedDiskSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskUnSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskSeqTime: 0.260 |
| loadTimeSeriesMetadataAlignedDiskUnSeqTime: 0.023 |
| constructAlignedChunkReadersDiskCount: 257 |
| constructAlignedChunkReadersDiskTime: 574.940 |
| pageReadersDecodeAlignedDiskCount: 19905 |
| pageReadersDecodeAlignedDiskTime: 4292.454 |
| [PlanNodeId 8]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 12108.741 ms |
| output: 1 rows |
| HasNext() Called Count: 61 |
| Next() Called Count: 60 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 6]: AlignedSeriesAggregationScanNode(AlignedSeriesAggregationScanOperator) |
| CPU Time: 12108.450 ms |
| output: 1 rows |
| HasNext() Called Count: 61 |
| Next() Called Count: 60 |
| Estimated Memory Size: : 262161 |
+-----------------------------------------------------------------------------------------------+
Total line number = 35
It costs 12.138sOr
SQL
select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'初始值
- 执行
select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-----+
|total|
+-----+
| 4059|
+-----+
Total line number = 1
It costs 136.991s- explain
explain select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-------------------------------------------------------------------------------------------------------------------------+
| distribution plan|
+-------------------------------------------------------------------------------------------------------------------------+
|┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐|
|│AlignedSeriesAggregationScan-5 │|
|│Series: root.dacoo_log.message_log[logTraceId, msgContent, deviceId] │|
|│Aggregator-0: COUNT, SINGLE │|
|│Predicate: root.dacoo_log.message_log.deviceId = "10000481" | root.dacoo_log.message_log.msgContent LIKE '^.*?Z624.*?$'│|
|│ScanOrder: ASC │|
|│Partition: 3 │|
|└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘|
+-------------------------------------------------------------------------------------------------------------------------+
Total line number = 8
It costs 0.004s- explain analyze
explain analyze select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 3.084 ms |
|Fetch Partition Cost: 2.241 ms |
|Fetch Schema Cost: 0.168 ms |
|Logical Plan Cost: 0.421 ms |
|Logical Optimization Cost: 0.129 ms |
|Distribution Plan Cost: 1.633 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_042721_00005_1.2.0][IP: 0.0.0.0][DataRegion: 3][State: FINISHED]|
| Total Wall Time: 134617 ms |
| Cost of initDataQuerySource: 0.067 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 12.043 ms, blocked queued time: 0.000 ms |
| [PlanNodeId 8]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 134585.852 ms |
| output: 1 rows |
| HasNext() Called Count: 659 |
| Next() Called Count: 658 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 6]: AlignedSeriesAggregationScanNode(AlignedSeriesAggregationScanOperator) |
| CPU Time: 134582.988 ms |
| output: 1 rows |
| HasNext() Called Count: 659 |
| Next() Called Count: 658 |
| Estimated Memory Size: : 262161 |
+-----------------------------------------------------------------------------------------------+
Total line number = 26
It costs 134.681s- explain analyze verbose
explain analyze verbose select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 4.796 ms |
|Fetch Partition Cost: 3.593 ms |
|Fetch Schema Cost: 0.291 ms |
|Logical Plan Cost: 0.393 ms |
|Logical Optimization Cost: 0.132 ms |
|Distribution Plan Cost: 0.381 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_043122_00006_1.2.0][IP: 0.0.0.0][DataRegion: 3][State: FINISHED]|
| Total Wall Time: 131129 ms |
| Cost of initDataQuerySource: 0.414 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 10.668 ms, blocked queued time: 0.000 ms |
| Query Statistics: |
| loadTimeSeriesMetadataAlignedDiskSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskUnSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskSeqTime: 0.227 |
| loadTimeSeriesMetadataAlignedDiskUnSeqTime: 0.108 |
| constructAlignedChunkReadersDiskCount: 257 |
| constructAlignedChunkReadersDiskTime: 697.556 |
| pageReadersDecodeAlignedDiskCount: 19905 |
| pageReadersDecodeAlignedDiskTime: 59411.785 |
| [PlanNodeId 8]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 131100.665 ms |
| output: 1 rows |
| HasNext() Called Count: 643 |
| Next() Called Count: 642 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 6]: AlignedSeriesAggregationScanNode(AlignedSeriesAggregationScanOperator) |
| CPU Time: 131097.952 ms |
| output: 1 rows |
| HasNext() Called Count: 643 |
| Next() Called Count: 642 |
| Estimated Memory Size: : 262161 |
+-----------------------------------------------------------------------------------------------+
Total line number = 35
It costs 131.146s取消 Like 下推
- 执行
select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-----+
|total|
+-----+
| 4059|
+-----+
Total line number = 1
It costs 85.942s- explain
explain select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-------------------------------------------------------------------------------------------------------------------------+
| distribution plan|
+-------------------------------------------------------------------------------------------------------------------------+
| ┌───────────────────────────┐ |
| │RawDataAggregation-3 │ |
| │Aggregator-0: COUNT, SINGLE│ |
| └───────────────────────────┘ |
| │ |
| │ |
|┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐|
|│Filter-2 │|
|│Predicate: root.dacoo_log.message_log.deviceId = "10000481" | root.dacoo_log.message_log.msgContent LIKE '^.*?Z624.*?$'│|
|└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘|
| │ |
| │ |
| ┌────────────────────────────────────────────────────────────────────┐ |
| │AlignedSeriesScan-1 │ |
| │Series: root.dacoo_log.message_log[logTraceId, msgContent, deviceId]│ |
| │QueryAllSensors: false │ |
| │ScanOrder: ASC │ |
| │Partition: 3 │ |
| └────────────────────────────────────────────────────────────────────┘ |
+-------------------------------------------------------------------------------------------------------------------------+
Total line number = 19
It costs 0.388s- explain analyze
explain analyze select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 4.627 ms |
|Fetch Partition Cost: 3.639 ms |
|Fetch Schema Cost: 0.162 ms |
|Logical Plan Cost: 2.112 ms |
|Logical Optimization Cost: 0.087 ms |
|Distribution Plan Cost: 2.593 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_050605_00003_1.2.0][IP: 0.0.0.0][DataRegion: 3][State: FINISHED]|
| Total Wall Time: 89721 ms |
| Cost of initDataQuerySource: 0.125 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 6.541 ms, blocked queued time: 0.000 ms |
| [PlanNodeId 7]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 89658.776 ms |
| output: 1 rows |
| HasNext() Called Count: 18061 |
| Next() Called Count: 18060 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 3]: RawDataAggregationNode(RawDataAggregationOperator) |
| CPU Time: 89650.039 ms |
| output: 1 rows |
| HasNext() Called Count: 18061 |
| Next() Called Count: 18060 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 2]: FilterNode(FilterAndProjectOperator) |
| CPU Time: 89621.686 ms |
| output: 4059 rows |
| HasNext() Called Count: 90302 |
| Next() Called Count: 18060 |
| Filtered Rows: 125 |
| [PlanNodeId 1]: AlignedSeriesScanNode(AlignedSeriesScanOperator) |
| CPU Time: 11935.637 ms |
| output: 29307453 rows |
| HasNext() Called Count: 90302 |
| Next() Called Count: 18060 |
| Estimated Memory Size: : 262144 |
+-----------------------------------------------------------------------------------------------+
Total line number = 38
It costs 89.751s- explain analyze verbose
explain analyze verbose select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 8.372 ms |
|Fetch Partition Cost: 7.542 ms |
|Fetch Schema Cost: 0.158 ms |
|Logical Plan Cost: 0.434 ms |
|Logical Optimization Cost: 0.100 ms |
|Distribution Plan Cost: 0.985 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_050810_00004_1.2.0][IP: 0.0.0.0][DataRegion: 3][State: FINISHED]|
| Total Wall Time: 84831 ms |
| Cost of initDataQuerySource: 0.156 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 4.706 ms, blocked queued time: 0.000 ms |
| Query Statistics: |
| loadTimeSeriesMetadataAlignedDiskSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskUnSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskSeqTime: 0.451 |
| loadTimeSeriesMetadataAlignedDiskUnSeqTime: 0.034 |
| constructAlignedChunkReadersDiskCount: 257 |
| constructAlignedChunkReadersDiskTime: 658.006 |
| pageReadersDecodeAlignedDiskCount: 19905 |
| pageReadersDecodeAlignedDiskTime: 3718.187 |
| [PlanNodeId 7]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 84803.357 ms |
| output: 1 rows |
| HasNext() Called Count: 18061 |
| Next() Called Count: 18060 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 3]: RawDataAggregationNode(RawDataAggregationOperator) |
| CPU Time: 84799.940 ms |
| output: 1 rows |
| HasNext() Called Count: 18061 |
| Next() Called Count: 18060 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 2]: FilterNode(FilterAndProjectOperator) |
| CPU Time: 84789.764 ms |
| output: 4059 rows |
| HasNext() Called Count: 90302 |
| Next() Called Count: 18060 |
| Filtered Rows: 125 |
| [PlanNodeId 1]: AlignedSeriesScanNode(AlignedSeriesScanOperator) |
| CPU Time: 10666.893 ms |
| output: 29307453 rows |
| HasNext() Called Count: 90302 |
| Next() Called Count: 18060 |
| Estimated Memory Size: : 262144 |
+-----------------------------------------------------------------------------------------------+
Total line number = 47
It costs 84.853s短路求值
- 执行
select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-----+
|total|
+-----+
| 4059|
+-----+
Total line number = 1
It costs 117.077s- explain
explain select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-------------------------------------------------------------------------------------------------------------------------+
| distribution plan|
+-------------------------------------------------------------------------------------------------------------------------+
|┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐|
|│AlignedSeriesAggregationScan-5 │|
|│Series: root.dacoo_log.message_log[logTraceId, msgContent, deviceId] │|
|│Aggregator-0: COUNT, SINGLE │|
|│Predicate: root.dacoo_log.message_log.deviceId = "10000481" | root.dacoo_log.message_log.msgContent LIKE '^.*?Z624.*?$'│|
|│ScanOrder: ASC │|
|│Partition: 1 │|
|└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘|
+-------------------------------------------------------------------------------------------------------------------------+
Total line number = 8
It costs 0.040s- explain analyze
explain analyze select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 3.939 ms |
|Fetch Partition Cost: 2.695 ms |
|Fetch Schema Cost: 0.205 ms |
|Logical Plan Cost: 0.709 ms |
|Logical Optimization Cost: 0.194 ms |
|Distribution Plan Cost: 3.921 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_085619_00003_1.2.0][IP: 0.0.0.0][DataRegion: 1][State: FINISHED]|
| Total Wall Time: 121272 ms |
| Cost of initDataQuerySource: 1.742 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 8.437 ms, blocked queued time: 0.000 ms |
| [PlanNodeId 8]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 121243.410 ms |
| output: 1 rows |
| HasNext() Called Count: 596 |
| Next() Called Count: 595 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 6]: AlignedSeriesAggregationScanNode(AlignedSeriesAggregationScanOperator) |
| CPU Time: 121239.934 ms |
| output: 1 rows |
| HasNext() Called Count: 596 |
| Next() Called Count: 595 |
| Estimated Memory Size: : 262161 |
+-----------------------------------------------------------------------------------------------+
Total line number = 26
It costs 121.304s- explain analyze verbose
explain analyze verbose select count(logTraceId) as total from root.dacoo_log.message_log where deviceId = '10000481' or msgContent like '%Z624%'
+-----------------------------------------------------------------------------------------------+
| Explain Analyze|
+-----------------------------------------------------------------------------------------------+
|Analyze Cost: 4.631 ms |
|Fetch Partition Cost: 3.577 ms |
|Fetch Schema Cost: 0.199 ms |
|Logical Plan Cost: 0.209 ms |
|Logical Optimization Cost: 0.175 ms |
|Distribution Plan Cost: 0.518 ms |
|Fragment Instances Count: 1 |
| |
|FRAGMENT-INSTANCE[Id: 20240712_085841_00004_1.2.0][IP: 0.0.0.0][DataRegion: 1][State: FINISHED]|
| Total Wall Time: 120211 ms |
| Cost of initDataQuerySource: 0.944 ms |
| Seq File(unclosed): 0, Seq File(closed): 1 |
| UnSeq File(unclosed): 0, UnSeq File(closed): 1 |
| ready queued time: 8.837 ms, blocked queued time: 0.000 ms |
| Query Statistics: |
| loadTimeSeriesMetadataAlignedDiskSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskUnSeqCount: 1 |
| loadTimeSeriesMetadataAlignedDiskSeqTime: 1.325 |
| loadTimeSeriesMetadataAlignedDiskUnSeqTime: 0.043 |
| constructAlignedChunkReadersDiskCount: 257 |
| constructAlignedChunkReadersDiskTime: 657.392 |
| pageReadersDecodeAlignedDiskCount: 19905 |
| pageReadersDecodeAlignedDiskTime: 53437.387 |
| [PlanNodeId 8]: IdentitySinkNode(IdentitySinkOperator) |
| CPU Time: 120186.922 ms |
| output: 1 rows |
| HasNext() Called Count: 592 |
| Next() Called Count: 591 |
| Estimated Memory Size: : 262161 |
| [PlanNodeId 6]: AlignedSeriesAggregationScanNode(AlignedSeriesAggregationScanOperator) |
| CPU Time: 120183.838 ms |
| output: 1 rows |
| HasNext() Called Count: 592 |
| Next() Called Count: 591 |
| Estimated Memory Size: : 262161 |
+-----------------------------------------------------------------------------------------------+
Total line number = 35
It costs 120.231s123