Schedule Time 时间过长

Viewed 63

最近在测试 TPC-DS 100G 时候发现,2.1.0 比 2.0.5 版本慢一些,定位发现 profile 里面 “Schedule Time” 时间比较长,想问一下这个正常吗?

image.png

SQL,TPC-DS query 2:

WITH wscs AS (
    SELECT
        sold_date_sk,
        sales_price
    FROM
        (
            SELECT
                ws_sold_date_sk sold_date_sk,
                ws_ext_sales_price sales_price
            FROM
                web_sales
            UNION
            ALL
            SELECT
                cs_sold_date_sk sold_date_sk,
                cs_ext_sales_price sales_price
            FROM
                catalog_sales
        ) T0
),
wswscs AS (
    SELECT
        d_week_seq,
        sum(
            CASE
                WHEN (d_day_name = 'Sunday') THEN sales_price
                ELSE NULL
            END
        ) sun_sales,
        sum(
            CASE
                WHEN (d_day_name = 'Monday') THEN sales_price
                ELSE NULL
            END
        ) mon_sales,
        sum(
            CASE
                WHEN (d_day_name = 'Tuesday') THEN sales_price
                ELSE NULL
            END
        ) tue_sales,
        sum(
            CASE
                WHEN (d_day_name = 'Wednesday') THEN sales_price
                ELSE NULL
            END
        ) wed_sales,
        sum(
            CASE
                WHEN (d_day_name = 'Thursday') THEN sales_price
                ELSE NULL
            END
        ) thu_sales,
        sum(
            CASE
                WHEN (d_day_name = 'Friday') THEN sales_price
                ELSE NULL
            END
        ) fri_sales,
        sum(
            CASE
                WHEN (d_day_name = 'Saturday') THEN sales_price
                ELSE NULL
            END
        ) sat_sales
    FROM
        wscs,
        date_dim
    WHERE
        d_date_sk = sold_date_sk
    GROUP BY
        d_week_seq
)
SELECT
    d_week_seq1,
    round(sun_sales1 / sun_sales2, 2),
    round(mon_sales1 / mon_sales2, 2),
    round(tue_sales1 / tue_sales2, 2),
    round(wed_sales1 / wed_sales2, 2),
    round(thu_sales1 / thu_sales2, 2),
    round(fri_sales1 / fri_sales2, 2),
    round(sat_sales1 / sat_sales2, 2)
FROM
    (
        SELECT
            wswscs.d_week_seq d_week_seq1,
            sun_sales sun_sales1,
            mon_sales mon_sales1,
            tue_sales tue_sales1,
            wed_sales wed_sales1,
            thu_sales thu_sales1,
            fri_sales fri_sales1,
            sat_sales sat_sales1
        FROM
            wswscs,
            date_dim
        WHERE
            date_dim.d_week_seq = wswscs.d_week_seq
            AND d_year = 2001
    ) y,
    (
        SELECT
            wswscs.d_week_seq d_week_seq2,
            sun_sales sun_sales2,
            mon_sales mon_sales2,
            tue_sales tue_sales2,
            wed_sales wed_sales2,
            thu_sales thu_sales2,
            fri_sales fri_sales2,
            sat_sales sat_sales2
        FROM
            wswscs,
            date_dim
        WHERE
            date_dim.d_week_seq = wswscs.d_week_seq
            AND d_year = 2001 + 1
    ) z
WHERE
    d_week_seq1 = d_week_seq2 -53
ORDER BY
    d_week_seq1;
2 Answers
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String(Nereids Planner)                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PhysicalCteAnchor ( cteId=CTEId#1 )                                                                                                                                    |
| --PhysicalCteProducer ( cteId=CTEId#1 )                                                                                                                                |
| ----hashAgg[GLOBAL]                                                                                                                                                    |
| ------PhysicalDistribute[DistributionSpecHash]                                                                                                                         |
| --------hashAgg[LOCAL]                                                                                                                                                 |
| ----------PhysicalProject                                                                                                                                              |
| ------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_date_sk = wscs.sold_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ws_sold_date_sk,cs_sold_date_sk] |
| --------------PhysicalUnion                                                                                                                                            |
| ----------------PhysicalDistribute[DistributionSpecHash]                                                                                                               |
| ------------------PhysicalProject                                                                                                                                      |
| --------------------PhysicalOlapScan[web_sales] apply RFs: RF0                                                                                                         |
| ----------------PhysicalDistribute[DistributionSpecHash]                                                                                                               |
| ------------------PhysicalProject                                                                                                                                      |
| --------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0                                                                                                     |
| --------------PhysicalDistribute[DistributionSpecReplicated]                                                                                                           |
| ----------------PhysicalProject                                                                                                                                        |
| ------------------PhysicalOlapScan[date_dim]                                                                                                                           |
| --PhysicalResultSink                                                                                                                                                   |
| ----PhysicalQuickSort[MERGE_SORT]                                                                                                                                      |
| ------PhysicalDistribute[DistributionSpecGather]                                                                                                                       |
| --------PhysicalQuickSort[LOCAL_SORT]                                                                                                                                  |
| ----------PhysicalProject                                                                                                                                              |
| ------------hashJoin[INNER_JOIN] hashCondition=((expr_cast(d_week_seq1 as BIGINT) = expr_(d_week_seq2 - 53))) otherCondition=()                                        |
| --------------PhysicalDistribute[DistributionSpecHash]                                                                                                                 |
| ----------------PhysicalProject                                                                                                                                        |
| ------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_week_seq = d_week_seq1)) otherCondition=() build RFs:RF2 d_week_seq->[d_week_seq]                    |
| --------------------PhysicalDistribute[DistributionSpecHash]                                                                                                           |
| ----------------------PhysicalProject                                                                                                                                  |
| ------------------------PhysicalCteConsumer ( cteId=CTEId#1 ) apply RFs: RF2                                                                                           |
| --------------------PhysicalDistribute[DistributionSpecHash]                                                                                                           |
| ----------------------PhysicalProject                                                                                                                                  |
| ------------------------filter((date_dim.d_year = 2001))                                                                                                               |
| --------------------------PhysicalOlapScan[date_dim]                                                                                                                   |
| --------------PhysicalDistribute[DistributionSpecHash]                                                                                                                 |
| ----------------PhysicalProject                                                                                                                                        |
| ------------------hashJoin[INNER_JOIN] hashCondition=((date_dim.d_week_seq = d_week_seq2)) otherCondition=() build RFs:RF1 d_week_seq->[d_week_seq]                    |
| --------------------PhysicalDistribute[DistributionSpecHash]                                                                                                           |
| ----------------------PhysicalProject                                                                                                                                  |
| ------------------------PhysicalCteConsumer ( cteId=CTEId#1 ) apply RFs: RF1                                                                                           |
| --------------------PhysicalDistribute[DistributionSpecHash]                                                                                                           |
| ----------------------PhysicalProject                                                                                                                                  |
| ------------------------filter((date_dim.d_year = 2002))                                                                                                               |
| --------------------------PhysicalOlapScan[date_dim]                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

帮忙确定下这几个问题哈:

  1. 确定是否 full analyze 了 databases
  2. 是tpcds总时间都慢,还是单条query测试的,具体慢多少?