Dear MingYu,
Great, it works. I did two changes as following: At first, I changed a big table as left table then found an error (unknown field a.producttype). I assumed it was related with partition. Then, I added [Shuffle] join type on the table with the error message. The result had the same number of records result as the initial SQL. There is an additional question: Can I believe that the records are totally the same with [Shuffle] join, or I have to verify the records of the new SQL? Thanks. Here is the modified SQL as following: ==================================== SELECT a.projectid, a.ContractGUID, c.CalID, c.calperiod, c.teamtype, c.HKRatio, d.MeasureMemberID, e.producttype, e.salestage, e.commisionstage, f.sequence FROM yj_roompayment_doris c JOIN yj_contractinfo_doris a ON a.projectid = c.projectid AND a.contractguid = c.contractguid AND a.ContractType = c.ContractType JOIN yj_salestage_doris b ON b.CalID = c.CalID AND b.projectid = c.projectid AND b.contractguid = c.contractguid JOIN yj_measure_limit d ON d.CalID = c.CalID AND d.projectid = c.projectid AND d.SaleStage = b.SaleStage AND d.producttype = a.producttype AND c.ContractType = d.ContractType AND c.HKRatio BETWEEN d.min AND d.max JOIN [Shuffle] yj_paramrules e ON c.projectid = e.projectid AND d.MeasureMemberID = e.MeasureMember AND d.producttype = e.producttype AND d.salestage = e.salestage AND a.ContractType = e.ContractType AND a.qsdate BETWEEN e.begindate AND e.enddate JOIN yj_dimensionmember f ON e.commisionstage = f.memberid WHERE c.CalID = '898' AND c.ProjectID = '26d614143fa73c16c4c8149455311e34' AND c.CalPeriod = 'c709bdf3ff7da83e23e1a1fc1289b68e' AND c.teamtype = 'ffab7099a45eb01583ea3b43faefad66' ====================================================== At 2021-09-01 13:06:57, "陈明雨" <morning...@163.com> wrote: >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============================
