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

DanielZhangQD commented 2 months ago

Is there an existing issue for the same bug?

[X] 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 commented 2 months ago

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

XuPeng-SH commented 2 months ago

depends #15708

gouhongshen commented 2 months ago

waiting

gouhongshen commented 1 month ago

在做 partition state 重构

gouhongshen commented 1 month ago

在做 reader

gouhongshen commented 1 month ago

在做 reader

gouhongshen commented 1 month ago

等待 reader 和 tombstone 做完

matrixorigin / matrixone