agg模型表in子查询jdbc catalog很慢

Viewed 50

版本: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                                                                                                       |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 Answers
  1. 看下pipeline 和 新优化器打开了不
    方式:
    show variables like "experimental_enable_nereids_planner";
    show variables like "experimental_enable_pipeline_engine";

如果没打开,可以打开试试,如果没效果,可以取个plan 和 profile 我们看看的

  1. explain $sql
  2. profile 获取profile