Dear experts, how to optimize TiFlash queries?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 各位大佬,tiflash查询怎么优化?

| username: TiDBer_ZHcgATCp

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] v6.5.0
[Reproduction Path] Operations performed that led to the issue
User edited conditions above, backend concatenates SQL query

SELECT
  count(t.oneid)
FROM
  (
    SELECT
      DISTINCT A.ONEID
    FROM
      (
        SELECT
          B.ONEID
        FROM
          (
            SELECT
              DISTINCT A.ONEID
            FROM
              (
                SELECT
                  DISTINCT A.ONEID
                FROM
                  (
                    SELECT
                      B.ONEID
                    FROM
                      (
                        SELECT
                          DISTINCT A.ONEID
                        FROM
                          (
                            SELECT
                              DISTINCT A.ONEID
                            FROM
                              (
                                SELECT
                                  ONEID,
                                  count(DISTINCT event_id)
                                FROM
                                  ads_nuza_event_detl_df
                                WHERE
                                  1 = 1
                                  AND (
                                    attr_time1 BETWEEN '2021-01-01 00:00:00'
                                    AND '2021-12-31 23:59:59'
                                  )
                                  AND event_type = "order"
                                  AND obj_attr13 = "301"
                                GROUP BY
                                  ONEID
                                HAVING
                                  count(DISTINCT event_id) >= 1
                              ) A
                          ) A
                        UNION
                        ALL
                        SELECT
                          DISTINCT A.ONEID
                        FROM
                          (
                            SELECT
                              ONEID,
                              count(DISTINCT event_id)
                            FROM
                              ads_nuza_event_detl_df
                            WHERE
                              1 = 1
                              AND (
                                attr_time1 BETWEEN '2022-01-01 00:00:00'
                                AND '2022-12-31 23:59:59'
                              )
                              AND event_type = "order"
                              AND obj_attr13 = "301"
                            GROUP BY
                              ONEID
                            HAVING
                              count(DISTINCT event_id) >= 1
                          ) A
                      ) B
                    GROUP BY
                      B.ONEID
                    HAVING
                      COUNT(*) > 1
                  ) A
              ) A
            UNION
            ALL
            SELECT
              DISTINCT A.ONEID
            FROM
              (
                SELECT
                  ONEID,
                  count(DISTINCT event_id)
                FROM
                  ads_nuza_event_detl_df
                WHERE
                  1 = 1
                  AND (
                    attr_time1 BETWEEN '2023-01-01 00:00:00'
                    AND '2023-04-30 23:59:59'
                  )
                  AND event_type = "order"
                  AND obj_attr13 = "301"
                GROUP BY
                  ONEID
                HAVING
                  count(DISTINCT event_id) >= 1
              ) A
          ) B
        GROUP BY
          B.ONEID
        HAVING
          COUNT(*) > 1
      ) A
  ) t;

[Encountered Issues: Problem Phenomenon and Impact]
Query error, slow query performance
[Resource Configuration]


This is our cluster configuration
[Attachments: Screenshots/Logs/Monitoring]
Below is the query plan
Execution Plan.txt (31.2 KB)

| username: TiDBer_ZHcgATCp | Original post link

Some experts in the group mentioned that hashagg is not pushed down. Can any experts take a look at what’s going on?

| username: tidb菜鸟一只 | Original post link

At least post the execution plan. Just looking at the SQL doesn’t reveal much.

| username: TiDBer_ZHcgATCp | Original post link

At least post it, just looking at the SQL.

There is a txt document under the execution plan, you need to download and check it :grimacing:

| username: Billmay表妹 | Original post link

It looks like the resources are insufficient.


Official recommended configuration: TiDB 软件和硬件环境建议配置 | PingCAP 文档中心

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.