版本：2.0.12
in子查询是查询jdbc catalog里面的一个表，数据量只有十几万，如下查询要8秒左右
select
'' as staDate,
count(a.ed_code) as exposurePointCount,
sum(a.play_dur) as playDuration,
sum(a.play_cnt) as playNumber
from
(
select
sdd.pt,
sdd.ed_code,
sum(sdd.play_dur) as play_dur,
sum(sdd.play_cnt) as play_cnt
from
xxx sdd
where
sdd.pt between '20240701'
and '20240708'
AND sdd.order_id in (
select
osdd.id
from
mysql_ad.ad.ad_order osdd
where
osdd.id = '2215296'
)
group by
sdd.pt,
sdd.ed_code
) a
limit
0, 1000
直接in，不去查询jdbc catalog只需要1.5秒左右就返回结果了，请问该怎么排查呢？
select
'' as staDate,
count(a.ed_code) as exposurePointCount,
sum(a.play_dur) as playDuration,
sum(a.play_cnt) as playNumber
from
(
select
sdd.pt,
sdd.ed_code,
sum(sdd.play_dur) as play_dur,
sum(sdd.play_cnt) as play_cnt
from
xxx sdd
where
sdd.pt between '20240701'
and '20240708'
AND sdd.order_id in ('2215296')
group by
sdd.pt,
sdd.ed_code
) a
limit
0, 1000
explain结果
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Explain String(Nereids Planner) |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| staDate[#136] |
| exposurePointCount[#137] |
| playDuration[#138] |
| playNumber[#139] |
| PARTITION: UNPARTITIONED |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| VRESULT SINK |
| |
| 1712:VAGGREGATE (merge finalize) |
| | output: count(partial_count(ed_code)[#130])[#133], sum(partial_sum(play_dur)[#131])[#134], sum(partial_sum(play_cnt)[#132])[#135] |
| | group by: |
| | cardinality=1 |
| | limit: 1000 |
| | projections: '', exposurePointCount[#133], playDuration[#134], playNumber[#135] |
| | project output tuple id: 9 |
| | |
| 1709:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: pt[#119], ed_code[#120] |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 1709 |
| UNPARTITIONED |
| |
| 1706:VAGGREGATE (update serialize) |
| | output: partial_count(ed_code[#127])[#130], partial_sum(play_dur[#128])[#131], partial_sum(play_cnt[#129])[#132] |
| | group by: |
| | cardinality=1 |
| | |
| 1700:VAGGREGATE (merge finalize) |
| | output: sum(partial_sum(play_dur)[#121])[#125], sum(partial_sum(play_cnt)[#122])[#126] |
| | group by: pt[#119], ed_code[#120] |
| | cardinality=296,883 |
| | projections: ed_code[#124], play_dur[#125], play_cnt[#126] |
| | project output tuple id: 6 |
| | |
| 1697:VEXCHANGE |
| offset: 0 |
| |
| PLAN FRAGMENT 2 |
| |
| PARTITION: HASH_PARTITIONED: order_id[#102] |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 1697 |
| HASH_PARTITIONED: pt[#119], ed_code[#120] |
| |
| 1694:VAGGREGATE (update serialize) |
| | STREAMING |
| | output: partial_sum(play_dur[#117])[#121], partial_sum(play_cnt[#118])[#122] |
| | group by: pt[#115], ed_code[#116] |
| | cardinality=296,883 |
| | |
| 1688:VHASH JOIN |
| | join op: LEFT SEMI JOIN(BUCKET_SHUFFLE)[] |
| | equal join conjunct: order_id[#102] = id[#0] |
| | runtime filters: RF000[in_or_bloom] <- id[#0](-1/0/2097152) |
| | cardinality=296,883 |
| | vec output tuple id: 3 |
| | vIntermediate tuple ids: 2 |
| | hash output slot ids: 100 101 108 109 |
| | |
| |----1685:VEXCHANGE |
| | offset: 0 |
| | |
| 1667:VOlapScanNode |
| TABLE: default_cluster:dw_app.xxx(xxx), PREAGGREGATION: OFF. Reason: No aggregate on scan. |
| PREDICATES: order_id[#102] = 2215296 AND pt[#100] <= '2024-07-08' AND pt[#100] >= '2024-07-01' |
| runtime filters: RF000[in_or_bloom] -> order_id[#102] |
| partitions=1/32 (p202407) |
| tablets=1/4, tabletList=524911 |
| cardinality=10402601, avgRowSize=0.0, numNodes=1 |
| pushAggOp=NONE |
| |
| PLAN FRAGMENT 3 |
| |
| PARTITION: RANDOM |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 1685 |
| BUCKET_SHFFULE_HASH_PARTITIONED: id[#0] |
| |
| 1676:VJdbcScanNode |
| TABLE: `ad`.`ad_order` |
| QUERY: SELECT `id` FROM `ad`.`ad_order` WHERE (`id` = 2215296) |
| PREDICATES: id[#0] = 2215296 |
+-----------------------------------------------------------------------------------------------------------------------------------------+
