LOCAL_MERGE_SOURCE (plan_node_id=17): CommonMetrics: - OperatorTotalTime: 10s659ms - OutputChunkBytes: 1.302 GB - PullChunkNum: 863 - PullRowNum: 3.362M (3362123) - PullTotalTime: 10s658ms ah, so 10s is waiting for something

@guicheng.cai can you run the query with

explain analyze

so that we dont wait for the network?

@guicheng.cai can you try with different dop like 16,8,4,2 to see how it relates?

Hi @Kevin Cai 1、Try do it with

create table xxx as select ...

to save the selected result to a table , and the query profile as follow:

Query:
  Summary:
     - Query ID: 019ae236-e105-7a10-99fa-7e860e50d692
     - Start Time: 2025-12-03 03:17:18
     - End Time: 2025-12-03 03:17:33
     - Total: 15s192ms
     - Query Type: Query
     - Query State: Finished
     - StarRocks Version: 4.0.1-cd9df36
     - User: root
     - Default Db
     - Sql Statement: CREATE TABLE trancare_ads.ads_perf_lch_google_app_launch_stage_di_tmp_test 
PROPERTIES ("replication_num" = "1") 
AS
SELECT 
    ta.tos AS tos,
    ta.rom_vr AS rom_vr,
    ta.os_vr AS os_vr,
    ta.device AS device,
    ta.brand AS brand,
    ta.cpu AS cpu,
 ...
       
 LOCAL_MERGE_SOURCE (plan_node_id=17):
          CommonMetrics:
             - OperatorTotalTime: 11s893ms
             - OutputChunkBytes: 1.302 GB
             - PullChunkNum: 832
             - PullRowNum: 3.362M (3362123)
             - PullTotalTime: 11s892ms
             - PushChunkNum: 0
             - PushRowNum: 0
             - PushTotalTime: 0ns

2、Removing the "ORDER BY

日期

DESC, device;" at the end of SQL, The executed query profile is shown in the attachment. By the way, the performance has indeed improved a lot after the removal of "order by", but in actual business applications, "order by" may be required. How can this be optimized?

basically i get 5s for query, 3 of which is mysql protocol conversion. this is 8 core machine r6id.2xlarge

on my test instance from main branch the simplest order by uses LOCAL_MERGE_SOURCE, while on 3.3 it uses parallel there is a regression somewhere

possible regression is commit 0468393d84, will check later

nope, with commit 0468393d84 reverted still get LOCAL_MERGE_SOURCE

hmm, for iceberg it is single threaded and for native table it is PARALLEL_MERGE_SOURCE

ok, the iceberg is different issue due to union node of eq deletes testing original query https://www.paste.org/129684 with patch reverted i get and set enable_parallel_merge = false | └──SORT (id=15) [ROW_NUMBER, SORT] | | │ Estimates: [row: 243000, cpu: 81645631.48, memory: 81645631.48, network: 81645631.48, cost: 578003486.50] | | │ TotalTime: 2s106ms (35.86%) [CPUTime: 2s106ms] | | │ OutputRows: 2.400M (2400000) | | │ PeakMemory: ?, AllocatedMemory: ? | | │ OrderByExprs: [<slot 58> 58: date, <slot 3> 3: device] | | │ SubordinateOperators: | | │ GROUP_EXCHANGE | | │ LOCAL_EXCHANGE [null] | with true i get LOCAL_PARALLEL_MERGE_SOURCE | └──SORT (id=15) [ROW_NUMBER, SORT] | | │ Estimates: [row: 243000, cpu: 81645631.48, memory: 81645631.48, network: 81645631.48, cost: 578003486.50] | | │ TotalTime: 439.625ms (9.97%) [CPUTime: 439.625ms] | | │ OutputRows: 2.400M (2400000) | | │ PeakMemory: ?, AllocatedMemory: ? | | │ OrderByExprs: [<slot 58> 58: date, <slot 3> 3: device] | | │ SubordinateOperators: | | │ GROUP_EXCHANGE | | │ LOCAL_EXCHANGE [Passthrough] | with main i get | └──SORT (id=16) [ROW_NUMBER, SORT] | | │ Estimates: [row: 1, cpu: ?, memory: ?, network: ?, cost: 4337.0] | | │ TotalTime: 2s594ms (37.05%) [CPUTime: 2s594ms] | | │ OutputRows: 2.400M (2400000) | | │ PeakMemory: ?, AllocatedMemory: ? | | │ OrderByExprs: [<slot 58> 58: date, <slot 3> 3: device] | and LOCAL_MERGE_SOURCE so my conclusion is that something wrong with 0468393d84

after running analyze on all tables and executing query TWICE, switches it to parallel merge so the culprit is somethere in statistics logic

verified that iceberg without eqdeletes also need stats to turn on parallel merge

@Kevin Cai i was debugging iceberg with eq deletes and parallel sort after FE restart mysql> show variables like ‘enable_parallel_merge’; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | enable_parallel_merge | true | +-----------------------+-------+ But BE does not see it enabled I20251204 142835.304095 126045939963456 topn_node.cpp:399] TopNNode::decompose_to_pipeline plan_node_id=9 __isset.enable_parallel_merge=1 enable_parallel_merge_value=0 ordering_exprs_count=2 need_merge=1 enable_parallel_merge=0 If i set it forcefully, BE sees it mysql> set enable_parallel_merge = true; I20251204 142919.729901 126045948356160 topn_node.cpp:399] TopNNode::decompose_to_pipeline plan_node_id=9 __isset.enable_parallel_merge=1 enable_parallel_merge_value=1 ordering_exprs_count=2 need_merge=1 enable_parallel_merge=1

and there is some strange stuff with default variables not being set somehow