SQL Query Is Slow Because Partitions Are Not Pruned

Symptom

The SQL query is slow. The queried partitioned table contains 18.5 billion data records, and the query criteria do not contain the partition key.

1
2

SELECT passtime FROM table where passtime<'2020-02-19 15:28:14' and passtime>'2020-02-18 15:28:37' order by passtime desc limit 10; 
SELECT max(passtime) FROM table where passtime<'2020-02-19 15:28:14' and passtime>'2020-02-18 15:28:37'; 

For a column-store table, the partition key is createtime and the hash distribution key is motorvehicleid.

Possible Causes

The query criteria of slow SQL statements do not include the partition field. As a result, partitions are not pruned from the execution plan and the entire table is scanned, severely deteriorating performance.

Cause Analysis

1. Communicate with the customer. It is found that some services of the customer are slow, and all these services involve the same table tb_motor_vehicle.
2. Collect several typical slow SQL statements and print their execution plans. The execution plans show that during the execution of the two SQL statements, time is mostly spent on the partition scanning of the Partitioned CStore Scan on public.tb_motor_vehicle column-store table.

   

   

3. According to the customer, the partition key of the table is createtime. However, the query criteria of the involved SQL statements do not contain createtime. It can be confirmed that partitions are not pruned from the execution plan of slow SQL statements. As a result, the entire table with 18.5 billion data records is scanned, and the scanning performance is poor.

Handling Procedure

Add the partition query criteria to the slow SQL statements to prevent full table scanning.

1

SELECT passtime FROM tb_motor_vehicle WHERE createtime > '2020-02-19 00:00:00' AND createtime < '2020-02-20 00:00:00' AND passtime > '2020-02-19 00:00:00' AND passtime < '2020-02-20 00:00:00' ORDER BY passtime DESC LIMIT 10000;