同一条sql,升级Doris后,通过catalog查询sql变慢,原来只需要几秒钟,现在需要一分钟。
升级前版本:Doris2.1.5
升级后版本:Doris2.1.6
执行SQL:
select *,concat(WORKSHOP,LINE_BODY) as workshop_line
from in_orcl.MODULE_BI.TB_MPL_PRODUCTION_PLANNING_XLS
where concat(week_id,plan_version) = (select max(concat(week_id,plan_version))
from in_orcl.MODULE_BI.TB_MPL_PRODUCTION_PLANNING_XLS);
in_orcl.MODULE_BI.TB_MPL_PRODUCTION_PLANNING_XLS表数据量:66427条。
explain执行计划:
PLAN FRAGMENT 0
OUTPUT EXPRS:
P_CODE[#305]
WEEK_ID[#306]
ID[#307]
PLAN_VERSION[#308]
PLAN_CATEGORY[#309]
GLASS_TYPE[#310]
COMPONENT_FACTORY[#311]
WORKSHOP[#312]
LINE_BODY[#313]
SALES_AREA[#314]
SALES_TEAM[#315]
CLIENT_NAME[#316]
REQUIREMENT_TYPE[#317]
ORDER_NAME[#318]
PRODUCT_CODE[#319]
CONTRACT_NO[#320]
OMS_ORDER[#321]
REVIEW_NUMBER[#322]
ERP_AGREEMENT_NUMBER[#323]
COMPONENT_STANDARD[#324]
COMPONENT_TYPE[#325]
PRODUCT_TYPE[#326]
EXTENDED_VERSION[#327]
FRAME_TYPE[#328]
PROJECT_TOTAL[#329]
WIRE_BOX_TERMINAL[#330]
WIRE_BOX_BODY[#331]
RIBBON_TYPE[#332]
SUPERVISION[#333]
INSPECTION_BATCH[#334]
SHIPPING_PLACE[#335]
INSPECTION_CYCLE[#336]
VERSION[#337]
ILLUSTRATE[#338]
POWER_HIT[#339]
POWER_SALES[#340]
POWER_PRODUCTION[#341]
PRODUCTION_YIELD[#342]
QUALITY_SALES[#343]
QUALITY_PRODUCTION[#344]
GEAR[#345]
DEMAND_POWER[#346]
YEAR_MONTH[#347]
BEFORE_DAY[#348]
DAY1[#349]
DAY2[#350]
DAY3[#351]
DAY4[#352]
DAY5[#353]
DAY6[#354]
DAY7[#355]
DAY8[#356]
DAY9[#357]
DAY10[#358]
DAY11[#359]
DAY12[#360]
DAY13[#361]
DAY14[#362]
DAY15[#363]
DAY16[#364]
DAY17[#365]
DAY18[#366]
DAY19[#367]
DAY20[#368]
DAY21[#369]
DAY22[#370]
DAY23[#371]
DAY24[#372]
DAY25[#373]
DAY26[#374]
DAY27[#375]
DAY28[#376]
DAY29[#377]
DAY30[#378]
DAY31[#379]
workshop_line[#380]
PARTITION: UNPARTITIONED
""
HAS_COLO_PLAN_NODE: false
""
VRESULT SINK
MYSQL_PROTOCAL
""
8:VEXCHANGE
offset: 0
distribute expr lists:
""
PLAN FRAGMENT 1
""
" PARTITION: HASH_PARTITIONED: expr_concat(week_id, plan_version)[#195]"
""
HAS_COLO_PLAN_NODE: false
""
STREAM DATA SINK
EXCHANGE ID: 08
UNPARTITIONED
""
7:VHASH JOIN(321)
| join op: INNER JOIN(PARTITIONED)[]
" | equal join conjunct: (expr_concat(week_id, plan_version)[#195] = max(concat(week_id, plan_version))[#76])"
| cardinality=1
| vec output tuple id: 6
| output tuple id: 6
| vIntermediate tuple ids: 5
| hash output slot ids: 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227
" | final projections: P_CODE[#228], WEEK_ID[#229], ID[#230], PLAN_VERSION[#231], PLAN_CATEGORY[#232], GLASS_TYPE[#233], COMPONENT_FACTORY[#234], WORKSHOP[#235], LINE_BODY[#236], SALES_AREA[#237], SALES_TEAM[#238], CLIENT_NAME[#239], REQUIREMENT_TYPE[#240], ORDER_NAME[#241], PRODUCT_CODE[#242], CONTRACT_NO[#243], OMS_ORDER[#244], REVIEW_NUMBER[#245], ERP_AGREEMENT_NUMBER[#246], COMPONENT_STANDARD[#247], COMPONENT_TYPE[#248], PRODUCT_TYPE[#249], EXTENDED_VERSION[#250], FRAME_TYPE[#251], PROJECT_TOTAL[#252], WIRE_BOX_TERMINAL[#253], WIRE_BOX_BODY[#254], RIBBON_TYPE[#255], SUPERVISION[#256], INSPECTION_BATCH[#257], SHIPPING_PLACE[#258], INSPECTION_CYCLE[#259], VERSION[#260], ILLUSTRATE[#261], POWER_HIT[#262], POWER_SALES[#263], POWER_PRODUCTION[#264], PRODUCTION_YIELD[#265], QUALITY_SALES[#266], QUALITY_PRODUCTION[#267], GEAR[#268], DEMAND_POWER[#269], YEAR_MONTH[#270], BEFORE_DAY[#272], DAY1[#273], DAY2[#274], DAY3[#275], DAY4[#276], DAY5[#277], DAY6[#278], DAY7[#279], DAY8[#280], DAY9[#281], DAY10[#282], DAY11[#283], DAY12[#284], DAY13[#285], DAY14[#286], DAY15[#287], DAY16[#288], DAY17[#289], DAY18[#290], DAY19[#291], DAY20[#292], DAY21[#293], DAY22[#294], DAY23[#295], DAY24[#296], DAY25[#297], DAY26[#298], DAY27[#299], DAY28[#300], DAY29[#301], DAY30[#302], DAY31[#303], concat(WORKSHOP[#235], LINE_BODY[#236])"
| final project output tuple id: 6
" | distribute expr lists: expr_concat(week_id, plan_version)[#195]"
" | distribute expr lists: max(concat(week_id, plan_version))[#76]"
|
|----4:VEXCHANGE
| offset: 0
| distribute expr lists:
|
6:VEXCHANGE
offset: 0
distribute expr lists:
""
PLAN FRAGMENT 2
""
PARTITION: RANDOM
""
HAS_COLO_PLAN_NODE: false
""
STREAM DATA SINK
EXCHANGE ID: 06
" HASH_PARTITIONED: expr_concat(week_id, plan_version)[#195]"
""
5:VJdbcScanNode(296)
" TABLE: ""MODULE_BI"".""TB_MPL_PRODUCTION_PLANNING_XLS"""
" QUERY: SELECT ""P_CODE"", ""WEEK_ID"", ""ID"", ""PLAN_VERSION"", ""PLAN_CATEGORY"", ""GLASS_TYPE"", ""COMPONENT_FACTORY"", ""WORKSHOP"", ""LINE_BODY"", ""SALES_AREA"", ""SALES_TEAM"", ""CLIENT_NAME"", ""REQUIREMENT_TYPE"", ""ORDER_NAME"", ""PRODUCT_CODE"", ""CONTRACT_NO"", ""OMS_ORDER"", ""REVIEW_NUMBER"", ""ERP_AGREEMENT_NUMBER"", ""COMPONENT_STANDARD"", ""COMPONENT_TYPE"", ""PRODUCT_TYPE"", ""EXTENDED_VERSION"", ""FRAME_TYPE"", ""PROJECT_TOTAL"", ""WIRE_BOX_TERMINAL"", ""WIRE_BOX_BODY"", ""RIBBON_TYPE"", ""SUPERVISION"", ""INSPECTION_BATCH"", ""SHIPPING_PLACE"", ""INSPECTION_CYCLE"", ""VERSION"", ""ILLUSTRATE"", ""POWER_HIT"", ""POWER_SALES"", ""POWER_PRODUCTION"", ""PRODUCTION_YIELD"", ""QUALITY_SALES"", ""QUALITY_PRODUCTION"", ""GEAR"", ""DEMAND_POWER"", ""YEAR_MONTH"", ""BEFORE_DAY"", ""DAY1"", ""DAY2"", ""DAY3"", ""DAY4"", ""DAY5"", ""DAY6"", ""DAY7"", ""DAY8"", ""DAY9"", ""DAY10"", ""DAY11"", ""DAY12"", ""DAY13"", ""DAY14"", ""DAY15"", ""DAY16"", ""DAY17"", ""DAY18"", ""DAY19"", ""DAY20"", ""DAY21"", ""DAY22"", ""DAY23"", ""DAY24"", ""DAY25"", ""DAY26"", ""DAY27"", ""DAY28"", ""DAY29"", ""DAY30"", ""DAY31"" FROM ""MODULE_BI"".""TB_MPL_PRODUCTION_PLANNING_XLS"""
" final projections: P_CODE[#77], WEEK_ID[#78], ID[#79], PLAN_VERSION[#80], PLAN_CATEGORY[#81], GLASS_TYPE[#82], COMPONENT_FACTORY[#83], WORKSHOP[#84], LINE_BODY[#85], SALES_AREA[#86], SALES_TEAM[#87], CLIENT_NAME[#88], REQUIREMENT_TYPE[#89], ORDER_NAME[#90], PRODUCT_CODE[#91], CONTRACT_NO[#92], OMS_ORDER[#93], REVIEW_NUMBER[#94], ERP_AGREEMENT_NUMBER[#95], COMPONENT_STANDARD[#96], COMPONENT_TYPE[#97], PRODUCT_TYPE[#98], EXTENDED_VERSION[#99], FRAME_TYPE[#100], PROJECT_TOTAL[#101], WIRE_BOX_TERMINAL[#102], WIRE_BOX_BODY[#103], RIBBON_TYPE[#104], SUPERVISION[#105], INSPECTION_BATCH[#106], SHIPPING_PLACE[#107], INSPECTION_CYCLE[#108], VERSION[#109], ILLUSTRATE[#110], POWER_HIT[#111], POWER_SALES[#112], POWER_PRODUCTION[#113], PRODUCTION_YIELD[#114], QUALITY_SALES[#115], QUALITY_PRODUCTION[#116], GEAR[#117], DEMAND_POWER[#118], YEAR_MONTH[#119], concat(WEEK_ID[#78], PLAN_VERSION[#80]), BEFORE_DAY[#120], DAY1[#121], DAY2[#122], DAY3[#123], DAY4[#124], DAY5[#125], DAY6[#126], DAY7[#127], DAY8[#128], DAY9[#129], DAY10[#130], DAY11[#131], DAY12[#132], DAY13[#133], DAY14[#134], DAY15[#135], DAY16[#136], DAY17[#137], DAY18[#138], DAY19[#139], DAY20[#140], DAY21[#141], DAY22[#142], DAY23[#143], DAY24[#144], DAY25[#145], DAY26[#146], DAY27[#147], DAY28[#148], DAY29[#149], DAY30[#150], DAY31[#151]"
final project output tuple id: 4
""
PLAN FRAGMENT 3
""
PARTITION: UNPARTITIONED
""
HAS_COLO_PLAN_NODE: true
""
STREAM DATA SINK
EXCHANGE ID: 04
" HASH_PARTITIONED: max(concat(week_id, plan_version))[#76]"
""
3:VAGGREGATE (merge finalize)(315)
" | output: max(partial_max(concat(week_id, plan_version))[#75])[#76]"
| group by:
| cardinality=1
| distribute expr lists:
|
2:VEXCHANGE
offset: 0
distribute expr lists:
""
PLAN FRAGMENT 4
""
PARTITION: RANDOM
""
HAS_COLO_PLAN_NODE: false
""
STREAM DATA SINK
EXCHANGE ID: 02
UNPARTITIONED
""
1:VAGGREGATE (update serialize)(309)
" | output: partial_max(concat(WEEK_ID[#1], PLAN_VERSION[#3]))[#75]"
| group by:
| cardinality=1
| distribute expr lists:
|
0:VJdbcScanNode(303)
" TABLE: ""MODULE_BI"".""TB_MPL_PRODUCTION_PLANNING_XLS"""
" QUERY: SELECT ""WEEK_ID"", ""PLAN_VERSION"" FROM ""MODULE_BI"".""TB_MPL_PRODUCTION_PLANNING_XLS"""
""
""
Statistics
planed with unknown column statistics