Help Center/ Data Warehouse Service/ Troubleshooting/ Cluster Performance/ Performance of Single-Table Point Query Is Poor
Updated on 2025-04-10 GMT+08:00
View PDF

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

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. Create indexes on columns that are frequently searched. This can improve the search efficiency.
  2. Do not define redundant or duplicate indexes.
  3. Place the columns with better filtering performance in the front of the index.
  4. Create indexes on columns frequently following keywords such as ORDER BY, GROUP BY, and DISTINCT.
  5. Create indexes on columns that usually use the WHERE clause. This improves the efficiency of condition evaluations.
Parent topic: Cluster Performance