This post is related to the statistics collection. Please see more details further down.
The query engine of LibrA uses a cost-based query optimizer. When the query plans of an SQL statement are generated, the optimizer uses statistics to evaluate the execution cost of each query plan and selects the one with the lowest cost. Therefore, the accuracy and reliability of statistics directly affect the performance of execution plans and queries.
After a large number of add, delete, and update operations on a database table, statistics collected based on the table data will expire. Therefore, you need to periodically collect statistics on a database table.
For example, to collect statistics on the sc_etcptjd table, run the following SQL statement:
ANALYZE sc_etcptjd;
Pay attention to the following when collecting statistics:
l Set the execution interval of statistics collection to once a month, and select a night period (from 20:00 to 6:00 the next day) when system load is low. Running the crontab command to periodically execute the GSQL script can be used as a reference.
l To collect statistics while reclaiming the storage space of a table, use the VACUUM FULL ANALYZE statement.
l Before collecting statistics, test and evaluate the operation time and ensure that the operation can be completed within a specified time window.
