Case: Adjusting Partial Clustering Keys

Symptom

During the test at a site, if the following execution plan is performed, the customer expects that the performance can be improved and the result can be returned within 3s.

Optimization Analysis

The analysis shows that the performance bottleneck of this plan is the scan operation on the lfbank.f_ev_dp_kdpl_zhminx table. The scan condition of this table is as follows:

Try to change the lfbank.f_ev_dp_kdpl_zhminx table to a column-store table. Then, create the PCK (local clustering) in the yezdminc column, and set PARTIAL_CLUSTER_ROWS to 100000000. The execution plan after optimization is as follows:

• This method actually sacrifices the performance during data import to improve the query performance.
• The number of local sorting tuples is increased, and you need to increase the value of psort_work_mem to improve the sorting efficiency.