Help Center/ GaussDB(DWS)/ Performance Tuning/ Optimization Cases/ Case: Rewriting SQL and Deleting Subqueries (Case 1)
Updated on 2023-03-30 GMT+08:00
View PDF

Case: Rewriting SQL and Deleting Subqueries (Case 1)

Symptom

1
2
3
4
 
select 
    1,
    (select count(*) from customer_address_001 a4 where a4.ca_address_sk = a.ca_address_sk) as GZCS 
from customer_address_001 a;

The performance of the SQL statement is poor. It is found that the execution plan contains subplans. The operator that references the subplan results may need to be repeatedly invoked to obtain the subplan values. This means the subplan results need to be executed for many times. The details are as follows:

Optimization

The core of this optimization is to eliminate subqueries. Based on the SQL semantics, the SQL statement can be modified as follows:

1
2
3
4
5
6
 
select 
    1, 
    coalesce(a4.c1, 0)
from
    (select count(*) c1, a4.ca_address_sk from customer_address_001 a4 group by a4.ca_address_sk) a4
right join customer_address_001 a on a4.ca_address_sk = a.ca_address_sk;
Parent topic: Optimization Cases