When a database is used for a period of time, the table data increases as services grow, or the table data is frequently added, deleted, or modified. This results in bloating tables and inaccurate statistics, deteriorating database performance.
You are advised to periodically perform VACUUM FULL and ANALYZE on tables that are frequently added, deleted, or modified.
- By default, 100 out of 30,000 records of statistics are collected. When a large amount of data is involved, the SQL execution is unstable, which may be caused by a changed execution plan. In this case, the sampling rate needs to be adjusted for statistics. You can run set default_statistics_target to increase the sampling rate, which helps the optimizer generate the optimal plan.
- Perform ANALYZE again. For details, see "ANALYZE | ANALYSE" in the Developer Guide.
To test whether disk fragments affect database performance, use the following function:
select * from pgxc_get_stat_dirty_tables(30,100000);