版本： 2.1.4
sql:
select datatime as date ,ROUND(tem_acc,1) as accTemperature ,ROUND(pre_acc,1) as accRainfall
from doris_dm_db.dm_zlsa_iot_qx_history_GOSO_DAY_tem_pre_acc_v2_ds -- doris_da_db.da_zlsa_iot_qx_history_tem_pre_acc_farm_ds
where DATE_FORMAT(datatime,'%Y') = '2024' --'2023' and latitude = ROUND('27.5',1) --'73.2312'
and longitude = ROUND('111.5',1) --'18.5432'
order by datatime;
explain plan
========== PARSED PLAN (time: 1ms) ==========
UnboundResultSink[5] ( )
+--LogicalSort[4] ( orderKeys=['datatime asc null first] )
+--LogicalProject[3] ( distinct=false, projects=[UnboundAlias('datatime) AS date, UnboundAlias('ROUND('tem_acc, 1)) AS accTemperature, UnboundAlias('ROUND('pre_acc, 1)) AS accRainfall], excepts=[] )
+--LogicalFilter[2] ( predicates=(('DATE_FORMAT('datatime, '%Y') = '2024') AND ('longitude = 'ROUND('111.5', 1))) )
+--LogicalCheckPolicy ( )
+--UnboundRelation ( id=RelationId#0, nameParts=doris_dm_db.dm_zlsa_iot_qx_history_GOSO_DAY_tem_pre_acc_v2_ds )
========== ANALYZED PLAN (time: 7ms) ==========
LogicalResultSink[24] ( outputExprs=[date#7, accTemperature#8, accRainfall#9] )
+--LogicalSort[23] ( orderKeys=[date#7 asc null first] )
+--LogicalProject[18] ( distinct=false, projects=[datatime#0 AS date#7, round(tem_acc#3, 1) AS accTemperature#8, round(pre_acc#4, 1) AS accRainfall#9], excepts=[] )
+--LogicalFilter[16] ( predicates=((date_format(datatime#0, '%Y') = '2024') AND (cast(longitude#1 as DOUBLE) = round(cast('111.5' as DOUBLE), 1))) )
+--LogicalFilter[10] ( predicates=(0 = DORIS_DELETE_SIGN#5) )
+--LogicalOlapScan ( qualified=internal.doris_dm_db.dm_zlsa_iot_qx_history_GOSO_DAY_tem_pre_acc_v2_ds, indexName=<index_not_selected>, selectedIndexId=18452288, preAgg=UNSET )
========== REWRITTEN PLAN (time: 48ms) ==========
LogicalResultSink[118] ( outputExprs=[date#7, accTemperature#8, accRainfall#9] )
+--LogicalSort[116] ( orderKeys=[date#7 asc null first] )
+--LogicalProject[113] ( distinct=false, projects=[datatime#0 AS date#7, round(tem_acc#3, 1) AS accTemperature#8, round(pre_acc#4, 1) AS accRainfall#9], excepts=[] )
+--LogicalFilter[111] ( predicates=(((date_format(datatime#0, '%Y') = '2024') AND (cast(longitude#1 as DOUBLE) = round(111.5, 1))) AND (DORIS_DELETE_SIGN#5 = 0)) )
+--LogicalOlapScan ( qualified=internal.doris_dm_db.dm_zlsa_iot_qx_history_GOSO_DAY_tem_pre_acc_v2_ds, indexName=dm_zlsa_iot_qx_history_GOSO_DAY_tem_pre_acc_v2_ds, selectedIndexId=18452288, preAgg=ON )
========== OPTIMIZED PLAN (time: 1ms) ==========
PhysicalResultSink[208] ( outputExprs=[date#7, accTemperature#8, accRainfall#9] )
+--PhysicalQuickSort[205]@3 ( orderKeys=[date#7 asc null first], phase=MERGE_SORT, stats=2,781,766.55 )
+--PhysicalDistribute[202]@5 ( stats=2,781,766.55, distributionSpec=DistributionSpecGather )
+--PhysicalQuickSort[199]@5 ( orderKeys=[date#7 asc null first], phase=LOCAL_SORT, stats=2,781,766.55 )
+--PhysicalProject[196]@2 ( stats=2,781,766.55, projects=[datatime#0 AS date#7, round(tem_acc#3, 1) AS accTemperature#8, round(pre_acc#4, 1) AS accRainfall#9] )
+--PhysicalFilter[193]@1 ( stats=2,781,766.55, predicates=(((date_format(datatime#0, '%Y') = '2024') AND (cast(longitude#1 as DOUBLE) = round(111.5, 1))) AND (DORIS_DELETE_SIGN#5 = 0)) )
+--PhysicalOlapScan[dm_zlsa_iot_qx_history_GOSO_DAY_tem_pre_acc_v2_ds]@0 ( stats=7,010,051,717 )
profile 比较大，没贴完整
Summary:
- Profile ID: 210978d9e3ed40ea-bf9c2996faed0899
- Task Type: QUERY
- Start Time: 2024-12-19 09:20:30
- End Time: 2024-12-19 09:20:34
- Total: 3s613ms
- Task State: EOF
- User: root
- Default Db:
- Sql Statement: /* ApplicationName=DBeaver 24.0.0 - SQLEditor <Script-16.sql> */ select datatime as date ,ROUND(tem_acc,1) as accTemperature ,ROUND(pre_acc,1) as accRainfall
from doris_dm_db.`dm_zlsa_iot_qx_history_GOSO_DAY_tem_pre_acc_v2_ds` -- doris_da_db.da_zlsa_iot_qx_history_tem_pre_acc_farm_ds
where DATE_FORMAT(datatime,'%Y') = '2024' --'2023' and latitude = ROUND('27.5',1) --'73.2312'
and longitude = ROUND('111.5',1) --'18.5432'
order by datatime
LIMIT 0, 200
Execution Summary:
- Parse SQL Time: 1ms
- Nereids Analysis Time: 9ms
- Nereids Rewrite Time: 55ms
- Nereids Optimize Time: 2ms
- Nereids Translate Time: 0ms
- Workload Group: normal
- Analysis Time: 9ms
- Plan Time: 67ms
- JoinReorder Time: N/A
- CreateSingleNode Time: N/A
- QueryDistributed Time: N/A
- Init Scan Node Time: N/A
- Finalize Scan Node Time: N/A
- Get Splits Time: N/A
- Get Partitions Time: N/A
- Get Partition Files Time: N/A
- Create Scan Range Time: N/A
- Schedule Time: 52ms
- Fragment Assign Time: 8ms
- Fragment Serialize Time: 9ms
- Fragment RPC Phase1 Time: 33ms
- Fragment RPC Phase2 Time: 2ms
- Fragment Compressed Size: 535.15 KB
- Fragment RPC Count: 12
- Wait and Fetch Result Time: 3s485ms
- Fetch Result Time: 3s480ms
- Write Result Time: 0ms
- Doris Version: doris-2.1.4-rc03-e93678fd1e
- Is Nereids: Yes
- Is Pipeline: Yes
- Is Cached: No
- Total Instances Num: 337
- Instances Num Per BE: 192.168.200.195:8065:56,192.168.200.196:8065:56,192.168.200.197:8065:57,192.168.200.198:8065:56,192.168.200.199:8065:56,192.168.200.200:8065:56
- Parallel Fragment Exec Instance Num: 56
- Trace ID:
- Transaction Commit Time: N/A
MergedProfile
Fragments:
Fragment 0:
Pipeline : 0(instance_num=1):
RESULT_SINK_OPERATOR (id=0):
- BlocksProduced: sum 1, avg 1, max 1, min 1
- CloseTime: avg 8.390us, max 8.390us, min 8.390us
- ExecTime: avg 368.10us, max 368.10us, min 368.10us
- InitTime: avg 31.8us, max 31.8us, min 31.8us
- InputRows: sum 0, avg 0, max 0, min 0
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 0.00 , avg 0.00 , max 0.00 , min 0.00
- OpenTime: avg 65.775us, max 65.775us, min 65.775us
- RowsProduced: sum 200, avg 200, max 200, min 200
- WaitForDependencyTime: avg 0ns, max 0ns, min 0ns
- WaitForDependency[RESULT_SINK_OPERATOR_DEPENDENCY]Time: avg 0ns, max 0ns, min 0ns
EXCHANGE_OPERATOR (id=2):
- BlocksProduced: sum 86, avg 86, max 86, min 86
- CloseTime: avg 998.240us, max 998.240us, min 998.240us
- ExecTime: avg 6.166ms, max 6.166ms, min 6.166ms
- InitTime: avg 1.297ms, max 1.297ms, min 1.297ms
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 408.06 KB, avg 408.06 KB, max 408.06 KB, min 408.06 KB
- OpenTime: avg 8.41us, max 8.41us, min 8.41us
- ProjectionTime: avg 0ns, max 0ns, min 0ns
- RowsProduced: sum 200, avg 200, max 200, min 200
- WaitForDependencyTime: avg 0ns, max 0ns, min 0ns
- WaitForData0: avg 493.950ms, max 493.950ms, min 493.950ms
- WaitForData280: avg 201.947ms, max 201.947ms, min 201.947ms
- WaitForData281: avg 289.642us, max 289.642us, min 289.642us
- WaitForData5: avg 8.737us, max 8.737us, min 8.737us
- WaitForData56: avg 2s778ms, max 2s778ms, min 2s778ms
- WaitForData59: avg 202.142us, max 202.142us, min 202.142us
Fragment 1:
Pipeline : 0(instance_num=336):
DATA_STREAM_SINK_OPERATOR (id=2,dst_id=2):
- BlocksProduced: sum 577, avg 1, max 2, min 0
- CloseTime: avg 18.219us, max 48.981us, min 7.777us
- ExecTime: avg 147.387us, max 588.46us, min 45.109us
- InitTime: avg 18.613us, max 60.738us, min 7.265us
- InputRows: sum 20.4K (20400), avg 60, max 200, min 0
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 318.75 KB, avg 971.00 B, max 3.75 KB, min 0.00
- OpenTime: avg 55.599us, max 241.879us, min 14.984us
- RowsProduced: sum 20.4K (20400), avg 60, max 200, min 0
- WaitForDependencyTime: avg 0ns, max 0ns, min 0ns
- WaitForRpcBufferQueue: avg 0ns, max 0ns, min 0ns
SORT_OPERATOR (id=1):
- BlocksProduced: sum 102, avg 0, max 1, min 0
- CloseTime: avg 0ns, max 0ns, min 0ns
- ExecTime: avg 1.761us, max 16.146us, min 379ns
- InitTime: avg 0ns, max 0ns, min 0ns
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 0.00 , avg 0.00 , max 0.00 , min 0.00
- OpenTime: avg 0ns, max 0ns, min 0ns
- ProjectionTime: avg 0ns, max 0ns, min 0ns
- RowsProduced: sum 20.4K (20400), avg 60, max 200, min 0
- WaitForDependency[SORT_OPERATOR_DEPENDENCY]Time: avg 1s931ms, max 3s475ms, min 381.446ms
Pipeline : 1(instance_num=336):
SORT_SINK_OPERATOR (id=1):
- CloseTime: avg 0ns, max 0ns, min 0ns
- ExecTime: avg 792.352us, max 3.749ms, min 8.682us
- InitTime: avg 760ns, max 5.372us, min 326ns
- InputRows: sum 253.44K (253440), avg 754, max 2.702K (2702), min 0
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 98.03 MB, avg 298.77 KB, max 1.41 MB, min 0.00
- SortBlocks: sum 3.63 MB, avg 11.05 KB, max 39.58 KB, min 0.00
- OpenTime: avg 17.528us, max 104.977us, min 3.681us
- WaitForDependency[SORT_SINK_OPERATOR_DEPENDENCY]Time: avg 0ns, max 0ns, min 0ns
LOCAL_EXCHANGE_OPERATOR (PASSTHROUGH) (id=-2):
- BlocksProduced: sum 5.632K (5632), avg 16, max 56, min 0
- CloseTime: avg 0ns, max 0ns, min 0ns
- ExecTime: avg 164.279us, max 771.656us, min 2.203us
- GetBlockFailedTime: sum 5.631K (5631), avg 16, max 56, min 0
- InitTime: avg 48ns, max 337ns, min 20ns
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 10.84 MB, avg 33.04 KB, max 360.00 KB, min 0.00
- OpenTime: avg 9.321us, max 106.657us, min 1.338us
- ProjectionTime: avg 0ns, max 0ns, min 0ns
- RowsProduced: sum 253.44K (253440), avg 754, max 2.702K (2702), min 0
- WaitForDependency[LOCAL_EXCHANGE_OPERATOR_DEPENDENCY]Time: avg 1s929ms, max 3s474ms, min 365.681ms
Pipeline : 2(instance_num=336):
LOCAL_EXCHANGE_SINK_OPERATOR (PASSTHROUGH) (id=-2):
- CloseTime: avg 0ns, max 0ns, min 0ns
- ExecTime: avg 297.629us, max 9.54ms, min 71.305us
- InitTime: avg 1.880us, max 6.16us, min 829ns
- InputRows: sum 253.44K (253440), avg 754, max 911, min 624
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 0.00 , avg 0.00 , max 0.00 , min 0.00
- OpenTime: avg 732ns, max 5.979us, min 217ns
- WaitForDependency[LOCAL_EXCHANGE_SINK_DEPENDENCY]Time: avg 0ns, max 0ns, min 0ns
OLAP_SCAN_OPERATOR (id=0. table name = dm_zlsa_iot_qx_history_GOSO_DAY_tem_pre_acc_v2_ds(dm_zlsa_iot_qx_history_GOSO_DAY_tem_pre_acc_v2_ds)):
- BlocksProduced: sum 5.632K (5632), avg 16, max 17, min 16
- CloseTime: avg 8.548ms, max 21.795ms, min 4.622ms
- ExecTime: avg 1s224ms, max 3s482ms, min 25.369ms
- InitTime: avg 91.61us, max 224.121us, min 44.715us
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 0.00 , avg 0.00 , max 0.00 , min 0.00
- OpenTime: avg 688.446ms, max 3s296ms, min 6.287ms
- ProjectionTime: avg 0ns, max 0ns, min 0ns
- RowsProduced: sum 253.44K (253440), avg 754, max 911, min 624
- RuntimeFilterInfo: sum , avg , max , min
- WaitForDependency[OLAP_SCAN_OPERATOR_DEPENDENCY]Time: avg 525.611ms, max 2s434ms, min 856.425us
VScanner:
- MemoryUsage: sum , avg , max , min
- FreeBlocks: sum 448.25 MB, avg 1.33 MB, max 2.87 MB, min 600.00 KB
