[Tech Request]: Optimize query for `statement_info` by `response_at`

[DanielZhangQD]

Is there an existing issue for the same tech request?

• I have checked the existing issues.

Does this tech request not affect user experience?

• This tech request doesn't affect user experience.

What would you like to be added ?

For cu calculation, the application needs to query `statement_info` by `response_at` to determine the actual CU consumed during a specific duration.
However, querying `statement_info` by `response_at` may be slow, so we need optimization in MO.

Why is this needed ?

No response

Additional information

No response

[aressu1985]

fixed

[DanielZhangQD]

The SQL is as following, no order by limit:

SELECT `statement_id`,any_value(`stats`) as `stats`,any_value(`duration`) as `duration`,any_value(`sql_source_type`) as `sql_source_type`,any_value(`account`) as `account`,any_value(`response_at`) as `response_at` FROM `system`.`statement_info` WHERE status != 'Running' and account != 'sys' and account != 'mocloud_mo_ob' and response_at >= '2024-09-25 23:58:00' and response_at < '2024-09-25 23:59:00' GROUP BY `statement_id`

[DanielZhangQD]

For the SQL with order by limit in cloud dev, it takes about 7s:

{"level":"WARN","time":"2024/09/26 00:04:12.766899 +0000","caller":"worker/controller.go:278","msg":"trace","elapsed":"6.875395539s","rows":1,"sql":"SELECT response_at FROM `system`.`statement_info` WHERE response_at > '2024-09-25 21:04:05.891' and response_at <= '2024-09-26 01:04:05.891' ORDER BY response_at desc LIMIT 1"}

[badboynt1]

这条query，只需要读很少的数据，算子内耗时很少。
目前主要瓶颈在于读取元数据，需要依赖shard功能来优化。