[Bug]: sum(mo_table_size) takes long time for the database with more than 20000 tables

[DanielZhangQD]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

1.2.1

Commit ID

v1.2.1-27b34c670-2024-06-28

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

• DB write_test_0424 has 22268 tables
• The following SQL takes more than 10 minutes:

mysql> select request_at,statement,statement_id,status,duration from system.statement_info where request_at > '2024-07-05 07:50:00' and statement_id = '019081e8-c056-7bf1-9c70-2b1ee674983d';
| 2024-07-05 08:00:32.854620 | explain analyze SELECT reldatabase, sum(mo_table_size (reldatabase, relname)) AS size FROM `mo_catalog`.`mo_tables` WHERE relkind in ('r','e','cluster','v') AND reldatabase in ('mo_catalog','gh_monitor','write_test_mo_mysql','mo_sample_data_tpch_sf10','saas_common','write_test_0424','system','system_metrics','information_schema','mo_sample_data_tpch_sf1','mysql','write_test','write_test_mo_mysql2','write_test_mo_mysql3','test') GROUP BY `reldatabase` | 019081e8-c056-7bf1-9c70-2b1ee674983d | Success | 600162520634 |

Expected Behavior

SQL succeeds in reasonable time

Steps to Reproduce

See detail in `Actual Behavior`

Additional information

Cloud prod env

[DanielZhangQD]

Result for explain analyze:
mo-20231112_data_query_result_moquery_019081e8-c056-7bf1-9c70-2b1ee674983d_20240705081033.csv

[XuPeng-SH]

depends #15708

[gouhongshen]

waiting

[gouhongshen]

在做 partition state 重构

[gouhongshen]

在做 reader