You can refer to https://mp.weixin.qq.com/s/YNGdTbnG6iBjT1qtsK912w
to see if it can help -- 此致！Best Regards 陈明雨 Mingyu Chen Email: chenmin...@apache.org At 2021-09-01 12:39:11, "Thomas Cai" <thomas...@126.com> wrote: >Dear All, > > >I am having the SQL execution performance issue. The followings are the SQL >and the result of SQL explain. But I have no ideas on how to tune the SQL to >improve. > > >Expect to receive your suggestions. Thanks. > > >Best Regards, >Thomas > > >=========SQL START================ >SELECT >a.projectid, >a.ContractGUID, >c.CalID, >c.calperiod, >c.teamtype, >c.HKRatio, >d.MeasureMemberID >FROM >yj_contractinfo_doris a >JOIN yj_salestage_doris b ON b.CalID = '898' and a.ProjectID = >'26d614143fa73c16c4c8149455311e34' >-- AND a.projectid = b.projectid >AND b.contractguid = a.contractguid >JOIN yj_roompayment_doris c ON c.calid = '898' >-- AND a.projectid = c.projectid >AND c.contractguid = a.contractguid >AND c.ContractType = a.ContractType >-- AND b.CalID = c.CalID >AND c.CalPeriod = 'c709bdf3ff7da83e23e1a1fc1289b68e' >AND c.teamtype = 'ffab7099a45eb01583ea3b43faefad66' >AND c.CalID = '898' >JOIN yj_measure_limit d ON d.CalID = '898' >AND d.projectid = a.projectid >AND d.producttype = a.producttype >AND d.SaleStage = b.SaleStage >AND d.ContractType = c.ContractType >AND c.HKRatio >=d.min and >c.HKRatio<= d.max >===========SQL END===================================== >Explain SQL Result as following: >===========EXPLAIN START================== >PLAN FRAGMENT 0 > OUTPUT EXPRS:`a`.`projectid` | `a`.`ContractGUID` | `c`.`CalID` | > `c`.`calperiod` | `c`.`teamtype` | `c`.`HKRatio` | `d`.`MeasureMemberID` > PARTITION: UNPARTITIONED > > > RESULT SINK > > > 10:EXCHANGE > tuple ids: 1 2 3 0 > > >PLAN FRAGMENT 1 > OUTPUT EXPRS: > PARTITION: RANDOM > > > STREAM DATA SINK > EXCHANGE ID: 10 > UNPARTITIONED > > > 6:HASH JOIN > | join op: INNER JOIN (BROADCAST) > | hash predicates: > | colocate: false, reason: left hash join node can not do colocate > | equal join conjunct: `b`.`contractguid` = `a`.`contractguid` > | equal join conjunct: `c`.`contractguid` = `a`.`contractguid` > | equal join conjunct: `c`.`ContractType` = `a`.`ContractType` > | equal join conjunct: `d`.`projectid` = `a`.`projectid` > | equal join conjunct: `d`.`producttype` = `a`.`producttype` > | tuple ids: 1 2 3 0 > | > |----9:EXCHANGE > | tuple ids: 0 > | > 4:HASH JOIN > | join op: INNER JOIN (BROADCAST) > | hash predicates: > | colocate: false, reason: Node type not match > | equal join conjunct: `b`.`SaleStage` = `d`.`SaleStage` > | equal join conjunct: `c`.`ContractType` = `d`.`ContractType` > | other predicates: `c`.`HKRatio` >= `d`.`min`, `c`.`HKRatio` <= `d`.`max` > | tuple ids: 1 2 3 > | > |----8:EXCHANGE > | tuple ids: 3 > | > 2:CROSS JOIN > | cross join: > | predicates is NULL. | tuple ids: 1 2 > | > |----7:EXCHANGE > | tuple ids: 2 > | > 0:OlapScanNode > TABLE: yj_salestage_doris > PREAGGREGATION: OFF. Reason: No AggregateInfo > PREDICATES: `b`.`CalID` = 898.0 > partitions=2/3 > rollup: yj_salestage_doris > tabletRatio=24/24 > tabletList=11523,11527,11531,11535,11539,11543,11547,11551,11555,11559 ... > cardinality=1199149 > avgRowSize=21.937193 > numNodes=3 > tuple ids: 1 > > >PLAN FRAGMENT 2 > OUTPUT EXPRS: > PARTITION: RANDOM > > > STREAM DATA SINK > EXCHANGE ID: 09 > UNPARTITIONED > > > 5:OlapScanNode > TABLE: yj_contractinfo_doris > PREAGGREGATION: OFF. Reason: null > PREDICATES: `a`.`ProjectID` = '26d614143fa73c16c4c8149455311e34' > partitions=1/1 > rollup: yj_contractinfo_doris > tabletRatio=1/12 > tabletList=17970 > cardinality=6092 > avgRowSize=72.79629 > numNodes=3 > tuple ids: 0 > > >PLAN FRAGMENT 3 > OUTPUT EXPRS: > PARTITION: RANDOM > > > STREAM DATA SINK > EXCHANGE ID: 08 > UNPARTITIONED > > > 3:OlapScanNode > TABLE: yj_measure_limit > PREAGGREGATION: OFF. Reason: null > PREDICATES: `d`.`CalID` = 898.0 > partitions=1/3 > rollup: yj_measure_limit > tabletRatio=12/12 > tabletList=20658,20662,20666,20670,20674,20678,20682,20686,20690,20694 ... > cardinality=90938 > avgRowSize=5.368526 > numNodes=3 > tuple ids: 3 > > >PLAN FRAGMENT 4 > OUTPUT EXPRS: > PARTITION: RANDOM > > > STREAM DATA SINK > EXCHANGE ID: 07 > UNPARTITIONED > > > 1:OlapScanNode > TABLE: yj_roompayment_doris > PREAGGREGATION: OFF. Reason: null > PREDICATES: `c`.`calid` = 898.0, `c`.`CalPeriod` = > 'c709bdf3ff7da83e23e1a1fc1289b68e', `c`.`teamtype` = > 'ffab7099a45eb01583ea3b43faefad66', `c`.`CalID` = 898.0 > partitions=2/3 > rollup: yj_roompayment_doris > tabletRatio=24/24 > tabletList=13675,13679,13683,13687,13691,13695,13699,13703,13707,13711 ... > cardinality=503798 > avgRowSize=52.880646 > numNodes=3 > tuple ids: 2 >===========EXPLAIN END============================
