【详述】开发中遇到一个很长的sql需要union all 多段sql查询，比较耗时
【StarRocks版本】例如：2.3
union all 是并行执行的吗，如果不是的话是不是只有通过客户端同时发送多段查询sql再将结果合并？
并行的，那个SQL麻烦发一下dump_query和profile。
PLAN FRAGMENT 0
OUTPUT EXPRS:7672: target_name | 7674: sub_target_name | 7703: factory_auttc_level_name | 7698: shop_type_desc | 7696: shop_level_desc | 7694: shop_code | 7695: shop_name | 7711: ifnull | 7712: ifnull | 7713: ifnull | 7714: concat | 7715: concat | 7716: concat
PARTITION: UNPARTITIONED
“”
RESULT SINK
“”
717:Project
| <slot 7672> : 7672: target_name
| <slot 7674> : 7674: sub_target_name
| <slot 7694> : 7694: shop_code
| <slot 7695> : 7695: shop_name
| <slot 7696> : 7696: shop_level_desc
| <slot 7698> : 7698: shop_type_desc
| <slot 7703> : 7703: factory_auttc_level_name
| <slot 7711> : 7711: ifnull
| <slot 7712> : 7712: ifnull
| <slot 7713> : 7713: ifnull
| <slot 7714> : 7714: concat
| <slot 7715> : 7715: concat
| <slot 7716> : 7716: concat
| limit: 20
|
716:MERGING-EXCHANGE
limit: 20
“”
PLAN FRAGMENT 1
OUTPUT EXPRS:
" PARTITION: HASH_PARTITIONED: 7672: target_name, 7674: sub_target_name, 7703: factory_auttc_level_name, 7698: shop_type_desc, 7696: shop_level_desc, 7694: shop_code, 7695: shop_name"
“”
STREAM DATA SINK
EXCHANGE ID: 716
UNPARTITIONED
“”
715:TOP-N
" | order by: <slot 7672> 7672: target_name DESC, <slot 7674> 7674: sub_target_name DESC, <slot 7703> 7703: factory_auttc_level_name DESC, <slot 7698> 7698: shop_type_desc DESC, <slot 7696> 7696: shop_level_desc DESC, <slot 7694> 7694: shop_code DESC, <slot 7695> 7695: shop_name DESC, <slot 7711> 7711: ifnull DESC, <slot 7712> 7712: ifnull DESC, <slot 7713> 7713: ifnull DESC, <slot 7717> 7717: round DESC, <slot 7718> 7718: round DESC, <slot 7719> 7719: round DESC"
| offset: 0
| limit: 20
|
714:Project
| <slot 7672> : 7672: target_name
| <slot 7674> : 7674: sub_target_name
| <slot 7694> : 7694: shop_code
| <slot 7695> : 7695: shop_name
| <slot 7696> : 7696: shop_level_desc
| <slot 7698> : 7698: shop_type_desc
| <slot 7703> : 7703: factory_auttc_level_name
" | <slot 7711> : ifnull(7707: sum, 0.0)"
" | <slot 7712> : ifnull(7708: sum, 0)"
" | <slot 7713> : ifnull(7709: sum, 0)"
" | <slot 7714> : concat(CAST(7730: round AS VARCHAR), ‘%’)"
" | <slot 7715> : concat(CAST(7726: round AS VARCHAR), ‘%’)"
" | <slot 7716> : concat(CAST(7732: round AS VARCHAR), ‘%’)"
| <slot 7717> : 7730: round
| <slot 7718> : 7726: round
| <slot 7719> : 7732: round
| common expressions:
| <slot 7728> : 7727: divide * 100.0
" | <slot 7729> : ifnull(7728: multiply, 0.0)"
" | <slot 7730> : round(7729: ifnull, 2)"
| <slot 7731> : 7730: round - 7726: round
" | <slot 7732> : round(7731: subtract, 2)"
" | <slot 7724> : ifnull(7710: max, 0.0)"
| <slot 7725> : CAST(7709: sum AS DOUBLE)
" | <slot 7726> : round(7724: ifnull, 2)"
| <slot 7727> : 7725: cast / 7707: sum
|
713:AGGREGATE (merge finalize)
" | output: sum(7707: sum), sum(7708: sum), sum(7709: sum), max(7710: max)"
" | group by: 7672: target_name, 7674: sub_target_name, 7703: factory_auttc_level_name, 7698: shop_type_desc, 7696: shop_level_desc, 7694: shop_code, 7695: shop_name"
|
712:EXCHANGE
“”
PLAN FRAGMENT 2
OUTPUT EXPRS:
" PARTITION: HASH_PARTITIONED: 7673: sub_target_id, 7675: staff_or_shop_code"
“”
STREAM DATA SINK
EXCHANGE ID: 712
" HASH_PARTITIONED: 7672: target_name, 7674: sub_target_name, 7703: factory_auttc_level_name, 7698: shop_type_desc, 7696: shop_level_desc, 7694: shop_code, 7695: shop_name"
“”
711:AGGREGATE (update serialize)
| STREAMING
" | output: sum(7706: avg(7676: decompose_target_qty)), sum(7704: sum), sum(7705: sum), max(7681: if)"
" | group by: 7672: target_name, 7674: sub_target_name, 7703: factory_auttc_level_name, 7698: shop_type_desc, 7696: shop_level_desc, 7694: shop_code, 7695: shop_name"
|
710:Project
| <slot 7672> : 7672: target_name
| <slot 7674> : 7674: sub_target_name
| <slot 7681> : 7681: if
| <slot 7694> : 7694: shop_code
| <slot 7695> : 7695: shop_name
| <slot 7696> : 7696: shop_level_desc
| <slot 7698> : 7698: shop_type_desc
| <slot 7703> : 7703: factory_auttc_level_name
| <slot 7704> : 7704: sum
| <slot 7705> : 7705: sum
| <slot 7706> : 7706: avg(7676: decompose_target_qty)
|
709:ANALYTIC
" | functions: [, avg(7676: decompose_target_qty), ]"
" | partition by: 7673: sub_target_id, 7675: staff_or_shop_code"
|
708:SORT
" | order by: <slot 7673> 7673: sub_target_id ASC, <slot 7675> 7675: staff_or_shop_code ASC"
| offset: 0
|
707:EXCHANGE
“”
PLAN FRAGMENT 3
OUTPUT EXPRS:
PARTITION: RANDOM
“”
STREAM DATA SINK
EXCHANGE ID: 707
" HASH_PARTITIONED: 7673: sub_target_id, 7675: staff_or_shop_code"
“”
0:UNION
|
|----361:EXCHANGE
|
|----531:EXCHANGE
|
|----706:EXCHANGE
|
178:EXCHANGE
“”
PLAN FRAGMENT 4
OUTPUT EXPRS:
" PARTITION: HASH_PARTITIONED: 6192: sub_target_id, 6200: staff_or_shop_code"
“”
STREAM DATA SINK
EXCHANGE ID: 706
RANDOM
“”
705:Project
| <slot 6175> : 6175: if
| <slot 6182> : 6182: target_name
| <slot 6192> : 6192: sub_target_id
| <slot 6193> : 6193: sub_target_name
| <slot 6200> : 6200: staff_or_shop_code
| <slot 6202> : 6202: decompose_target_qty
| <slot 7340> : 7340: sum
| <slot 7341> : 7341: sum
| <slot 7456> : 7456: shop_code
| <slot 7457> : 7457: shop_name
| <slot 7459> : 7459: shop_type_desc
| <slot 7465> : 7465: shop_level_desc
| <slot 7607> : 7607: factory_auttc_level_name
|
704:HASH JOIN
| join op: LEFT SEMI JOIN (BROADCAST)
| hash predicates:
" | colocate: false, reason: "
| equal join conjunct: 7369: sales_org_code = 7667: perm_value
|
|----703:EXCHANGE
|
700:Project
| <slot 6175> : 6175: if
| <slot 6182> : 6182: target_name
| <slot 6192> : 6192: sub_target_id
| <slot 6193> : 6193: sub_target_name
| <slot 6200> : 6200: staff_or_shop_code
| <slot 6202> : 6202: decompose_target_qty
| <slot 7340> : 7340: sum
| <slot 7341> : 7341: sum
| <slot 7369> : 7369: sales_org_code
| <slot 7456> : 7456: shop_code
| <slot 7457> : 7457: shop_name
| <slot 7459> : 7459: shop_type_desc
| <slot 7465> : 7465: shop_level_desc
| <slot 7607> : 7607: factory_auttc_level_name
|
699:HASH JOIN
| join op: LEFT OUTER JOIN (BROADCAST)
| hash predicates:
" | colocate: false, reason: "
| equal join conjunct: 7493: biz_cust_code = 7579: biz_cust_code
|
|----698:EXCHANGE
|
695:Project
| <slot 6175> : 6175: if
| <slot 6182> : 6182: target_name
| <slot 6192> : 6192: sub_target_id
| <slot 6193> : 6193: sub_target_name
| <slot 6200> : 6200: staff_or_shop_code
| <slot 6202> : 6202: decompose_target_qty
| <slot 7340> : 7340: sum
| <slot 7341> : 7341: sum
| <slot 7369> : 7369: sales_org_code
| <slot 7456> : 7456: shop_code
| <slot 7457> : 7457: shop_name
| <slot 7459> : 7459: shop_type_desc
| <slot 7465> : 7465: shop_level_desc
| <slot 7493> : 7493: biz_cust_code
|
694:HASH JOIN
| join op: LEFT OUTER JOIN (BROADCAST)
| hash predicates:
" | colocate: false, reason: "
| equal join conjunct: 7324: depot_code = 7456: shop_code
|
|----693:EXCHANGE
|
690:Project
| <slot 6175> : 6175: if
| <slot 6182> : 6182: target_name
| <slot 6192> : 6192: sub_target_id
| <slot 6193> : 6193: sub_target_name
| <slot 6200> : 6200: staff_or_shop_code
| <slot 6202> : 6202: decompose_target_qty
| <slot 7324> : 7324: depot_code
| <slot 7340> : 7340: sum
| <slot 7341> : 7341: sum
| <slot 7369> : 7369: sales_org_code
|
689:HASH JOIN
| join op: INNER JOIN (BROADCAST)
| hash predicates:
" | colocate: false, reason: "
| equal join conjunct: 6200: staff_or_shop_code = 7347: staff_code
|
|----688:EXCHANGE
|
685:Project
| <slot 6175> : 6175: if
| <slot 6182> : 6182: target_name
| <slot 6192> : 6192: sub_target_id
| <slot 6193> : 6193: sub_target_name
| <slot 6200> : 6200: staff_or_shop_code
| <slot 6202> : 6202: decompose_target_qty
| <slot 7324> : 7324: depot_code
| <slot 7340> : 7340: sum
| <slot 7341> : 7341: sum
|
684:HASH JOIN
| join op: LEFT OUTER JOIN (PARTITIONED)
| hash predicates:
" | colocate: false, reason: "
| equal join conjunct: 6192: sub_target_id = 6556: sub_target_id
| equal join conjunct: 6200: staff_or_shop_code = 6564: staff_or_shop_code
|
|----683:EXCHANGE
|
578:EXCHANGE
“”
PLAN FRAGMENT 5
OUTPUT EXPRS:
PARTITION: RANDOM
“”
STREAM DATA SINK
EXCHANGE ID: 703
UNPARTITIONED
“”
702:Project
| <slot 7667> : 7667: perm_value
|
701:OlapScanNode
TABLE: sys_user_data_perm
PREAGGREGATION: OFF. Reason: Has can not pre-aggregation Join
" PREDICATES: 7667: perm_value IS NOT NULL, 7665: user_id = ‘80325388’, 7666: perm_key = ‘SALES_ORG’, 7669: enabled = 1"
partitions=1/1
rollup: sys_user_data_perm
tabletRatio=10/10
" tabletList=662751,662755,662759,662763,662767,662771,662775,662779,662783,662787"
cardinality=117515
avgRowSize=4.0
numNodes=0
“”
PLAN FRAGMENT 6
OUTPUT EXPRS:
PARTITION: RANDOM
“”
STREAM DATA SINK
EXCHANGE ID: 698
UNPARTITIONED
“”
697:Project
| <slot 7579> : 7579: biz_cust_code
| <slot 7607> : 7607: factory_auttc_level_name
|
696:OlapScanNode
TABLE: dim_crm_business_customer
PREAGGREGATION: ON
PREDICATES: 7636: enabled = 1
partitions=1/17
rollup: dim_crm_business_customer
tabletRatio=8/8
" tabletList=18682522,18682526,18682530,18682534,18682538,18682542,18682546,18682550"
cardinality=18052
avgRowSize=18.835596
numNodes=0
“”
PLAN FRAGMENT 7
OUTPUT EXPRS:
PARTITION: RANDOM
“”
STREAM DATA SINK
EXCHANGE ID: 693
UNPARTITIONED
“”
692:Project
| <slot 7456> : 7456: shop_code
| <slot 7457> : 7457: shop_name
| <slot 7459> : 7459: shop_type_desc
| <slot 7465> : 7465: shop_level_desc
| <slot 7493> : 7493: biz_cust_code
|
691:OlapScanNode
TABLE: dim_crm_shop
PREAGGREGATION: OFF. Reason: Has can not pre-aggregation Join
PREDICATES: 7563: enabled = 1
partitions=1/1
rollup: dim_crm_shop
tabletRatio=10/10
" tabletList=18682588,18682592,18682596,18682600,18682604,18682608,18682612,18682616,18682620,18682624"
cardinality=14835
avgRowSize=6.0
numNodes=0
“”
PLAN FRAGMENT 8
OUTPUT EXPRS:
PARTITION: RANDOM
“”
STREAM DATA SINK
EXCHANGE ID: 688
UNPARTITIONED
“”
687:Project
| <slot 7347> : 7347: staff_code
| <slot 7369> : 7369: sales_org_code
|
686:OlapScanNode
TABLE: dim_crm_staff_info
PREAGGREGATION: OFF. Reason: Has can not pre-aggregation Join
" PREDICATES: 7430: enabled = 1, 7369: sales_org_code IS NOT NULL"
partitions=1/5
rollup: dim_crm_staff_info
tabletRatio=16/16
" tabletList=18682891,18682895,18682899,18682903,18682907,18682911,18682915,18682919,18682923,18682927 …"
cardinality=5541
avgRowSize=3.0
numNodes=0
“”
PLAN FRAGMENT 9
OUTPUT EXPRS:
" PARTITION: HASH_PARTITIONED: 6556: sub_target_id, 6564: staff_or_shop_code, 7324: depot_code"
“”
STREAM DATA SINK
EXCHANGE ID: 683
" HASH_PARTITIONED: 6556: sub_target_id, 6564: staff_or_shop_code"
“”
682:AGGREGATE (merge finalize)
" | output: sum(7340: sum), sum(7341: sum)"
" | group by: 6556: sub_target_id, 6564: staff_or_shop_code, 7324: depot_code"
|
681:EXCHANGE
“”
PLAN FRAGMENT 10
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 6217: sub_target_id
“”
STREAM DATA SINK
EXCHANGE ID: 681
" HASH_PARTITIONED: 6556: sub_target_id, 6564: staff_or_shop_code, 7324: depot_code"
“”
680:AGGREGATE (update serialize)
| STREAMING
" | output: sum(7339: if), sum(7338: cast)"
" | group by: 6556: sub_target_id, 6564: staff_or_shop_code, 7324: depot_code"
|
679:Project
| <slot 6556> : 6556: sub_target_id
| <slot 6564> : 6564: staff_or_shop_code
| <slot 7324> : 7324: depot_code
| <slot 7338> : 7338: cast
" | <slot 7339> : if(7321: expr = ‘2022-09-13’, 7338: cast, 0)"
|
678:HASH JOIN
| join op: LEFT OUTER JOIN (BROADCAST)
| hash predicates:
" | colocate: false, reason: "
| equal join conjunct: 6564: staff_or_shop_code = 7330: expr
" | other join predicates: (6548: target_dim_code = 0) OR ((6548: target_dim_code = 1) AND (((6560: model_type = 1) AND (find_in_set(7334: item_code, replace(replace(replace(6562: model_code, ‘[’, ‘’), ‘]’, ‘’), ’ ', ‘’)) > 0)) OR ((6560: model_type = 2) AND (find_in_set(7336: cast, replace(replace(replace(6562: model_code, ‘[’, ‘’), ‘]’, ‘’), ’ ', ‘’)) > 0)))), 6548: target_dim_code IN (0, 1), 7321: expr >= 6567: task_start_date, 7321: expr <= least(6568: task_end_date, ‘2022-09-13’), (7332: category_type = CAST(CAST(6550: target_object_code AS BIGINT) + 1 AS VARCHAR(1048576))) OR (6550: target_object_code = 2)"
|
|----677:EXCHANGE
|
624:Project
| <slot 6548> : 6548: target_dim_code
| <slot 6550> : 6550: target_object_code
| <slot 6556> : 6556: sub_target_id
| <slot 6560> : 6560: model_type
| <slot 6562> : 6562: model_code
| <slot 6564> : 6564: staff_or_shop_code
| <slot 6567> : 6567: task_start_date
| <slot 6568> : 6568: task_end_date
|
623:HASH JOIN
| join op: INNER JOIN (PARTITIONED)
| hash predicates:
" | colocate: false, reason: "
| equal join conjunct: 6217: sub_target_id = 6556: sub_target_id
|
|----622:EXCHANGE
|
619:EXCHANGE
“”
PLAN FRAGMENT 11
OUTPUT EXPRS:
PARTITION: RANDOM
“”
STREAM DATA SINK
EXCHANGE ID: 677
UNPARTITIONED
“”
625:UNION
|
|----676:EXCHANGE
|
650:EXCHANGE
“”
PLAN FRAGMENT 12
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 7167: biz_cust_id
“”
STREAM DATA SINK
EXCHANGE ID: 676
RANDOM
“”
675:Project
| <slot 6963> : 6963: quantity
| <slot 6976> : 6976: sku_code
| <slot 6986> : 6986: category_type
| <slot 7001> : 7001: item_code
| <slot 7156> : 7156: depot_code
| <slot 7317> : ‘’
| <slot 7318> : CAST(6945: dayno AS VARCHAR)
|
674:HASH JOIN
| join op: LEFT OUTER JOIN (PARTITIONED)
| hash predicates:
" | colocate: false, reason: "
| equal join conjunct: 7167: biz_cust_id = 7231: biz_cust_id
|
|----673:EXCHANGE
|
670:EXCHANGE
“”
PLAN FRAGMENT 13
OUTPUT EXPRS:
PARTITION: RANDOM
“”
STREAM DATA SINK
EXCHANGE ID: 673
HASH_PARTITIONED: 7231: biz_cust_id
“”
672:Project
| <slot 7231> : 7231: biz_cust_id
|
671:OlapScanNode
TABLE: dim_csc_business_customer
PREAGGREGATION: ON
PREDICATES: 7287: enabled = 1
partitions=1/33
rollup: dim_csc_business_customer
tabletRatio=8/8
" tabletList=18684147,18684151,18684155,18684159,18684163,18684167,18684171,18684175"
cardinality=42911
avgRowSize=12.0
numNodes=0
“”
PLAN FRAGMENT 14
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 6957: active_depot_id
“”
STREAM DATA SINK
EXCHANGE ID: 670
HASH_PARTITIONED: 7167: biz_cust_id
“”
669:Project
| <slot 6945> : 6945: dayno
| <slot 6963> : 6963: quantity
| <slot 6976> : 6976: sku_code
| <slot 6986> : 6986: category_type
| <slot 7001> : 7001: item_code
| <slot 7156> : 7156: depot_code
| <slot 7167> : 7167: biz_cust_id
|
668:HASH JOIN
| join op: LEFT OUTER JOIN (PARTITIONED)
| hash predicates:
" | colocate: false, reason: "
| equal join conjunct: 6957: active_depot_id = 7154: depot_id
|
|----667:EXCHANGE
|
664:EXCHANGE
“”
PLAN FRAGMENT 15
您好，不是explain。
请问您这个SQL是不是没有执行成功过？
架构是多少台FE，BE，磁盘是SSD还是HDD
看您那些表的数据量比较少，跑这个SQL需要多少秒？现在问题是什么？
enable_pipeline_engine = false， parallel_fragment_exec_instance_num设置为CPU核数，看效率会不会提高。
这个sql 跑成功了，需要10秒时间，架构具体的情况不太清楚，测试环境的数据
能跑出就搞个profile分析一下