Performance of Single-Table Point Query Is Poor

Symptom

The customer expects the result of single-table query to be returned within 1 second. However, the execution takes more than 10 seconds.

Possible Causes

This problem occurs because incorrect row- and column-store tables are selected. In this scenario, the row-store table and B-tree index should be used.

Cause Analysis

1. The execution information about the faulty SQL statements shows that most of the time is spent on CStore Scan.

   

2. The base table contains more than one billion data records. Incremental data is imported to the database in batches every night, and a small amount of data is cleaned. In the daytime, a large number of concurrent query operations are performed. The query does not involve table association, and the values of returned results are not large.

Handling Procedure

1. Adjust the table definition, change the table to a row-store table, and create a B-tree index. The principles for creating a B-tree index are as follows.
   1. Analyze the customer's SQL statements before creating a B-tree index.
   2. Do not create redundant indexes.
   3. Place the columns with better filtering performance in the front of the index.
   4. Include as many filter criteria as possible in the index.