Low Query Efficiency

Solution

Perform the following procedure to locate the cause of this fault.

1. Run the analyze command to analyze the database.

   The analyze command updates data statistics information, such as data sizes and attributes in all tables. This is a lightweight command and can be executed frequently. If the query efficiency is improved or restored after the command execution, the autovacuum process does not function well and requires further analysis.

2. Check whether the query statement returns unnecessary information.

   For example, if the query statement to query all records in a table, and use result in only the first 10 records. If the ACS contains 50 record table. The query up is very fast. However, when the table contains records reaches 50000 to query efficiency will decrease.

   If an application requires only a part of data information but the query statement returns all information, add a LIMIT clause to the query statement to restrict the number of returned records. In this way, the database optimizer can optimize space and improve query efficiency.

3. Check whether the query statement still has a low response even when it is solely executed.

   Run the query statement when there are no or only a few other query requests in the database, and observe the query efficiency. If the efficiency is high, the previous issue is possibly caused by a heavily loaded host in the database system or an inefficient execution plan.

4. Check the same query statement repeatedly to check the query efficiency.

   One major cause of low query efficiency is that the required information is not cached in the memory or is replaced by other query requests because of insufficient memory resources.

   Run the same query statement repeatedly. If the query efficiency increases gradually, the previous issue might be caused by this reason.