【详述】
创建表格导入数据后，查询两个相同SQL，只有是否添加【Limit 2000】的差别（实际结果条数只有1500行），经初步查看Profile中“table4”的 DataStreamSender耗时2s458ms，占比很大，还请大家帮忙看看是什么原因
【StarRocks版本】2.1.6
【集群规模】1 fe（follower）+1 be
【机器信息】20C/150G available
【并行度】32
【cbo是否开启】
cbo_cte_reuse—— false
cbo_enable_dp_join_reorder——true
cbo_enable_greedy_join_reorder —— true
cbo_enable_low_cardinality_optimize —— true
cbo_enable_replicated_join —— true
cbo_max_reorder_node_use_dp —— 10
cbo_max_reorder_node_use_exhaustive —— 4
cbo_use_correlated_join_estimate —— true
【附件】
目录：
- SQL
- 执行计划（不加Limit——3s19ms）
- 执行计划（加Limit——556ms）
- Profile（不加Limit——3s19ms）放评论区
- Profile（加Limit——556ms）放评论区
- SQL
SELECT B.VCEYD,
B.POSMC,
B.ETAERD,
LEFT(B.ETAERD,6) as CALMONTH,
A.GADJD,
A.TOMJG,
A.FUTRT,
A.FRSTA,
A.MUCSG,
A.PLAEG,
A.LHUTS,
A.LHUTS LHUTS_1,
CASE WHEN E.JHGCY <> ‘’ THEN ‘00’ ELSE E.JHGCY END JHGCY,
E.HBCUY,
B.MCJAG,
(B.DJBHY+B.USGFD)/B.MCJAG PRICASE,
(CASE WHEN A.GADJD = ‘ZRE’ THEN -1 ELSE 1 END)*(B.DJBHY+B.USGFD) SAMOUNT,
B.LEPGG,
CASE WHEN LEFT(E.YWTEB,2) IN (‘MS’,‘PT’,‘RE’,‘QC’) THEN ‘X’ ELSE ‘’ END JXCPBS,
CASE WHEN LEFT(E.YWTEB,2) IN (‘MS’,‘PT’,‘RE’,‘QC’) AND F2.EBIEWS IS NOT NULL THEN round(cast(F2.EBIEWS as decimal(16,3)),2)
WHEN LEFT(E.YWTEB,2) IN (‘MS’,‘PT’,‘RE’,‘QC’) AND F1.EBIEWS IS NOT NULL THEN round(cast(F1.EBIEWS as decimal(16,3)),2)
ELSE 1 END FDBL
FROM table1 A
INNER JOIN table2 B ON A.VCEYD = B.VCEYD
INNER JOIN table3 E ON A.VCEYD = E.VCEYD AND E.POSMC = ‘000000’
LEFT JOIN table4 G ON B.LEPGG = G._HYS_MKJSHGF
LEFT JOIN table5 F1 ON G._UYJHS_JHGWBF = F1.KIUHGGY and F1.SDRGTF = ‘00000000000000000000’
AND F1.SGFDFHB<=B.ETAERD AND F1.DTRYTR>= B.ETAERD
LEFT JOIN table5 F2 ON G._UYJHS_JHGWBF = F2.KIUHGGY AND G._BIC_SDRGTF = F2.SDRGTF and F2.SDRGTF <> ‘00000000000000000000’
AND F2.SGFDFHB<=B.ETAERD AND F2.DTRYTR>= B.ETAERD
WHERE B.MCJAG <> 0 and B.etaerd=‘20100428’
AND (A.GADJD <> ‘ZRE’ OR LEFT(E.FJIUG,3) NOT IN (‘TAG’,‘GUB’) ) limit 2000;
- 执行计划（不加Limit——3s19ms）
PLAN FRAGMENT 0
OUTPUT EXPRS:10: vceyd | 9: posmc | 11: etaerd | 39: left | 2: gadjd | 3: tomjg | 4: futrt | 5: frsta | 6: mucsg | 7: plaeg | 8: lhuts | 8: lhuts | 40: case | 19: hbcuy | 12: mcjag | 41: expr | 42: expr | 15: lepgg | 43: case | 44: case
PARTITION: UNPARTITIONED
RESULT SINK
20:EXCHANGE
PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 20
UNPARTITIONED
19:Project
| <slot 2> : 2: gadjd
| <slot 3> : 3: tomjg
| <slot 4> : 4: futrt
| <slot 5> : 5: frsta
| <slot 6> : 6: mucsg
| <slot 7> : 7: plaeg
| <slot 8> : 8: lhuts
| <slot 9> : 9: posmc
| <slot 10> : 10: vceyd
| <slot 11> : 11: etaerd
| <slot 12> : 12: mcjag
| <slot 15> : 15: lepgg
| <slot 19> : 19: hbcuy
| <slot 39> : left(CAST(11: etaerd AS VARCHAR), 6)
| <slot 40> : if(18: jhgcy != ‘’, ‘00’, 18: jhgcy)
| <slot 41> : CAST(CAST(13: djbhy AS DECIMAL64(18,2)) + CAST(14: usgfd AS DECIMAL64(18,2)) AS DECIMAL128(38,2)) / CAST(12: mcjag AS DECIMAL128(38,3))
| <slot 42> : CAST(if(2: gadjd = ‘ZRE’, -1, 1) AS DECIMAL64(18,0)) * CAST(13: djbhy AS DECIMAL64(18,2)) + CAST(14: usgfd AS DECIMAL64(18,2))
| <slot 43> : if(46: expr, ‘X’, ‘’)
| <slot 44> : CASE WHEN (46: expr) AND (38: EBIEWS IS NOT NULL) THEN round(CAST(CAST(38: EBIEWS AS DECIMAL64(16,3)) AS DOUBLE), 2) WHEN (46: expr) AND (31: EBIEWS IS NOT NULL) THEN round(CAST(CAST(31: EBIEWS AS DECIMAL64(16,3)) AS DOUBLE), 2) ELSE 1.0 END
| common expressions:
| <slot 45> : left(20: ywteb, 2)
| <slot 46> : 45: left IN (‘MS’, ‘PT’, ‘RE’, ‘QC’)
|
18:HASH JOIN
| join op: LEFT OUTER JOIN (BROADCAST)
| hash predicates:
| colocate: false, reason:
| equal join conjunct: 23: _uyjhs_jhgwbf = 35: KIUHGGY
| equal join conjunct: 24: _bic_sdrgtf = 36: SDRGTF
| other join predicates: CAST(33: SGFDFHB AS DATETIME) <= CAST(11: etaerd AS DATETIME), CAST(34: DTRYTR AS DATETIME) >= CAST(11: etaerd AS DATETIME)
|
|----17:EXCHANGE
|
15:Project
| <slot 2> : 2: gadjd
| <slot 3> : 3: tomjg
| <slot 4> : 4: futrt
| <slot 5> : 5: frsta
| <slot 6> : 6: mucsg
| <slot 7> : 7: plaeg
| <slot 8> : 8: lhuts
| <slot 9> : 9: posmc
| <slot 10> : 10: vceyd
| <slot 11> : 11: etaerd
| <slot 12> : 12: mcjag
| <slot 13> : 13: djbhy
| <slot 14> : 14: usgfd
| <slot 15> : 15: lepgg
| <slot 18> : 18: jhgcy
| <slot 19> : 19: hbcuy
| <slot 20> : 20: ywteb
| <slot 23> : 23: _uyjhs_jhgwbf
| <slot 24> : 24: _bic_sdrgtf
| <slot 31> : 31: EBIEWS
|
14:HASH JOIN
| join op: LEFT OUTER JOIN (BROADCAST)
| hash predicates:
| colocate: false, reason:
| equal join conjunct: 23: _uyjhs_jhgwbf = 28: KIUHGGY
| other join predicates: CAST(26: SGFDFHB AS DATETIME) <= CAST(11: etaerd AS DATETIME), CAST(27: DTRYTR AS DATETIME) >= CAST(11: etaerd AS DATETIME)
|
|----13:EXCHANGE
|
10:Project
| <slot 2> : 2: gadjd
| <slot 3> : 3: tomjg
| <slot 4> : 4: futrt
| <slot 5> : 5: frsta
| <slot 6> : 6: mucsg
| <slot 7> : 7: plaeg
| <slot 8> : 8: lhuts
| <slot 9> : 9: posmc
| <slot 10> : 10: vceyd
| <slot 11> : 11: etaerd
| <slot 12> : 12: mcjag
| <slot 13> : 13: djbhy
| <slot 14> : 14: usgfd
| <slot 15> : 15: lepgg
| <slot 18> : 18: jhgcy
| <slot 19> : 19: hbcuy
| <slot 20> : 20: ywteb
| <slot 23> : 23: _uyjhs_jhgwbf
| <slot 24> : 24: _bic_sdrgtf
|
9:HASH JOIN
| join op: LEFT OUTER JOIN (BROADCAST)
| hash predicates:
| colocate: false, reason:
| equal join conjunct: 15: lepgg = 22: _hys_mkjshgf
|
|----8:EXCHANGE
|
6:Project
| <slot 2> : 2: gadjd
| <slot 3> : 3: tomjg
| <slot 4> : 4: futrt
| <slot 5> : 5: frsta
| <slot 6> : 6: mucsg
| <slot 7> : 7: plaeg
| <slot 8> : 8: lhuts
| <slot 9> : 9: posmc
| <slot 10> : 10: vceyd
| <slot 11> : 11: etaerd
| <slot 12> : 12: mcjag
| <slot 13> : 13: djbhy
| <slot 14> : 14: usgfd
| <slot 15> : 15: lepgg
| <slot 18> : 18: jhgcy
| <slot 19> : 19: hbcuy
| <slot 20> : 20: ywteb
|
5:HASH JOIN
| join op: INNER JOIN (COLOCATE)
| hash predicates:
| colocate: true
| equal join conjunct: 1: vceyd = 17: vceyd
| other join predicates: (2: gadjd != ‘ZRE’) OR (left(21: fjiug, 3) NOT IN (‘TAG’, ‘GUB’))
|
|----4:Project
| | <slot 17> : 17: vceyd
| | <slot 18> : 18: jhgcy
| | <slot 19> : 19: hbcuy
| | <slot 20> : 20: ywteb
| | <slot 21> : 21: fjiug
| |
| 3:OlapScanNode
| TABLE: table3
| PREAGGREGATION: ON
| PREDICATES: 16: posmc = ‘000000’
| partitions=1/1
| rollup: table3
| tabletRatio=18/18
| tabletList=14445,14449,14453,14457,14461,14465,14469,14473,14477,14481 …
| cardinality=72164999
| avgRowSize=6.0
| numNodes=0
|
2:HASH JOIN
| join op: INNER JOIN (COLOCATE)
| hash predicates:
| colocate: true
| equal join conjunct: 1: vceyd = 10: vceyd
|
|----1:OlapScanNode
| TABLE: table2
| PREAGGREGATION: ON
| PREDICATES: 12: mcjag != 0, 11: etaerd = ‘2010-04-28’
| partitions=1/1
| rollup: table2
| tabletRatio=18/18
| tabletList=14370,14374,14378,14382,14386,14390,14394,14398,14402,14406 …
| cardinality=22544143
| avgRowSize=7.0
| numNodes=0
|
0:OlapScanNode
TABLE: table1
PREAGGREGATION: ON
partitions=1/1
rollup: table1
tabletRatio=18/18
tabletList=14295,14299,14303,14307,14311,14315,14319,14323,14327,14331 …
cardinality=36239136
avgRowSize=37.820656
numNodes=0
PLAN FRAGMENT 2
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 17
UNPARTITIONED
16:OlapScanNode
TABLE: table5
PREAGGREGATION: ON
PREDICATES: 36: SDRGTF != ‘00000000000000000000’
partitions=1/1
rollup: table5
tabletRatio=1/1
tabletList=14280
cardinality=68
avgRowSize=5.0
numNodes=0
PLAN FRAGMENT 3
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 13
UNPARTITIONED
12:Project
| <slot 26> : 26: SGFDFHB
| <slot 27> : 27: DTRYTR
| <slot 28> : 28: KIUHGGY
| <slot 31> : 31: EBIEWS
|
11:OlapScanNode
TABLE: table5
PREAGGREGATION: ON
PREDICATES: 29: SDRGTF = ‘00000000000000000000’
partitions=1/1
rollup: table5
tabletRatio=1/1
tabletList=14280
cardinality=136
avgRowSize=5.0
numNodes=0
PLAN FRAGMENT 4
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 08
UNPARTITIONED
7:OlapScanNode
TABLE: table4
PREAGGREGATION: ON
partitions=1/1
rollup: table4
tabletRatio=1/1
tabletList=14287
cardinality=1461163
avgRowSize=3.0
numNodes=0
- 执行计划（加Limit——556ms）
PLAN FRAGMENT 0
OUTPUT EXPRS:10: vceyd | 9: posmc | 11: etaerd | 39: left | 2: gadjd | 3: tomjg | 4: futrt | 5: frsta | 6: mucsg | 7: plaeg | 8: lhuts | 8: lhuts | 40: case | 19: hbcuy | 12: mcjag | 41: expr | 42: expr | 15: lepgg | 43: case | 44: case
PARTITION: UNPARTITIONED
RESULT SINK
21:EXCHANGE
limit: 2000
PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 23: _uyjhs_jhgwbf, 24: _bic_sdrgtf
STREAM DATA SINK
EXCHANGE ID: 21
UNPARTITIONED
20:Project
| <slot 2> : 2: gadjd
| <slot 3> : 3: tomjg
| <slot 4> : 4: futrt
| <slot 5> : 5: frsta
| <slot 6> : 6: mucsg
| <slot 7> : 7: plaeg
| <slot 8> : 8: lhuts
| <slot 9> : 9: posmc
| <slot 10> : 10: vceyd
| <slot 11> : 11: etaerd
| <slot 12> : 12: mcjag
| <slot 15> : 15: lepgg
| <slot 19> : 19: hbcuy
| <slot 39> : left(CAST(11: etaerd AS VARCHAR), 6)
| <slot 40> : if(18: jhgcy != ‘’, ‘00’, 18: jhgcy)
| <slot 41> : CAST(CAST(13: djbhy AS DECIMAL64(18,2)) + CAST(14: usgfd AS DECIMAL64(18,2)) AS DECIMAL128(38,2)) / CAST(12: mcjag AS DECIMAL128(38,3))
| <slot 42> : CAST(if(2: gadjd = ‘ZRE’, -1, 1) AS DECIMAL64(18,0)) * CAST(13: djbhy AS DECIMAL64(18,2)) + CAST(14: usgfd AS DECIMAL64(18,2))
| <slot 43> : if(46: expr, ‘X’, ‘’)
| <slot 44> : CASE WHEN (46: expr) AND (38: EBIEWS IS NOT NULL) THEN round(CAST(CAST(38: EBIEWS AS DECIMAL64(16,3)) AS DOUBLE), 2) WHEN (46: expr) AND (31: EBIEWS IS NOT NULL) THEN round(CAST(CAST(31: EBIEWS AS DECIMAL64(16,3)) AS DOUBLE), 2) ELSE 1.0 END
| common expressions:
| <slot 45> : left(20: ywteb, 2)
| <slot 46> : 45: left IN (‘MS’, ‘PT’, ‘RE’, ‘QC’)
| limit: 2000
|
19:HASH JOIN
| join op: LEFT OUTER JOIN (PARTITIONED)
| hash predicates:
| colocate: false, reason:
| equal join conjunct: 23: _uyjhs_jhgwbf = 35: KIUHGGY
| equal join conjunct: 24: _bic_sdrgtf = 36: SDRGTF
| other join predicates: CAST(33: SGFDFHB AS DATETIME) <= CAST(11: etaerd AS DATETIME), CAST(34: DTRYTR AS DATETIME) >= CAST(11: etaerd AS DATETIME)
| limit: 2000
|
|----18:EXCHANGE
|
16:EXCHANGE
limit: 2000
PLAN FRAGMENT 2
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 18
HASH_PARTITIONED: 35: KIUHGGY, 36: SDRGTF
17:OlapScanNode
TABLE: table5
PREAGGREGATION: ON
PREDICATES: 36: SDRGTF != ‘00000000000000000000’
partitions=1/1
rollup: table5
tabletRatio=1/1
tabletList=14280
cardinality=68
avgRowSize=5.0
numNodes=0
PLAN FRAGMENT 3
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 16
HASH_PARTITIONED: 23: _uyjhs_jhgwbf, 24: _bic_sdrgtf
15:Project
| <slot 2> : 2: gadjd
| <slot 3> : 3: tomjg
| <slot 4> : 4: futrt
| <slot 5> : 5: frsta
| <slot 6> : 6: mucsg
| <slot 7> : 7: plaeg
| <slot 8> : 8: lhuts
| <slot 9> : 9: posmc
| <slot 10> : 10: vceyd
| <slot 11> : 11: etaerd
| <slot 12> : 12: mcjag
| <slot 13> : 13: djbhy
| <slot 14> : 14: usgfd
| <slot 15> : 15: lepgg
| <slot 18> : 18: jhgcy
| <slot 19> : 19: hbcuy
| <slot 20> : 20: ywteb
| <slot 23> : 23: _uyjhs_jhgwbf
| <slot 24> : 24: _bic_sdrgtf
| <slot 31> : 31: EBIEWS
| limit: 2000
|
14:HASH JOIN
| join op: LEFT OUTER JOIN (BROADCAST)
| hash predicates:
| colocate: false, reason:
| equal join conjunct: 23: _uyjhs_jhgwbf = 28: KIUHGGY
| other join predicates: CAST(26: SGFDFHB AS DATETIME) <= CAST(11: etaerd AS DATETIME), CAST(27: DTRYTR AS DATETIME) >= CAST(11: etaerd AS DATETIME)
| limit: 2000
|
|----13:EXCHANGE
|
10:Project
| <slot 2> : 2: gadjd
| <slot 3> : 3: tomjg
| <slot 4> : 4: futrt
| <slot 5> : 5: frsta
| <slot 6> : 6: mucsg
| <slot 7> : 7: plaeg
| <slot 8> : 8: lhuts
| <slot 9> : 9: posmc
| <slot 10> : 10: vceyd
| <slot 11> : 11: etaerd
| <slot 12> : 12: mcjag
| <slot 13> : 13: djbhy
| <slot 14> : 14: usgfd
| <slot 15> : 15: lepgg
| <slot 18> : 18: jhgcy
| <slot 19> : 19: hbcuy
| <slot 20> : 20: ywteb
| <slot 23> : 23: _uyjhs_jhgwbf
| <slot 24> : 24: _bic_sdrgtf
| limit: 2000
|
9:HASH JOIN
| join op: RIGHT OUTER JOIN (BUCKET_SHUFFLE)
| hash predicates:
| colocate: false, reason:
| equal join conjunct: 22: _hys_mkjshgf = 15: lepgg
| limit: 2000
|
|----8:EXCHANGE
| limit: 2000
|
0:OlapScanNode
TABLE: table4
PREAGGREGATION: ON
partitions=1/1
rollup: table4
tabletRatio=1/1
tabletList=14287
cardinality=1461163
avgRowSize=3.0
numNodes=0
PLAN FRAGMENT 4
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 13
UNPARTITIONED
12:Project
| <slot 26> : 26: SGFDFHB
| <slot 27> : 27: DTRYTR
| <slot 28> : 28: KIUHGGY
| <slot 31> : 31: EBIEWS
|
11:OlapScanNode
TABLE: table5
PREAGGREGATION: ON
PREDICATES: 29: SDRGTF = ‘00000000000000000000’
partitions=1/1
rollup: table5
tabletRatio=1/1
tabletList=14280
cardinality=136
avgRowSize=5.0
numNodes=0
PLAN FRAGMENT 5
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 08
BUCKET_SHFFULE_HASH_PARTITIONED: 15: lepgg
7:Project
| <slot 2> : 2: gadjd
| <slot 3> : 3: tomjg
| <slot 4> : 4: futrt
| <slot 5> : 5: frsta
| <slot 6> : 6: mucsg
| <slot 7> : 7: plaeg
| <slot 8> : 8: lhuts
| <slot 9> : 9: posmc
| <slot 10> : 10: vceyd
| <slot 11> : 11: etaerd
| <slot 12> : 12: mcjag
| <slot 13> : 13: djbhy
| <slot 14> : 14: usgfd
| <slot 15> : 15: lepgg
| <slot 18> : 18: jhgcy
| <slot 19> : 19: hbcuy
| <slot 20> : 20: ywteb
| limit: 2000
|
6:HASH JOIN
| join op: INNER JOIN (COLOCATE)
| hash predicates:
| colocate: true
| equal join conjunct: 1: vceyd = 17: vceyd
| other join predicates: (2: gadjd != ‘ZRE’) OR (left(21: fjiug, 3) NOT IN (‘TAG’, ‘GUB’))
| limit: 2000
|
|----5:Project
| | <slot 17> : 17: vceyd
| | <slot 18> : 18: jhgcy
| | <slot 19> : 19: hbcuy
| | <slot 20> : 20: ywteb
| | <slot 21> : 21: fjiug
| |
| 4:OlapScanNode
| TABLE: table3
| PREAGGREGATION: ON
| PREDICATES: 16: posmc = ‘000000’
| partitions=1/1
| rollup: table3
| tabletRatio=18/18
| tabletList=14445,14449,14453,14457,14461,14465,14469,14473,14477,14481 …
| cardinality=72164999
| avgRowSize=6.0
| numNodes=0
|
3:HASH JOIN
| join op: INNER JOIN (COLOCATE)
| hash predicates:
| colocate: true
| equal join conjunct: 1: vceyd = 10: vceyd
|
|----2:OlapScanNode
| TABLE: table2
| PREAGGREGATION: ON
| PREDICATES: 12: mcjag != 0, 11: etaerd = ‘2010-04-28’
| partitions=1/1
| rollup: table2
| tabletRatio=18/18
| tabletList=14370,14374,14378,14382,14386,14390,14394,14398,14402,14406 …
| cardinality=22544143
| avgRowSize=7.0
| numNodes=0
|
1:OlapScanNode
TABLE: table1
PREAGGREGATION: ON
partitions=1/1
rollup: table1
tabletRatio=18/18
tabletList=14295,14299,14303,14307,14311,14315,14319,14323,14327,14331 …
cardinality=36239136
avgRowSize=37.820656
numNodes=0